Monday, May 21, 2012

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>

No comments: