Shulou(Shulou.com)06/01 Report--

The Audit is used to monitor the database operations performed by the user, and Oracle stores the audit trail results in the OS file (default location is $ORACLE_BASE/admin / $ORACLE_SID/adump/) or in the database (stored in the SYS.AUD$ table in the system tablespace, which can be viewed through the view dba_audit_trail). Audit can provide useful information to reveal the abuse and misuse of authority. When a certain granularity is needed, DBA can use fine-grained auditing to monitor access to certain rows or columns in the table, not just whether or not to access the table.

In oracle 11g, the audit function (AUDIT_TRAIL) is turned on by default. The audit data is stored on the AUD$ audit dictionary base table under the SYSTEM table space by default. Opening the audit database will increase consumption and reduce business performance, so if it is not necessary, after installing the database, you can choose to turn off the database audit function.

Oracle also recommends audit logging based on OS files (OSaudit trail files). When AUDIT_TRAIL is set to OS, the audit record file will be generated in the directory specified by the AUDIT_FILE_DEST parameter.

Type of audit

Statement audit

Audit SQL statements by statement type, regardless of which specific schema object is accessed. You can also specify one or more users in the database to audit them for specific statements

Authority audit

Audit system permissions, such as CREATE TABLE or ALTER INDEX. Like statement auditing, permission auditing can specify one or more specific users as the target of the audit

Pattern object audit

Audit specific statements running on specific schema objects (for example, UPDATE statements on the DEPARTMENTS table). Schema object auditing is always applied to all users in the database

Fine-grained audit

Audit table access and permissions based on the contents of the access object. Use the package DBMS_FGA to establish policies on specific tables

II. Audit location

Audit records can be sent to SYS.AUD$ database tables or operating system files. To enable auditing and specify where audit records are recorded, set the initial parameter AUDIT_TRAIL to one of the following values:

NONE, FALSE

Disable auditing

OS

Enable auditing and send audit records to operating system files

DB, TRUE

Enable auditing to send audit records to the SYS.AUD$ table

DB_EXTENDED

Enable auditing, send audit records to the SYS.AUD$ table, and record additional information in the CLOB columns SQLBIND and SQLTEXT

XML

Enable audit to write all audit records in XML format

EXTENDED

Enable auditing to record all columns, including SqlText and SqlBind values, in the audit trail

The parameter AUDIT_TRAIL is not dynamic, and the database must be shut down and restarted in order for the changes in the AUDIT_TRAIL parameter to take effect. When auditing a SYS.AUD$ table, care should be taken to monitor the size of the table to avoid affecting the space requirements of other objects in the SYS tablespace. It is recommended that you archive rows in SYS.AUD$ periodically and intercept the table.

CREATE SESSION is recorded as an audited right in Oracle11g, so when the SYSTEM table space cannot be expanded due to disk space, this part of the audit record cannot be generated, which will eventually lead to the normal creation of new sessions for ordinary users, and ordinary users will not be able to log in to the database. In this scenario, users who still use SYSDBA identity can create sessions, delete some records after the audit data has been properly backed up, or TRUNCATE AUD$ can solve the above problems. By default, AUTOEXTEND ON tablespaces are created with the SYSTEM automatic extension option, so the system tablespaces automatically grow when necessary. What we need to pay attention to is whether the remaining space on disk can meet its growth needs, and the upper limit of datafile expansion. For a normal 8k smallfile tablespace, the maximum size of a single datafile is 32G.

III. Audit function of account opening

SQL > alter system set audit_trail=db_extended scope=spfile

Restarting the database takes effect:

SQL > startup force

Fourth, sentence audit:

For statement auditing, the format of the audit command looks like this:

AUDIT sql_statement_clause BY {SESSION | ACCESS} WHENEVER [NOT] SUCCESSFUL

The parameters are as follows:

Sql_statement_clause contains many different pieces of information, such as the type of SQL statement you want to audit and who you want to audit.

By access audits each action when it occurs, and by session audits it only once. The default is by session.

Whenever successful audit successful action: there is no statement that generates an error message.

Whether the command of the whenever not successful audit statement failed

For most types of audit methods, if you do want to audit all types of table access or any permissions of a user, you can specify an all instead of a single statement type or object.

Table 1-1 lists the types of statements that can be audited and contains a brief description of the relevant statements in each category. If all is specified, any statements in the list are audited. However, the statement types in tables 1-2 do not fall into the all category when auditing is enabled; they must be explicitly specified in the audit command.

Table 1-1 auditable statements included in the ALL category

Statement option

SQL operation

ALTER SYSTEM

All ALTER SYSTEM options, for example, dynamically change instance parameters

Count, switch to the next log filegroup, and terminate the user session

CLUSTER

CREATE, ALTER, DROP, or TRUNCATE clusters

CONTEXT

CREATE CONTEXT or DROP CONTEXT

DATABASE LINK

CREATE or DROP database link

DIMENSION

CREATE, ALTER, or DROP dimension

DIRECTORY

CREATE or DROP directory

INDEX

CREATE, ALTER, or DROP index

MATERIALIZED VIEW

Materialized views of CREATE, ALTER, or DROP

NOT EXISTS

Failure of a SQL statement due to a reference object that does not exist

PROCEDURE

CREATE or DROP FUNCTION, LIBRARY, PACKAGE,

PACKAGE BODY or PROCEDURE

PROFILE

CREATE, ALTER, or DROP profile

PUBLIC DATABASE LINK

CREATE or DROP public database link

PUBLIC SYNONYM

Public synonyms for CREATE or DROP

ROLE

CREATE, ALTER, DROP, or SET roles

ROLLBACK

SEGMENT CREATE, ALTER, or DROP rollback segment

SEQUENCE

CREATE or DROP sequence

SESSION

Login and logout

SYNONYM

Synonyms for CREATE or DROP

SYSTEM

AUDIT or NOAUDIT of AUDIT system permissions

SYSTEM GRANT

GRANT or REVOKE system permissions and roles

TABLE

CREATE, DROP, or TRUNCATE table

TABLESPACE

CREATE, ALTER, or DROP tablespace

TRIGGER

CREATE, ALTER (enable / disable), DROP trigger; with

ENABLE ALL TRIGGERS or DISABLE ALL

ALTER TABLE of TRIGGERS

TYPE

CREATE, ALTER, and DROP types and type bodies

USER

CREATE, ALTER, or DROP user

VIEW

CREATE or DROP view

Table 1-2 explicitly specified statement types

Statement option

SQL operation

ALTER SEQUENCE

Any ALTER SEQUENCE command

ALTER TABLE

Any ALTER TABLE command

COMMENT TABLE

Add comments to a table, view, materialized view, or any column in them

DELETE TABLE

Delete rows in a table or view

EXECUTE PROCEDURE

Execute a procedure, function, or any variable or cursor in a package

GRANT DIRECTORY

Permissions on GRANT or REVOKE DIRECTORY objects

GRANT PROCEDURE

Permissions on GRANT or REVOKE procedures, functions, or packages

GRANT SEQUENCE

Permissions on GRANT or REVOKE sequences

GRANT TABLE

Permissions on GRANT or REVOKE tables, views, or materialized views

GRANT TYPE

Permissions on GRANT or REVOKE TYPE

INSERT TABLE

INSERT INTO table or view

LOCK TABLE

LOCK TABLE commands on tables or views

SELECT SEQUENCE

Any command that references the CURRVAL or NEXTVAL of a sequence

SELECT TABLE

SELECT FROM table, view, or materialized view

UPDATE TABLE

Perform UPDATE on a table or view

Note:

Starting with Oracle Database 11g, columns SQL_TEXT and SQL_BIND in DBA_AUDIT_TRAIL are populated only if the initial parameter AUDIT_TRAIL is set to DB_EXTENDED. By default, the value of AUDIT_TRAIL is DB.

Example:

[oracle@ocp Desktop] $rlwrap sqlplus / as sysdba

SYS@orcl > show parameter audit_trail

SYS@orcl > alter system set audit_trail=db_extended

Scope=spfile

SYS@orcl > startup force

SYS@orcl > show parameter audit_trail

SYS@orcl > alter user scott account unlock identified by

Scott

SYS@orcl > audit create table by scott by access

SYS@orcl > select user_name,audit_option,success,failure from

Dba_stmt_audit_opts where user_name='SCOTT'

SYS@orcl > conn scott/scott

SCOTT@orcl > create table tt (id int)

SCOTT@orcl > conn / as sysdba

SYS@orcl > select username,to_char (timestamp,'MM/DD/YY

HH24:MI') Timestamp,obj_name, action_name, sql_text from

Dba_audit_trail where username= 'SCOTT'

Turn off the audit using the noaudit command, as follows:

SYS@orcl > noaudit create table by scott

SYS@orcl > conn scott/scott

SCOTT@orcl > create table ee (id int)

SCOTT@orcl > conn / as sysdba

SYS@orcl > select username,to_char (timestamp,'MM/DD/YY

HH24:MI') Timestamp,obj_name, action_name, sql_text from

Dba_audit_trail where username= 'SCOTT'

5. Authority statistics

Audit system permissions have the same basic syntax as statement auditing, but audit system permissions are specified in sql_statement_clause, not in statements.

For example, grant ALTER TABLESPACE permissions to all DBA to generate audit records. The command to enable auditing of this permission:

Audit alter tablespace by access whenever successful

Each time the ALTER TABLESPACE permission is successfully used, a line is added to the SYS.AUD$.

System administrators who use SYSDBA and SYSOPER privileges or connect to the database as SYS users can take advantage of special auditing. To enable this additional audit level, you can set the initial parameter AUDIT_SYS_OPERATIONS to TRUE. This audit record is sent to the same location as the operating system audit record. All SQL statements executed when using one of these permissions, as well as any SQL statements executed as user SYS, are sent to the operating system audit location.

SYS@orcl > alter system set AUDIT_SYS_OPERATIONS=true

Scope=spfile

SYS@orcl > startup force

[oracle@ocp Desktop] $cd / u01/app/oracle/admin/orcl/adump/

Vi views audit content

Permission audit example:

SYS@orcl > audit create table by scott by access

SYS@orcl > audit session by scott

SYS@orcl > select user_name,privilege,success,failure from

Dba_priv_audit_opts where user_name='SCOTT' order by

User_name

SYS@orcl > conn scott/scott

SCOTT@orcl > create table tt (id int)

SCOTT@orcl > conn / as sysdba

SYS@orcl > select username,to_char (timestamp,'MM/DD/YY

HH24:MI') Timestamp,obj_name, action_name, sql_text from

Dba_audit_trail where username= 'SCOTT'

SYS@orcl > noaudit create table by scott

VI. Object audit

AUDIT schema_object_clause BY {SESSION | ACCESS} WHENEVER [NOT] SUCCESSFUL

Schema_object_clause specifies the type of object access and the object to access.

You can audit 14 different types of operations on specific objects. The object audit options are as follows:

Object option

State clearly

ALTER

Change the table, sequence, or materialized view

AUDIT

Audit commands on any object

COMMENT

Add comments to a table, view, or materialized view

DELETE

Delete rows from a table, view, or materialized view

EXECUTE

Execute a procedure, function, or package

FLASHBACK

Perform a flashback operation on a table or view

GRANT

Grant permissions on any type of object

INDEX

Create an index on a table or materialized view

INSERT

Insert rows into a table, view, or materialized view

LOCK

Lock a table, view, or materialized view

READ

Perform a read operation on the contents of the DIRECTORY object

RENAME

Rename a table, view, or procedure

SELECT

Select rows from a table, view, sequence, or materialized view

UPDATE

Update a table, view, or materialized view

Object audit example:

SYS@orcl > show parameter audit_trail

SYS@orcl > alter system set audit_trail=db_extended

Scope=spfile

SYS@orcl > show parameter audit_trail

SYS@orcl > startup force

SYS@orcl > alter user scott account unlock identified by

Scott

SYS@orcl > audit select,insert,delete on scott.dept by

Access

SYS@orcl > select

Object_name,object_type,alt,del,ins,upd,sel from

Dba_obj_audit_opts

SYS@orcl > conn scott/scott

SCOTT @ orcl > insert into dept values (11 recordings, girls, girls, girls)

SCOTT @ orcl > insert into dept values (12mai BBQ MIT BB')

SCOTT @ orcl > commit

SCOTT@orcl > conn / as sysdba

SYS@orcl > select timestamp,action_name,sql_text from

Dba_audit_trail where owner='SCOTT'

SYS@orcl > noaudit select,insert,delete on scott.dept

Audit inquiry

SYS@orcl > show parameter audit_trail

SYS@orcl > alter system set audit_trail=db_extended

Scope=spfile

SYS@orcl > startup force

SYS@orcl > show parameter audit_trail

SYS@orcl > alter user scott account unlock identified by

Scott

SYS@orcl > audit select,insert,delete on scott.dept by

Access

SYS@orcl > delete sys.aud$

SYS@orcl > conn scott/scott

SCOTT @ orcl > insert into dept values (13)

SCOTT @ orcl > select * from dept

SCOTT @ orcl > delete from dept where deptno=11

SCOTT @ orcl > update dept set loc='abc' where deptno=12

SCOTT@orcl > conn / as sysdba

SYS@orcl > select

Username,timestamp,owner,action_name,obj_name from

Dba_audit_object

VII. Fine-grained audit

Fine-grained object auditing, or FGA, becomes more focused and accurate. FGA is implemented by a PL/SQL package called DBMS_FGA.

Using standard auditing, you can easily find out which objects have been accessed and by whom, but you cannot know which rows or columns have been accessed. Fine-grained auditing solves this problem by specifying not only the predicates (or where clauses) for the rows that need to be accessed, but also the columns accessed in the table. You can greatly reduce the number of audit table entries by auditing access to the table only when certain rows and columns are accessed.

The package DBMS_FGA has four processes:

ADD_POLICY adds an audit policy that uses predicates and audit columns

DROP_POLICY delete audit policy

DISABLE_POLICY disables audit policies, but retains policies associated with tables or views

ENABLE_POLICY enable Policy

User wl usually accesses the HR.EMPLOYEES table every day to find the e-mail address of the employee. The system administrator suspected that wl was looking at managers' salary information, so they established a FGA policy to audit any manager's access to the SALARY column.

The audit records of fine-grained audits can be accessed using the data dictionary view DBA_FGA_AUDIT_TRAIL. If you generally need to view standard audit lines and fine-grained audit lines, the data dictionary view DBA_COMMON_AUDIT_TRAIL combines rows of these two audit types.

Examples are as follows:

[oracle@ocp Desktop] $rlwrap sqlplus / as sysdba

SYS@orcl > show parameter audit_trail

SYS@orcl > alter system set audit_trail=db_extended

Scope=spfile

SYS@orcl > startup force

SYS@orcl > show parameter audit_trail

SYS@orcl > create user wl identified by wl

SYS@orcl > grant create session to wl

SYS@orcl > grant select on hr.employees to wl

SYS@orcl > alter user hr account unlock identified by hr

SYS@orcl > begin

Dbms_fga.add_policy (object_schema = > 'HR'

Object_name = > 'EMPLOYEES'

Policy_name = > 'SAL_SELECT_AUDIT'

Audit_condition = > 'instr (job_id,''_MAN'') > 0'

Audit_column = > 'SALARY')

End

/

SYS@orcl > conn wl/wl

WL@orcl > select employee_id, first_name, last_name, email

From hr.employees where employee_id = 114,

WL@orcl > conn / as sysdba

SYS@orcl > select to_char (timestamp,'mm/dd/yy hh34:mi')

Timestamp,object_schema, object_name, policy_name

Statement_type from dba_fga_audit_trail where db_user =

'WL'

SYS@orcl > conn wl/wl

WL@orcl > select employee_id, first_name, last_name, salary

From hr.employees where employee_id = 114,

WL@orcl > conn / as sysdba

SYS@orcl > select to_char (timestamp,'mm/dd/yy hh34:mi')

Timestamp,object_schema, object_name, policy_name

Statement_type from dba_fga_audit_trail where db_user =

'WL'

The first query accesses manager information, but does not access the SALARY column. The second query is the same as the first query, but accesses the SALARY column, thus triggering the FGA policy, which generates a row in the audit trail

VIII. Audit-related data dictionary view

Data dictionary view

State clearly

AUDIT_ACTIONS

Contains a description of the audit trail action type code, such as

INSERT, DROP VIEW, DELETE, LOGON and LOCK

DBA_AUDIT_OBJECT

Audit trail records related to objects in the database

DBA_AUDIT_POLICIES

Fine-grained Audit Strategy in Database

DBA_AUDIT_SESSION

All audit trail records related to CONNECT and DISCONNECT

DBA_AUDIT_STATEMENT

Audit trail entries related to GRANT, REVOKE, AUDIT, NOAUDIT, and ALTER SYSTEM commands

DBA_AUDIT_TRAIL

Contains standard audit trail entries. USER_AUDIT_TRAIL contains only audit lines for connected users

DBA_FGA_AUDIT_TRAIL

Audit trail entries for fine-grained audit policies

DBA_COMMON_AUDIT_TRAIL

Combine standard audit lines with fine-grained audit lines in one view

DBA_OBJ_AUDIT_OPTS

Audit options that are valid for database objects

DBA_PRIV_AUDIT_OPTS

Audit options that take effect on system permissions

DBA_STMT_AUDIT_OPTS

Audit options that take effect on statements

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.