Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted Nov 5, 2009

Explicit GRANTs and ROLES in Oracle Database 11g

By James Koopmann



I don’t know how many database shops I’ve gone into that have no idea of the security breaches that occur across user granted privilege and floating unused synonyms.

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:

1.  Granting privileges explicitly to a user or group of users

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

3.  Creating a secure application role



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.

» See All Articles by Columnist James Koopmann



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM