DEFAULT role explained

It seems at times when roles are assigned they are not assigned as DEFAULT role to a user.

The reason for seems to be the following: In the table sys.user$ there is a column called “DEFROLE” which indicates if the role will be a default role or not. The possible values that seem to be are as follows:
0 – None (So when a role is assigned to a user the role is not a default) and none of the current roles are default. The value is set to 0 by “ALTER USER test DEFAULT ROLE NONE”.
1 – Roles assigned to users and the ones that will be assigned will be default roles. The value is set to 1 by “ALTER USER test DEFAULT ROLE ALL”.
2 – Specific roles are default roles, the list of roles that are default to a user is stored in the table “DEFROLE$” and also can be seen when querying “DBA_ROLE_PRIVS” column “DEFAULT_ROLE”. The value is set to 2 by “ALTER USER test DEFAULT ROLE <role name>” only if the value was 0, if the value is 1 it can't be changed to 2 unless to 0 (NONE) first.

So once a value of “DEFROLE” is 1, it can only be changed if the default role is set to “NONE” so any roles assigned or will be assigned are DEFAULT roles.

Example: When the value of DEFROLE = 0 (NONE) for a user
SQL> create user test identified by abcd1234;
User created.

SQL> alter user test default role none;
User altered.

SQL> grant dba to test;
Grant succeeded.

SQL> grant imp_full_database to test;
Grant succeeded.

— Shows none of the roles assigned are default roles
SQL> select * FROM dba_role_privs where grantee = 'TEST';
GRANTEE GRANTED_ROLE ADM DEF
—————————— —————————— — —
TEST DBA NO NO
TEST IMP_FULL_DATABASE NO NO

Example: When the value of DEFROLE = 1 (ALL) for a user
SQL> create user test identified by abcd1234;
User created.

SQL> alter user test default role none;
User altered.

SQL> grant imp_full_database to test;
Grant succeeded.

SQL> grant dba to test;
Grant succeeded.

SQL> select * FROM dba_role_privs where grantee = 'TEST';
GRANTEE GRANTED_ROLE ADM DEF
—————————— —————————— — —
TEST DBA NO NO
TEST IMP_FULL_DATABASE NO NO

SQL> alter user test default role all;
User altered.

SQL> select * FROM dba_role_privs where grantee = 'TEST';
GRANTEE GRANTED_ROLE ADM DEF
—————————— —————————— — —
TEST DBA NO YES
TEST IMP_FULL_DATABASE NO YES

SQL> grant exp_full_database to test;
Grant succeeded.

SQL> select * FROM dba_role_privs where grantee = 'TEST';
GRANTEE GRANTED_ROLE ADM DEF
—————————— —————————— — —
TEST DBA NO YES
TEST EXP_FULL_DATABASE NO YES
TEST IMP_FULL_DATABASE NO YES

Example: When the value of DEFROLE = 2 for a user
SQL> create user test identified by abcd1234;
User created.

SQL> alter user test default role none;
User altered.

SQL> select user#, defrole from sys.user$ where name = 'TEST';
USER# DEFROLE
———- ———-
35 0

SQL> grant dba to test;
Grant succeeded.

SQL> alter user test default role dba;
User altered.

SQL> grant imp_full_database to test;
Grant succeeded.

SQL> select user#, defrole from sys.user$ where name = 'TEST';
USER# DEFROLE
———- ———-
35 2

— Shows the DBA role is default and INP_FULL_DATABASE is not the default role
SQL> select * FROM dba_role_privs where grantee = 'TEST';
GRANTEE GRANTED_ROLE ADM DEF
—————————— —————————— — —
TEST DBA NO YES
TEST IMP_FULL_DATABASE NO NO

Advertisements

Changing Oracle password in 11g using ALTER USER IDENTIFIED BY VALUES

In 11g the Oracle the hash password is no longer stored in DBA_USERS, it is stored in SYS.USER$ table in the column “PASSWORD” and “SPARE4”. So there are different ways password can be set depending on if “PASSWORD” and “SPARE4” are set in SYS.USER$ and what you want PASSWORD_VERSIONS to be.

If only “SPARE4” is used to set the password the password version is 11g and in SYS.USER$ password value becomes NULL after ALTER statement
SQL> select password, spare4 from sys.user$ where name = 'SCOTT';

PASSWORD
——————————
SPARE4
——————————————————————————–
F894844C34402B67
S:CAED10CB7E2A275ACCCFCFB597530005310CFD9555FC5773802B4129B346

SQL> select password_versions from dba_users where username = 'SCOTT';

PASSWORD
——–
10G 11G

SQL> alter user scott identified by values 'S:CAED10CB7E2A275ACCCFCFB597530005310CFD9555FC5773802B4129B346';

User altered.

SQL> select password_versions from dba_users where username = 'SCOTT';

PASSWORD
——–
11G

SQL> select password, spare4 from sys.user$ where name = 'SCOTT';

PASSWORD
——————————
SPARE4
———————————————————————–

S:CAED10CB7E2A275ACCCFCFB597530005310CFD9555FC5773802B4129B346

If both password and SPARE4 is used to restore the password, the PASSWORD_VERSIONS is “10G 11G”, to set this the IDENTIFIED BY VALUES is passed in as “SPARE4;PASSWORD” from sys.user$

SQL> alter user scott identified by values 'S:AEAEF0006791D6D6C90F9067BDDD37A475B4087625AA14F8BFF612A0145B;F894844C34402B67';

User altered.

SQL> select password_versions from dba_users where username = 'SCOTT';

PASSWORD
——–
10G 11G

SQL> select password, spare4 from sys.user$ where name = 'SCOTT';

PASSWORD
——————————
SPARE4
——————————————————————–
F894844C34402B67
S:AEAEF0006791D6D6C90F9067BDDD37A475B4087625AA14F8BFF612A0145B

If the value from PASSWORD is used from SYS.USER$ the password version is set to 10g and column SPARE4 is set to NULL
SQL> alter user scott identified by values 'F894844C34402B67';

User altered.

SQL> select password_versions from dba_users where username = 'SCOTT';

PASSWORD
——–
10G

SQL> select password, spare4 from sys.user$ where name = 'SCOTT';

PASSWORD
——————————
SPARE4
————————————————————————-
F894844C34402B67

Oracle passwords in 11g

In 11g Oracle supports password to be case sensitive. But the password can be case insensitive depending on value of “PASSWORD_VERSIONS” and “sec_case_sensitive_logon”.

In 11g dba_users view has a new column called “PASSWORD_VERSIONS”, it can have 3 possible values “10G”, “10G 11G”, “11G”. Below shows what they mean along with parameter “sec_case_sensitive_logon”.

Scenario when the value of password_versions in DBA_USERS is “10G 11G”, when the value “10G 11G” the column “PASSWORD” and “SPARE4” has a value
SQL> select password_versions from dba_users where username = 'SCOTT';

PASSWORD
——–
10G 11G

SQL> select password, spare4 from sys.user$ where name = 'SCOTT';

PASSWORD
——————————
SPARE4
————————————————————————
F894844C34402B67
S:AEAEF0006791D6D6C90F9067BDDD37A475B4087625AA14F8BFF612A0145B

-- When the value of PASSWORD_VERSIONS is 10G 11G and sec_case_sensitive_login is TRUE, Oracle matches the case of the password.
SQL> show parameter sec_case_sensitive_logon;
NAME TYPE VALUE
———————————— ———– ——————-
sec_case_sensitive_logon boolean TRUE

SQL> connect scott/TIGER
ERROR:
ORA-01017: invalid username/password; logon denied

SQL> connect scott/tiger
Connected.
SQL>

-- When the value of PASSWORD_VERSIONS is 10G 11G and sec_case_sensitive_login is FALSE the password entered does not have to be case-sensitive.
SQL> alter system set sec_case_sensitive_logon=FALSE;

System altered.

SQL> connect scott/TIGER
Connected.

Scenario when password_versions is 11G – In this scenario the password is case-sensitive irrespective what the value of sec_case_sensitive_logon so the password is always case-sensitive. In this case the column “PASSWORD” is NULL and SPARE4 is not NULL
SQL> select password_versions from dba_users where username = 'SCOTT';

PASSWORD
——–
11G

SQL> select password, spare4 from sys.user$ where name = 'SCOTT';

PASSWORD
——————————
SPARE4
——————————————————————-

S:CAED10CB7E2A275ACCCFCFB597530005310CFD9555FC5773802B4129B346

-- Shows example when sec_case_sensitive_logon is TRUE and password versions is 11G the password is case-sensitive irrespective the value of “sec_case_sensitive_logon”.
SQL> alter system set sec_case_sensitive_logon=TRUE;

System altered.

SQL> connect scott/TIGER
ERROR:
ORA-01017: invalid username/password; logon denied

-- Shows example when set sec_case_sensitive_logon is FALSE when password versions is 11G that it only works when password is case-sensitive
SQL> alter system set sec_case_sensitive_logon=FALSE;

System altered.

SQL> connect scott/TIGER
ERROR:
ORA-01017: invalid username/password; logon denied

SQL> connect scott/tiger
Connected.

Scenario when password_versions is 10G – In this scenario the password is not case-sensitive. In this case in sys.user$ “PASSWORD” is NOT NULL and SPARE4 is NULL.
SQL> select password_versions from dba_users where username = 'SCOTT';

PASSWORD
——–
10G

SQL> select password, spare4 from sys.user$ where name = 'SCOTT';

PASSWORD
——————————
SPARE4
—————————————————————–
F894844C34402B67

-- Shows the password is not case sensitive so changing the parameter sec_case_sensitive_logon has no effect when password is entered for SCOTT
SQL> alter system set sec_case_sensitive_logon=true;

System altered.

SQL> connect scott/TIGER
Connected.

SQL> show parameter sec_case_sensitive_logon;

NAME TYPE VALUE
———————————— ———– ——-
sec_case_sensitive_logon boolean FALSE
SQL> connect scott/TIGER
Connected.

ORA-28221: REPLACE not specified

When a user name has a profile that has a password verify function set and the user tries to change the password using “ALTER USER” without the REPLACE clause, and also the user doesn’t have ALTER USER privilege Oracle will generate the “ORA-28221: REPLACE not specified” error message. So the user needs to include the “REPLACE” clause.

Example:
ALTER USER <username> IDENTIFIED BY <new password> REPLACE <old password>

How to extend an account whose password is expiring?

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