ORA-28405

This post is an update to an earlier one, which I have now deleted. I tested the first part of it in an Oracle 11.1.0.6 database. First I created a role which was identified by a password:

 

SQL> conn / as sysdba

Connected.

SQL> create role low identified by secret_password

  2  /

 

Role created.

 

SQL>

 

I granted the role to a user and made sure it had no default roles:

 

SQL> grant create session, low

  2  to andrew identified by reid

  3  /

 

Grant succeeded.

 

SQL> alter user andrew default role none

  2  /

 

User altered.

 

SQL>

 

I connected as the user and tried to activate the role but this failed with an ORA-01979  as I had not supplied the password:

 

SQL> conn andrew/reid

Connected.

SQL> set role low

  2  /

set role low

*

ERROR at line 1:

ORA-01979: missing or invalid password for role 'LOW'

 

SQL> select role from session_roles

  2  /

 

no rows selected

 

SQL>

 

When I supplied the password, I was able to activate the role successfully:

 

SQL> set role low identified by secret_password

  2  /

 

Role set.

 

SQL> select role from session_roles

  2  /

 

ROLE

------------------------------

LOW

 

SQL>

 

I created another role without a password:

 

SQL> conn / as sysdba

Connected.

SQL> create role high

  2  /

 

Role created.

 

SQL>

 

… granted the first role to it:

 

SQL> grant low to high

  2  /

 

Grant succeeded.

 

SQL>

 

… and granted the 2^nd role to the user:

 

SQL> grant high to andrew

  2  /

 

Grant succeeded.

 

SQL>

 

I then connected as the user and activated the 2^nd role. This had the effect of also activating the 1^st role without needing to supply the password. This has always seemed wrong to me:

 

SQL> conn andrew/reid

Connected.

SQL> select role from session_roles

  2  /

 

no rows selected

 

SQL> set role high

  2  /

 

Role set.

 

SQL> select role from session_roles

  2  /

 

ROLE

------------------------------

HIGH

LOW

 

SQL>

 

I went to a presentation given by Simon Pane from Pythian when I was at  a UKOUG conference. He said that this behaviour changed in Oracle 11.2.0.4 so I decided to repeat the test in a database on this version:

 

SQL> conn / as sysdba

Connected.

SQL> create role low identified by secret_password

  2  /

 

Role created.

 

SQL> grant create session, low

  2  to andrew identified by reid

  3  /

 

Grant succeeded.

 

SQL> alter user andrew default role none

  2  /

 

User altered.

 

SQL> conn andrew/reid

Connected.

SQL> set role low

  2  /

set role low

*

ERROR at line 1:

ORA-01979: missing or invalid password for role 'LOW'

 

SQL> select role from session_roles

  2  /

 

no rows selected

 

SQL> set role low identified by secret_password

  2  /

 

Role set.

 

SQL> select role from session_roles

  2  /

 

ROLE

------------------------------

LOW

 

SQL> conn / as sysdba

Connected.

SQL> create role high

  2  /

 

Role created.

 

SQL>

 

It all worked as before until I tried to grant the 1^st role to the 2^nd role where this failed with an ORA-28405, which seems much more sensible to me:

 

SQL> grant low to high

  2  /

grant low to high

*

ERROR at line 1:

ORA-28405: cannot grant secure role to a role

 

SQL>

 

So when I granted the 2^nd role to the user:

 

SQL> grant high to andrew

  2  /

 

Grant succeeded.

 

SQL> conn andrew/reid

Connected.

SQL> select role from session_roles

  2  /

 

no rows selected

 

SQL>

 

… he was able to activate the 2^nd role but the 1^st one, which had the password, was not activated:

 

SQL> set role high

  2  /

 

Role set.

 

SQL> select role from session_roles

  2  /

 

ROLE

------------------------------

HIGH

 

SQL>

How to Test a Database Link

Before creating a database link, you need a schema to connect to in the remote database:

 

SQL> conn /@remotedb

Connected.

SQL> grant create session to link_schema

  2  identified by link_schema_password

  3  /

 

Grant succeeded.

 

SQL>

 

Then you can create a database link in the local database as follows:

 

SQL> create database link andrews_link

  2  connect to link_schema

  3  identified by link_schema_password

  4  using 'REMOTEDB'

  5  /

 

Database link created.

 

SQL>

 

... and you can test it like this:

 

SQL> select * from dual@andrews_link

  2  /

 

D

-

X

 

SQL>

 

If things go wrong in the remote database, the test will fail in the local database and will often give you a good idea of what is wrong. Here is one example:

 

SQL> conn /@remotedb

Connected.

SQL> revoke create session from link_schema

  2  /

 

Revoke succeeded.

 

SQL> conn /@localdb

Connected.

SQL> select * from dual@andrews_link

  2  /

select * from dual@andrews_link

                   *

ERROR at line 1:

ORA-01045: user LINK_SCHEMA lacks CREATE SESSION privilege; logon denied

ORA-02063: preceding line from ANDREWS_LINK

 

SQL>

 

... and here is another:

 

SQL> conn /@remotedb

Connected.

SQL> alter user link_schema

  2  identified by new_password

  3  /

 

User altered.

 

SQL> conn /@localdb

Connected.

SQL> select * from dual@andrews_link

  2  /

select * from dual@andrews_link

                   *

ERROR at line 1:

ORA-01017: invalid username/password; logon denied

ORA-02063: preceding line from ANDREWS_LINK

 

SQL>

XS$NULL

Oracle introduced a new user in version 11 called XS$NULL. It is for Oracle’s internal use only and you should leave it alone. For this reason it is locked and expired when it is created:

 

SQL> select account_status from dba_users

  2  where username = 'XS$NULL';

 

ACCOUNT_STATUS

--------------------------------

EXPIRED & LOCKED

 

SQL>

 

You are advised not to alter this account in any way, even if an auditor asks you to. Oracle does not let you change its password:

 

SQL> conn / as sysdba

Connected.

SQL> alter user xs$null identified by new_pwd

  2  /

alter user xs$null identified by new_pwd

                                 *

ERROR at line 1:

ORA-01031: insufficient privileges

 

SQL>

 

Although you can do so with the password command:

 

SQL> select password from sys.user$

  2  where name = 'XS$NULL';

 

PASSWORD

------------------------------

DC4FCC8CB69A6733

 

SQL> password xs$null

Changing password for xs$null

New password:

Retype new password:

Password changed

SQL> select password from sys.user$

  2  where name = 'XS$NULL';

 

PASSWORD

------------------------------

C17AE3B0A14EA63F

 

SQL>

 

This is because of bug 12822989 and you must not do this.

Another Example Using Intersect

In the course of helping a colleague today, I had to find a database user who had 2 roles assigned. You can do this with a couple of subqueries but it is easier with an INTERSECT. I was logged in as user ORACLE when I did this and I was surprised to see ORACLE in the list alongside USER_2. It seems that, if you create a role, it is automatically assigned to you. I did not know this. You can see this demonstrated at the end of the example. I ran it on an Oracle 9 database but the same thing happens in Oracle 10 and 11:

 

SQL> conn /

Connected.

SQL> show user

USER is "ORACLE"

SQL> create role role_a

  2  /

 

Role created.

 

SQL> create role role_b

  2  /

 

Role created.

 

SQL> grant role_a to user_1

  2  identified by user_1

  3  /

 

Grant succeeded.

 

SQL> grant role_a, role_b to user_2

  2  identified by user_2

  3  /

 

Grant succeeded.

 

SQL> grant role_b to user_3

  2  identified by user_3

  3  /

 

Grant succeeded.

 

SQL> select distinct grantee from dba_role_privs

  2  where grantee in

  3  (select grantee from dba_role_privs

  4   where granted_role = 'ROLE_A')

  5  and grantee in

  6  (select grantee from dba_role_privs

  7   where granted_role = 'ROLE_B')

  8  /

 

GRANTEE

------------------------------

ORACLE

USER_2

 

SQL> select grantee from dba_role_privs

  2  where granted_role = 'ROLE_A'

  3  intersect

  4  select grantee from dba_role_privs

  5  where granted_role = 'ROLE_B'

  6  /

 

GRANTEE

------------------------------

ORACLE

USER_2

 

SQL> create role blah

  2  /

 

Role created.

 

SQL> select grantee from dba_role_privs

  2  where granted_role = 'BLAH'

  3  /

 

GRANTEE

------------------------------

ORACLE

 

SQL>

Password Encryption up to Oracle 10

Up to and including version 10, Oracle created its encrypted passwords from a concatenation of username and password. I will look at version 11 in future posts:
  

SQL> CREATE USER A IDENTIFIED BY BCDEF
  2  /

User created.

SQL> CREATE USER AB IDENTIFIED BY CDEF
  2  /

User created.

SQL> CREATE USER ABC IDENTIFIED BY DEF
  2  /

User created.

SQL> CREATE USER ABCD IDENTIFIED BY EF
  2  /

User created.

SQL> CREATE USER ABCDE IDENTIFIED BY F
  2  /

User created.

SQL>

The encrypted password was then stored in the PASSWORD column of DBA_USERS:

SQL> SELECT USERNAME, PASSWORD
  2  FROM DBA_USERS
  3  WHERE USERNAME IN ('A','AB','ABC','ABCD','ABCDE')
  4  ORDER BY 1
  5  /

USERNAME                       PASSWORD       
------------------------------ ------------------------------ A                              016811C1486D026B    
AB                             016811C1486D026B    
ABC                            016811C1486D026B                
ABCD                           016811C1486D026B          
ABCDE                          016811C1486D026B

SQL>

ORA-00972: identifier is too long

Tested on an Oracle 9 database. The maximum length of a password is 30 characters. If you try and set one longer than that, Oracle returns an ORA-00972:

 

SQL> CREATE USER A IDENTIFIED BY

  2  ABCDEFGHIJKLMNOPQRSTUVWXYZ1234

  3  /

 

User created.

 

SQL> ALTER USER A IDENTIFIED BY

  2  ABCDEFGHIJKLMNOPQRSTUVWXYZ12345

  3  /

ABCDEFGHIJKLMNOPQRSTUVWXYZ12345

*

ERROR at line 2:

ORA-00972: identifier is too long

 

SQL> CREATE USER B IDENTIFIED BY

  2  ABCDEFGHIJKLMNOPQRSTUVWXYZ12345

  3  /

ABCDEFGHIJKLMNOPQRSTUVWXYZ12345

*

ERROR at line 2:

ORA-00972: identifier is too long

 

SQL>

Create User

This example was tested on an Oracle 9 database. When you create a user, you must either specify a password or make the user externally identified. Otherwise you will get an ORA-01938:
  

SQL> create user andrew;

create user andrew

                 *

ERROR at line 1:

ORA-01938: IDENTIFIED BY must be specified for CREATE USER

SQL> create user andrew identified by reid;

User created.

SQL> drop user andrew;

User dropped.

SQL> create user andrew identified externally;

User created.

SQL>

SQL*Plus PASSWORD Command

Most of you will know how to use the ALTER USER command to change your password:

SQL> conn system/manager1@test10
Connected.
SQL> alter user system identified by manager2;
User altered.
SQL> conn system/manager2@test10
Connected.
SQL>

But SQL*Plus also has a PASSWORD command which allows you to do the same thing. In the example below, the user tries it out. Oracle asks for the old password then asks for the new password twice. Neither the old nor the new password are displayed on the screen:

SQL> password
Changing password for SYSTEM
Old password: ********
New password: ********
Retype new password: ********
Password changed
SQL> conn system/manager3@test10
Connected.
SQL>

An administrator can use the PASSWORD command to change another user's password. In this case, Oracle does not ask for the old password. The example below is from Oracle 11g release 2:

SQL> conn / as sysdba

Connected.

SQL> show user

USER is "SYS"

SQL> password andrew

Changing password for andrew

New password:

Retype new password:

Password changed

SQL>