Unified Audit
SQL> create audit policy t actions logon;
Audit policy created.
SQL> audit policy t by sys, system;
Audit succeeded.
SQL> select view_name from dba_views where view_name like '%UNIF%';
VIEW_NAME
--------------------------------------------------------------------------------
V_$UNIFIED_AUDIT_TRAIL
GV_$UNIFIED_AUDIT_TRAIL
V_$UNIFIED_AUDIT_RECORD_FORMAT
AUDIT_UNIFIED_POLICIES
AUDIT_UNIFIED_ENABLED_POLICIES
AUDIT_UNIFIED_CONTEXTS
UNIFIED_AUDIT_TRAIL
AUDIT_UNIFIED_POLICY_COMMENTS
CDB_UNIFIED_AUDIT_TRAIL
9 rows selected.
SQL> DESC AUDIT_UNIFIED_ENABLED_POLICIES
Name Null? Type
----------------------------------------- -------- ----------------------------
USER_NAME VARCHAR2(128)
POLICY_NAME VARCHAR2(128)
ENABLED_OPT VARCHAR2(8)
SUCCESS VARCHAR2(3)
FAILURE VARCHAR2(3)
SQL> SELECT * FROM AUDIT_UNIFIED_ENABLED_POLICIES WHERE POLICY_NAME = 'T';
USER_NAME
--------------------------------------------------------------------------------
POLICY_NAME
--------------------------------------------------------------------------------
ENABLED_ SUC FAI
-------- --- ---
SYS
T
BY YES YES
SYSTEM
T
BY YES YES
USER_NAME
--------------------------------------------------------------------------------
POLICY_NAME
--------------------------------------------------------------------------------
ENABLED_ SUC FAI
-------- --- ---
SQL> audit policy t by c##tmp;
Audit succeeded.
SQL> SELECT * FROM AUDIT_UNIFIED_ENABLED_POLICIES WHERE POLICY_NAME = 'T';
USER_NAME
--------------------------------------------------------------------------------
POLICY_NAME
--------------------------------------------------------------------------------
ENABLED_ SUC FAI
-------- --- ---
SYS
T
BY YES YES
SYSTEM
T
BY YES YES
USER_NAME
--------------------------------------------------------------------------------
POLICY_NAME
--------------------------------------------------------------------------------
ENABLED_ SUC FAI
-------- --- ---
C##TMP
T
BY YES YES
SQL> audit policy t except sys, system;
audit policy t except sys, system
*
ERROR at line 1:
ORA-46350: Audit policy T already applied with the BY clause.
SQL>
Data Redaction Features
connect / as sysdba
drop user c##t1 cascade;
drop role c##t100;
create user c##t1 identified by t;
grant create session to c##t1;
grant unlimited tablespace to c##t1;
create role c##t100;
grant c##t100 to c##t1;
grant create table to c##t1;
create table c##t1.t as select 1 id from dual;
create view c##t1.v as select * from c##t1.t;
BEGIN
DBMS_REDACT.ADD_POLICY(
OBJECT_SCHEMA => 'C##T1',
OBJECT_NAME => 'T',
COLUMN_NAME => 'ID',
POLICY_NAME => 'ID',
FUNCTION_TYPE => DBMS_REDACT.FULL,
EXPRESSION => 'SYS_CONTEXT(''SYS_SESSION_ROLES'',''C##T100'') = ''FALSE''');
DBMS_REDACT.ADD_POLICY(
OBJECT_SCHEMA => 'C##T1',
OBJECT_NAME => 'V',
COLUMN_NAME => 'ID',
POLICY_NAME => 'ID1',
FUNCTION_TYPE => DBMS_REDACT.NONE,
EXPRESSION => 'SYS_CONTEXT(''SYS_SESSION_ROLES'',''C##T100'') = ''FALSE''');
END;
/
grant c##t100 to c##t1 container=all;
alter user c##t1 default role all except c##t100;
connect c##t1/t;
select * from t;
select * from v;
set role c##t100;
select * from t;
select * from v;
Oracle Data redaction
BEGIN
DBMS_REDACT.ADD_POLICY(
object_schema => 'C##TMP',
object_name => 'TMP100',
column_name => 'ID',
policy_name => 'ID_1',
function_type => DBMS_REDACT.RANDOM,
expression => '1=1');
END;
/
SQL> show user
USER is "C##TMP"
SQL> /
ID NAME
---------- ----------------------------------------------------------------------------------------------------
2 test
1 test2
6 test3
7 test
0 test2
7 test3
6 rows selected.
SQL> /
ID NAME
---------- ----------------------------------------------------------------------------------------------------
8 test
6 test2
2 test3
8 test
4 test2
7 test3
6 rows selected.
SQL> /
ID NAME
---------- ----------------------------------------------------------------------------------------------------
1 test
5 test2
4 test3
8 test
1 test2
8 test3
6 rows selected.
SQL> connect / as sysdba
Connected.
SQL> select * from c##tmp.tmp100;
ID NAME
---------- ----------------------------------------------------------------------------------------------------
1 test
2 test2
3 test3
1 test
2 test2
3 test3
6 rows selected.
SQL> select * from c##tmp.tmp100;
ID NAME
---------- ----------------------------------------------------------------------------------------------------
1 test
2 test2
3 test3
1 test
2 test2
3 test3
6 rows selected.
SQL> select * from c##tmp.tmp100;
ID NAME
---------- ----------------------------------------------------------------------------------------------------
1 test
2 test2
3 test3
1 test
2 test2
3 test3
6 rows selected.
Flashback cdb (pdb offline)
SQL> create pluggable database tmp10 admin user test identified by t file_name_convert=('seed', 'tmp1000');
Pluggable database created.
SQL> alter session set container=tmp10;
Session altered.
SQL> alter database open;
Database altered.
SQL> create table tmp100 as select * from dba_objects;
Table created.
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
2811997
SQL> delete from tmp100;
91043 rows deleted.
SQL> commit;
Commit complete.
SQL> alter pluggable database close;
Pluggable database altered.
SQL> alter pluggable database tmp10 datafile all offline;
Pluggable database altered.
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options
c:\project\tmp>sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Thu Oct 22 22:34:03 2015
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 2566914048 bytes
Fixed Size 3048920 bytes
Variable Size 671091240 bytes
Database Buffers 1879048192 bytes
Redo Buffers 13725696 bytes
Database mounted.
SQL> flashback database to scn 2811997;
Flashback complete.
SQL> alter database open resetlogs;
Database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 TMP10 MOUNTED
4 TEST MOUNTED
SQL> alter session set container=tmp10;
Session altered.
SQL> alter pluggable database tmp10 datafile all online;
Pluggable database altered.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01190: control file or data file 23 is from before the last RESETLOGS
ORA-01110: data file 23: 'C:\APP\ORACLE\ORADATA\ORCL\PDBTMP1000\SYSAUX01.DBF'
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-19909: datafile 23 belongs to an orphan incarnation
ORA-01110: data file 23: 'C:\APP\ORACLE\ORADATA\ORCL\PDBTMP1000\SYSAUX01.DBF'
SQL>
DDL log (where is time)
SQL> show parameter ddl
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
ddl_lock_timeout integer 0
enable_ddl_logging boolean FALSE
SQL> alter system set enable_ddl_logging=true;
System altered.
SQL> create table tmp100 (id number);
Table created.
c:\project\tmp>adrci
ADRCI: Release 12.1.0.2.0 - Production on Tue Oct 20 11:07:29 2015
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
ADR base = "C:\app\oracle"
adrci> show homes
ADR Homes:
diag\clients\user_oracle\host_3547557942_82
diag\rdbms\db12\db12
diag\rdbms\db12cdb\db12cdb
C:\app\oracle\diag\rdbms\db12cdb\db12cdb\log>cat ddl_db12cdb.log
diag_adl:create table tmp100 (id number)
C:\app\oracle\diag\rdbms\db12cdb\db12cdb\log>cd ddl
C:\app\oracle\diag\rdbms\db12cdb\db12cdb\log\ddl>ls
log.xml
C:\app\oracle\diag\rdbms\db12cdb\db12cdb\log\ddl>cat log.xml
<msg time='2015-10-20T11:07:25.907+01:00' org_id='oracle' comp_id='rdbms'
msg_id='opiexe:4383:2946163730' type='UNKNOWN' group='diag_adl'
level='16' host_id='SCPC011' host_addr='130.246.77.105'
version='1'>
<txt>create table tmp100 (id number)
</txt>
</msg>
Common user contains different set of objects
SQL> alter session set container=cdb$root;
Session altered.
SQL> create user c##admin identified by t;
User created.
SQL> create table c##admin.t (id number);
Table created.
SQL> desc c##admin.t
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
SQL> alter session set container=test;
Session altered.
SQL> desc c##admin.t
ERROR:
ORA-04043: object c##admin.t does not exist
Common user should have the same temporary tablespace in all pdbs
SQL> create user c##admin1 identified by t temporary tablespace t2;
create user c##admin1 identified by t temporary tablespace t2
*
ERROR at line 1:
ORA-65048: error encountered when processing the current DDL statement in
pluggable database PDBORCL
ORA-00959: tablespace 'T2' does not exist
SQL> create audit policy t actions logon;
Audit policy created.
SQL> audit policy t by sys, system;
Audit succeeded.
SQL> select view_name from dba_views where view_name like '%UNIF%';
VIEW_NAME
--------------------------------------------------------------------------------
V_$UNIFIED_AUDIT_TRAIL
GV_$UNIFIED_AUDIT_TRAIL
V_$UNIFIED_AUDIT_RECORD_FORMAT
AUDIT_UNIFIED_POLICIES
AUDIT_UNIFIED_ENABLED_POLICIES
AUDIT_UNIFIED_CONTEXTS
UNIFIED_AUDIT_TRAIL
AUDIT_UNIFIED_POLICY_COMMENTS
CDB_UNIFIED_AUDIT_TRAIL
9 rows selected.
SQL> DESC AUDIT_UNIFIED_ENABLED_POLICIES
Name Null? Type
----------------------------------------- -------- ----------------------------
USER_NAME VARCHAR2(128)
POLICY_NAME VARCHAR2(128)
ENABLED_OPT VARCHAR2(8)
SUCCESS VARCHAR2(3)
FAILURE VARCHAR2(3)
SQL> SELECT * FROM AUDIT_UNIFIED_ENABLED_POLICIES WHERE POLICY_NAME = 'T';
USER_NAME
--------------------------------------------------------------------------------
POLICY_NAME
--------------------------------------------------------------------------------
ENABLED_ SUC FAI
-------- --- ---
SYS
T
BY YES YES
SYSTEM
T
BY YES YES
USER_NAME
--------------------------------------------------------------------------------
POLICY_NAME
--------------------------------------------------------------------------------
ENABLED_ SUC FAI
-------- --- ---
SQL> audit policy t by c##tmp;
Audit succeeded.
SQL> SELECT * FROM AUDIT_UNIFIED_ENABLED_POLICIES WHERE POLICY_NAME = 'T';
USER_NAME
--------------------------------------------------------------------------------
POLICY_NAME
--------------------------------------------------------------------------------
ENABLED_ SUC FAI
-------- --- ---
SYS
T
BY YES YES
SYSTEM
T
BY YES YES
USER_NAME
--------------------------------------------------------------------------------
POLICY_NAME
--------------------------------------------------------------------------------
ENABLED_ SUC FAI
-------- --- ---
C##TMP
T
BY YES YES
SQL> audit policy t except sys, system;
audit policy t except sys, system
*
ERROR at line 1:
ORA-46350: Audit policy T already applied with the BY clause.
SQL>
Data Redaction Features
connect / as sysdba
drop user c##t1 cascade;
drop role c##t100;
create user c##t1 identified by t;
grant create session to c##t1;
grant unlimited tablespace to c##t1;
create role c##t100;
grant c##t100 to c##t1;
grant create table to c##t1;
create table c##t1.t as select 1 id from dual;
create view c##t1.v as select * from c##t1.t;
BEGIN
DBMS_REDACT.ADD_POLICY(
OBJECT_SCHEMA => 'C##T1',
OBJECT_NAME => 'T',
COLUMN_NAME => 'ID',
POLICY_NAME => 'ID',
FUNCTION_TYPE => DBMS_REDACT.FULL,
EXPRESSION => 'SYS_CONTEXT(''SYS_SESSION_ROLES'',''C##T100'') = ''FALSE''');
DBMS_REDACT.ADD_POLICY(
OBJECT_SCHEMA => 'C##T1',
OBJECT_NAME => 'V',
COLUMN_NAME => 'ID',
POLICY_NAME => 'ID1',
FUNCTION_TYPE => DBMS_REDACT.NONE,
EXPRESSION => 'SYS_CONTEXT(''SYS_SESSION_ROLES'',''C##T100'') = ''FALSE''');
END;
/
grant c##t100 to c##t1 container=all;
alter user c##t1 default role all except c##t100;
connect c##t1/t;
select * from t;
select * from v;
set role c##t100;
select * from t;
select * from v;
Oracle Data redaction
BEGIN
DBMS_REDACT.ADD_POLICY(
object_schema => 'C##TMP',
object_name => 'TMP100',
column_name => 'ID',
policy_name => 'ID_1',
function_type => DBMS_REDACT.RANDOM,
expression => '1=1');
END;
/
SQL> show user
USER is "C##TMP"
SQL> /
ID NAME
---------- ----------------------------------------------------------------------------------------------------
2 test
1 test2
6 test3
7 test
0 test2
7 test3
6 rows selected.
SQL> /
ID NAME
---------- ----------------------------------------------------------------------------------------------------
8 test
6 test2
2 test3
8 test
4 test2
7 test3
6 rows selected.
SQL> /
ID NAME
---------- ----------------------------------------------------------------------------------------------------
1 test
5 test2
4 test3
8 test
1 test2
8 test3
6 rows selected.
SQL> connect / as sysdba
Connected.
SQL> select * from c##tmp.tmp100;
ID NAME
---------- ----------------------------------------------------------------------------------------------------
1 test
2 test2
3 test3
1 test
2 test2
3 test3
6 rows selected.
SQL> select * from c##tmp.tmp100;
ID NAME
---------- ----------------------------------------------------------------------------------------------------
1 test
2 test2
3 test3
1 test
2 test2
3 test3
6 rows selected.
SQL> select * from c##tmp.tmp100;
ID NAME
---------- ----------------------------------------------------------------------------------------------------
1 test
2 test2
3 test3
1 test
2 test2
3 test3
6 rows selected.
Flashback cdb (pdb offline)
SQL> create pluggable database tmp10 admin user test identified by t file_name_convert=('seed', 'tmp1000');
Pluggable database created.
SQL> alter session set container=tmp10;
Session altered.
SQL> alter database open;
Database altered.
SQL> create table tmp100 as select * from dba_objects;
Table created.
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
2811997
SQL> delete from tmp100;
91043 rows deleted.
SQL> commit;
Commit complete.
SQL> alter pluggable database close;
Pluggable database altered.
SQL> alter pluggable database tmp10 datafile all offline;
Pluggable database altered.
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options
c:\project\tmp>sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Thu Oct 22 22:34:03 2015
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 2566914048 bytes
Fixed Size 3048920 bytes
Variable Size 671091240 bytes
Database Buffers 1879048192 bytes
Redo Buffers 13725696 bytes
Database mounted.
SQL> flashback database to scn 2811997;
Flashback complete.
SQL> alter database open resetlogs;
Database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 TMP10 MOUNTED
4 TEST MOUNTED
SQL> alter session set container=tmp10;
Session altered.
SQL> alter pluggable database tmp10 datafile all online;
Pluggable database altered.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01190: control file or data file 23 is from before the last RESETLOGS
ORA-01110: data file 23: 'C:\APP\ORACLE\ORADATA\ORCL\PDBTMP1000\SYSAUX01.DBF'
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-19909: datafile 23 belongs to an orphan incarnation
ORA-01110: data file 23: 'C:\APP\ORACLE\ORADATA\ORCL\PDBTMP1000\SYSAUX01.DBF'
SQL>
DDL log (where is time)
SQL> show parameter ddl
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
ddl_lock_timeout integer 0
enable_ddl_logging boolean FALSE
SQL> alter system set enable_ddl_logging=true;
System altered.
SQL> create table tmp100 (id number);
Table created.
c:\project\tmp>adrci
ADRCI: Release 12.1.0.2.0 - Production on Tue Oct 20 11:07:29 2015
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
ADR base = "C:\app\oracle"
adrci> show homes
ADR Homes:
diag\clients\user_oracle\host_3547557942_82
diag\rdbms\db12\db12
diag\rdbms\db12cdb\db12cdb
C:\app\oracle\diag\rdbms\db12cdb\db12cdb\log>cat ddl_db12cdb.log
diag_adl:create table tmp100 (id number)
C:\app\oracle\diag\rdbms\db12cdb\db12cdb\log>cd ddl
C:\app\oracle\diag\rdbms\db12cdb\db12cdb\log\ddl>ls
log.xml
C:\app\oracle\diag\rdbms\db12cdb\db12cdb\log\ddl>cat log.xml
<msg time='2015-10-20T11:07:25.907+01:00' org_id='oracle' comp_id='rdbms'
msg_id='opiexe:4383:2946163730' type='UNKNOWN' group='diag_adl'
level='16' host_id='SCPC011' host_addr='130.246.77.105'
version='1'>
<txt>create table tmp100 (id number)
</txt>
</msg>
Common user contains different set of objects
SQL> alter session set container=cdb$root;
Session altered.
SQL> create user c##admin identified by t;
User created.
SQL> create table c##admin.t (id number);
Table created.
SQL> desc c##admin.t
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
SQL> alter session set container=test;
Session altered.
SQL> desc c##admin.t
ERROR:
ORA-04043: object c##admin.t does not exist
Common user should have the same temporary tablespace in all pdbs
SQL> create user c##admin1 identified by t temporary tablespace t2;
create user c##admin1 identified by t temporary tablespace t2
*
ERROR at line 1:
ORA-65048: error encountered when processing the current DDL statement in
pluggable database PDBORCL
ORA-00959: tablespace 'T2' does not exist
Комментариев нет:
Отправить комментарий