What is mandatory auditing?

Oracle Database always audits certain database-related operations and writes them to the operating system audit files.

It includes the actions of any user who is logged in with the SYSDBA or SYSOPER privilege. This is called mandatory auditing.

Even if you have enabled the database audit trail (that is, setting the AUDIT_TRAIL parameter to DB), Oracle Database still writes mandatory records to operating system files.

Mandatory auditing includes the following operations:

* Database startup
* SYSDBA and SYSOPER logins
* Database shutdown

Source: Oracle Documentation

What is Row Movement and how is it used?

When you add the clause "enable row movement" to a create table statement, you are giving Oracle permission to change the ROWIDs.

Features such as flashback table depends on row movement being enabled on the table.

To enable row movement on an existing table:

ALTER TABLE SCOTT.EMP ENABLE ROW MOVEMEMENT;

Using enable row movement can corrupt any Oracle features that rely on ROWID, such as nested tables.
Check for such objects before you enable row movement:

SELECT COUNT(*)
FROM dba_nested_tables
WHERE owner = 'SCOTT';


Sources: Oracle Documentation and Don Burleson

How to audit ALTER TABLE statements on a specific table

Enable auditing on specific table:

AUDIT ALTER ON SCOTT.T1;

Verify that the object is being audited:

SELECT OBJECT_NAME,OBJECT_TYPE,ALT
FROM  DBA_OBJ_AUDIT_OPTS
WHERE  OWNER ='SCOTT'
ORDER BY 1 ASC; 

Result:

|------------------------------------|
|object_name  | object_type  | alt   |
|-------------|--------------|-------|
|T1           | TABLE        | S/S   |
|------------------------------------|

Object is indeed being audited.

Make a change to the table:

ALTER TABLE SCOTT.T1
 ADD (age  number(2));

Check that the change is captured in the audit-trail:

SELECT TIMESTAMP, OS_USERNAME, OBJ_NAME,USERHOST, SESSIONID, USERNAME,ACTION_NAME, RETURNCODE
FROM   DBA_AUDIT_TRAIL
WHERE  OBJ_NAME = 'T1'
AND    OWNER = 'SCOTT'
ORDER  BY TIMESTAMP DESC;

|-----------------------------------------------------------------------------------------------------------|
|TIMESTAMP     | OS_USERNAME | OBJ_NAME  | USERHOST| SESSIONID | USERNAME | ACTION_NAME  | RETURNCODE |
|-----------------------------------------------------------------------------------------------------------|
|03.11.2013 08:32:28 | SCOTT | T1    | scottpc  | 172154    | SCOTT   | ALTER TABLE  | 0         |
|03.11.2013 08:32:18 | SCOTT | T1    | scottpc  | 172154 | SCOTT   | ALTER TABLE  | 1430      |
|03.11.2013 08:32:18 | SCOTT | T1    | scottpc  | 172154 | SCOTT   | ALTER TABLE  | 911       |
|01.11.2013 16:14:17 | SCOTT | T1    | scottpc  | 234766 | SCOTT   | ALTER TABLE  | 0         |
|-----------------------------------------------------------------------------------------------------------|

The return codes can be checked by using the oerr utility that comes with the oracle installed binaries, for example:

oerr ora 1430
01430, 00000, "column being added already exists in table"
// *Cause:
// *Action:

Note that other DDL statements, too, are captured in the audit trail for the table, such as DROP and TRUNCATE.
If you drop the table without purging the recyclebin, the auditing will still be in place, and if you query the DBA_OBJ_AUDIT_OPTS after a DROP without purge, it will show you the recyclebase name in the view.

How to use flashback table

Use a point-in-time in the past:

FLASHBACK TABLE SCOTT.EMP TO TIMESTAMP
TO_TIMESTAMP('09-12-2008 14:00:00', 'DD-MM-YYYY HH24:MI:SS');

Notice the peculiar syntax:

"TO TIMESTAMP TO_TIMESTAMP..."

Use a specific SCN:

FLASHBACK TABLE SCOTT.EMP TO SCN 144003819;

If you want to flash back multiple tables, you can use many table names separated by a comma as follows:

FLASHBACK TABLE SCOTT.EMP, SCOTT.DEPT TO SCN 144003819;

Use a restore point:

FLASHBACK TABLE SCOTT.EMP TO RESTORE POINT BEFORE_MAINTENANCE;

Flashback to before drop:

FLASHBACK TABLE SCOTT.EMP TO BEFORE DROP;

You can add the RENAME TO clause to rename the retrieved table:

FLASHBACK TABLE SCOTT.EMP TO BEFORE DROP RENAME TO EMP_OLD;

Note:
For Oracle Flashback Table to succeed, the system must retain enough undo information to satisfy the specified SCN or timestamp, and the integrity constraints specified on the tables cannot be violated. Also, row movement must be enabled on the table.

Row movement must be enabled for the table you attempt to flash back. It can be switched on right before you use the flashback table feature. If you are flashing back to a time when row movement was *not* enabled, that is no problem, you can still use flashback table, as long as row movment is enabled *at the time* of executing the flashback table command.

There is one important exception to the requirement that row movement must be enabled: flashing back table TO BEFORE DROP. Such an operation Oracle calls a "flashback drop operation", and it uses dropped data in the recyclebin rather than undo data.

You can check the content of the recyclebin before you flash back a table to before it was dropped, like this:

select owner,object_name,original_name,operation,droptime,can_undrop from dba_recyclebin where original_name='EMP';

OWNER                          OBJECT_NAME                    ORIGINAL_NAME                  OPERATION DROPTIME            CAN
------------------------------ ------------------------------ ------------------------------ --------- ------------------- ---
SCOTT                  BIN$StmkAPSYpxbgUzwGAQph5w==$0 EMP               DROP      2017-03-16:14:00:09 YES

There are some restrictions to flashback table. Read about them here "Notes on Flashing Back Dropped Tables"

How to create a consistent dump set using expdp

Use the FLASHBACK_TIME or FLASHBACK_SCN directives.

The simplest approach:

FLASHBACK_TIME="to_timestamp(to_char(sysdate,'ddmmyyyy hh24:mi:ss'),'ddmmyyyy hh24:mi:ss')"

How to audit CREATE SESSION

Audit all session connecting to the database:

audit create session [ whenever successful | not successful ];

To audit sessions created by a specific user:

audit create session by SCOTT [ whenever successful | not successful ];

If you omit the condition "whenever successful/not successful", Oracle will audit all connections, regardless of their success or failure.


To disable auditing:

noaudit create session;
noaudit create session by scott;

To verify that the auditing options has indeed been set:

SELECT * 
 FROM DBA_PRIV_AUDIT_OPTS 
 WHERE USER_NAME = 'SCOTT';

Result:

USER_NAME	PROXY_NAME	PRIVILEGE	SUCCESS	FAILURE
SCOTT		CREATE SESSION	BY ACCESS	BY ACCESS

The view DBA_PRIV_AUDIT_OPTS gives you "current system privileges being audited across the system and by user".
Note that a NULL value in the user_name column of DBA_PRIV_AUDIT_OPTS means system-wide auditing.

To find your audit info, use the query outlined here

Can you revoke all object privileges from a role in one go?

Answer: No.

The closest shortcut you get is to revoke all privileges per object from a role.

Example:

REVOKE ALL ON SCOTT.T1 FROM TEST_ROLE;