Oracle Security Tips by
This is an excerpt from the
bestselling book "Oracle
Privacy Security Auditing", a complete Oracle security reference
with working Oracle security scripts.
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:
claim_amount = 10000
claim_id = 1234
The output comes
ERROR at line 1:
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:
-- Copyright © 2003 by Rampant TechPress Inc.
-- Free for non-commercial use.
-- For commercial licensing, e-mail
grantee = 'JUDY'
The output may look
TABLE_ COLUMN_NAME GRAN PRIVILEGE GRA
------------ ------ ----------- ---- ---------- ---
CLAIM_SCHEMA CLAIMS PAID_AMOUNT JUDY INSERT
CLAIM_SCHEMA CLAIMS PAID_AMOUNT JUDY UPDATE
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
* 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.
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.
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
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.
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
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 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
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.
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
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
GRANT CREATE TABLE
The privileges can be retracted using:
REVOKE CREATE TABLE
||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.