Beiträge getaggt mit 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 🙂
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!“ 🙂
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.