How To Manually Install OLS , DV In PDB In Multitenant Enviornment (Doc ID 2362597.1)

Scenario: If the target database is migrated from rman backup, then dbvault  is not installed.
In this case the following steps should be followed.

1. As user who has been granted the SYSDBA administrative privilege, log in to the PDB in which you want to install Oracle Database Vault.

For example, to log in to a PDB named hr_pdb:

sqlplus sec_admin@hr_pdb as sysdba
Enter password: password

Note: To find the available PDBs, query the DBA_PDBS data dictionary view. To check the current PDB, run the 'show con_name' command.

2. If necessary, Query DBA_DV_STATUS, CDB_DV_STATUS, DBA_OLS_STATUS, and CDB_OLS_STATUS data dictionary views to verify if Oracle Database Vault and Oracle Label Security are already configured and enabled in this PDB. If not, proceed to next step.


3. Install Oracle Label Security by executing the catols.sql script.

@$ORACLE_HOME/rdbms/admin/catols.sql

Oracle Label Security must be installed before you can use Oracle Database Vault.


4. Install Oracle Database Vault by executing the catmac.sql script.

@$ORACLE_HOME/rdbms/admin/catmac.sql

At the Enter value for 1 prompt, enter the default tablespace for the PDB.

At the Enter value for 2 prompt, enter the temporary tablespace for the PDB.

 5. After the installation is complete, you can register Oracle Database Vault.

Database Vault 12cR2 installation and configuration

Starting with Oracle Database 12c, Oracle Database Vault is installed by default but not enabled. Customers can enable it using DBCA or from the command line using SQL*Plus.

This post covers the following topics:

1. DB Vault Enable

2. Realm Creation

3.  DB vault testing

4.  Secure Applicatin Role Creation

# Step_01: Checking the DB Vault service is enabled or not.
---------------------------------------------------------
SQL> SELECT parameter, VALUE FROM v$option WHERE parameter = 'Oracle Database Vault';

PARAMETER                                                        VALUE
---------------------------------------------------------------- -----------------------------
Oracle DATABASE Vault                                            FALSE

----------------------------------------------------------------
# Step_02: Create user dbv_owner and dbv_acctmgr if doesn't exist
----------------------------------------------------------------

SQL> CREATE USER dbv_owner IDENTIFIED BY dbv_owner ;

USER created.

SQL> CREATE USER dbv_acctmgr IDENTIFIED BY dbv_acctmgr;

USER created.

----------------------------------------------------------------------------------------------
# Step_03: Configure DBVAULT for those users
----------------------------------------------------------------------------------------------

SQL> EXEC DVSYS.CONFIGURE_DV (dvowner_uname => 'dbv_owner', dvacctmgr_uname => 'dbv_acctmgr');

-----------------------------------------------------------------------------------------------
# Step_4: Enable DBVAULT
-----------------------------------------------------------------------------------------------

SQL> EXEC DVSYS.CONFIGURE_DV (dvowner_uname => 'dbv_owner', dvacctmgr_uname => 'dbv_acctmgr');

PL/SQL PROCEDURE successfully completed.

SQL> CONNECT dbv_owner/dbv_owner
Connected.
SQL> EXEC dbms_macadm.enable_dv;

PL/SQL PROCEDURE successfully completed.

-----------------------------------------------------------------------------------------------
# Step_5: Recheck DBVAULT Status
-----------------------------------------------------------------------------------------------

SQL> SET lines 150
SQL> SELECT parameter, VALUE FROM v$option WHERE parameter = 'Oracle Database Vault';

PARAMETER                                                        VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
Oracle DATABASE Vault                                            TRUE



#======== Database Vault 12cR2 testing (Manually REALM Creation) ===========

-----------------------------------------------------------------------------------------------
# Step_1: Creating an object on CMS Schema to apply REALM
-----------------------------------------------------------------------------------------------
SQL> conn cms/cms
Connected.
SQL>
SQL>
SQL> create table cms.app (sl number, host varchar2(20));

Table created.

SQL> insert into app values (10, 'app1.aibl.com');

1 row created.

SQL> commit;

Commit complete.

-----------------------------------------------------------------------------------------------
# Step_2: Creating another user to access object on CMS schema
-----------------------------------------------------------------------------------------------

SQL> conn dbv_acctmgr/dbv_acctmgr
Connected.
SQL>
SQL>
SQL> create user app_read identified by app_read;

User created.

SQL> GRANT CONNECT TO app_read;

Grant succeeded.

SQL> conn sys / as sysdba
Connected.

SQL> GRANT SELECT, UPDATE ON cms.app to app_read;

------------------------------------------------------------------------------------------------------------------------------------------
# Step_3: REALM CREATION:
# In PL/SQL it gives the two below command, parameter realm_type is set to 0 to avoid creation of a mandatory realms which means that
# objects owner (CMS) will still have full access to its objects. I also activate the realms right after its creation (enabled parameter):
--------------------------------------------------------------------------------------------------------------------

SQL> conn dbv_acctmgr/dbv_acctmgr
Connected.

SQL> EXEC dbms_macadm.create_realm(realm_name => 'CMS schema', description => 'Protect CMS Schema ', enabled => 'Y', audit_options => 1, realm_type =>'0' );

PL/SQL procedure successfully completed.

-----------------------------------------------------------------------------------------------
# Step_4: Add Object to REALM
-----------------------------------------------------------------------------------------------------------------

SQL> EXEC dbms_macadm.add_object_to_realm(realm_name => 'CMS schema', object_owner => 'CMS', object_name => '%', object_type => '%' );

PL/SQL procedure successfully completed.

-------------------------------------------------------------------------------------------------------
# Step_5: Add Authorizaion to REALM:
# If you grant the APP account as a participant to its own realm (can also be done with Cloud Control):
-------------------------------------------------------------------------------------------------------

SQL> EXEC dbms_macadm.add_auth_to_realm(realm_name => 'CMS schema', grantee => 'CMS', rule_set_name => '', auth_options => DBMS_MACUTL.G_REALM_AUTH_PARTICIPANT);

PL/SQL procedure successfully completed.

-------------------------------------------------------------------------------------------------
#  Checking the impact of REALM from SYS/System user who has the sysdba grant:
-------------------------------------------------------------------------------------------------

Step_1:
SQL> conn sys / as sysdba
Connected.

SQL> select * from cms.app;
select * from cms.app
                  *
ERROR at line 1:
ORA-01031: insufficient privileges

--------------------------------------------------------------------------------------------------
# Step_2: Checking the impact of REALM from a different user who has the right to read the object:
--------------------------------------------------------------------------------------------------

SQL> conn app_read/app_read
Connected.

SQL>
SQL> select * from cms.app;

        SL HOST
---------- --------------------
        10 app1.aibl.com


------------------------------------------------------------------------------------------------
# Step_3: Checking the impact of REALM from the schema user:
------------------------------------------------------------------------------------------------

SQL> conn cms/cms
Connected.

SQL>
SQL> select * from cms.app;

        SL HOST
---------- --------------------
        10 app1.aibl.com


## -------------- CHANGING REALM ROLE ------------

SQL> EXEC dbms_macadm.delete_auth_from_realm(realm_name => 'CMS schema', grantee => 'CMS');

PL/SQL PROCEDURE successfully completed.

SQL> EXEC dbms_macadm.add_auth_to_realm(realm_name => 'CMS schema', grantee => 'CMS', rule_set_name => '', auth_options => DBMS_MACUTL.G_REALM_AUTH_OWNER);

PL/SQL PROCEDURE successfully completed.

## Note: now owner of the schema can grant it's own objects now:

## ------------- REMOVE REALM -----------------

To remove everything simply execute:

SQL> EXEC dbms_macadm.delete_realm(realm_name => 'CMS schema');

PL/SQL PROCEDURE successfully completed.




===============Create Secure Applicatin Role===============================


Step 1: Create a Rule

begin
DVSYS.DBMS_MACADM.CREATE_RULE(rule_name => 'CMSAPPRULE', rule_expr => 'SYS_CONTEXT('USERENV', 'SESSION_USER') != 'SCOTT'');
end;

Step 2: Create a Rule Set

begin
DECLARE x VARCHAR2(40);
static_option BOOLEAN := FALSE;
BEGIN
x:='N';
IF x = 'Y' THEN
static_option := TRUE;
ELSE static_option := FALSE;
END IF;
DVSYS.DBMS_MACADM.CREATE_RULE_SET(rule_set_name => 'APP_USER_AUTH', description => ' ssss', enabled => 'Y', eval_options => 1, audit_options => 1, fail_options => 1, fail_message => '', fail_code => '', handler_options => 0, handler => '',is_static => static_option);
END;
DVSYS.DBMS_MACADM.ADD_RULE_TO_RULE_SET(rule_set_name => 'APP_USER_AUTH', rule_name => 'CMSAPPRULE', rule_order => '1', enabled => 'Y');
end;

Step 3:
Create a Secure Application Role

begin
DVSYS.DBMS_MACADM.CREATE_ROLE(role_name=>DBMS_ASSERT.ENQUOTE_NAME('DV_APP_USR_ROLE'), enabled => 'Y', rule_set_name => 'APP_USER_AUTH');
end;


Step 4: grant to created DV Role

GRANT SELECT, UPDATE ON APP TO DV_APP_USR_ROLE;
GRANT SELECT, UPDATE ON TEST01 TO DV_APP_USR_ROLE;

Step 5:
SQL>  EXEC DBMS_MACSEC_ROLES.SET_ROLE('DV_APP_USR_ROLE');

