Oracle Column Privileges Security 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.
 

---

Column Privileges

A lesser-known feature of Oracle is the ability to grant privileges on individual columns, rather than complete tables. For instance, the user CLAIM_SCHEMA can grant privileges to JUDY so that she can update only the PAID_AMOUNT field of the table CLAIMS, nothing else. The CLAIM_SCHEMA can issue:

grant update (paid_amount) on claims to judy;

If the user JUDY updates any other column as:

update
   claim_schema.claim
set
   claim_amount = 10000
where
   claim_id = 1234
/

The output comes back as:

update claim_schema.claim                    *

ERROR at line 1:

ORA-01031: insufficient privileges

She gets an insufficient privileges error. To find out the column privileges granted, you could query the data dictionary view dba_col_privs as follows:

* find_col_privs.sql

--**********************************************
--
--   Copyright © 2003 by Rampant TechPress Inc.
--
--   Free for non-commercial use.
--   For commercial licensing, e-mail info@rampant.cc
--
-- *********************************************

select
    owner,
    table_name,
    column_name,
    grantor,
    privilege,
    grantable

 from
    dba_col_privs
 where
    grantee = 'JUDY'
/

The output may look like this:

OWNER        TABLE_ COLUMN_NAME GRAN  PRIVILEGE  GRA
------------ ------ ----------- ----  ---------- ---
CLAIM_SCHEMA CLAIMS PAID_AMOUNT JUDY  INSERT     NO
CLAIM_SCHEMA CLAIMS PAID_AMOUNT JUDY  UPDATE     NO

Note the column COLUMN_NAME. It indicates which column has been granted the privilege to the grantee. Just like the above discussion on object privileges, the privileges can be granted with GRANT OPTION. If someone other than the owner grants it, the GRANTOR column will be different.

In addition to the UPDATE privileges, the column level privileges can be set on INSERT and REFERENCES statements. If set on INSERT, the grantee can insert some value into those columns only; all other columns will be merely defaulted or left with null values. Setting this on REFERENCES limits the grantee's ability to create foreign key constraints against those columns only.

* For HIPAA requirements, one of the key things is to protect the Protected Health Information (PHI). This can be easily met by protecting the PHI columns on the tables with the INSERT and UPDATE column privileges, as described.

System Privileges

The preceding section talked about the privileges required for the regular users to access certain specific objects in some specific manner, e.g. the user JUDY can SELECT from table MEMBERS, but not UPDATE it. However, they can do that only when they have privileges to get into the database in the first place. Similarly, the user can create a table, but we can control which tablespace she can create the table on, etc.

These types of privileges, which are not tied to any specific objects, are known as system privileges. Oracle has several system privileges, as described in the Administrators Manual of the Oracle documentation. We will not repeat the information. In this section we will discuss only a few of the system privileges related to the security of the database as defined in table 4.6.

PRIVILEGES	DESCRIPTION
CREATE SESSION	This allows a user to connect to the database, but that is all this privilege can do. It does not allow creation of any objects, nor does it allow the reading of data. All users who need a connection to the database need this privilege.
ALTER SESSION	Typically, this privilege is not necessary in regular operation. Users can issue session altering statements such as ALTER SESSION SET NLS_DATE_FORMAT without needing this privilege. Therefore, this is not necessary. The only time it is needed is when the session has to write something on the server side filesystem, such as in ALTER SESSION SET SQL_TRACE = TRUE statement.
RESTRICTED SESSION	When a database is started in restricted mode, typically to conduct some maintenance operations, regular users are not allowed to connect. However some non-DBA users can be allowed to connect during this restricted session status; and this system privilege allows that. Suppose a report program uses an id called REPUSER that has only SELECT privileges on the tables, not any modification privileges. This user can be allowed to connect during the time when some maintenance is going on since the user does not modify anything. However, issue this privilege if and only if you determine that the user does not do something that is consistent with the maintenance activities.
CREATE DATABASE LINK	This allows the user to create database links. Typically, you would give these privileges very selectively, only to those users who might create private database links to other databases. However, good security models suggest that PUBLIC database links be created by the SYS user instead, without password, whenever possible.

CREATE TABLE,
CREATE INDEX, CREATE SEQUENCE,
CREATE VIEW, CREATE SNAPSHOT, etc.

Create Table privilege, as the name suggests, allows the user to create a table. However, it does not allow the creation in a tablespace where the user does not have a quota. We will discuss more about quotas later. In the meantime, this privilege should be granted only to the users who will have schema objects. Typically, in an application, a specific user or a group of users own the table, and all other users are given access to them. Only the former, never the latter, should be given these privileges.

CREATE ANY TABLE,
CREAT EANY VIEW, etc.

Note the keyword ANY before the object type. This allows the user to create an object of that type in any schema, not just his or her schema. For example, if JUDY is given this privilege like:

Grant create any table to JUDY;

Judy can now create a table like

Create table NATHAN.CLAIMS (..);

Note the table was created as owned by Nathan, not Judy. This is a very powerful privilege; Judy can even create objects owned by the SYSTEM user! Needless to say, never grant this privilege to anyone.

UNLIMITED TABLESPACE

This privilege allows the user to create objects without any boundaries in any tablespace, including the SYSTEM tablespace. This should not be given to any user.

SELECT ANY TABLE

This privilege allows the user to select from any table/view owned by any user, including the data dictionary tables and views. Obviously, this creates several security holes as the user can see data that could be sensitive. Therefore, this privilege should not be given out without a proper analysis. In most cases, this privilege is never required and should not be given. If a user truly has to select from other users' tables, say, for reporting, then explicit grants should be given to the user on those objects.

SELECT ANY

DICTIONARY

This privilege, new in Oracle 9i, allows the user to select any data dictionary table. This may not be necessary in any normal case. Suppose you want to create a user for a performance-monitoring tool you installed. This tool will have to query all the data dictionary views such as v$sysstat to get the performance metrics. Earlier, the SELECT ANY TABLE privilege was needed, but not anymore. The SELECT ANY DICTIONARY privilege allows the user to select any dictionary table and at the same time protect sensitive database tables.

Table 4.6 Important System Privileges

The system privileges are granted by the following command.

GRANT CREATE TABLE TO JUDY;

The privileges can be retracted using:

REVOKE CREATE TABLE TO JUDY;

 

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.