Wednesday, 6 February 2019

Oracle Database Vault

Oracle Database Vault provides powerful security controls to help protect application data from unauthorized access and comply with privacy and regulatory requirements.  Recently, we had a customer purchase the Database Vault option for their environment and was looking for a demonstration of the product to help them get started.  This blog entry is a brief introduction on Database Vault that demonstrates how to protect sensitive data from abuse by privileged users like the DBA.
Database Vault protects sensitive data by using Realms. A database Realm is created and contains one or more objects (Tables, Indexes,…) from one or more schemas.  Users are authorized directly by the DV_OWNER and the schema owner by granting access.  Mandatory Realms only allow users who are directly authorized by DV_OWNER to access the protected objects (not schema owners or anyone granted access by the schema owner).  Administrative users can not access objects unless specifically authorized.
database_vault-1
(Courtesy Oracle America)
In this post, I will demonstrate how to configure Database Vault and demonstrate protecting data from the SYSDBA user.  For this post, I’m using Enterprise Edition Database 12c (12.1.0.2) with Oracle Multi-tenancy on a Windows machine.

Oracle Database Vault Configuration

Database Vault is installed when you perform a default installation of Oracle Database. After the database is installed, you must register Oracle Database Vault and then enable the Oracle Database Vault Account Manager user account. If Oracle Label Security is not enabled, then the registration process enables it as well as Database Vault.
Register Database Vault:
  • Log in as SYS user with SYSDBA privileges
  • Check if Oracle Database Vault has already been enabled. The PARAMETER column is case sensitive.SELECT value FROM V$OPTION WHERE parameter = ‘Oracle Database Vault’; If TRUE, then Database Vault is registered.  If FALSE, then Database Vault registration is required as follow.
Register Database Vault with a common user to manage the CDB Root Container Database:
Create accounts for the Database Vault Owner and Account Manager (DV_OWNER and DV_ACCTMGR Roles)
sys@ORCL> grant create session, set container to c##dbv_owner_root identified by oracle_4U container=all;
Grant succeeded.
sys@ORCL> grant create session, set container to c##dbv_acctmgr_root identified by oracle_4U container=all;
Grant succeeded.
Configure Database Vault user accounts
sys@ORCL> begin
   2  dvsys.configure_dv (
   3    dvowner_uname => 'c##dbv_owner_root',
   4    dvacctmgr_uname => 'c##dbv_acctmgr_root');
   5  end;
   6  /
