Oracle Action and SES_ACTION 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.
 

---

Important Point about Action and Ses_Action

Sometimes the record may show a different value when Action = SELECT and the SES_ACTION field is null. Additionally, another record may exist for another table with SES_ACTIONS set to “---------S---------“, which also indicates SELECT (S in 10th place). This could appear confusing. The ACTION field has the value of SESSION REC in the second case.

This can be easily understood by the applicability of audit in various situations. If a table is specifically being audited for SELECT, as in Object Auditing, a record with ACTION value SELECT will be generated. That is not statement auditing, therefore the SES_ACTIONS field is left null. In the second case, the auditing is of Statement in nature, with the statement SELECT TABLE being audited, therefore ACTION shows a more general SESSION REC and the SELECT tab on the SES_ACTIONS is set to S.

COMMENT_TEXT – This holds miscellaneous comments on the connection or the session, more specifically, the connection information on the session, i.e. how the user was authenticated. A typical line may look like:

Authenticated by: DATABASE; Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=x.y.z.w)(PORT=1791))

Let’s analyze the various parts of this message. It shows the user was connected through database authentication and shows the client address (masked in this example). Note the port number is 1791. We don’t have such a port defined; the only defined ports are 1521 and 1526. Where did it come from? It comes from the dynamic ports allocated by the listener. This phenomenon has been described in depth in the Chapter on Network Security.

ACTION and ACTION_NAME – These hold the Action# and the decoded Action Description as defined in the table audit_actions. The following Table 8.4 has a list of the actions audited.

ALTER CLUSTER

ALTER DATABASE

ALTER DIMENSION

ALTER FUNCTION

ALTER INDEX

ALTER JAVA

ALTER OPERATOR

ALTER OUTLINE

ALTER PACKAGE

ALTER PACKAGE BODY

ALTER PROCEDURE

ALTER PROFILE

ALTER RESOURCE COST

ALTER ROLE

ALTER ROLLBACK SEG

ALTER SEQUENCE

ALTER SESSION

ALTER SNAPSHOT

ALTER SNAPSHOT LOG

ALTER SUMMARY

ALTER SYSTEM

ALTER TABLE

ALTER TABLESPACE

ALTER TRIGGER

ALTER TYPE

ALTER TYPE BODY

ALTER USER

ANALYZE CLUSTER

ANALYZE INDEX

ANALYZE TABLE

ASSOCIATE STATISTICS

AUDIT DEFAULT

AUDIT OBJECT

CALL METHOD

COMMENT

COMMIT

CREATE CLUSTER

CREATE CONTEXT

CREATE CONTROL FILE

CREATE DATABASE

CREATE DATABASE LINK

CREATE DIMENSION

CREATE DIRECTORY

CREATE FUNCTION

CREATE INDEX

CREATE INDEXTYPE

CREATE JAVA

CREATE LIBRARY

CREATE OPERATOR

CREATE OUTLINE

CREATE PACKAGE

CREATE PACKAGE BODY

CREATE PROCEDURE

CREATE PROFILE

CREATE PUBLIC DATABASE LINK

CREATE PUBLIC SYNONYM

CREATE ROLE

CREATE ROLLBACK SEG

CREATE SCHEMA

CREATE SEQUENCE

CREATE SNAPSHOT

CREATE SNAPSHOT LOG

CREATE SUMMARY

CREATE SYNONYM

CREATE TABLE

CREATE TABLESPACE

CREATE TRIGGER

CREATE TYPE

CREATE TYPE BODY

CREATE USER

CREATE VIEW

DELETE

DISABLE ALL TRIGGERS

DISABLE TRIGGER

DISASSOCIATE STATISTICS

DROP CLUSTER

DROP CONTEXT

DROP DATABASE LINK

DROP DIMENSION

DROP DIRECTORY

DROP FUNCTION

DROP INDEX

DROP INDEXTYPE

DROP JAVA

DROP LIBRARY

DROP OPERATOR

DROP OUTLINE

DROP PACKAGE

DROP PACKAGE BODY

DROP PROCEDURE

DROP PROFILE

DROP PUBLIC DATABASE LINK

DROP PUBLIC SYNONYM

DROP ROLE

DROP ROLLBACK SEG

DROP SEQUENCE

DROP SNAPSHOT

DROP SNAPSHOT LOG

DROP SUMMARY

DROP SYNONYM

DROP TABLE

DROP TABLESPACE

DROP TRIGGER

DROP TYPE

DROP TYPE BODY

DROP USER

DROP VIEW

