Database Support: Security DB Auditing, Consider

Dear Readers,

My name is Franz Devantier, creator of this blog. I am an Oracle Certified Professional (OCP DBA 11g) Security DBA. I will be sharing with you the basic duties of an Oracle DBA, and also some of the undocumented, and not so well known tasks.

I will make a deal with you: If you refer me to a company that needs database support, from a few hours per week to full time, and I am able to sign a contract with them.

Then I will give you 10% of the monthly contract or deal price every month. When the contract ends, and we re-sign the contract, I will again give you 10% of the monthly contract price. This will go on until the company no longer employs or contracts me or my agents to look after their databases.

I can do this, because that 10% is my marketing budget. When we re-sign the contract, in the future, it may depend on you giving the thumbs up again, and that is worth 10% of the monthly contract price, to be given to you as commission.

Contact: Franz

Security DB Auditing, Considerations - Part 2

Audit Records and Audit Trails

Audit records carry lots of information about the operation that was audited. The type of information includes the user details, date/time stamp, etc. You can store the audit records in the database audit trail (AUD$) or in the operating system audit trail or files.

There are two types of auditing:

-> Standard Auditing, which is based on privileges, schemas, objects, and statements

-> Fine-Grained Auditing (FGA), which can audit down to the column level.

The standard audit records are written to the DBA_AUDIT_TRAIL (sys.aud$), or to the operating system. Fine grained audit records are written to the DBA_FGA_AUDIT_TRAIL (sys.fga_log$). The view DBA_COMMON_AUDIT_TRAIL, combines the standard and the fine-grained audit records.

Database Audit Trail (DBA_AUDIT_TRAIL)

The database audit trail consists of a single table named SYS.AUD$. There are a few predefined views that are provided to help you to use the information in this table.

SQL> SELECT view_name

2 FROM dba_views

3 WHERE view_name LIKE 'DBA%AUDIT%'

4 ORDER BY view_name;

VIEW_NAME

------------------------------

DBA_AUDIT_EXISTS

DBA_AUDIT_MGMT_CLEANUP_JOBS

DBA_AUDIT_MGMT_CLEAN_EVENTS

DBA_AUDIT_MGMT_CONFIG_PARAMS

DBA_AUDIT_MGMT_LAST_ARCH_TS

DBA_AUDIT_OBJECT

DBA_AUDIT_POLICIES

DBA_AUDIT_POLICY_COLUMNS

DBA_AUDIT_SESSION

DBA_AUDIT_STATEMENT

DBA_AUDIT_TRAIL

DBA_COMMON_AUDIT_TRAIL

DBA_FGA_AUDIT_TRAIL

DBA_OBJ_AUDIT_OPTS

DBA_PRIV_AUDIT_OPTS

DBA_REPAUDIT_ATTRIBUTE

DBA_REPAUDIT_COLUMN

DBA_STMT_AUDIT_OPTS

The audit trail record can contain different types of information, depending on the events audited and the auditing options set.

Columns shown in sys.aud$ or DBA_AUDIT_TRAIL

Column Description (Name)	Also Appears in the Operating System Audit Trail?
Operating system login user name (CLIENT USER)	Yes.
Database user name (DATABASE USER)	Yes.
Session identifier	Yes.
Terminal identifier	Yes.
Name of the schema object accessed	Yes.
Operation performed or attempted (ACTION)	Yes.
Completion code of the operation	Yes.
Date and time stamp in UTC (Coordinated Universal Time) format	Date and time yes, but not in UTC format.
System privileges used (PRIVILEGE)	Yes.
Proxy Session audit ID	No.
Global User unique ID	No.
Distinguished name	Yes.
Instance number	No.
Process number	No.
Transaction ID	No.
SCN (system change number) for the SQL statement	No.
SQL text that triggered the auditing (SQLTEXT)	No.
Bind values used for the SQL statement, if any (SQLBIND)	No.

The columns SQLBIND and SQLTEXT are not populated unless you specify:

AUDIT_TRAIL=DB,EXTENDED for the initialization parameter.

SQL> alter system set AUDIT_TRAIL=DB,EXTENDED scope=spfile;

Then you just restart the database.

CLOB’s are relatively expensive to populate, and so they are not populated by default.

If the database destination for audit records becomes full or is unavailable, then it is unable to accept new records. This results in an error message, and the audit record is not created.

Operating System Audit Trail

Audit records are written to a file outside of the database. The target database varies by platform. On Solaris it is $ORACLE_HOME/rdbms/audit, it may differ on other operating systems. In Windows the information is accessed through Event Viewer.

If the initSID.ora file or the spfile specifies AUDIT_TRAIL=XML, then the audit records are written to the operating system as .XML files. You can access these files from SQL*Plus by querying the V$XML_AUDIT_TRAIL. This view makes XML audit records available to DBA’s. When you query this view, all the files in the AUDIT_FILE_DEST with a .xml extension are parsed and presented in relational table format. XML is a standard document format, and so many utilities are available that can parse and analyze XML data.

