Tuesday, July 24, 2012

ORA-01934

This example was tested on Oracle 11.2. It should be fairly obvious that you cannot grant a role to itself. You get an ORA-01934 if you try:

SQL> create role andrew1;

Role created.

SQL> grant andrew1 to andrew1;
grant andrew1 to andrew1
*
ERROR at line 1:
ORA-01934: circular role grant detected

SQL>

However, if you weren't paying attention, you might, for example, grant one role to a second role then grant that second role to a third role. If you later tried to grant the third role back to the first role, you would get another ORA-01934: 

SQL> create role andrew2;

Role created.

SQL> create role andrew3;

Role created.

SQL> grant andrew1 to andrew2;

Grant succeeded.

SQL> grant andrew2 to andrew3;

Grant succeeded.

SQL> grant andrew3 to andrew1;
grant andrew3 to andrew1
*
ERROR at line 1:
ORA-01934: circular role grant detected

SQL>

No comments: