I'm Top Oracle DBA

Generate for disable privilege auditing 

|

before we go to generate for enable privilege audit, I have to talk about the structure of oracle, They have many kinds of audit types so I think the three types of audit that I’ll say about, it’s exactly the same meaning, let get the look.

Using Default Auditing for Security-Relevant SQL Statements and Privileges

Oracle Database audits the AUDIT ROLE SQL statement by default. The privileges that are audited by default are as follows:
For example:

AUDIT ALTER PROFILE BY ACCESS;
AUDIT CREATE PUBLIC DB LINK BY ACCESS;
AUDIT DROP ANY TABLE BY ACCESS;
AUDIT GRANT ANY OBJECT PRIVILEGE BY ACCESS;
AUDIT GRANT ANY PRIVILEGE BY ACCESS;
AUDIT GRANT ANY ROLE BY ACCESS;

Individually Auditing SQL Statements

The SQL statements that you can audit are in the following categories:

DDL statements.
For example,
enabling the auditing of tables (AUDIT TABLE) audits all CREATE and DROP TABLE statements

DML statements.
For example,
enabling the auditing of SELECT TABLE audits all SELECT … FROM TABLE/VIEW statements, regardless of the table or view

Individually Auditing Privileges

Privilege auditing is a way to audit statements that can use a system privilege, such as the SELECT ANY TABLE statement. You can audit the use of any system privilege. Similar to statement auditing, privilege auditing can audit the activities of all database users or of only a specified list. As with SQL statement auditing, you use the AUDIT and NOAUDIT statements to enable and disable privilege auditing.

For example:

AUDIT DELETE ANY TABLE BY ACCESS WHENEVER NOT SUCCESSFUL;

Thanks to the knowledge that makes more understand from docs.oracle.com

***Note***

When I was doing the maintenance task, I kept the standard value as a “Enable auditing activity step” before you maintain the task then, you have to “Disable privilege step” or disable auditing so that up to your convenience.

Generate for enable privilege auditing activity

for keeping data, before you do maintenance tasks (done forget, There have many views to keep audit data you can adapt, for your situation)

  • log in to the database as a sys or system user.
SELECT 'AUDIT '||PRIVILEGE||';'
FROM DBA_PRIV_AUDIT_OPTS
WHERE PRIVILEGE NOT IN 'CREATE SESSION' ;

Generate for Disable privilege auditing activity

for running data, before you do maintenance tasks (done forget, There have many views to keep audit data you can adapt, for your situation)

  • log in to the database as a sys or system user.
SELECT 'NOAUDIT '||PRIVILEGE||';'
FROM DBA_PRIV_AUDIT_OPTS
WHERE PRIVILEGE NOT IN 'CREATE SESSION' ;
  •  if you want to grant somebody
SELECT 'AUDIT '||PRIVILEGE||' BY '||USER_NAME||';'
FROM DBA_PRIV_AUDIT_OPTS
WHERE PRIVILEGE NOT IN 'CREATE SESSION' ;
  • Run disable privilege auditing
SQL> noaudit ALTER SYSTEM;

Noaudit succeeded.

SQL> SELECT * FROM DBA_PRIV_AUDIT_OPTS
open only CREATE SESSION FOR AUDIT

Auditing Oracle Database Activity

How to disable and enable audit privilege on Oracle Database 10g onward

638
error: Content is protected !!