Explicit GRANTs and ROLES in Oracle Database 11g

The Oracle 2-Day Security Guide starts out chapter 4 talking about managing user privileges. The discussion begins by telling the reader that they must control user privileges through:

I’ll first say that there are many things I’ve seen within database shops that rub me the wrong way in regards to security but the inability to follow, at least the first two points, is one of the most blatantly obvious holes I consistently see. I really don’t know if it is laziness or the inability to comprehend how to begin the process of gaining control of privileges. One of the first queries I’ll run within a database shop is the following; it gives me a really quick outline of how the PUBLIC user group is being used, or abused, for accessing tables. If you see any of your application users in the OWNER column, chances are you have some privileges out of whack, as there is no real good reason to grant this group any form of access to application data.

 
SQL> SELECT grantee,owner,count(*)
  2    FROM dba_tab_privs
  3   WHERE grantee = 'PUBLIC'
  4   GROUP BY grantee, owner;
 

So, with this article, I’d like to give a brief explanation or tutorial of how to at least perform points 1 and 2 from above. Not only does the 2-Day Security Guide explain how to take advantage of these but I have hopes that they will be understood and enable DBAs to regain and tighten the control of privileges within their database.

In order to begin we must first establish a few prerequisites. Let’s create a single table and three users. This will allow us to grant privileges for a single user as well as create groups of users. As user SYS we can issue the following:

 
	1.	CREATE USER u1 IDENTIFIED BY u1;
	2.	GRANT CREATE SESSION TO u1;
	3.	CREATE USER u2 IDENTIFIED BY u2;
	4.	GRANT CREATE SESSION TO u2;
	5.	CREATE USER u3 IDENTIFIED BY u3;
	6.	GRANT CREATE SESSION TO u3;
	7.	CREATE TABLE t1 (col1 number);
	8.	INSERT INTO t1 VALUES (1);
 

Now let’s begin:

Granting privileges explicitly to a user or group of users

Hopefully, we can all see that each of the following new users will not have any access to the new table T1. To prove this, we can connect to the database as one of the users and select from the tables:

$ sqlplus u1/u1
SQL*Plus: Release 11.1.0.6.0 - Production on Thu Oct 22 17:56:21 2009
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from sys.t1;
select * from sys.t1
              *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> select * from sys.t2;
select * from sys.t2
              *
ERROR at line 1:
ORA-00942: table or view does not exist

For explicitly granting table privileges to the user U1, we can issue the following command:

SQL> GRANT SELECT ON t1 TO u1;
Grant succeeded.

Then issuing a subsequent SELECT as the U1 user, we can actually see some results:

SQL> select * from sys.t1;
      COL1
----------
         1

Likewise you could issue the GRANT to a group of users separated by commas:

SQL> GRANT SELECT ON t1 TO u1, u2;
Grant succeeded.

This method of granting privileges is probably what most of us have grown to love (or hate) over the years—granting explicit privileges to each and every user. I personally find this very cumbersome, confusing, and catastrophic under most scenarios. What I’ve seen over the years is every user having the exact same privileges. AND 100’s of them! To make matters worse, when a new user is created, there isn’t a controlled mechanism or set of scripts to grant privileges to this user. What usually happens is a DBA will reverse engineer the GRANTs, issuing a select, spooling the output, and then running the new query such as:

SQL> SELECT 'GRANT '||privilege||
  2           ' ON '||owner||'.'||table_name||
  3          ' TO '||'u3;'
  4    FROM dba_tab_privs
  5   WHERE grantee = 'U1';
'GRANT'||PRIVILEGE||'ON'||OWNER||'.'||TABLE_NAME||'TO'||'U3;'
-------------------------------------------------------------
GRANT SELECT ON SYS.T1 TO u3;

Before going further, let’s REVOKE the SELECT privilege we granted to our users:

SQL> revoke select on sys.t1 from u1,u2,u3;
Revoke succeeded.

This now leads us into the next method of granting privileges to a user or group of users.

Assigning privileges to a role and then granting that role to users

The ROLE. The ROLE is a target, to issue grants to, for a collection of grants where the role can then be issued to a user or group of users. The beauty of this is that, typically, a ROLE is created around a set of application privileges that the DBA doesn’t necessarily need to remember but can just grant the role to a user. Very simply, you can create a role by:

SQL> CREATE ROLE u_role IDENTIFIED BY u_role;
Role created.

We can then grant our previous SELECT privilege to the new role by:

SQL> GRANT SELECT ON sys.t1 TO u_role;
Grant succeeded.

Now we can grant the ROLE to our users:

SQL> GRANT u_role TO u1,u2,u3;
Grant succeeded.

Granted, no pun intended, granting privileges is not an exciting topic. Nevertheless, databases that do not control the use of privileges are often plagued with uncertain security issues. After all, it is the goal of each and every security measure to restrict access where not needed—giving only the bare necessities to users or applications to do their job or perform their functions. If this is not performed well, when audited, DBAs will have much to explain. The use of ROLEs enables DBAs and Security Administrators to properly group privileges so that, when it comes time to explain access rights, they can quickly explain and prove their database is under control—making use of proper security measures. And please, whatever you do, do not rely upon the PUBLIC group to handle any form of privilege. This default group is best left alone or trimmed down at best.