oracle10g fine-grained auditing

Jul 30 posted in Oracle (RSS), 86 views.

Test the success of their own examples:

sqlplus / nolog
conn / as sysdba

begin
dbms_fga.add_policy (
object_schema => 'system', # # # # program name
object_name => 'nbstutb', # # # # Table name
policy_name => 'nbstu', # # # # Since the definition of the strategy
statement_types => 'SELECT, DELETE, INSERT, UPDATE'
);
end;
/

Removal of test data: removal of fine-grained audit of the audit records:

sqlplus / nolog

conn / as sysdba

delete sys.fga_log $;
commit;

ORACLE 10g Fine-grained auditing

ORACLE 10g In fine-grained auditing (FGA) has made a lot of expansion. Fine-grained audit 9i only supports the SELECT statement, and 10g DML provides support (support for Insert, Update and Delete, Merge is not an independent statement of the DML).

10g Not only the fine-grained auditing to audit a related field can be audited on a number of fields. Audit_column_opts can be set up in dbms_fga.all_columns or dbms_fga.any_columns.

Audit logs can be SQL text and bind variables into the LO B field. If the cost of the audit is too large, you can shut down the information written into the LOB.

The following is a 10G DBMS_FGA.ADD_POLICY example, one can see the fine-grained auditing and 9i are very different:

dbms_fga.add_policy (object_schema => 'PIET', object_name => 'EMP',

policy_name => 'MYPOLICY1', audit_condition => NULL,

audit_column => 'SALARY, COMMISSION_PCT',

audit_column_opts => DBMS_FGA.ALL_COLUMNS,

audit_trail => DBMS_FGA.DB_EXTENDED,

statement_types => 'INSERT, UPDATE');

An example the following INSERT presentation of the audit process:

SQL> connect piet / piet

Connected.

SQL> CREATE TABLE EMP (

EMPNO NUMBER (4) NOT NULL,

ENAME VARCHAR2 (10),

JOB VARCHAR2 (9),

MGR NUMBER (4) CONSTRAINT EMP_SELF_KEY REFERENCES EMP (EMPNO),

HIREDATE DATE,

SAL NUMBER (7,2),

COMM NUMBER (7,2),

DEPTNO NUMBER (2) NOT NULL,

CONSTRAINT EMP_PRIMARY_KEY PRIMARY KEY (EMPNO));

Table created.