ENABLE ALL TRIGGERS

ENABLE TRIGGER

EXECUTE PROCEDURE

EXECUTE TYPE

EXPLAIN

GRANT OBJECT

GRANT ROLE

INSERT

LOCK

LOGOFF

LOGOFF BY CLEANUP

LOGON

NETWORK ERROR

NOAUDIT DEFAULT

NOAUDIT OBJECT

NO-OP

PL/SQL EXECUTE

RENAME

REVOKE OBJECT

REVOKE ROLE

ROLLBACK

SAVEPOINT

SELECT

SESSION REC

SET ROLE

SET TRANSACTION

SYSTEM AUDIT

SYSTEM GRANT

SYSTEM NOAUDIT

SYSTEM REVOKE

TRUNCATE CLUSTER

TRUNCATE TABLE

UPDATE

UPDATE INDEXES

USER COMMENT

VALIDATE INDEX

Table 8.4 Auditable Actions

The following views are based on the dba_audit_trail view. All these views have some columns of the view dba_audit_trail, and the column names and their explanations are the same as the parent view.

user_audit_trail This view is based on all the columns of the dba_audit_trail view, but presents information on the current logged in user only. Unlike other data dictionary view, there is no equivalent all_audit_trail view, as it is not relevant in this case. This view contains those records from dba_audit_trail for which the object owner or the audited user is the logged in user.

dba_audit_exists This is an audit trail of the entries where the operation failed due to non-existing objects. This is actually filtered from the view dba_audit_trail where the return code is something other than zero. Only the following return codes are captured in this view.

1, 942, 943, 951, 955, 957, 959, 1418, 1430, 1432, 1433, 1434, 1435, 1452, 1471, 1534, 1535, 1543, 1758, 1917, 1918, 1919, 1920, 1921, 1922, 2019, 2024, 2239, 2264, 2266, 2273, 2289, 2292, 2297, 2378, 2379, 2382, 4042, 4043, 4080, 4081, 12006 and 12325

Some of these error codes are well known, e.g.1 indicates Unique Key violation, 942 indicates invalid table name or lack of privileges to access the table/view, etc.

dba_audit_session This records all activities where the users connected and disconnected from the database. It also records if the action failed due to various reasons such security violations or invalid passwords. These audit records are indicated by action#s 100, 101 and 102 in the table aud$, also known as SESSION REC as a descriptive name.

Example"

The following query shows the clients that could not be connected to the database and the Oracle error.

select
   os_username,
   username,
   userhost||’-‘||terminal client,
   action_name,
   returncode
from
   dba_audit_session
where
   returncode != 0
/

The output comes back as:

OS_USERNAME USERNAME     CLIENT     ACTION_NAME RETURNCODE
----------- ------------ ---------- ----------- ----------
jdoe        CLAIM_SCHEMA cap1-pts/5 LOGON             1017

Here we see that the operating system user jdoe tried to login as CLAIM_SCHEMA from the terminal pts/5 on machine cap1. The return code was 1017, which is the Oracle error for "invalid username/password; logon denied". This proves that the user supplied a wrong password for CLAIM_SCHEMA. Does this smell of attempted break-in? It could. There could be a simple explanation – the user forgot the password of CLAIM_SCHEMA and at the second attempt provided the correct one. A series of repeated attempts, however, would arouse suspicion.

Another thing to note here is the OS user jdoe was doing this. Is jdoe authorized to connect to CLAIM_SCHEMA? If jdoe is a DBA, or an application owner, this may not arouse any suspicion, but if that user is really a claim analyst, he or she has no reason to connect to the CLAIM_SCHEMA user, and this event certainly needs more investigation.

dba_audit_statement This view contains information where the user entered statements that did not particularly access the data inside an object, e.g. ALTER SYSTEM, GRANT, REVOKE on objects, etc. Here is a complete list of the statements that are captured in this view:

ACTION#	STATEMENT
17	GRANT OBJECT
18	REVOKE OBJECT
30	AUDIT OBJECT
31	NOAUDIT OBJECT
49	ALTER SYSTEM
104	SYSTEM AUDIT
105	SYSTEM NOAUDIT
106	AUDIT DEFAULT
107	NOAUDIT DEFAULT
108	SYSTEM GRANT
109	SYSTEM REVOKE
114	GRANT ROLE
115	REVOKE ROLE

dba_audit_object This view shows all the audit records that were based on database object accesses – e.g. a user selected from some table, or altered another table, etc. It has some columns of the dba_audit_trail view, filtered by the actions that are related to objects only.

 

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.