PL/SQL procedure successfully completed.
Recompile Invalid Database Objects
sys@ORCL> @?/rdbms/admin/utlrp.sql
--
-- Cut for brevity
--
PL/SQL procedure successfully completed.
sys@ORCL>
Just a note – Oracle recommends creating Backup Accounts for the DV Owner and Account Manager Roles. (e.g. c##dbv_owner_root_bkup and c##dbv_acctmgr_root_bkup)
Connect to Root Container as Database Vault Owner and Enable Database Vault
sys@ORCL> connect c##dbv_owner_root/oracle_4U
Connected.
c##dbv_owner_root@ORCL> exec dbms_macadm.enable_dv;
PL/SQL procedure successfully completed.
c##dbv_owner_root@ORCL>
Connect to Root Container as SYSDBA and Bounce the Database.
c##dbv_owner_root@ORCL> connect / as sysdba
Connected.
ys@ORCL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

sys@ORCL> startup
ORACLE instance started.
Total System Global Area 2550136832 bytes
Fixed Size                  3048872 bytes
Variable Size             671091288 bytes
Database Buffers         1862270976 bytes
Redo Buffers               13725696 bytes
Database mounted.
Database opened.
sys@ORCL>
Register Database Vault common user to manage specific PDBs
Grant Privileges for Database Vault Owner and Account Manager in the Pluggable Database (PDB1)
sys@ORCL> connect sys/oracle_4U@//localhost:1521/pdb1.us.oracle.com as sysdba
Connected.
sys@PDB1> grant create session, set container to c##dbv_owner_root container = current;
Grant succeeded.
sys@PDB1> grant create session, set container to c##dbv_acctmgr_root container = current;
Grant succeeded.
Configure Database Vault user accounts for PDB
sys@PDB1> begin
  2  dvsys.configure_dv (
  3     dvowner_uname => 'c##dbv_owner_root',
  4     dvacctmgr_uname => 'c##dbv_acctmgr_root');
  5  end;
  6  /
PL/SQL procedure successfully completed.
Recompile Invalid Database Objects in PDB
sys@PDB1> @?/rdbms/admin/utlrp.sql
--
-- Cut for brevity
--
PL/SQL procedure successfully completed.
Note:  In a multitenant environment, you must register Oracle Database Vault in the root first, then in the PDBs.  If you try to register in a PDB first, then an ORA-47503: Database Vault is not enabled on CDB$ROOT error appears.
Connect to PDB Container as Database Vault Owner and Enable Database Vault
sys@PDB1> connect c##dbv_owner_root/oracle_4U@//localhost:1521/pdb1.us.oracle.com
Connected.
c##dbv_owner_root@PDB1> exec dbms_macadm.enable_dv;
PL/SQL procedure successfully completed.
Connect to Root Container as SYSDBA and Bounce the Pluggable Database
c##dbv_owner_root@PDB1> connect / as sysdba
Connected.
sys@ORCL> alter pluggable database pdb1 close immediate;
Pluggable database altered.
sys@ORCL> alter pluggable database pdb1 open;
Pluggable database altered.
sys@ORCL>

Verify Database Vault is Configured and Enabled

The V$OPTION view and the DVSYS.DBA_DV_STATUS and DBA_OLS_STATUS data dictionary views verify if Oracle Database Vault is configured and enable.
Query the V$OPTION view to verify Database Vault and Label Security are configured
sys@ORCL> connect sys/oracle_4U@//localhost:1521/pdb1.us.oracle.com as sysdba
Connected.
sys@PDB1> select value from v$option where parameter = 'Oracle Database Vault';
VALUE
----------------------------------------------------------------
TRUE
sys@PDB1> select value from v$option where parameter = 'Oracle Label Security';
VALUE
----------------------------------------------------------------
TRUE
Query the DVSYS.DBA_DV_STATUS view to verify Database Vault status
sys@PDB1> select * from dvsys.dba_dv_status;
NAME                STATUS
------------------- ---------------------------------
DV_CONFIGURE_STATUS TRUE
DV_ENABLE_STATUS    TRUE

Database Vault Demonstration:

The V$OPTION view and the DVSYS.DBA_DV_STATUS and DBA_OLS_STATUS data dictionary views verify if Oracle Database Vault is configured and enabled.
Enable the Scott account and grant access to the Order Entry Customer’s table
idle> connect sys/oracle_4U@//localhost:1521/pdb1.us.oracle.com as sysdba
Connected.
sys@PDB1> alter user scott identified by oracle_4U account unlock;
User altered.
sys@PDB1> alter user oe identified by oracle_4U account unlock;
User altered.
sys@PDB1> connect oe/oracle_4U@//localhost:1521/pdb1.us.oracle.com
Connected.
oe@PDB1> grant select on customers to scott;
Grant succeeded.
Query the OE.CUSTOMERS table as SYS and SCOTT
At this stage, both SYS and SCOTT have select privileges on the CUSTOMERS table because SYS has Administrative privileges and SCOTT has a direct grant.
oe@PDB1> connect sys/oracle_4U@//localhost:1521/pdb1.us.oracle.com as sysdba
Connected.
sys@PDB1> select count(*) from oe.customers;
  COUNT(*)
----------
       319
sys@PDB1> connect scott/oracle_4U@//localhost:1521/pdb1.us.oracle.com
Connected.
scott@PDB1> select count(*) from oe.customers;
  COUNT(*)
----------
       319
Create a Realm to Protect the OE.CUSTOMERS Table
I’m sorry – We have to do this the hard way, I don’t have Enterprise Manager Cloud Control on my Laptop
scott@PDB1> connect c##dbv_owner_root/oracle_4U@//localhost:1521/pdb1.us.oracle.com

Connected.

c##dbv_owner_root@PDB1> begin
  2  DBMS_MACADM.CREATE_REALM (
  3     realm_name => 'OE Protections',
  4     description => 'Realm to protect OE schema',
  5     enabled => DBMS_MACUTL.G_YES,
  6     audit_options => DBMS_MACUTL.G_REALM_AUDIT_FAIL,
  7     realm_type => 1);
  8  end;
  9  /
PL/SQL procedure successfully completed.
Add OE tables to Realm for protection
c##dbv_owner_root@PDB1> begin
  2  DBMS_MACADM.ADD_OBJECT_TO_REALM(
  3     realm_name => 'OE Protections',
  4     object_owner => 'OE',
  5     object_name => '%',
  6     object_type => 'TABLE');
  7  end;
  8  /
PL/SQL procedure successfully completed.
c##dbv_owner_root@PDB1>
Create Realm Authorizations for OE and Scott
c##dbv_owner_root@PDB1> begin
  2  DBMS_MACADM.ADD_AUTH_TO_REALM(
  3     realm_name => 'OE Protections',
  4     grantee => 'OE',
  5     auth_options => DBMS_MACUTL.G_REALM_AUTH_OWNER);
  6  end;
  7  /

c##dbv_owner_root@PDB1> begin
  2  dbms_macadm.add_auth_to_realm(
  3     realm_name => 'OE Protections',
  4     grantee => 'SCOTT',
  5     auth_options => DBMS_MACUTL.G_REALM_AUTH_PARTICIPANT);
  6  end;
  7  /
PL/SQL procedure successfully completed.
Test our Database Vault protections for the OE Tables
scott@PDB1> connect oe/oracle_4U@//localhost:1521/pdb1.us.oracle.com
Connected.
oe@PDB1> select table_name from user_tables;
TABLE_NAME
----------------------------------------------------------------------------------------------------
CUSTOMERS
WAREHOUSES
ORDER_ITEMS
ORDERS
INVENTORIES
PRODUCT_INFORMATION
PRODUCT_DESCRIPTIONS
PROMOTIONS
PRODUCT_REF_LIST_NESTEDTAB
SUBCATEGORY_REF_LIST_NESTEDTAB
10 rows selected.

oe@PDB1> select count(*) from customers;
  COUNT(*)
----------
       319

oe@PDB1> select count(*) from warehouses;
  COUNT(*)
----------
         9
This just shows that the Owner of the Realm has access to their tables.
idle> connect scott/oracle_4U@//localhost:1521/pdb1.us.oracle.com
Connected.
scott@PDB1> select count(*) from oe.customers;
  COUNT(*)
----------
       319

scott@PDB1> select count(*) from oe.warehouses;
select count(*) from oe.warehouses
                        *
ERROR at line 1:
ORA-00942: table or view does not exist
This shows that Scott has access to the customers table in the Realm but not the warehouses table.  While the Realm we configured contains all of OE’s tables, any user in the Realm like Scott still needs to have appropriate privileges to the objects in the Realm.
scott@PDB1> connect sys/oracle_4U@//localhost:1521/pdb1.us.oracle.com as sysdba
Connected.
sys@PDB1> select count(*) from oe.customers;
select count(*) from oe.customers
                        *
ERROR at line 1:
ORA-01031: insufficient privileges

sys@PDB1> select count(*) from oe.warehouses;
select count(*) from oe.warehouses
                        *
ERROR at line 1:
ORA-01031: insufficient privileges
Finally, this shows that SYS with SYSDBA privileges does not have authority to view data that is in the Realm!  As you can see, using Database Vault helps prevent the “insider attack”.

References:

Database Vault Administrator’s Guide – https://docs.oracle.com/database/121/DVADM/toc.htm
Database 2 Day + Security Guide – https://docs.oracle.com/database/121/TDPSG/toc.htm
Oracle Sample Schemas on GitHub – https://github.com/oracle/db-sample-schemas
Posted by at No comments:
Labels:

Introduction to Oracle Label Security 12c

In the previous post, we looked at how to support fine-grained access control of tables using Oracle VPD technology. In this post, we are going to have a quick look at another technology with similar purpose called OLS (Oracle Label Security) which is built on top of VPD.

A commonly raised question is that if both VPD and OLS serve the same purpose, why do we need OLS? While VPD is a generic solution, OLS addresses a very specific and common use case in many government and corporate environment - allow access to data based on the need to know basis. Unlike VPD which requires administrator to write PL/SQL code for the policy function, OLS requires no programming. You can think of OLS as a out-of-the-box VPD with built-in policies.

What exact does OLS address?
Many government and business organizations, label their data using different sensitivity levels, such as top secret (TS), secret (S), confidential (C), unclassified (UC), depending on the perceived risk levels so that they can selectively protect most valuable assets of the organization. Users are assigned different clearance levels based on their job functions and seniority. Each user is allowed to access only those data records with the sensitivity level no more than the clearance level of the user. For example, if user tom is assigned the clearance level C, he can access data records with the sensitive levels C or UC only. He is not allowed to access data records with sensitivity levels greater than C, that is, TS or S.

How is OLS enforced?
OLS is a security in depth feature, meaning that users must first have the DAC table privileges necessary to access the OLS protected rows. On top that, there could be VPD policies that may be enforced as well. The following diagram shows the order of enforcement. (Not shown in the diagram: a database vault policy may be enforced on the table as well.)

Figure: OLS Enforcement

How does OLS work?
The following diagram shows the example that we will walk through later in this post. The table flight has data of different sensitivity level and based users' clearance levels they see different set of records even though they issue the same query. Row level access control is enforced transparent to end users.

Figure: OLS Example

How are row sensitivity labels assigned?
When a new record is inserted to an OLS protected table, there are three ways to assign sensitivity label to each row:
- Explicit specify a label (which should be between the maximum and minimum clearance of the user)
- Select 'LABEL_DEFAULT' option when creating the policy and the sessions ROW_LABEL value will be used automatically when inserting records
- Create a labeling function and specify when applying the policy on the table. This will automatically call your labeling function when inserting records

How are user clearance levels decided?
If it is a database user, SA_USER_ADMIN.SET_LEVELS procedure assigns a default level to each user. At the time of login user will have this clearance level. In a session, user can change the clearance level to a level less than the maximum level granted using SA_SESSION.SET_LABEL procedure.

If it is an application user who does not have a database user, SA_SESSION.SET_ACCESS_PROFILE procedure allows to set an existing database user's profile to be the profile of the application user.

A working example:
SQL> -- First do the test setup
SQL> conn sys/password@cdb1_pdb1 as sysdba
Connected.
SQL> create user gov identified by gov;
User created.

 SQL> create user bob identified by bob;
User created.

 SQL> create user tim identified by tim;
User created.

 SQL> grant create session to gov, bob, tim;
Grant succeeded.

 SQL> grant create table, unlimited tablespace to gov;
Grant succeeded.

 SQL> conn gov/gov@cdb1_pdb1
Connected.
SQL> create table flight(flight# number, destination varchar2(100), payload varchar2(100));
Table created.

 SQL> grant select, insert on flight to bob, tim, scott;
Grant succeeded.

 SQL> -- insert test data
SQL> insert into flight values (505, 'Iraq', 'Weapon');
1 row created.
SQL> insert into flight values (506, 'Canada', 'Charcoal');
1 row created.
SQL> insert into flight values (706, 'Japan', 'Battery');
1 row created.
SQL> insert into flight values (501, 'Syria', 'Weapon');
1 row created.
SQL> insert into flight values (508, 'Israel', 'Jets');
1 row created.
SQL> insert into flight values (509, 'India', 'Aid');
1 row created.

 SQL> 
SQL> conn lbacsys/password@cdb1_pdb1
Connected.
SQL> BEGIN
  2  -- Create OLS Policy
  3  -- Notice that the default_options is set to no_control to disable the policy
  4  -- in order add labels to the existing data items
  5  SA_SYSDBA.CREATE_POLICY(
  6    policy_name => 'ols_pol1',
  7    column_name => 'lb_col',
  8    default_options => 'no_control'
  9  );
 10  
 11  -- Create label component levels
 12  -- TOP_SECRET has the highest level of access
 13  SA_COMPONENTS.CREATE_LEVEL(
 14    policy_name => 'ols_pol1',
 15    level_num   => 4,
 16    short_name => 'TS',
 17    long_name   => 'top_secret'
 18  );
 19  
 20  
 21  SA_COMPONENTS.CREATE_LEVEL(
 22    policy_name => 'ols_pol1',
 23    level_num   => 3,
 24    short_name => 'S',
 25    long_name   => 'secret'
 26  );
 27  
 28  SA_COMPONENTS.CREATE_LEVEL(
 29    policy_name => 'ols_pol1',
 30    level_num   => 2,
 31    short_name => 'C',
 32    long_name   => 'confidential'
 33  );
 34  
 35  SA_COMPONENTS.CREATE_LEVEL(
 36    policy_name => 'ols_pol1',
 37    level_num   => 1,
 38    short_name => 'UC',
 39    long_name   => 'unclassified'
 40  );
 41  
 42  -- Create data labels
 43  SA_LABEL_ADMIN.CREATE_LABEL(
 44    policy_name => 'ols_pol1',
 45    label_tag   => 40,
 46    label_value => 'TS',
 47    data_label  => TRUE
 48  );
 49  
 50  SA_LABEL_ADMIN.CREATE_LABEL(
 51    policy_name => 'ols_pol1',
 52    label_tag   => 30,
 53    label_value => 'S',
 54    data_label  => TRUE
 55  );
 56  
 57  SA_LABEL_ADMIN.CREATE_LABEL(
 58    policy_name => 'ols_pol1',
 59    label_tag   => 20,
 60    label_value => 'C',
 61    data_label  => TRUE
 62  );
 63  
 64  SA_LABEL_ADMIN.CREATE_LABEL(
 65    policy_name => 'ols_pol1',
 66    label_tag   => 10,
 67    label_value => 'UC',
 68    data_label  => TRUE
 69  );
 70  
 71  -- Apply access_pol policy on table gov.flight
 72  SA_POLICY_ADMIN.APPLY_TABLE_POLICY(
 73    policy_name       => 'ols_pol1',
 74    schema_name       => 'gov',
 75    table_name        => 'flight',
 76    table_options  => null,
 77    label_function => null,
 78    predicate         => null
 79  );
 80  
 81  -- Add user authorizations (i.e. clearance levels)
 82  SA_USER_ADMIN.SET_LEVELS(
 83    policy_name => 'ols_pol1',
 84    user_name   => 'bob',
 85    max_level   => 'S',
 86    min_level   => 'UC',
 87    def_level   => 'S',
 88    row_level   => 'S'
 89  );
 90  
 91  SA_USER_ADMIN.SET_LEVELS(
 92    policy_name => 'ols_pol1',
 93    user_name   => 'tim',
 94    max_level   => 'UC',
 95    min_level   => 'UC',
 96    def_level   => 'UC',
 97    row_level   => 'UC'
 98  );
 99  END;
100  /
PL/SQL procedure successfully completed.

SQL> 
SQL> conn system/password@cdb1_pdb1
Connected.
SQL> -- Add labels to rows
SQL> update gov.flight set lb_col = char_to_label('ols_pol1', 'TS') where payload in ('Weapon');
2 rows updated.

SQL> update gov.flight set lb_col = char_to_label('ols_pol1', 'S') where payload in ('Jets');
1 row updated.

SQL> update gov.flight set lb_col = char_to_label('ols_pol1', 'C') where payload in ('Battery');
1 row updated.

SQL> update gov.flight set lb_col = char_to_label('ols_pol1', 'UC') where payload in ('Charcoal', 'Aid');
2 rows updated.

SQL> 
SQL> conn lbacsys/password@cdb1_pdb1
Connected.
SQL> BEGIN
  2  -- Now we change the policy to enfoce on read by first altering the policy
  3  -- and then removing and applying the policy again
  4  SA_SYSDBA.ALTER_POLICY(
  5    policy_name => 'ols_pol1',
  6    default_options => 'read_control, label_default'
  7  );
  8  
  9  SA_POLICY_ADMIN.REMOVE_TABLE_POLICY(
 10    policy_name => 'ols_pol1',
 11    schema_name => 'gov',
 12    table_name  => 'flight',
 13    drop_column => false
 14  );
 15  
 16  SA_POLICY_ADMIN.APPLY_TABLE_POLICY(
 17    policy_name => 'ols_pol1',
 18    schema_name => 'gov',
 19    table_name  => 'flight'
 20  );
 21  END;
 22  /

PL/SQL procedure successfully completed.

SQL> -- This is to demonstrate OLS can be bypassed with
SQL> -- special privileges. Here, scott is granted to read
SQL> -- the OLS protected table without any specific 
SQL> -- clearance level  
SQL> BEGIN
  2  SA_USER_ADMIN.SET_USER_PRIVS(
  3    policy_name  => 'ols_pol1',
  4    user_name    => 'scott',
  5    privileges   => 'READ'
  6  );
  7  END;
  8  /
PL/SQL procedure successfully completed.

SQL> column flight# format 9999;
SQL> column destination format a15;
SQL> column payload format a15;
SQL> 
SQL> 
SQL> conn bob/bob@cdb1_pdb1
Connected.
SQL> -- The following demonstrates how to change the row label
SQL> -- of the current session so that that label is used
SQL> -- when bob is inserting new records to gov.flight. 
SQL> select SA_SESSION.ROW_LABEL('ols_pol1') from DUAL;

SA_SESSION.ROW_LABEL('OLS_POL1')
--------------------------------
S

SQL> select SA_SESSION.LABEL('ols_pol1') from DUAL;

SA_SESSION.LABEL('OLS_POL1')
----------------------------
S

SQL> 
SQL> BEGIN
  2  SA_SESSION.SET_ROW_LABEL(
  3    policy_name  => 'ols_pol1',
  4    label        => 'UC'
  5  );
  6  END;
  7  /
PL/SQL procedure successfully completed.

SQL> -- The label of this row is automatically assigned as UC
SQL> insert into gov.flight (flight#, destination, payload) values (599, 'Peru', 'Medicine');
1 row created.

SQL> select flight#, destination, payload from gov.flight;

FLIGHT# DESTINATION     PAYLOAD
------- --------------- ---------------
    506 Canada          Charcoal
    706 Japan           Battery
    508 Israel          Jets
    509 India           Aid
    599 Peru            Medicine

SQL> 
SQL> conn tim/tim@cdb1_pdb1
Connected.
SQL> select flight#, destination, payload from gov.flight;

FLIGHT# DESTINATION     PAYLOAD
------- --------------- ---------------
    506 Canada          Charcoal
    509 India           Aid
    599 Peru            Medicine

SQL> 
SQL> conn scott/tiger@cdb1_pdb1
Connected.
SQL> select * from gov.flight;

FLIGHT# DESTINATION     PAYLOAD             LB_COL
------- --------------- --------------- ----------
    505 Iraq            Weapon                  40
    506 Canada          Charcoal                10
    706 Japan           Battery                 20
    501 Syria           Weapon                  40
    508 Israel          Jets                    30
    509 India           Aid                     10
    599 Peru            Medicine                10

7 rows selected.
SQL> -- Now we do the clean up
SQL> conn lbacsys/password@cdb1_pdb1
Connected.
SQL> BEGIN
  2  SA_SYSDBA.DROP_POLICY(
  3    policy_name => 'ols_pol1'
  4  );
  5  END;
  6  /
PL/SQL procedure successfully completed.

SQL> 
SQL> conn sys/password@cdb1_pdb1 as sysdba
Connected.
SQL> drop user gov cascade;
User dropped.
SQL> drop user bob cascade;
User dropped.
SQL> drop user tim cascade;
User dropped.
There is more to OLS:
I only scratched the surface of OLS. A data label is actually consists of three components:

1. Sensitivity level - has an ordering relationship
2. Compartment (optional) - used to segregate data within a level
3. Group (optional) - used to organizationally segregate data within a level - has a hierarchical relationship

In order for a user to able to access a labeled record, the following conditions must be satisfied:
a. User's clearance level should be at least as high as the the data record's sensitivity level
b. User has all the compartments associated with the data record
c. User belongs to one of the groups associated with the data record or a parent group of one of the associated groups

With the quick understanding of the OLS technology, I encourage you to look the Oracle documentation to learn more about OLS and its new powerful features.
Posted by at No comments:
Labels:

Tuesday, 5 February 2019

Enable Oracle 12c Unified Auditing - Pure Mode

Why we want Pure Mode?
The first is the audit trails are no longer written to their traditional pre-12c audit locations. Auditing is consolidated into the Unified Audit views and stored using Oracle SecureFiles. Oracle Secured Files use a proprietary format which means that Unified Audit logs cannot be viewed using editors such vi and may preclude or affect the use of third party logging solutions such as Splunk or HP ArcSight.

Operations done by SYS are also recorded.

Unified Auditing comes standard with Oracle Enterprise Edition; no additional license is required. It is installed by default, but not fully enabled by default.

In Command window
rename %ORACLE_HOME%/bin/orauniaud12.dll.dbl file %ORACLE_HOME%/bin/orauniaud12.dll

In sqlplus as SYSDBA
SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.

In Command window as Administrator
net stop OracleService<sid>
net start OracleService<sid>

Now pure mode unified auditing is enabled. Let's check.
In sqlplus as SYSDBA
SQL*Plus: Release 12.1.0.2.0 Production on Sat Dec 9 01:03:46 2017

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, Oracle Label Security, OLAP, Advanced Analytics,
Real Application Testing and Unified Auditing options

SQL> SELECT value FROM v$option WHERE parameter = 'Unified Auditing';

VALUE
----------------------------------------------------------------
TRUE

Check out the audit output
SELECT * FROM unified_audit_trail
ORDER BY event_timestamp DESC;

For performance reason, you may want to use queued-write method
In sqlplus as SYSDBA
BEGIN
DBMS_AUDIT_MGMT.set_audit_trail_property(
audit_trail_type           => DBMS_AUDIT_MGMT.audit_trail_unified,
audit_trail_property       => DBMS_AUDIT_MGMT.audit_trail_write_mode, 
audit_trail_property_value => DBMS_AUDIT_MGMT.audit_trail_queued_write
);
END;
/

Check configuration 
SELECT * FROM dba_audit_mgmt_config_params
order by audit_trail, parameter_name;

Check what is being auditing out of the box
SELECT * FROM audit_unified_policies
ORDER BY policy_name,
         audit_option;

SELECT * FROM AUDIT_UNIFIED_ENABLED_POLICIES;

Setup how many days of audit records we want to keep
BEGIN
DBMS_AUDIT_MGMT.set_last_archive_timestamp(
audit_trail_type     => DBMS_AUDIT_MGMT.audit_trail_unified,
last_archive_time    => SYSTIMESTAMP-90
);
END;
/

Check archive setting
SELECT audit_trail,
last_archive_ts
FROM   dba_audit_mgmt_last_arch_ts;

We have to run this for the very first time
BEGIN
DBMS_AUDIT_MGMT.INIT_CLEANUP(
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.audit_trail_all,
DEFAULT_CLEANUP_INTERVAL => 24 /*hours*/
);
END;
/

Let's purge manually
SELECT COUNT(*) FROM unified_audit_trail;
BEGIN
DBMS_AUDIT_MGMT.clean_audit_trail(
audit_trail_type        => DBMS_AUDIT_MGMT.audit_trail_unified,
use_last_arch_timestamp => TRUE);
END;
/
SELECT COUNT(*) FROM unified_audit_trail;

Let's automate this purging process by setting up scheduled job
DBMS_SCHEDULER.create_job (
job_name        => 'audit_last_archive_time',
job_type        => 'PLSQL_BLOCK',
job_action      => 'DECLARE
l_days NUMBER := 90;
BEGIN
  DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(DBMS_AUDIT_MGMT.audit_trail_unified, TRUNC(SYSTIMESTAMP)-l_days);
  DBMS_AUDIT_MGMT.clean_audit_trail(
    audit_trail_type        => DBMS_AUDIT_MGMT.audit_trail_unified,
    use_last_arch_timestamp => TRUE);
END;',
start_date      => SYSTIMESTAMP,
repeat_interval => 'freq=daily; byhour=1; byminute=0; bysecond=0;',
end_date        => NULL,
enabled         => TRUE,
comments        => 'Automatically set audit last archive time.');
END;
/
Posted by at No comments:
Labels:

Friday, 14 September 2018

Oracle Drop if exists 

BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE ' || table_name;
EXCEPTION
   WHEN OTHERS THEN
      IF SQLCODE != -942 THEN
         RAISE;
      END IF;
END;

ADDENDUM For reference, here are the equivalent blocks for other object types:

Sequence

BEGIN
  EXECUTE IMMEDIATE 'DROP SEQUENCE ' || sequence_name;
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -2289 THEN
      RAISE;
    END IF;
END;

View

BEGIN
  EXECUTE IMMEDIATE 'DROP VIEW ' || view_name;
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -942 THEN
      RAISE;
    END IF;
END;

Trigger

BEGIN
  EXECUTE IMMEDIATE 'DROP TRIGGER ' || trigger_name;
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -4080 THEN
      RAISE;
    END IF;
END;

Index

BEGIN
  EXECUTE IMMEDIATE 'DROP INDEX ' || index_name;
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -1418 THEN
      RAISE;
    END IF;
END;

Column

BEGIN
  EXECUTE IMMEDIATE 'ALTER TABLE ' || table_name
                || ' DROP COLUMN ' || column_name;
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -904 THEN
      RAISE;
    END IF;
END;

Database Link

BEGIN
  EXECUTE IMMEDIATE 'DROP DATABASE LINK ' || dblink_name;
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -2024 THEN
      RAISE;
    END IF;
END;

Materialized View

BEGIN
  EXECUTE IMMEDIATE 'DROP MATERIALIZED VIEW ' || mview_name;
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -12003 THEN
      RAISE;
    END IF;
END;

Type

BEGIN
  EXECUTE IMMEDIATE 'DROP TYPE ' || type_name;
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -4043 THEN
      RAISE;
    END IF;
END;

Constraint

BEGIN
  EXECUTE IMMEDIATE 'ALTER TABLE ' || table_name
            || ' DROP CONSTRAINT ' || constraint_name;
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -2443 THEN
      RAISE;
    END IF;
END;

Scheduler Job

BEGIN
  DBMS_SCHEDULER.drop_job(job_name);
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -27475 THEN
      RAISE;
    END IF;
END;

User / Schema

BEGIN
  EXECUTE IMMEDIATE 'DROP USER ' || user_name;
  /* you may or may not want to add CASCADE */
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -1918 THEN
      RAISE;
    END IF;
END;

Package

BEGIN
  EXECUTE IMMEDIATE 'DROP PACKAGE ' || package_name;
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -4043 THEN
      RAISE;
    END IF;
END;

Procedure

BEGIN
  EXECUTE IMMEDIATE 'DROP PROCEDURE ' || procedure_name;
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -4043 THEN
      RAISE;
    END IF;
END;

Function

BEGIN
  EXECUTE IMMEDIATE 'DROP FUNCTION ' || function_name;
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -4043 THEN
      RAISE;
    END IF;
END;

Tablespace

BEGIN
  EXECUTE IMMEDIATE 'DROP TABLESPACE' || tablespace_name;
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -959 THEN
      RAISE;
    END IF;
END;

Posted by at No comments:
Labels:

Thursday, 6 September 2018

Copy oracle schema to Another schema 

First create a database link;


CREATE DATABASE LINK loopback CONNECT TO tomasz  IDENTIFIED BY dbaora  USING 'PORA12C1.dbaora.com';

then create procedure ;

CREATE OR REPLACE PROCEDURE pro_copy_schema 
(
  in_srce_user        IN VARCHAR2,
  in_dest_user        IN VARCHAR2,
  in_dest_user_pswd   IN VARCHAR2,
  in_paral_lvl        IN NUMBER   DEFAULT 8,
  in_netwk_link       IN VARCHAR2 DEFAULT 'loopback',
  in_incld_rows       IN NUMBER   DEFAULT 1 
) 
AUTHID CURRENT_USER
AS
  l_hnd    NUMBER; -- job handle
  l_js     user_datapump_jobs.state%TYPE; -- to hold job status
  l_q      VARCHAR2(1) := chr(39); -- single quote
BEGIN
  /* open a new schema level import job using our loopback DB link */
  l_hnd := dbms_datapump.open ('IMPORT','SCHEMA', in_netwk_link);

  /* set parallel level */
  dbms_datapump.set_parallel(handle => l_hnd, degree => in_paral_lvl);
  
  /* make any data copied consistent with respect to now */
  dbms_datapump.set_parameter
  (l_hnd, 'FLASHBACK_SCN', dbms_flashback.get_system_change_number);
  
  /* restrict to the schema we want to copy */
  dbms_datapump.metadata_filter
  (l_hnd, 'SCHEMA_LIST', l_q || in_srce_user || l_q);
  
  /* remap the importing schema name to the schema we want to create */
  dbms_datapump.metadata_remap
  (l_hnd,'REMAP_SCHEMA',in_srce_user,in_dest_user);
  
  /* copy_data for each table or not 1 - yes 0 - meta data only */
  dbms_datapump.data_filter
  (l_hnd,'INCLUDE_ROWS',in_incld_rows,NULL,NULL);

  /* start the job */
  dbms_datapump.start_job(l_hnd);
  
  /* wait for the job to finish */
  dbms_datapump.wait_for_job(l_hnd, l_js);
  
  /* change the password for new user */
  EXECUTE IMMEDIATE 'ALTER USER ' || in_dest_user || 
                    ' IDENTIFIED BY ' || in_dest_user_pswd;
END;
/

and then execute ;
BEGIN
  pro_copy_schema 
  (
    in_srce_user      => 'SRC_USER',
    in_dest_user      => 'DST_USER',
    in_dest_user_pswd => 'dst_user_passwd',
    in_paral_lvl      => 8,
    in_netwk_link     => 'LOOPBACK',
    in_incld_rows     => 1 
  );
END;
/

Source : http://dbaora.com/oracle-clone-schema-using-plsql/

Posted by at No comments:
Labels:
Subscribe to: Comments (Atom)