Oracle Object Security Privileges Oracle Security Tips by Burleson Consulting

This is an excerpt from the bestselling book "Oracle Privacy Security Auditing", a complete Oracle security reference with working Oracle security scripts.
 

---

Object Privileges

Our discussion now turns to the traditional security model used by databases, file systems, and operating systems of all types – limiting access to a specific object for a specific type. These accesses can be given to almost all types of objects in Oracle – tables, views, sequences, and much more.

In this section, we will discuss what these privileges are and how they should be enforced. We will also discover the data dictionary views that show the current level of allocation of privileges, and how they can be reported so that potential holes can be unearthed.

Here is an example of the SELECT privilege given on the CLAIMS table to user JUDY. This is executed by the schema owner of the table CLAIMS.

grant select on claims to judy;

Certain privileges only matter for specific types of objects. For example, a procedure can have an execute privilege, a table can't. Similarly, a table may have a select privilege, a procedure can't. Here is a list of privileges that can be assigned to a user.

PRIVILEGE	DESCRIPTION
ALTER	Allows the grantee to modify the structure as in ALTER TABLE. This is valid only for tables and sequences. No other user can alter any other object types owned by other users.
DELETE	Allows grantee to delete records in that object. This is valid only for tables, views and materialized views. Note that the DELETE privilege makes sense only when the materialized view is updateable.
EXECUTE	Allows the grantee to execute some stored code. Naturally, it's applicable to only the code in the database such as procedure, function, package, library, methods inside user-defined types, user defined operators and index types.
DEBUG	This allows the grantee to run a debugging session on the object where the object is code. However, the privilege applies to TABLEs and VIEWs too, since the DEBUG privilege allows debugging of triggers defined on the tables and views. In addition, the privilege allows debugging on methods inside user-defined types.
FLASHBACK	Oracle 9i introduced a new concept called flashback query where the user can see the value of an updated object as of some time in the past. This privilege allows the grantee to enable flashback on the object. Can be granted on tables, views and materialized views only.
INDEX	This allows the grantee to create an index on some other user's table. This should not be granted. Ideally, the owner of the table should own all indexes; there is no advantage to indexes owned by JUDY on tables owned by CLAIM_SCHEMA.
INSERT	Allows the grantee to insert records into the object. Since inserts can be specific to only some objects, they can be granted on tables, views and materialized views.
ON COMMIT REFRESH	In real time data warehouse or OLTP environments where the materialized view (a.k.a. snapshot) has to be refreshed as soon as the source table row is updated, the MV must be created with the ON COMMIT REFRESH clause. This privilege allows the grantee to create an MV in that manner.
QUERY REWRITE	In data warehouse environments, the MVs could be pre-created as the queries the users might execute, and when users actually run those queries, Oracle merely retrieves the data from the pre-created MVs, increasing performance. This process is called query rewriting. This privilege allows the grantee's session to rewrite the query to select from the MV.
READ	This applies only to the database objects of type directory. This privilege allows the grantee to read from that directory.
REFERENCES	While defining foreign key constraints, the child tables must select from the parent table to validate data. However, that is not the same as selecting data from the parent table and for security reasons, the selection from the parent table may be restricted. This privilege resolves that problem. The grantee can create FK constraints on this granted table without needing a SELECT privilege. For instance, CLAIM_SCHEMA owns the table CLAIMS, which has a FK relationship to table MEMBERS owned by MEMBER_SCHEMA. The MEMBER_SCHEMA can issue GRANT REFERENCES ON MEMBERS; This will allow the user CLAIM_SCHEMA to define a FK on CLAIMS pointing to MEMBERS.
SELECT	Allows the grantee to select from the object. This can be granted on tables, views, sequences, and materialized views only.
UNDER	Oracle 9i introduced a new concept called subview, which can be defined under a view. Similarly, a user-defined type can be defined under another type. This privilege allows the grantee to create subviews and subtypes.
UPDATE	Allows the grantee to update the object. Naturally, it applies to the objects that can be updated which are tables, views and materialized views.
WRITE	Oracle 9i introduced a new type of table called an External Table, which is actually a flat file residing on the filesystem that can be read as a table. While reading the external table, Oracle needs to write a log file and a bad file to a directory. This privilege on a directory allows the grantee to invoke an external table whose bad file and log file can be written to that directory.

Table 4.4 Common Object Grants

One of the more important things to assess in HIPAA compliance or any other security and privacy law is who has what privilege on which object. The data dictionary view dba_tab_privs holds this information. Note the name with the inference to TAB, or tables, actually, the view contains privilege information on all types of objects – tables, views, procedures, packages, etc., not just tables. Here is a brief explanation of the view and its columns.

COLUMN NAME	DESCRIPTION
GRANTEE	The user or role the privilege is granted to.
OWNER	The owner of the object on which the privilege is granted.
TABLE_NAME	The name of the table or other object on which the privilege is granted. Please note that the name of the column is misleading – it's not just tables. (see note above)
GRANTOR	The user who granted the privilege. It may not be the same as the owner of the object. Described later.
PRIVILEGE	The exact privilege granted, e.g. SELECT or UPDATE, etc.
GRANTABLE	If this privilege is grantable by the grantee, discussed later.