PL/SQL procedure successfully completed.

RMAN-04006: error from auxiliary database: ORA-01017: invalid username/password; logon denied

Scenario: When creating a physical standby database using rman duplication command

bash-4.2$ rman target sys/pwd@primary auxiliary sys/pwd @stby

After running the above command, then the following error shows:

RMAN-04006: error from auxiliary database: 

ORA-01017: invalid username/password; logon denied

Solution:

Case 1:

SQL> show parameter REMOTE_LOGIN_PASSWORD

NAME                                 TYPE                              VALUE

------------------------------------ --------------------------------- ------------------------------

remote_login_passwordfile            string                            EXCLUSIVE

SQL>show parameter REMOTE_OS_AUTHENT

NAME                                 TYPE                              VALUE

------------------------------------ --------------------------------- ------------------------------

remote_os_authent                    boolean                           FALSE

By default remote_login_passwordfile is EXCLUSIVE and remote_os_authent is FALSE.

If  other than EXCLUSIVE and FALSE then run the following command, which should resolve the problem.

SQL> alter system set REMOTE_LOGIN_PASSWORD= EXCLUSIVE

SQL> alter system set REMOTE_OS_AUTHENT = FALSE

Case 2:

I have such problem where I never explicitly set a password for the SYS user in the newly created database and I could still connect locally. I have fixed that by the following way:

1.      Execute the following command in primary/target database.

SQL>ALTER USER SYS IDENTIFIED BY pwd;

2.      The command update the password file automatically $ORACLE_HOME/dbs/orapwORACLE_SID

3.      Copy password file to standby server

Transparent Data Encryption (TDE) In Oracle 12c

-- Creating Keystore directory
$ mkdir -p /orafs/app/oracle/12.2.0.1/db_1/server_wallet_home

-- Editing sqlnet.ora file as following:
$ vi /orafs/app/oracle/12.2.0.1/db_1/network/admin/sqlnet.ora

### Advance security wallet location
ENCRYPTION_WALLET_LOCATION =
 (SOURCE =
   (METHOD = FILE)
   (METHOD_DATA =
     (DIRECTORY = /orafs/app/oracle/12.2.0.1/db_1/server_wallet_home)
    )
  )


-- Bouncing Database 
SQL > shutdown immediate;
SQL > startup;

-- Checking status of wallet
SQL > set pages 200 lines 200
col WRL_PARAMETER for a100
SELECT wrl_type, wrl_parameter, status from gv$encryption_wallet order by inst_id;

## Query status: NOT_AVAILABLE

-- Creating the keystore
SQL> administer key management create keystore '/orafs/app/oracle/12.2.0.1/db_1/server_wallet_home' identified by "wlt123";

-- Checking status of wallet
SQL > set pages 200 lines 200
col WRL_PARAMETER for a100
SELECT wrl_type, wrl_parameter, status from gv$encryption_wallet order by inst_id;

## Query status: CLOSED

-- Opening the keystore

SQL> administer key management set keystore open identified by "wlt123";

-- Checking status of wallet
SQL > set pages 200 lines 200
col WRL_PARAMETER for a100
SELECT wrl_type, wrl_parameter, status from gv$encryption_wallet order by inst_id;

## Query status : OPEN_NO_MASTER_KEY

-- Creating a MASTER KEY
SQL> administer key management set key identified by "wlt123" with backup;

-- Checking status of wallet
SQL > set pages 200 lines 200
col WRL_PARAMETER for a100
SELECT wrl_type, wrl_parameter, status from gv$encryption_wallet order by inst_id;

## Query status : OPEN


-- Creating auto login setup :

SQL> administer key management create auto_login keystore from keystore '/orafs/app/oracle/12.2.0.1/db_1/server_wallet_home' identified by "wlt123";





 ======================== Checking TDE =======================

---- Encrypting column of an existing table:

SQL> alter table test.account modify (ACCNAME encrypt);

Table altered.

-- You can turn off the encryption for a table:
SQL> alter table test.account modify (ACCNAME decrypt);

Table altered.


--- Encrypting a tablespace online using TDE encryption

SQL> alter tablespace test_tsp ENCRYPTION online ENCRYPT FILE_NAME_CONVERT = ('test_dbs.dbf', 'test_dbs_encrypt.dbf');

Tablespace altered.


SQL> select file_name from dba_data_files where tablespace_name = 'test_tsp';

FILE_NAME
-------------------------------------------------------------------------------------
/u01/app/oracle/oradata/test_dbs_encrypt.dbf


--- Decrypting the same tablespace online using TDE encryption
SQL> alter tablespace saidur_tsp ENCRYPTION ONLINE DECRYPT FILE_NAME_CONVERT = ('test_dbs_encrypt.dbf', 'test_dbs.dbf');

Tablespace altered.


 ============================== End =====================