Oracle Security: A Definition in Plain English, Part 4

This is part 4 of a 3 part series. Heh I guess this qualifies as a little lagniappe on security and audit. I could have called this Oracle Audit but I already spoke about audit in Part 3. I just decided to continue on in the series.

This is a brief, technical discussion about the implementation of audit in Oracle 10g. I’ll quickly show how to turn on standard audit and how to see some of the audit taking place. I’ll follow that with an implementation of FGA and finally a trigger based audit.


One aspect of auditing is to audit your administrators. This is a log to track what your DBA type folks might be doing in the database. You have to allow your DBAs full access to your database. Trying to hide things from them is nearly impossible and will hurt you when they try to debug system issues. The best thing to do is audit them.

Sys (meaning the sys database user and SYSDBA) auditing will record every SQL run by anyone connected as a system DBA user.

To turn on audit in the database you use an initialization parameter: AUDIT_SYS_OPERATIONS. Whenever this parameter is changed, the database must be stopped and restarted. To ensure that the parameter is not changed, you should have a process in place to verify the parameter on database restart.

Sys audit records are always written to the OS. DBAs can maintain the DB audit tables (as they can get very large, very fast) but you can secure the OS audit log from DBA access. In Windows, the audit log is actually the system event viewer. This is a graphical event log. It’s a nice little feature actually.

Standard Audit
Ok. Now on to standard audit. Standard audit is the ability to audit a users object access and statement use. You can also audit on system privileges but I have not found that to be very useful. In general, for basic audit and compliance, you will use object audits.

An object audit is auditing everyone’s access to a particular table or view. In my example, I will use the EMP table in the SCOTT schema.

Before we can audit records, we must turn on the audit trail. You turn on the audit trail by setting an initialization parameter, AUDIT_TRAIL. This is another parameter that requires the database to be restarted. You can choose to audit to a table in the database or to an OS file. I chose database:

SQL> alter system set audit_trail = DB scope = spfile;

Once you’ve set the parameter, you can issue audit statements.

SYS-SQL> audit insert, delete on scott.emp by access;

This statement will audit any insert or delete statements against the EMP table, which is owned by scott.

If I insert a record as SCOTT:
SCOTT-SQL> insert into emp (empno) values (-1)
/

I can query the audit trail:
SYS-SQL> /

NAME ACTION OBJNAME
———- ———- ———-
SCOTT INSERT EMP

I update the table as SCOTT:
SCOTT-SQL> update emp set ename = ‘lewis’
2 where empno = -1;

1 row updated.

There is no change to the audit as I am not auditing update:
SYS-SQL> /

NAME ACTION OBJNAME
———- ———- ———-
SCOTT INSERT EMP

Now, try a delete:
SCOTT-SQL> delete from emp where empno = -1;

1 row deleted.

Rollback the delete.
SCOTT-SQL> rollback;

Rollback complete.

And even though we rolled back the change, we still have the delete entry:
SYS-SQL> /

NAME ACTION OBJNAME
———- ———- ———-
SCOTT INSERT EMP
SCOTT DELETE EMP

If you’re wondering what query I was running, it is:

select substr(userid,1,10) name,
substr(name,1,10) action,
substr(obj$name,1,10) objName
from aud$, audit_actions
where action = action#
/

Instead of this query, you can use views provided by oracle. There are several and they are well documented.

When you are auditing, you can chose to audit by session or by access. If you audit by session, you create a single record for each DISTINCT action a user takes. If you audit by access, you create an audit record for every occurrence of an action.

FGA
And now, on to FGA. As a refresher, FGA stands for Fine Grained Audit. FGA is context auditing, which is auditing based on the values in columns. If you read my second security entry, you are familiar with FGAC. FGA is implemented a lot like FGAC.

You define a policy,

begin
DBMS_FGA.ADD_POLICY(object_schema => ‘SCOTT’, object_name => ‘EMP’, policy_name => ‘scott_emp_sal’, audit_condition => ‘sal> 2500’, statement_types => ‘insert,update,delete,select’);
end;
/

