How to grant all privileges in Oracle
This is a short paper showing how to grant "all privileges" to a user in Oracle and more importantly what privileges are needed to do this. This was a posting I made to one of the newsgroups/mailing lists recently. This is for information only as it is useful to know BUT one important fact that should be highlighted here is that i cannot think of any circumstances or when ALL PRIVILEGES should be granted to anyone. It is simply unnecessary. Do the job correctly and find out the exact privileges needed for the job in hand and grant those. Granting all privileges is a security risk as it means the user having those privileges can do just about anything in your database.
Remember use least privilege principle at all times and grant what is needed. Do not grant everything just to get the job done quickly. Here is the example code! Connected to:
Personal Oracle9i Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL>
SQL> sho user
USER is "SYSTEM"
SQL> select * from system_privilege_map
2 where name like '%PRIV%';
PRIVILEGE NAME PROPERTY
---------- ---------------------------------------- ----------
-167 GRANT ANY PRIVILEGE 0
-244 GRANT ANY OBJECT PRIVILEGE 0
SQL>
SQL> -- Create a new user with just create session (to log on) and grant
SQL> -- any privilege to, well grant all privileges.
SQL> create user emil identified by emil;
User created.
SQL> grant create session, grant any privilege to emil;
Grant succeeded.
SQL> -- because we want to test this privilege create a second user to
SQL> -- test it with
SQL> create user zulia identified by zulia;
User created.
SQL> -- connect as emil and grant all privileges to Zulia
SQL> connect emil/emil@sans
Connected.
SQL> grant all privileges to zulia;
Grant succeeded.
SQL> -- connect as system and find out if it worked.
SQL> connect system/manager@sans
Connected.
SQL> select count(*),grantee
2 from dba_sys_privs
3 where grantee in ('MDSYS','EMIL','ZULIA')
4* group by grantee
SQL> /
COUNT(*) GRANTEE
---------- ------------------------------
2 EMIL
139 MDSYS
139 ZULIA
SQL>
We used MDSYS as a checkpoint as MDSYS has all privileges granted to it
by default in a default installation of Oracle. The privilege you need
therefore is GRANT ANY PRIVILEGE.
