Oracle Security Privileges on Columns of Tables Granted 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.
 

---

Privileges on Columns of Tables Granted

In this section, we will see the privileges on column level security granted to users. Although this type of grant can be made to users as well as roles, we will show them both in a single output as follows.

Included is a column for GRANTOR, i.e. who granted the privilege, if it is someone other than the owner.

System Privileges Granted to Users

This shows the system privileges, not object privileges, granted to the users directly, not through any roles, etc. The following is a partial output.

 

GRANTEE              PRIVILEGE                 ADM
-------------------- ------------------------- ---
ALI                  CREATE SESSION            NO
ALI                  UNLIMITED TABLESPACE      NO
ANANDA               AUDIT ANY                 NO
ANANDA               DROP ANY TABLE            NO
ANANDA               GRANT ANY PRIVILEGE       NO
ANANDA               UNLIMITED TABLESPACE      NO
ANDREY               CREATE SESSION            NO
ANONYMOUS            CREATE SESSION            NO
APPUSER              CREATE SESSION            NO
APPUSER              UNLIMITED TABLESPACE      NO
BHC                  CREATE SESSION            NO
BHC                  UNLIMITED TABLESPACE      NO

In addition to the user who has been granted the privilege (GRANTEE) and the privilege granted, the output also shows if the privileges are granted with ADMIN_OPTION. If it’s YES, then the grantee can grant the same privilege to another user. In an ideal situation, you would never have a user with a privilege that can be administered by that user.

System Privileges Granted to Users through Roles

In the previous section, we saw the privileges granted to users directly. However, they can also be granted some privileges through roles. This sections shows which privileges have been granted to users via roles. Here is a partial output from the section.

GRANTEE      THROUGH_ROLE PRIVILEGE                 ADM DEF
------------ ------------ ------------------------- --- ---
BHC          CONNECT      ALTER SESSION             NO  YES
BHC          CONNECT      CREATE CLUSTER            NO  YES
BHC          CONNECT      CREATE DATABASE LINK      NO  YES
BHC          CONNECT      CREATE SEQUENCE           NO  YES
BHC          CONNECT      CREATE SESSION            NO  YES
BHC          CONNECT      CREATE SYNONYM            NO  YES
BHC          CONNECT      CREATE TABLE              NO  YES
BHC          CONNECT      CREATE VIEW               NO  YES
BHC          RESOURCE     CREATE CLUSTER            NO  YES
BHC          RESOURCE     CREATE INDEXTYPE          NO  YES

In addition to the usual columns showing the users and the privileges, we can see here whether the privileges can be granted to others (ADMIN_OPTION). The role through which these privileges have come to the users is also shown here (THROUGH_ROLE). Finally, the last column shows if the role is default for the user, i.e. will be active after logging in.

Quota on Tablespaces Granted to Users

This is not strictly a privilege, but is important to list as a part of the overall privilege management system. The following is a partial output from this section.

USERNAME             TABLESPACE_NAME   USED_BYTES MAX_BYTES
-------------------- ----------------- ---------- ---------
HR                   EXAMPLE            1,638,400 UNLIMITED
ODM                  ODM                5,439,488 UNLIMITED
ODM_MTR              ODM                4,259,840 UNLIMITED
OE                   EXAMPLE            6,946,816 UNLIMITED
OEM_ANANDA           OEM_REPOSITORY    33,488,896 UNLIMITED
OEM_ANANDA           TEMP                       0 UNLIMITED
OLAPSYS              CWMLITE           13,303,808 UNLIMITED
PERFSTAT             USERS                589,824 UNLIMITED
PM                   EXAMPLE            3,342,336 UNLIMITED
QS                   EXAMPLE            1,572,864 UNLIMITED
QS_ADM               EXAMPLE                    0 UNLIMITED
QS_CB                EXAMPLE                    0 UNLIMITED
QS_CBADM             EXAMPLE              851,968 UNLIMITED
QS_CS                EXAMPLE              655,360 UNLIMITED
QS_ES                EXAMPLE            1,179,648 UNLIMITED
QS_OS                EXAMPLE            1,179,648 UNLIMITED
QS_WS                EXAMPLE            1,179,648 UNLIMITED
SECUSER              USERS                458,752 UNLIMITED
SH                   EXAMPLE          125,763,584   102,400

It shows the allowed quota on the tablespaces (MAX_BYTES) for the users, and how much of the tablespace is filled up by the user (USED_BYTES). If the quota is unlimited, that is shown.

View table_privileges

There is a view named table_privileges, to show different permissions for various objects. Unfortunately this view is de-supported, but is available as of Oracle 9.2 for backward compatibility with Oracle 6. This view may actually provide an easier to understand portrayal of the permissions and grants than that of the dba_tab_privs view. It has been explained here, in addition to dba_tab_privs, for the sake of completeness. Please note that in future versions, this may not exist.

The following table shows the columns of the view table_privileges.

COLUMN	DESCRIPTION
GRANTEE	The user or the role to whom the privilege has been granted.
OWNER	The owner of the table object on which the privilege is granted.
TABLE_NAME	The name of the object. As it has been seen before, this column name is actually a misnomer. It shows the name of the object, which may be a table, a view, a procedure, or any other type of object, not necessarily a table.
GRANTOR	The user who granted the privilege to the grantee. It is not necessarily the same as the object owner.
SELECT_PRIV	Shows if Select Privilege is granted.
INSERT_PRIV	Shows if Insert Privilege is granted.
DELETE_PRIV	Shows if Delete Privilege is granted.
UPDATE_PRIV	Shows if Update Privilege is granted.
REFERENCES_PRIV	Shows if References Privilege is granted. Used for creating Referential integrity constraints on the table.
ALTER_PRIV	Shows if Alteration Privilege is granted.
INDEX_PRIV	Shows if Index Creation Privilege is granted.

Table 10.1 Columns of View table_privileges

Using the script check_grants.sql or its sections separately, we can quickly create a baseline of the privileges that exist in the database. These scripts can be periodically run, and the output generated can be preserved, after being certified by the auditors. This alone satisfies a number of HIPAA regulations.

However, this is, as already said, just a baseline. There is nothing that prevents an authorized user from altering these grants after the baseline is generated. Therefore, the auditor somehow has to guarantee that the grants are not altered after they are issued. Even if they are altered, they must be audited so that the changes can be reconciled. The next section discusses how to track (or audit) the changes to the grants.

 

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.