ФОРУМ за света на PTC CREO CAD/CAM/CAE софтуерните решения


www.soft.cadcamcae.bg - Софтуерни решения за машиностроене;

www.forum.cadcamcae.bg - Форум;

www.blog.cadcamcae.bg - Блог;

http://e-shop.cadcamcae.bg - Електронен CAD магазин;



3D инженерни мишки;


ФОРУМ за света на PTC CREO CAD/CAM/CAE софтуерните решения

ФОРУМ: Част от проекта на списание CAD ДАЙДЖЕСТ - www.cadcamcae.bg;

CAD ДАЙДЖЕСТ . Едно онлайн издание за CAD/CAM/CAE технологии и машиностроене

ИндексИндекс  PortalPortal  КалендарКалендар  ГалерияГалерия  Въпроси/ОтговориВъпроси/Отговори  ТърсенеТърсене  ПотребителиПотребители  Потребителски групиПотребителски групи  Регистрирайте сеРегистрирайте се  ВходВход  
Искам да влизам автоматично с всяко посещение: 
:: Забравих си паролата!
Social bookmarking
Social bookmarking digg  Social bookmarking delicious  Social bookmarking reddit  Social bookmarking stumbleupon  Social bookmarking slashdot  Social bookmarking yahoo  Social bookmarking google  Social bookmarking blogmarks  Social bookmarking live      

Bookmark and share the address of ФОРУМ за света на PTC CREO CAD/CAM/CAE софтуерните решения on your social bookmarking website
Новини от twitter.
Latest topics

Display results as :
Rechercher Advanced Search
Споделете с приятел.
Споделете форума В,,, с,,,в,,,е,,,т,,,а,,, н,,,а,,, CAD/CAM/CAE/PLM i Вашия форум за социално споделянеBookmark and Share
Юли 2018

Share | 

 Intralink SQL: Monitoring User Passwords Part 1

Go down 

Male Брой мнения : 796
Местожителство : София, България
Job/hobbies : CAD/CAM специалист
Reputation : 3
Points : 5897
Registration date : 27.03.2008

ПисанеЗаглавие: Intralink SQL: Monitoring User Passwords Part 1   Вто 13 Май - 14:59:41

Intralink SQL: Monitoring User Passwords Part 1

PTC provides very little control over passwords, and unfortunately expiration dates cannot be set from within the GUI. However, it can be accomplished in the oracle backend using triggers. Not with C based Intralink triggers (you can't trigger on a password change), but with PL/SQL based oracle triggers.

The basic concept is to setup a trigger that monitors changes to the password column of the user table (PDM_USER). Generally, the trigger writes the username, date, and optionally the "encrypted" password to a separate tracking table.

In the simplest application, an sql query on the tracking table would report users who have not changed their password within the required period. A cron job or scheduled task can use this query to send nag messages to users. If desired, this process could update the user's password field in the PDM_USER table to a value that would effectively disable the account.

A more complex trigger can prohibit password reuse, disallow known bad passwords, purge old "good" passwords from the database, and potentially send emails itself.

In this two part example, I will show you how to setup a trigger that tracks passwords, prohibits "known bad passwords", and prohibits some password reuse. 180 days is used here as a threshold, but any numeric values can be used, just be consistent.

Tracking Table:

You need a table to store the user/password/date information. The following code will create a table using datatypes from the PDM_USER table itself in the form of a query.

create table pdm.custom_pwchange_track unrecoverable asselectuserid as pwcid,userid,userpassword,modifiedby,modifiedonfrompdm.PDM_USERwhereuserid=-1;
Even though the query doesn't return any values, it does provide a table structure with which to build a table. By matching datatypes with the PDM_USER table, we don't need to worry about the data size of the columns in the new table because they will be the same.

The table columns and sizes can be verified with 'describe':

describe pdm.custom_pwchange_track;
Here are a few examples of queries to get useful info:

-- set column widths (for two queries below)--column UserName format a15column UserPassword format a15column ModifiedBy format a12column ModDateTime format a20column lastchange format a20-- report password history, all users--select PWCID, a.userid, username, a.userpassword, a.MODIFIEDBY,to_char(a.modifiedon,'DD-MM-YY HH:MI:SS') moddatetimefrom pdm.custom_pwchange_track a, pdm.pdm_user bwhere a.userid=b.userid;-- report users who have not changed their password in 180 days--select username,to_char(max(a.modifiedon),'YY-MM-DD HH:MI:SS') lastchangefrom pdm.custom_pwchange_track a, pdm.pdm_user bwhere a.userid=b.useridgroup by username having max(a.modifiedon)<sysdate-180;
Bad Password Table:

The bad password table concept is a little tricky to implement. Since the password is encrypted, the bad encrypted passwords would need to be stored for each user. A "new user" script or process, could go through the motions of changing the new user's password to a list of bad passwords, before changing to the password given to the user.

With the tracking table and trigger in place, the bad passwords can be pulled from the tracking table and inserted into the bad password table. As I said, it's a little tricky, but it can be done without too much effort. Might make for a big table if you get too strict about "bad" passwords.

Code to create the table, again, based on a query:

create table pdm.custom_bad_passwords unrecoverable asselectuserid as bpid,userid,userpasswordfrompdm.PDM_USERwhereuserid=-1;
Commands to verify the table:

describe pdm.custom_bad_passwords;select * from pdm.custom_bad_passwords;-- Take values from old password table for user 'fred' and insert-- into bad password table--insert into pdm.custom_bad_passwords (bpid, userid, userpassword)select pwcid, userid, userpassword from pdm.custom_pwchange_trackwhere userid=(select userid from pdm.pdm_user where username='fred');
Sequence for unique IDs:

Typically a well designed database uses sequences to generate unique integer ids. This allows differentiation between each record in a table. In this case, it is not absolutely necessary, because we are not linking multiple tables together, but it's generally a good thing and very easy to do.

Code to create the sequence:

create sequence pdm.custom_pwchange_track_seqincrement by 1start with 1;
Commands to verify the sequence:

Върнете се в началото Go down
Вижте профила на потребителя http://cadcamcae-bg.top-forum.net
Intralink SQL: Monitoring User Passwords Part 1
Върнете се в началото 
Страница 1 от 1

Permissions in this forum:Не Можете да отговаряте на темите
ФОРУМ за света на PTC CREO CAD/CAM/CAE софтуерните решения :: PTC CREO 4.0: СОФТУЕР ЗА МАШИНОСТРОЕНЕ :: CREO 4.0: Моделиер от висок клас (бивш Pro/ENGINEER Wildfire) :: PDM/PLM - Pro/INTRALINK; Windchill, ProductPoint-
Идете на: