Home » Databases » Oracle: How to create a non-expiring (password) Login Profile using sqlplus

Oracle: How to create a non-expiring (password) Login Profile using sqlplus

Creating a login profile is very easy in Oracle Enterprise Manager, click click and you’re effectively done. ┬áIf you’re not using OEM or have access to OEM, you may want to know how to do it using SQLPlus.

First create the non-expiring login profile:

create profile DBA LIMIT
PASSWORD_VERIFY_FUNCTION NULL
PASSWORD_LIFE_TIME UNLIMITED;

We need to assign a user or a hundred to it. In this case, just the SYS and SYSTEM logins:

alter user SYS profile DBA;
alter user SYSTEM profile DBA;

If the logins have expired, in the grace period or you’re seeing a warning that the login will expire in X days, you will need to set the password. Don’t worry, it can be the exact same password:

alter user SYS identified by ....;
alter user SYSTEM identified by .....;

What if you don’t know the password for a particular user? Easy, use the password hash! Let’s get the password hash:

In Oracle 10g:

select username, password
from dba_users
where username = 'myuser';

In Oracle 11g:

select dbms_metadata.get_ddl('USER','myuser') 
from dual;

Plug in the hash into the alter user statement:

alter user myuser identified by "<insert hash here>";

For more details on the password hash, see Password Hash In Oracle 11g.

Share Button

Leave a Reply

Your email address will not be published. Required fields are marked *

*
*

Facebook login by WP-FB-AutoConnect