Any access on the EMP table that accesses a record with a SAL > 2500 will generate an audit record in the DBA_FGA_AUDIT_TRAIL view. So, this command by SCOTT:

SCOTT-SQL> select ename, job, sal
2 from emp
3 where ename = ‘TURNER’;

ENAME JOB SAL
———- ——— ———-
TURNER SALESMAN 1500

Generates no audit record, while the following query does generate an audit record:

SQL> select ename, job, sal
2 from emp
3 where ename = ‘FORD’;

ENAME JOB SAL
———- ——— ———-
FORD ANALYST 3000

SYS-SQL> select substr(db_user,1,15) username,
2 substr(object_name,1,10) objname,
3 substr(policy_name,1,15) policyname,
4 statement_type
5 from dba_fga_audit_trail
6 /

USERNAME OBJNAME POLICYNAME STATEME
————— ———- ————— ——-
SCOTT EMP SCOTT_EMP_SAL SELECT

The reason this generated a statement and the previous did not is based on the policy. The EMP with a SAL > 2500 generated an audit record. You do not have to access the policy column. The ploicy is evaluated regardless of what columns are accessed.

Optionally, when you define a policy, you can include a stored procedure. This procedure can then store additional information from the environment or generate an alert. The procedure is standard PL/SQL.

Trigger-based Audit
Finally, on to trigger based auditing. A trigger is a stored procedure that is tied to an object. An insert trigger on a table would execute every time an insert happened to that table. If we wanted to audit the new and old values of SAL in the emp table, we would define an audit table and a trigger on EMP.

SCOTT-SQL> CREATE TABLE emp_aud (
2 empno number,
3 old_sal number,
4 new_sal number )
5 /

Table created.

SCOTT-SQL> CREATE OR REPLACE TRIGGER emp_aud_trg
2 BEFORE UPDATE OF SAL ON EMP
3 FOR EACH ROW
4 BEGIN
5 INSERT INTO EMP_AUD
6 (EMPNO, OLD_SAL, NEW_SAL)
7 VALUES (:NEW.EMPNO, :OLD.SAL, :NEW.SAL);
8 END;
9 /

Trigger created.

SCOTT-SQL> select * from emp_aud;

no rows selected

SCOTT-SQL> insert into emp (empno,sal)
2 values (-1, 400);

1 row created.

SCOTT-SQL> select * from emp_aud;

no rows selected

SCOTT-SQL> update emp
2 set deptno = 10
3 where empno = – 1;

1 row updated.

SCOTT-SQL> select * from emp_aud;

no rows selected

SCOTT-SQL> update emp
2 set sal = 1500
3 where empno = -1;

1 row updated.

SCOTT-SQL> select * from emp_aud;

EMPNO OLD_SAL NEW_SAL
———- ———- ———-
-1 400 1500

SCOTT-SQL>

You should not grant any permissions on the audit table to anyone. Only SCOTT would have permission to view or change the audit table. By granting permissions to access the EMP table, you are implicitly granting permissions on the trigger.

That’s it. As you can see, standard audit requires no code and very little administration. FGA requires some code if you need to take special action but requires very little administration. Triggers require a lot of code and a lot of administration (whenever an object changes). When you can, opt for standard audit. When that doesn’t work, try FGA. Finally, use triggers when that is the only way to meet your requirement.

Due to the nature of blogging, I’ve kept this as brief as I could. I hope I made it plain how easy it is to meet your Audit requirements in Oracle. If you have any questions or comments, please ask!

If you would like to read more of the details on Oracle and Security, Oracle maintains a document, Oracle Database Security Guide, describing all of this functionality in great detail. Oracle also provides all of it’s documentation, for free, on OTN.

That’s it for me today.

Lewis

Technorati : , , , , , , , , , , , , , ,

You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed.

Comments are closed.