Posts Tagged auditing

Auditing ALTER SYSTEM commands

A recent OTN Thread inspired this posting: How can we track down initialization parameter changes that someone has done with ALTER SYSTEM? One way would be to monitor the alert.log, but I think auditing should be most appropriate here. Preparing the playing field:

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0    Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> show parameter audit_trail

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
audit_trail                 string     NONE

When you use DBCA to create an 11g Database, AUDIT_TRAIL will show as DB – I did the creation on the command line, therefore it is still NONE. When we want to see the particular ALTER SYSTEM command like in the demonstration, the value EXTENDED is additionally required.

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

System altered.

SQL> shutdown immediate

ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area  313159680 bytes
Fixed Size            2227944 bytes
Variable Size          255852824 bytes
Database Buffers       50331648 bytes
Redo Buffers            4747264 bytes
Database mounted.
Database opened.
SQL> audit alter system by access;

Audit succeeded.

SQL> alter system set processes=250 scope=spfile;

System altered.

SQL> alter system set parallel_degree_policy=limited;

System altered.

SQL> alter system set result_cache_mode=force scope=memory;

System altered.

You may notice that I was still SYS when doing the ALTER SYSTEM commands. That is important here, because they are not audited, as we can see below. If you want auditing for SYS, this posting shows how to do that.

 
SQL> grant dba to adam identified by adam;

Grant succeeded.

SQL> connect adam/adam
Connected.

SQL> alter system set processes=200 scope=spfile;

System altered.

SQL> alter system set parallel_degree_policy=manual;

System altered.

SQL> alter system set result_cache_mode=manual scope=memory;

System altered.

SQL> select username,sql_text from dba_audit_trail where sql_text like 'alter system%';

USERNAME   SQL_TEXT
---------- ----------------------------------------------------------------------
ADAM       alter system set processes=200 scope=spfile
ADAM       alter system set parallel_degree_policy=manual
ADAM       alter system set result_cache_mode=manual scope=memory

Conclusion: ALTER SYSTEM commands can be audited very comfortable into the audit trail as long as you don’t do them as SYS. All actions of SYS can be audited also with a different approach. As always: Don’t believe it, test it :-)

About these ads

, ,

1 Comment

Auditing for Physical Standby Databases with Real-Time Query

Today I got asked by a colleague whether we can do auditing on a Physical Standby that does Real-Time Query. My response was: Probably yes, with AUDIT_TRAIL=OS for the Standby and the audit command specified on the Primary. I did just test that and it really works this way! There is no need to have the AUDIT_TRAIL parameter value different from NONE on the Primary even. Just call a command like

SQL> audit session whenever not successful;

on the Primary and it will reach the Physical Standby via Redo-Apply. I tested that with

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0    Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

but I suppose it will work the same with lower versions. But then: “Don’t believe it, test it!” :-)

, , ,

1 Comment

Fine Grained Auditing and SYS_CONTEXT

For an Oracle Database, we have several ways to audit user activity. With this posting, we will take a closer look at Fine Grained Auditing (FGA), working together with the very useful standard function SYS_CONTEXT. The demonstration is done with 11gR2 but should work very similar with older versions down to 9i, where FGA was introduced, if I recall that right.

For a general overview about auditing, see my presentation slides here.

The standard function SYS_CONTEXT shows us many interesting properties of the connected sessions and is not only useful in the context of auditing:

SQL> connect sys/oracle@orcl as sysdba
Connected.

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0    Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

SQL> set serveroutput on
begin
dbms_output.put_line('AUTENTICATION_METHOD: '||sys_context('userenv','authentication_method'));
dbms_output.put_line('CLIENT IDENTIFIER:    '||sys_context('userenv','client_identifier'));
dbms_output.put_line('CURRENT_SCHEMA:       '||sys_context('userenv','current_schema'));
dbms_output.put_line('CLIENT HOSTNAME:      '||sys_context('userenv','host'));
dbms_output.put_line('INSTANCE:             '||sys_context('userenv','instance_name'));
dbms_output.put_line('CLIENT IP ADDRESS:    '||sys_context('userenv','ip_address'));
dbms_output.put_line('IS SYSDBA:            '||sys_context('userenv','isdba'));
dbms_output.put_line('OS USER:              '||sys_context('userenv','os_user'));
dbms_output.put_line('SERVER HOSTNAME:      '||sys_context('userenv','server_host'));
dbms_output.put_line('USER:                 '||sys_context('userenv','session_user'));
dbms_output.put_line('SESSION ID:           '||sys_context('userenv','sid'));
end;

AUTENTICATION_METHOD: PASSWORD
CLIENT IDENTIFIER:
CURRENT_SCHEMA:       SYS
CLIENT HOSTNAME:      uhesse-PC
INSTANCE:             orcl
CLIENT IP ADDRESS:    555.555.55.55
IS SYSDBA:            TRUE
OS USER:              uhesse-pc\uhesse
SERVER HOSTNAME:      uhesse
USER:                 SYS
SESSION ID:           33

PL/SQL procedure successfully completed.

Notice that there is a difference between “current_schema” and “session_user”: The first would change after an “alter session set current_schema=someotherschema;” but the second stays the same.

Following creates a small demo schema with one table, containing the sensitive column “BUDGET”:

SQL> grant dba to adam identified by adam;
connect adam/adam
create table adam.audept (deptno number, dname varchar2(20),budget number);
insert into adam.audept values (10,'DELIVERY',10000);
insert into adam.audept values (20,'MARKETING',100000);
commit;

End-users connect with the Database user ADAM, using the “Audept Application”. Normally, they do not access the audept table directly. Instead, they use a view that does not show the budget column:

SQL> create or replace view audeptvu as select deptno,dname from audept;

View created.

Our goal is now to audit access on the audept table, but only if the sensitive column “BUDGET” is touched and even then only if the access is not done through the “Audept Application”. This goal cannot be reached with traditional auditing, because this would audit each & every select on the table:

SQL> connect / as sysdba
Connected.
SQL> truncate table aud$;

Table truncated.

AUD$ is the base table for the Data Dictionary View DBA_AUDIT_TRAIL, like FGA_LOG$ is for DBA_FGA_AUDIT_TRAIL. As sys, we can truncate it, which can be monitored as well. A more sophisticated way to restrict and monitor activities of highly privileged users like sys would be the separate products Database Vault (restrict) resp. Audit vault (monitor).

SQL> audit select on adam.audept by access;

Audit succeeded.

SQL> connect adam/adam
Connected.
SQL> exec dbms_session.set_identifier('AUDEPT_APPLICATION')

PL/SQL procedure successfully completed.

SQL> select * from audeptvu;

    DEPTNO DNAME
---------- --------------------
    10 DELIVERY
    20 MARKETING

SQL> select count(*) from dba_audit_trail where obj_name='AUDEPT';

  COUNT(*)
----------
     1

Although the access on the audept table was completely compliant with our “Business Rules”, it was audited by traditional auditing. Therefore, FGA was introduced:

SQL> connect adam/adam
Connected.

SQL> create or replace function auditif
return number
as
begin
if  
sys_context('userenv','session_user')<>'ADAM' 
or 
sys_context('userenv','client_identifier')<>'AUDEPT_APPLICATION' 
or
sys_context('userenv','client_identifier') is null
then return 1;
else return 0; 
end if;
end;
/   

Function created.

This function will be used later on to determine the Audit Condition for Fine Grained Auditing. We could specify many more conditions (from SYS_CONTEXT or else) here of course. Although it was tempting (at least for me) to use a boolean return value, it is not a good idea: You cannot test it then with select from dual. Fine Grained Auditing is implemented with the DBMS_FGA standard package:

SQL> begin
dbms_fga.add_policy(object_schema=>'ADAM',
                    object_name=>'AUDEPT',
                    policy_name=>'AUDEPT_POLICY',
                    audit_column=>'BUDGET',
                    audit_condition=>'ADAM.AUDITIF=1');
end;
/

PL/SQL procedure successfully completed.

With this policy, an audit entry is only generated if the column “BUDGET” is touched. Additionally, in order to get audited, the access must be done without using the “Audept Application”. We look at a compliant access first:

SQL> connect / as sysdba
Connected.
SQL> truncate table fga_log$;

Table truncated.

SQL> select count(*) from dba_fga_audit_trail;

  COUNT(*)
----------
     0

SQL> connect adam/adam
Connected.

A Client Identifier can be used to differentiate multiple (application or human) users, connecting with the same Database user. This is a common technique, executed (and verified) at the application layer:

SQL> exec dbms_session.set_identifier('AUDEPT_APPLICATION')

PL/SQL procedure successfully completed.

SQL> select * from audeptvu;

    DEPTNO DNAME
---------- --------------------
    10 DELIVERY
    20 MARKETING

SQL> select * from audept;

    DEPTNO DNAME            BUDGET
---------- -------------------- ----------
    10 DELIVERY             10000
    20 MARKETING            100000

SQL> select count(*) from dba_fga_audit_trail;

  COUNT(*)
----------
     0

No audit entry was generated. Now we do not follow the business rules:

SQL> connect adam/adam
Connected.
SQL> select * from audeptvu;

    DEPTNO DNAME
---------- --------------------
    10 DELIVERY
    20 MARKETING

SQL> select count(*) from dba_fga_audit_trail;

  COUNT(*)
----------
     0

SQL> select * from audept;

    DEPTNO DNAME            BUDGET
---------- -------------------- ----------
    10 DELIVERY             10000
    20 MARKETING            100000

SQL> select count(*) from dba_fga_audit_trail;

  COUNT(*)
----------
     1

The first SELECT did not show the critical column and was therefore not audited, although no Client Identifier was specified. The second select was audited with many details, though:

SQL> select db_user,os_user,object_schema,object_name,policy_name,sql_text,timestamp 
from dba_fga_audit_trail;

DB_US OS_USER          OBJEC OBJECT_NAM POLICY_NAME        SQL_TEXT         TIMESTAMP
----- --------------- ----- ---------- -------------------- -------------------- ---------
ADAM  oracle          ADAM  AUDEPT     AUDEPT_POLICY        select * from audept 22-AUG-11

Notice that FGA will show the SQL text, regardless of the setting of AUDIT_TRAIL – no “EXTENDED” necessary here:

SQL> show parameter audit_trail

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
audit_trail                 string     DB

Conclusion: With Fine Grained Auditing, we have the option to limit our audit entries to only non compliant access, according to our business rules. One comfortable way to specify audit conditions is the standard function SYS_CONTEXT.

,

3 Comments

Follow

Get every new post delivered to your Inbox.

Join 2,889 other followers

%d bloggers like this: