Wednesday, May 04, 2011

Drop User Privilege

I ran this example on Oracle 9 to demonstrate the DROP USER privilege. First I created two users, A and X:

SQL> conn / as sysdba
Connected.
SQL> grant create session to a identified by b;
 
Grant succeeded.
 
SQL> grant create session to x identified by y;
 
Grant succeeded.

SQL>

... then I logged on as user A and tried to drop user X. This failed as user A did not have the DROP USER privilege:

SQL> conn a/b
Connected.
SQL> drop user x;
drop user x
*
ERROR at line 1:
ORA-01031: insufficient privileges

SQL>

I granted the DROP USER privilege to user A and this allowed it to drop user X:

SQL> conn / as sysdba
Connected.
SQL> grant drop user to a;
 
Grant succeeded.
 
SQL> conn a/b
Connected.
SQL> drop user x;
 
User dropped.
 
SQL>

No comments: