Oracle Security Tracking Grants 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.
 

---

Tracking Grants

The simplest way to track the grant and revocation of privileges is by auditing the GRANT statement itself. For instance, if we wanted to track all types of grants on the table CLAIMS, we can issue

audit grant on claim_schema.claims by access;

This audits all grants made by a non-SYS user on the table claims owned by CLAIM_SCHEMA. To confirm the setting, check the view dba_obj_audit_opts as follows.

select GRA
from dba_obj_audit_opts
where and owner = ‘CLAIM_SCHEMA’
and object_name = 'CLAIMS'
/

This query returns

GRA
---
S/S

It means the grants are audited whether successful or not. In this setting, if the user CLAIM_SCHEMA issues the following statement to grant selection on this table to JUDY:

grant select on claims to judy
/

This grant will trigger an audit record to be generated and placed in the aud$ table. The record is visible from the dba_audit_trail view, too, but there is a special view named dba_audit_statement that is used solely for this purpose. To see this audit, we will issue

select
   timestamp,
   username,
   action_name,
   obj_privilege, owner, obj_name, grantee from
 dba_audit_statement
/

The query returns:

TIMESTAMP USERNAME     ACTION_NAME    OBJ_PRIVILEGE
--------- ------------ -------------- ----------------
OWNER            OBJ_NAME         GRANTEE
---------------- ---------------- --------------------

20-AUG-03 CLAIM_SCHEMA GRANT OBJECT   ---------Y------
CLAIM_SCHEMA     CLAIMS           JUDY

Note the various columns of the result. They show that the user CLAIMS_SCHE MA (Column: USERNAME) granted some object privilege (Column: ACTION_NAME) on CLAIM_SCHEMA’s object CLAIMS (Column: OBJ_NAME) to user JUDY (Column: GRANTEE).

The query answered the most of the essential questions.  However, note that while it accurately recorded that a user did some object grant, it did not say which specific grant. The column that shows this is OBJ_PRIVILEGE.

This column shows a value "---------Y------" a series of hyphens with a single Y in between. This set of characters show which type of grant was performed on this table. This shows Y in the 10th position, meaning the grant was on SELECT statement. The above example would be deciphered as the user issued a grant select on table CLAIMS to JUDY.

The position of the characters in the string shown by OBJ_PRIVILEGE identifies the type of grant. Table 10.2 shows the position and what each position represents.

POSITION	AUDITED STATEMENT
1	ALTER
2	AUDIT
3	COMMENT
4	DELETE
5	GRANT
6	INDEX
7	INSERT
8	LOCK
9	RENAME
10	SELECT
11	UPDATE
12	REFERENCES
13	EXECUTE

Table 10.2 Significance of Characters in OBJ_PRIVILEGE

If the position contains a hyphen, the corresponding statement was not granted. A value of Y indicates it was granted and a value of N indicates it was revoked.

If multiple privileges are granted on the table, then each of the corresponding characters in the string are populated. For instance, if the owner grants SELECT, INSERT and DELETE to JUDY in one statement, the audit record will look like

---Y--Y--Y------

Here, the positions 4, 7 and 10 are populated, indicating that the statement granted the privileges of DELETE, INSERT and SELECT.

If a user who has the GRANT ANY OBJECT PRIVILEGE grants the privileges, then the audit record shows that fact, too. In this example, the user ANANDA has the above-mentioned system privilege and it issues:

grant select on claim_schema.claims to judy
/

Checking the audit records:

select priv_used from dba_audit_statement
/

We see:

PRIV_USED
----------------------------------------
GRANT ANY OBJECT PRIVILEGE

Note how the column value clearly shows which privilege was used, i.e. GRANT ANY OBJECT PRIVILEGE. When the user CLAIM_SCHEMA issues the same grant statements, this column, PRIV_USED, is not populated, simply because the user granted on his or her own table, so no special privileges were necessary. Therefore, the PRIV_USED column provides valuable information required to firmly establish accountability.

Table 10.3 below shows the various columns of the view dba_audit_statement and their descriptions.

COLUMN	DESCRIPTION
OS_USERNAME	The Operating System username
USERNAME	The database username
USERHOST	The host name from where the user is connected.
TERMINAL	The terminal number. In case of Windows, this shows the machine name. The USERHOST column shows nothing.
TIMESTAMP	The Timestamp when it occurred.
OWNER	The owner of the object on which the audit occurred.
OBJ_NAME	Name of the object on which the auditing occurred. However, this also shows non-objects such as roles, as we will see later.
ACTION_NAME	The action performed that triggered the audit. The action may not be complete, i.e. it might show GRANT OBJECT that indicates some type of object privilege (select, insert, etc.) was issued on the object. The actual grant is shown in the OBJ_PRIVILEGE column and the object is shown in the OBJ_NAME column.
NEW_NAME	If the object is renamed, and the operation is audited, this column shows the new name.
OBJ_PRIVILEGE	The Object Privilege granted that triggered the audit. It has been explained earlier in detail.
SYS_PRIVILEGE	If the auditing event triggered by granting a System Privilege, then the privilege is shown here.
ADMIN_OPTION	If the grant is given with an ADMIN OPTION clause, this column is populated.
GRANTEE	The user or role this privilege was granted or revoked from.
AUDIT_OPTION	Audit option set with the statement.
SES_ACTIONS	Session Actions, in the format similar to OBJ_PRIVILEGE.
COMMENT_TEXT	If this field is populated, it yields some information about the way the user was authenticated.
SESSIONID	The auditing session id as selected from v$session view.
ENTRYID	The entry id inside a single session, relevant only if the session has more than a single entry.
STATEMENTID	The statement inside a session. One statement may cause multiple actions.
RETURNCODE	The result of the action. Typically it’s zero (0), for a successful action. If the action fails due to some reason, it’s set to the Oracle error number.
PRIV_USED	If the privilege used by the user is something special, then it’s mentioned here. For instance, is user SCOTT drops his own table EMP, there is no special privilege required, and this field is left null. But if user FRED, who has DROP ANY TABLE privilege, drops Scott’s table EMP, then the special privilege is used, and is recorded in this column.
CLIENT_ID	If the session sets the client identifier using DBMS_APPLICATION_INFO, it’s shown here.
SESSION_CPU	The amount of CPU consumed by the session.

Table 10.3 Columns of View dba_audit_statement

The view captures audit records only on certain types of statements. As you can see, the view is specifically for grants and audit related records, the kind we are interested in.

ALTER SYSTEM

AUDIT DEFAULT

AUDIT OBJECT

GRANT OBJECT

GRANT ROLE

NOAUDIT DEFAULT

NOAUDIT OBJECT

REVOKE OBJECT

REVOKE ROLE

SYSTEM AUDIT

SYSTEM GRANT

SYSTEM NOAUDIT

SYSTEM REVOKE

Please note: all types of audit records are visible in the view dba_audit_trail, regardless of their nature.

Tracking Role Grants

A special type of auditing is required for granting roles to users. The auditing event is known as SYSTEM GRANT, given as follows.

audit system grant by access;

This tracks all role grants to users. For instance, is the user Nathan is granted the role JUNIOR_CLAIM_VIEWER as:

grant JUNIOR_CLAIM_VIEWER to nathan;

The action is captured in the audit records and can be accessed by issuing:

select
   timestamp,
   username,
   action_name,
   obj_name,
   grantee,
   priv_used
from dba_audit_statement
/

The results come out as:

TIMESTAMP USERNAME                       ACTION_NAME
--------- ------------------------------ -----------
OBJ_NAME
----------------------------------------------------
GRANTEE                        PRIV_USED
------------------------------ ---------------------
25-AUG-03 ANANDA               GRANT ROLE       JUNIOR_CLAIM_VIEWER
NATHAN                         GRANT ANY ROLE

Note the important points here – the ACTION_NAME column shows GRANT ROLE, and the role name is shown in the column OBJ_NAME, as the value JUNIOR_CLAIM_VIEWER. Therefore, the column OBJ_NAME does not show only objects, but grants as well. This is an important fact to remember.

These audit trails clearly show when the privileges were granted or revoked, and by whom. Combining this information with the regular audits, we can identify another important requirement of HIPAA and other security regulations, namely whether the user had any possibility of accessing the data at any point in time. It will also capture any malicious activity in the past. For instance, the user APPUSER is not expected to update the table CLAIMS. However, due to an honest mistake or a deliberate act, the user was granted the privilege for a very short time, during which the user updated some key values, and then the privilege was revoked. A later analysis will reveal that the user does not have update privileges on CLAIMS, so there would be no questioning the possibility of such an act. Although the AUDIT record will show that the update occurred, the analysis will raise more questions than answers,  due to this ambiguity. However, if the grants auditing is enabled, the act of granting and revoking the privilege will be captured too, and the act can be discovered and explained.

 

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.