SQL> INSERT INTO EMP VALUES (7839, 'KING', 'PRESIDENT', NULL,'17-NOV-81 ', 5000, NU;

1 row created.

SQL> grant all on emp to miller;

Grant succeeded.

SQL> conn system / manager

SQL> execute sys.DBMS_FGA.ADD_POLICY (--

object_schema => 'PIET', --

object_name => 'EMP', --

policy_name => 'mypolicy1', --

audit_condition => 'sal <1000', --

audit_column => 'comm', --

enable => TRUE, --

statement_types => 'INSERT');

PL / SQL procedure successfully completed.

SQL> select * from DBA_AUDIT_POLICY_COLUMNS;

OBJECT_SCHEMA OBJECT_NAME

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

POLICY_NAME POLICY_COLUMN

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

PIET EMP

MYPOLICY1 COMM

SQL> select OBJECT_SCHEMA, OBJECT_NAME, POLICY_NAME, POLICY_TEXT,

POLICY_COLUMN, ENABLED, SEL, INS, UPD , DEL

from DBA_AUDIT_POLICIES;

OBJECT_SCHEMA OBJECT_NAME POLICY_NAME POLICY_TEXT POLICY_COLUMN ENA SEL INS UPD DEL

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

PIET EMP MYPOLICY1 sal <1000 COMM YES NO YES NO NO

SQL> conn miller / miller

Connected.

The following example is inserted into a sal a record of less than 1000, in line with audit requirements:

SQL> INSERT INTO PIET.EMP (EMPNO, ENAME, SAL, COMM, DEPTNO)

VALUES (1000, 'SAM', 800, 15, 10);

1 row created.

Insert the following example of a sal more than 1000 records, do not meet the conditions of the audit:

SQL> INSERT INTO PIET.EMP (EMPNO, ENAME, SAL, COMM, DEPTNO)

VALUES (3000, 'TOM', 20000, 1000, 20);

1 row created.

Insert the following statement does not contain a field audit, non-audit:

SQL> INSERT INTO PIET.EMP (EMPNO, ENAME, SAL, DEPTNO)

VALUES (1111, 'RAMA', 98,30);

1 row created.

SQL> commit;

Commit complete.

Click below to verify the results of the audit:

conn system / manager

SQL> select DB_USER, OBJECT_SCHEMA "SCHEMA", OBJECT_NAME,

POLICY_NAME, SQL_TEXT

from dba_fga_audit_trail;

DB_USER SCHEMA OBJECT POLICY_NAME

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

SQL_TEXT

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

MILLER PIET EMP MYPOLICY1

INSERT INTO PIET.EMP (EMPNO, ENAME, SAL, COMM, DEPTNO)

VALUES (1000, 'SAM', 800, 15, 10)

The following examples demonstrate that contains the function (sysdate, uid, user, round, etc.) of the audit:

execute dbms_fga.drop_policy (object_schema => 'PIET', --

object_name => 'EMP', --

policy_name => 'mypolicy1');

SQL> execute sys.DBMS_FGA.ADD_POLICY (--

object_schema => 'PIET', --

object_name => 'EMP', --

policy_name => 'mypolicy1', --

audit_condition => 'round (sal, -2)> = 3000', --

audit_column => 'comm', --

enable => TRUE, --

statement_types => 'INSERT');

PL / SQL procedure successfully completed.

SQL> connect miller / miller

Connected.

SQL> INSERT INTO PIET.EMP (EMPNO, ENAME, SAL, COMM, DEPTNO)

VALUES (5000, 'RUDY', 2979, 15, 10);

1 row created.

SQL> commit;

Commit complete.

Now to verify what the audit findings:

conn system / manager

SQL> select DB_USER, OBJECT_SCHEMA "SCHEMA", OBJECT_NAME,

POLICY_NAME, SQL_TEXT

from dba_fga_audit_trail;

DB_USER SCHEMA OBJECT POLICY_NAME

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

SQL_TEXT

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

MILLER PIET EMP MYPOLICY1

INSERT INTO PIET.EMP (EMPNO, ENAME, SAL, COMM, DEPTNO)

VALUES (1000, 'SAM', 800, 15, 10)

MILLER PIET EMP MYPOLICY1

INSERT INTO PIET.EMP (EMPNO, ENAME, SAL, COMM, DEPTNO)

VALUES (5000, 'RUDY', 2979, 15, 10)

The following is an audit of the use of user functions:

execute dbms_fga.drop_policy (object_schema => 'PIET', --

object_name => 'EMP', --

policy_name => 'mypolicy1');

SQL> execute sys.DBMS_FGA.ADD_POLICY (--

object_schema => 'PIET', --

object_name => 'EMP', --

policy_name => 'mypolicy1', --

audit_condition => 'ename = USER', --

audit_column => 'comm', --

enable => TRUE, --

statement_types => 'INSERT');

PL / SQL procedure successfully completed.

SQL> connect miller / miller

Connected.

SQL> conn piet / piet

Connected.

SQL> INSERT INTO PIET.EMP (EMPNO, ENAME, SAL, COMM, DEPTNO)

VALUES (89, 'MILLER', 800, 1000, 10);

1 row created.

SQL> commit;

Commit complete.

SQL> conn miller / miller

Connected.

SQL> INSERT INTO PIET.EMP (EMPNO, ENAME, SAL, COMM, DEPTNO)

VALUES (69, 'MILLER', 700, 700, 10);

1 row created.

SQL> commit;

Commit complete.

Just to test the audit findings:

con system / manager

SQL> select DB_USER, OBJECT_SCHEMA "SCHEMA", OBJECT_NAME,

POLICY_NAME, SQL_TEXT

from dba_fga_audit_trail;

DB_USER SCHEMA OBJECT POLICY_NAME

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

SQL_TEXT

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

MILLER PIET EMP MYPOLICY1

INSERT INTO PIET.EMP (EMPNO, ENAME, SAL, COMM, DEPTNO)

VALUES (1000, 'SAM', 800, 15, 10)

MILLER PIET EMP MYPOLICY1

INSERT INTO PIET.EMP (EMPNO, ENAME, SAL, COMM, DEPTNO)

VALUES (5000, 'RUDY', 2979, 15, 10)

MILLER PIET EMP MYPOLICY1

INSERT INTO PIET.EMP (EMPNO, ENAME, SAL, COMM, DEPTNO)

VALUES (69, 'MILLER', 800, 1000, 10)

. . . . . . . . . . . . . . . . . . . .

dbms_fga.add_policy (
object_schema => 'PIET',
object_name => 'EMP',
policy_name => 'MYPOLICY1',
audit_condition => NULL,
audit_column => 'SALARY, COMMISSION_PCT',
audit_column_opts => DBMS_FGA.ALL_COLUMNS,
audit_trail => DBMS_FGA.DB_EXTENDED,
statement_types => 'INSERT, UPDATE');

FGA strategy (fine-grained auditing)

FGA strategy (fine-grained auditing)

DBMS_FGA: audit has become more concerned about a certain area, and more precise.

Through it, you can know which set out to visit and what line.

DBMS_FGA package has four processes:
Add the use of predicate ADD_POLICY out the audit and audit strategy
DROP_POLICY to delete the audit strategy
Disable DISABLE_POLICY audit strategy, while keeping the table or view associated with the strategy
ENABLE_POLICY opening strategy

Test users usually visit daily itmanager.perfdata table,
FGA therefore can create a strategy for perfdatda.value out any audit of any visit:
begin
dbms_fga.add_policy (
object_schema => 'ITMANAGER',
object_name => 'PERFDATA',
policy_name => 'SAL_SELECT_PERFDATA',
audit_column => 'VALUE'
);
end;

View related include:

Fine-grained audit strategy DBA_FGA_AUDIT_TRAIL audit trail entries
FGA audit strategy to increase GRANT RESOURCE, CONNECT TO BANK IDENTIFIED BY BANK;

CREATE TABLE BANK.ACCOUNTS
(
ACCT_NO NUMBER PRIMARY KEY,
CUST_ID NUMBER NOT NULL,
BALANCE NUMBER (15,2) NULL
);
insert into bank.accounts values (1,1,10000);
insert into bank.accounts values (2,2,20000);
commit;

Begin
dbms_fga.drop_policy (
object_schema => 'BANK',
object_name => 'ACCOUNTS',
policy_name => 'ACCOUNTS_Access');
dbms_fga.add_policy (
object_schema => 'BANK',
object_name => 'ACCOUNTS',
policy_name => 'ACCOUNTS_ACCESS');
end;
/

select * from bank.accounts;
select timestamp, db_user, os_user, object_schema, object_name, sql_text from dba_fga_audit_trail;

Auditors and audit requirements set out in add_policy add audit_column => 'BALANCE'
audit_condition => 'BALANCE> = 11000'
Begin
dbms_fga.drop_policy (
object_schema => 'BANK',
object_name => 'ACCOUNTS',
policy_name => 'ACCOUNTS_ACCESS');

dbms_fga.add_policy (
object_schema => 'BANK',
object_name => 'ACCOUNTS',
audit_column => 'BALANCE',
audit_condition => 'BALANCE> = 11000',
policy_name => 'ACCOUNTS_ACCESS');
end;
/

This is not, Why?
select BALANCE from bank.accounts;
select timestamp, db_user, os_user, object_schema, object_name, sql_text from dba_fga_audit_trail;

FGA governance strategy to delete the policy, you can use the following statement:
begin
dbms_fga.drop_policy (

object_schema => 'BANK',
object_name => 'ACCOUNTS',
policy_name => 'ACCOUNTS_ACCESS'
);
end;
/

Strategy for change, there is no check as used with the solution. To change any of the parameters of the strategy, the strategy must be deleted, and then change the parameters of the use of strategies to add.
Auditors need to temporarily disable the collection - for example, if you want to move leads to a different table table space or table you want to remove clues. You can disable the FGA method according to the strategy are as follows:
begin
dbms_fga.enable_policy (
object_schema => 'BANK',
object_name => 'ACCOUNTS',
policy_name => 'ACCOUNTS_ACCESS',
enable => FALSE
);
end;
/
Is very simple to re-enable enable => TRUE

Presentation when the audit operation and when not to audit the operation of the various situations the state audit SQL statements select balance from accounts; audit. Add the user to choose the strategy set out by the designated audit BALANCE.
select * from accounts; audit. Even if the user is not specified column BALANCE, * also implicitly select it.
select cust_id from accounts where balance <10000; audit. Even if the user is not specified column BALANCE, where clause also implicitly select it.
select cust_id from accounts; not audit. Users have no choice out BALANCE.
select count (*) from accounts; not audit. User does not have explicit or implicit choice of column BALANCE.

Processor module is not only the function of FGA audit trail record of the incident; FGA also can be the implementation process.

Process can perform an action, such as when the user select from the table when a particular e-mail sent to the audit warned, or you can write the audit trail in a different.
This code can be stored in the process of independent package or in the process, the processor module is called strategy.
In fact due to security reasons, it does not need to base itself in the same mode, you may want to deliberately place it in a different mode.
SELECT arise as a result of the process as long as the implementation will be very similar to DML statement to start the trigger, you can also be regarded as the SELECT statement trigger.
The following parameter specifies a processor module will be assigned to the strategy:
handler_schema process has a data name of the software process model handler_module Development Network processor module package can also be used to replace the name of the process name. In this case, the parameters handler_module in the format specified in package.procedure.

FGA data dictionary view
FGA strategy definition DBA_AUDIT_POLICIES is located in the data dictionary view. Table 2 contains the list view in a number of important short description.
Audit trail collection in the SYS-owned table FGA_LOG $ in. SYS-owned for any original form, this form some view in a user-friendly display of information. DBA_FGA_AUDIT_TRAIL the form of a view.
Out is an important SQL_BIND, it specified the use of query the value of bind variables - this is a significant function of the tool to enhance a message.
Another important column is the SCN, when the occurrence of a particular query, it changed its recording system.
This information is used to identify users in a specific time to see what, rather than the current value, it uses flashback query, this query can be displayed in the SCN value specified in the data.

View and FGA
So far I have discussed the application in the form FGA; Let us now look at how to view the use of FGA. Assume that the definition in the ACCOUNTS table VW_ACCOUNTS view as follows:

create view bank.vw_accounts as select * from bank.accounts;

select * from bank.vw_accounts;
select timestamp, db_user, os_user, object_schema, object_name, sql_text from dba_fga_audit_trail;

If you just want to audit queries on the view instead of the query table, you can establish a strategy for the view itself.
View by name rather than the name of the table passed to the process of packaging parameters dbms_fga.add_policy in object_name, can accomplish this task.
OBJECT_NAME then out of DBA_FGA_AUDIT_TRAIL will show the name of view, and will not appear on the table access to additional records.

Records of other uses in addition to the choice of access to the table, FGA can also be used for certain other situations:
You can use the data warehouse FGA, in order to capture a specific table, view, or materialized views on all statements, which helps plan the index. You do not need to go to V $ SQL view to obtain such information. Even if the SQL statement has exceeded the duration of the V $ SQL, in the FGA audit trail will always provide it.
FGA capture as a result of bind variables, which can help you understand the value of the mode of bind variables, which contribute to the design collection, such as histogram.
Processor module or to audit DBA to send a warning, which helps to track malicious applications.
FGA as a result of SELECT statement can be used as triggers, you may need this kind of functionality at any time to use it.
Conclusion FGA in Oracle Database enables you to support strategy and functions of privacy. Because of the internal audit took place in the database rather than applications, so users no matter what access method (such as SQL * Plus and other tools or applications), an audit of the operation and promised to set up very simple.
Next time I will discuss the technologies, as well as senior FGA in Oracle Database 10g new features, these features make a very strong function of FGA, applicable to all types of audits.

Data dictionary view DBA_AUDIT_POLICIES important OBJECT_SCHEMA out its strategy for the definition of the FGA or OBJECT_NAME view the owner's name or view name POLICY_NAME strategy - for example, ACCOUNTS_ACCESS
Add POLICY_TEXT strategy specified in the audit of the conditions - for example, BALANCE> = 11000
Audit POLICY_COLUMN out - for example, BALANCE
ENABLED if the opening is YES, otherwise NO
PF_SCHEMA processor module has a strategy mode (if it exists)
PF_PACKAGE processor module package name (if exists)
The process of PF_FUNCTION processor module name (if exists)

Data dictionary view columns DBA_FGA_AUDIT_TRAIL important conversation identifier SESSION_ID audit; and V $ SESSION view different TIMESTAMP session identifier at the time of the audit records generated time tag database queries issued DB_USER users USERHOST users OS_USER operating system user to connect the host machine Customers who CLIENT_ID identifier (if the process of packing the call set dbms_session.set_identifier)
External authentication EXT_NAME name customers, such as LDAP users to access the table OBJECT_SCHEMA trigger an audit of the owner of the table OBJECT_NAME the SELECT operation on the table to trigger an audit of the name of the table to trigger the audit strategy POLICY_NAME name (if the definition of a number of table strategy, each strategy will be to insert a record.
In this case, the column shows the line which is inserted by which the strategy. )
SCN recorded in the audit of the Oracle system change number
SQL_TEXT submitted by the user SQL_BIND the SQL statement used by the SQL statements to bind variables (if exists)