An operating system audit trail or file system can also become full, and therefore be unable to accept new records. In a case like this Oracle allows the action to continue, even although the audit record can’t be stored, because the operating system is full. On the other hand with a database audit trail, Oracle prevents the action from completing if the audit record can’t be stored.

System administrators should ensure that the operating system or the file system does not fill completely. Most operating systems will give fair warning, and enough time to take action, for the system administrators, should the operating system start to become full.

Balance the database audit trail, where audited actions can’t complete, if there is no space for the audit record, and Operating system audit records, where the database action can complete if the file system is full. You will need to decide if auditing is more important, or if business continuity is more important.

Syslog Audit Trail

A potential vulnerability for an Operating System Audit trail is that a privileged user, such as a DBA, can modify or delete audit records. Fortunately there is a way to minimize this risk. You can use a syslog audit trail. Syslog is a standard protocol on UNIX/Linux based systems, for logging information from different components. Applications can call the syslog() function to log information to the syslog daemon. The syslog daemon will then determine where to log the information. You can configure syslog in a file named “syslog.conf”. The information can be logged to the console or screen, or even to a remote, dedicated log host. You can also configure syslog to alert a specified set of users when information is logged.

Applications such as an Oracle process, use the syslog() function to log information to the syslog daemon. It is good practice to make sure that privileged users, do not have access or permissions to the file system where the messages are logged. This means that audit records that are stored using the syslog audit trail are potentially more secure than audit records stored using the standard operating system audit trail.

In windows the operating system audit trail is more secure, because audit records can’t be modified directly, but need to be accessed through the Windows Event Viewer.

Operating System and Syslog Audit Records

The operating system as well as syslog audit trails are encoded, except for error messages which are decoded. The audit records are also decoded in data dictionary files.

The following fields are included in the audit trail:

-> Action code: This code describes the operation performed or attempted. A list of these codes with their descriptions can be found in the data dictionary. AUDIT_ACTIONS

SQL> SELECT * FROM AUDIT_ACTIONS;

ACTION NAME

---------- ----------------------------

0 UNKNOWN

1 CREATE TABLE

2 INSERT

3 SELECT

4 CREATE CLUSTER

5 ALTER CLUSTER

6 UPDATE

7 DELETE

8 DROP CLUSTER

9 CREATE INDEX

10 DROP INDEX

11 ALTER INDEX

12 DROP TABLE

13 CREATE SEQUENCE

14 ALTER SEQUENCE

15 ALTER TABLE

16 DROP SEQUENCE

17 GRANT OBJECT

18 REVOKE OBJECT

19 CREATE SYNONYM

20 DROP SYNONYM

21 CREATE VIEW

22 DROP VIEW

23 VALIDATE INDEX

24 CREATE PROCEDURE

25 ALTER PROCEDURE

26 LOCK

27 NO-OP

28 RENAME

29 COMMENT

30 AUDIT OBJECT

31 NOAUDIT OBJECT

32 CREATE DATABASE LINK

33 DROP DATABASE LINK

34 CREATE DATABASE

35 ALTER DATABASE

36 CREATE ROLLBACK SEG

37 ALTER ROLLBACK SEG

38 DROP ROLLBACK SEG

39 CREATE TABLESPACE

40 ALTER TABLESPACE

41 DROP TABLESPACE

42 ALTER SESSION

43 ALTER USER

44 COMMIT

45 ROLLBACK

46 SAVEPOINT

47 PL/SQL EXECUTE

48 SET TRANSACTION

49 ALTER SYSTEM

50 EXPLAIN

51 CREATE USER

52 CREATE ROLE

53 DROP USER

54 DROP ROLE

55 SET ROLE

56 CREATE SCHEMA

57 CREATE CONTROL FILE

59 CREATE TRIGGER

60 ALTER TRIGGER

61 DROP TRIGGER

62 ANALYZE TABLE

63 ANALYZE INDEX

64 ANALYZE CLUSTER

65 CREATE PROFILE

66 DROP PROFILE

67 ALTER PROFILE

68 DROP PROCEDURE

70 ALTER RESOURCE COST

71 CREATE MATERIALIZED VIEW LOG

72 ALTER MATERIALIZED VIEW LOG

73 DROP MATERIALIZED VIEW LOG

74 CREATE MATERIALIZED VIEW

75 ALTER MATERIALIZED VIEW

76 DROP MATERIALIZED VIEW

77 CREATE TYPE

78 DROP TYPE

79 ALTER ROLE

80 ALTER TYPE

81 CREATE TYPE BODY

82 ALTER TYPE BODY

83 DROP TYPE BODY

84 DROP LIBRARY

85 TRUNCATE TABLE

86 TRUNCATE CLUSTER

91 CREATE FUNCTION

92 ALTER FUNCTION

93 DROP FUNCTION

94 CREATE PACKAGE

95 ALTER PACKAGE

96 DROP PACKAGE

97 CREATE PACKAGE BODY

98 ALTER PACKAGE BODY

99 DROP PACKAGE BODY

100 LOGON

101 LOGOFF

102 LOGOFF BY CLEANUP

103 SESSION REC

104 SYSTEM AUDIT

105 SYSTEM NOAUDIT

106 AUDIT DEFAULT

107 NOAUDIT DEFAULT

108 SYSTEM GRANT

109 SYSTEM REVOKE

110 CREATE PUBLIC SYNONYM

111 DROP PUBLIC SYNONYM

112 CREATE PUBLIC DATABASE LINK

113 DROP PUBLIC DATABASE LINK

114 GRANT ROLE

115 REVOKE ROLE

116 EXECUTE PROCEDURE

117 USER COMMENT

118 ENABLE TRIGGER

119 DISABLE TRIGGER

120 ENABLE ALL TRIGGERS

121 DISABLE ALL TRIGGERS

122 NETWORK ERROR

123 EXECUTE TYPE

128 FLASHBACK

129 CREATE SESSION

130 ALTER MINING MODEL

131 SELECT MINING MODEL

133 CREATE MINING MODEL

134 ALTER PUBLIC SYNONYM

157 CREATE DIRECTORY

158 DROP DIRECTORY

159 CREATE LIBRARY

160 CREATE JAVA

161 ALTER JAVA

162 DROP JAVA

163 CREATE OPERATOR

164 CREATE INDEXTYPE

165 DROP INDEXTYPE

166 ALTER INDEXTYPE

167 DROP OPERATOR

168 ASSOCIATE STATISTICS

169 DISASSOCIATE STATISTICS

170 CALL METHOD

171 CREATE SUMMARY

172 ALTER SUMMARY

173 DROP SUMMARY

174 CREATE DIMENSION

175 ALTER DIMENSION

176 DROP DIMENSION

177 CREATE CONTEXT

178 DROP CONTEXT

179 ALTER OUTLINE

180 CREATE OUTLINE

181 DROP OUTLINE

182 UPDATE INDEXES

183 ALTER OPERATOR

192 ALTER SYNONYM

197 PURGE USER_RECYCLEBIN

198 PURGE DBA_RECYCLEBIN

199 PURGE TABLESPACE

200 PURGE TABLE

201 PURGE INDEX

202 UNDROP OBJECT

204 FLASHBACK DATABASE

205 FLASHBACK TABLE

206 CREATE RESTORE POINT

207 DROP RESTORE POINT

208 PROXY AUTHENTICATION ONLY

209 DECLARE REWRITE EQUIVALENCE

210 ALTER REWRITE EQUIVALENCE

211 DROP REWRITE EQUIVALENCE

212 CREATE EDITION

213 ALTER EDITION

214 DROP EDITION

215 DROP ASSEMBLY

216 CREATE ASSEMBLY

217 ALTER ASSEMBLY

218 CREATE FLASHBACK ARCHIVE

219 ALTER FLASHBACK ARCHIVE

220 DROP FLASHBACK ARCHIVE

225 ALTER DATABASE LINK

305 ALTER PUBLIC DATABASE LINK

177 rows selected.

SQL>

So for example, if you suspected that your system would be breached, and the method would be by altering a profile, then you can search for: Action Code 67, which is ALTER PROFILE.

-> Privileges used: This describes any system privileges that are used to perform the operation. The SYSTEM_PRIVILEGE_MAP lists these codes and their descriptions.

SQL> select privilege, name from system_privilege_map order by 1 desc;

PRIVILEGE NAME

---------- ----------------------------------------

-3 ALTER SYSTEM

-4 AUDIT SYSTEM

-5 CREATE SESSION

-6 ALTER SESSION

-7 RESTRICTED SESSION

-10 CREATE TABLESPACE

-11 ALTER TABLESPACE

-12 MANAGE TABLESPACE

-13 DROP TABLESPACE

-15 UNLIMITED TABLESPACE

-20 CREATE USER

…

-> Completion code: describes the result of the attempted operation. Successful operations would return a value of zero. Any unsuccessful operations would return the Oracle error code, which describes why the operation was unsuccessful.

In Unix / Linux you can use the utility # oerr ORA 1555

This will give you a description of the oracle error. In windows this utility does not ship with the Oracle installation.

A workaround that I use, is this simple discreet procedure to return the oracle error:

SQL> Set lines 1000 pages 100 trimspool on long 1000 serveroutput on

SQL> EXECUTE DBMS_OUTPUT.PUT_LINE (SQLERRM (-1555));

ORA-01555: snapshot too old: rollback segment number with name "" too small

PL/SQL procedure successfully completed.

SQL> Set lines 80 trimspool off serveroutput off

Just change the error number to see a different error message.

SQLERRM (-1555)

Change to for example:

SQLERRM (-60)