Auditing an Oracle database for security issues is very important. provides all of the information and tools that you will need Click here for details of Limited's detailed Oracle database security audit service Click here for details of Limited's Oracle Security Training Courses
There are 63 visitors online    
Cookie Policy:We only use essential cookies on small sections of this website. For details see here.


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 zulia identified by zulia;
	User created.
	SQL> -- connect as emil and grant all privileges to Zulia
	SQL> connect emil/emil@sans
	SQL> grant all privileges to zulia;
	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','ZULIA')
	  4* group by grantee
	SQL> /
	---------- ------------------------------
	         2 EMIL
	       139 MDSYS
	       139 ZULIA
	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.