Call: +44 (0)1904 557620 Call
All Privs

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 - Production
	With the Partitioning, OLAP and Oracle Data Mining options
	JServer Release - Production

	SQL> sho user
	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> -- 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 JULIA identified by JULIA;

	User created.

	SQL> -- connect as emil and grant all privileges to JULIA
	SQL> connect emil/emil@sans
	SQL> grant all privileges to JULIA;

	Grant succeeded.

	SQL> -- connect as system and find out if it worked.
	SQL> connect system/manager@sans

	SQL> select count(*),grantee
	  2  from dba_sys_privs
	  3  where grantee in ('MDSYS','EMIL','JULIA')
	  4* group by grantee
	SQL> /

	---------- ------------------------------
	         2 EMIL
	       139 MDSYS
	       139 JULIA


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.