Oracle DBA Daily Experiences

One of the ways to extend the password of an expiring account is to change the profile of the user.

– show the current user profile which shows the password expires 180 days
SQL> select * FROM dbA_profiles where profile = ‘APPLICATIONS’;

PROFILE RESOURCE_NAME RESOURCE LIMIT
—————————— ——————————– ——– —————————————-
..
APPLICATIONS FAILED_LOGIN_ATTEMPTS PASSWORD UNLIMITED
APPLICATIONS PASSWORD_LIFE_TIME PASSWORD 180
..

– show the user profile and expiry date and encrypted password
13:08:03 sys> select expiry_date, password, profile from dba_users where username = ‘SCOTT’;

EXPIRY_DA PASSWORD PROFILE
——— —————————— ——————————
21-DEC-09 8C465A58AE456660 APPLICATIONS

1 row selected.

– show the profile for DEFAULT which is set to not expire
SQL> select * FROM dbA_profiles where profile = ‘DEFAULT’;
PROFILE RESOURCE_NAME RESOURCE LIMIT
—————————— ——————————– ——– —————————————-
..
DEFAULT PASSWORD_LIFE_TIME PASSWORD UNLIMITED

– change the profile
13:08:17 sys@> alter user SCOTT profile default;

User altered.

– change the password
13:08:28 sys@> alter user SCOTT identified by values ’8C465A58AE456660′;

User altered.

– change the profile for the user back to expire password
13:08:42 sys@> alter user SCOTT profile APPLICATIONS;

User altered.

– check the password expiry date has changed
13:08:51 sys@> select expiry_date, password, profile from dba_users where username = ‘SCOTT’;

EXPIRY_DA PASSWORD PROFILE
——— —————————— ——————————
20-JUN-10 8C465A58AE456660 APPLICATIONS

– shows the password history is kept, note the password changed wasn’t captured in the password history
sys> SELECT name, password_date
FROM sys.user$, sys.user_history$
WHERE user$.user# = user_history$.user#
and name = ‘SCOTT’;

NAME PASSWORD_
—————————— ———
SCOTT 01-MAY-06
SCOTT 26-APR-07
SCOTT 17-APR-08

To disable to trigger that are enabled in cases when loading data manually in a particular schema or table can be done using the following
Oracle:
SELECT 'ALTER TRIGGER ' || trigger_name || ' DISABLE;'
FROM user_triggers
WHERE status = 'ENABLED'

SQL Server:
SELECT 'ALTER TABLE ' + pobj.name + ' DISABLE TRIGGER ' + str.name + ';'
FROM sysobjects str, sysobjects pobj
where str.type = 'TR'
AND pobj.id = str.parent_obj
AND OBJECTPROPERTY(str.[id], 'ExecIsTriggerDisabled') = 0