Table 4.5 Columns in dba_tab_privs

In the view dba_tab_privs, the column name TABLE_NAME is a misnomer, it contains all types of objects.

A role is described later in this chapter. For the moment, please consider users and roles as the same, an entity to which the privilege is granted.

Another important concept to understand is the grants with GRANT OPTION. In this case, the owner of the original object grants the privilege on the object first with the special clause called GRANT OPTION. This enables the grantee to grant it further to the other users. If the grantee indeed does so, then the grantor column is different from the schema owner in dba_tab_privs.

To illustrate this concept, let's assume the user SYS grants execute privilege on the package dbms_session to JUDY. SYS also grants execute privileges with the GRANT OPTION on package dbms_lock to JUDY.

SQL> connect sys/53cr3t
SQL> grant execute on dbms_session to judy;
SQL> grant execute on dbms_lock to judy with grant option;

If the data dictionary view is now queried for privileges granted to JUDY as follows

select
   owner,
   table_name,
   grantor,
   privilege,
   grantable
from
   dba_tab_privs
where
   grantee = 'JUDY'
/

The output will be like this:

OWNER      TABLE_NAME      GRANTOR    PRIVILEGE      GRA
---------- --------------- ---------- -------------- ---
SYS        DBMS_SESSION    SYS        EXECUTE        NO
SYS        DBMS_LOCK       SYS        EXECUTE        YES

Note for the column GRANTABLE, the value is YES for one, where the GRANT OPTION was used and for the other the value is NO.

Since JUDY has the privilege with the GRANT OPTION, she can grant this to NATHAN.

SQL> connect judy/5ucc355
SQL> grant execute on sys.dbms_lock to nathan;

Now selecting the privileges for NATHAN:

select
   owner,
   table_name,
   grantor,
   privilege,
   grantable
from
   dba_tab_privs
where
   grantee = 'NATHAN'
/

OWNER      TABLE_NAME      GRANTOR    PRIVILEGE      GRA
---------- --------------- ---------- -------------- ---
SYS        DBMS_LOCK       JUDY       EXECUTE        NO

Note the GRANTOR of the privilege; it's not SYS, the owner of the package. Instead, the GRANTOR is the user who actually granted it, JUDY.

For good security, you should never have privileges granted with the GRANT OPTION. All the privileges should be given directly by the schema owner and controlled from there. The GRANT OPTION just creates numerous permutations for the grants to be given, and is a management nightmare.

The following script shows whether you have any granted privileges with the grant option.

select
   owner,
   table_name,
   grantor,
   privilege
from
   dba_tab_privs
where
   grantable = 'YES'
and
   owner not in ('SYS','SYSTEM')
/

If there are any such privileges, immediately revoke them and re-grant them without the grant option. The following script shows privileges where the original owner did not grant the privileges.

select
   owner,
   table_name,
   grantor,
   privilege
from
   dba_tab_privs
where
   owner != grantor
and
   owner not in ('SYS','SYSTEM')
/

There shouldn't be any such privileges. If you find some, revoke them and re-grant from the schema owner.

* Tip: The privileges on the objects should be given directly by the owner of the object and without the GRANT option. Check to see if these conditions are met. If necessary, correct them.

Finally, it's time to gather information on who has what privileges. This report satisfies the HIPAA requirement that the capabilities (or privileges) should be documented.

* show_obj_grants.sql

--**********************************************
--
--   Copyright © 2003 by Rampant TechPress Inc.
--
--   Free for non-commercial use.
--   For commercial licensing, e-mail info@rampant.cc
--
-- *********************************************
select
   grantee,
   owner,
   table_name,
   grantor,
   privilege,
   grantable
from
   dba_tab_privs
where
   grantee not in ('SYS','SYSTEM')
order
   by 1,2,3,4
/

A sample output is given below:

GRANTEE  OWNER TABLE_NAME    GRANTOR    PRIVILEGE GRA
------- ------ ------------- ---------- --------- ---
ANANDA   SYS    DBMS_LOCK       JUDY    EXECUTE   NO
ANANDA   SYS    LOGMNR_DICT     SYS     READ      YES
ANANDA   SYS    LOGMNR_DICT     SYS     WRITE     YES
ANANDA   SYS    MYDIR           SYS     READ      YES
ANANDA   SYS    MYDIR           SYS     WRITE     YES

This report can be run periodically and filed away for documentation. The above example is intentionally shown with unsecured privileges granted to user ANANDA - can you spot them?

* Tip: Run the script periodically and file the output for later review. This simple act will satisfy the requirements of many security policies.

Finally, there is an ALL privilege, which simply grants all the privileges to the grantee in one shot. This is granted as

grant all on CLAIMS to JUDY;

Judy can now do anything – modify the structure, create a trigger, even drop the table. Naturally, this creates a huge security hole and should never be granted to any user.

In the chapter for auditing grants security, we will cover this in more detail.

 

This is an excerpt from the book "Oracle Privacy Security Auditing". You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle security and auditing scripts.