ORA-01919

This example, tested on Oracle 9, demonstrates the ORA-01919 error message, which some people find misleading. First create a user called ANDREW, a role called BLAH and another user called ANOTHER_USER:

 

SQL> conn / as sysdba

Connected.

SQL> grant create session to andrew

  2  identified by reid

  3  /

 

Grant succeeded.

 

SQL> create role blah

  2  /

 

Role created.

 

SQL> create user another_user

  2  identified by another_password

  3  /

 

User created.

 

SQL>

 

Connect as ANDREW then try to grant role BLAH to ANOTHER_USER. This fails but the error message is not helpful. Role BLAH does exist but it has not been granted to ANDREW yet:

 

SQL> conn andrew/reid

Connected.

SQL> grant blah to another_user

  2  /

grant blah to another_user

*

ERROR at line 1:

ORA-01919: role 'BLAH' does not exist

 

SQL>

 

Grant the role BLAH to ANDREW:

 

SQL> conn / as sysdba

Connected.

SQL> grant blah to andrew

  2  /

 

Grant succeeded.

 

SQL>

 

Then login as ANDREW and try to grant BLAH to ANOTHER_USER again. This time the error message gives a better explanation:

 

SQL> conn andrew/reid

Connected.

SQL> grant blah to another_user

  2  /

grant blah to another_user

*

ERROR at line 1:

ORA-01932: ADMIN option not granted for role 'BLAH'

 

SQL>

 

Now grant the role to ANDREW with the ADMIN option:

 

SQL> conn / as sysdba

Connected.

SQL> grant blah to andrew with admin option

  2  /

 

Grant succeeded.

 

SQL>

 

Login as ANDREW again. This time the GRANT statement works:

 

SQL> conn andrew/reid

Connected.

SQL> grant blah to another_user

  2  /

 

Grant succeeded.

 

SQL>