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 optionsSQL> select * from sys.t1;
select * from sys.t1
*
ERROR at line 1:
ORA-00942: table or view does not existSQL> 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.
