Re: synchronization userid password

BELAEN Lydia wrote:
>
> We want to synchronize userid and password between our test and
> production environment.
>
> We also want to check the number of retries to login and to keep track
> of last update date of his password .
> Does anyone have experience in this area ?
>
> Lydia.

For your first question you could do something like this (this assumes that the same users exist on both machines, just with different passwords. If you want to create users that don't already exist, that would be a little more complicated):

• on instance A (The one with the passwords as you want them to be) create a SQL file that looks something like this (lets call it getpwds.sql): set echo off set feedback off spool passwords.sql select 'ALTER USER ' || UserName || ' IDENTIFIED BY PASSWORDS ''' || Password || ''';' from SYS.DBA_USERS;
• whenever you want to update the passwords on instance B, you will need to run the getpwds.sql file on instance A (logged on as a user with DBA privleges), copy the passwords.sql file that is created to instance B. Then you need to run the passwords.sql file on instance B (again logged in as a user with DBA privleges.
• You might want to add: WHERE Username = 'TESTACCOUNT' to the getpwds.sql script until you get the scripts working just right, so only one test account (instead of all accounts) will be affected.

  ---

For your second question, the only way I know of doing this is with auditing. You will need to do the following to enable auditing on your machine: - in your INITxxx.ORA file add (or change) the audit_trail= line to read

	audit_trail=db
	or audit_trail=true

- then issue the following SQL statements (as a DBA user):
	AUDIT CONNECT WHENEVER NOT SUCCESSFUL;    -- That will keep track of failed logins
	AUDIT USER BY ACCESS WHENEVER SUCCESSFUL; -- That will keep track of CREATE, ALTER, and 

• You might also want to issue the following statements: AUDIT SYSTEM AUDIT BY ACCESS WHENEVER SUCCESSFUL; --This will log changes to system auditing settings. AUDIT DELETE ON SYS.AUD$ BY ACCESS; --This will keep track of deletions (which can only be made by DBA's) from the audit table.

You can then use the SYS.DBA_AUDIT_TRAIL view to look at the audit trail that is generated. You'll probably want to create some scripts to pull out just the details that you are looking for.

-- 
-------------------------------------------------
Ken Johnson -  Senior Technical Consultant
Tapestry Computing, Inc. http://www.tapestry.com