Installing DB Vault to an Oracle 12c non-Container Database

Database Vault is a product by which you can restrict access even for SYS, DBA users and so on , so this tool is for Security Administrators to control even DBAs.

We are configuring Oracle Audit Vault and Database Firewall  which is one product and used to find SQL injections and block harmful SQL statements . This product is perfect when you are not connecting locally. But if you connected to the server via ssh and from there connected to the database using sqlplus then Oracle AVDF will not help. For that case there exists Database Vault.

Don’t be consufed : Database Vault  and Audit Vault are two different products. DB Vault should be inabled from the database , let’s see how we do it:

— To check Oracle Label security And DB Vault use below SQL

SQL> select comp_id,status from dba_registry where comp_id in (‘OLS’,’DV’);
COMP_ID STATUS
—————————— ——————————————–
OLS VALID

Note: As you see in my database Label Security is already installed but Database Vault not.

— Check DB vault if already registered

SQL> SELECT parameter,value FROM gv$OPTION WHERE PARAMETER in
2 ( ‘Oracle Database Vault’,’Oracle Label Security’);
PARAMETER VALUE
—————————————————————- —————————————————————-
Oracle Label Security TRUE
Oracle Database Vault FALSE

–Take  backup of  some tables and views.

SQL> create table a_dba_network_acls as
select * FROM  cdb_network_acls;

SQL> create table a_dba_network_acl_privileges as
select * from cdb_network_acl_privileges;

SQL> create table a_gv$parameter as
select * from gv$parameter ;

SQL> create table a_dba_tab_privs as
Select * from dba_tab_privs;

SQL> create table a_dba_sys_privs as
Select * from dba_sys_privs;

SQL> create table a_dba_sys_privs as
Select * from dba_sys_privs;

SQL> create table a_dba_role_privs as
Select * from dba_role_privs;

SQL> create table a_dba_objects as
select owner,object_name,object_type
from dba_objects
where status=’INVALID’ and object_type <> ‘SYNONYM’ ;

SQL> create table a_dba_registry as
select * from dba_registry;

Note: You must consider that enabling Database Vault causes the followng:

1. Enables parameter sql92_security (needs database restart) .

“The SQL92 standards specify that security administrators should be able to require that users have SELECT privilege on a table when executing an UPDATE or DELETE statement that references table column values in  WHERE or SET clause. SQL92_SECURITY specifies whether users must have been granted the SELECT object privilege in order to execute such UPDATE or DELETE statements.”

So better to generate script that will grant necessary SELECT privileges:

select ‘grant select on ‘||owner||’.”‘||table_name||'” to ‘||grantee||’;’
from (
select grantee, owner, table_name
from dba_tab_privs
where privilege in (‘UPDATE’,’DELETE’,’INSERT’)
minus
select grantee, owner, table_name
from dba_tab_privs
where privilege=’SELECT’
)

Run grants generated above not to have problems after restart.

2. Revokes some privileges from roles and users (even from SYS). So use the following queries to generate grant scripts and in case it is necessary regrant them.

select ‘grant ‘||privilege||’ on ‘||owner||’.’||table_name||’ to ‘|| grantee||’;’
from (
select * from a_dba_tab_privs
minus
select * from dba_tab_privs
);

select ‘grant ‘||privilege||’ to ‘|| grantee||’;’
from (
select * from a_dba_sys_privs
minus
select * from dba_sys_privs
);

select ‘grant ‘||granted_role||’ to ‘|| grantee||’;’
from (
select * from a_dba_role_privs
minus
select * from dba_role_privs
)

–Create DB Vault owner and User Administrator users

SQL> CREATE USER dvowner IDENTIFIED BY oracle
DEFAULT TABLESPACE USERS
QUOTA UNLIMITED ON USERS;

SQL> GRANT CREATE SESSION TO dvowner;

SQL> CREATE USER dvacctmngr IDENTIFIED BY oracle
DEFAULT TABLESPACE USERS
QUOTA UNLIMITED ON USERS;

SQL> GRANT CREATE SESSION TO dvacctmngr;

 

–Configure DB Vault

SQL> BEGIN
DVSYS.CONFIGURE_DV (
dvowner_uname => ‘dvowner’,
dvacctmgr_uname => ‘dvacctmngr’);
END;
/

If your environment is like mine than you will see the followng error and go directly to the section “Scenario 2”

ORA-06550: line 2, column 1:
PLS-00201: identifier ‘DVSYS.CONFIGURE_DV’ must be declared
ORA-06550: line 2, column 1:
PL/SQL: Statement ignored

Let’s continue the Scenario 1 when you have no errors after running DVSYS.CONFIGURE_DV and then I will write how to avoid this error.

–Recompile objects

SQL> @?/rdbms/admin/utlrp.sql

–Enable Database Vault

SQL> EXEC DBMS_MACADM.ENABLE_DV;
SQL> commit;

–Startup the Database and the installation is finished

SQL> shutdown immediate;
SQL> startup;

That’s it.

Scenario 2: Having errors: 

We should use DBCA to add option DV, I don’t support GUIs so writing script in silent mode:

–Check what options we have in DBCA

[oracle@TCIPreRel bin]$ dbca -CONFIGUREDATABASE -silent -h

-configureDatabase
-sourceDB <Database unique name for RAC Database and SID for Single Instance Database>
[-sysDBAUserName <user name with SYSDBA privileges>
-sysDBAPassword <password for sysDBAUserName user name>]
[-registerWithDirService|-unregisterWithDirService|-regenerateDBPassword <true | false>
-dirServiceUserName <user name for directory service>
-dirServicePassword <password for directory service >
-walletPassword <password for database wallet >]
[-addDBOption <Specify any of the following DB Options as a comma separated list: JSERVER | ORACLE_TEXT | IMEDIA | CWMLITE | SPATIAL | OMS | APEX | DV>]

[-dvConfiguration <true | false Specify “true” to configure and enable Database Vault
-dvUserName <Specify Database Vault Owner user name>
-dvUserPassword <Specify Database Vault Owner password>
-dvAccountManagerName <Specify separate Database Vault Account Manager >
-dvAccountManagerPassword <Specify Database Vault Account Manager password>]

–Run the configuration

[oracle@TCIPreRel bin]$ dbca -CONFIGUREDATABASE -silent -sourceDB LBTCI -sysDBAUserName sys -sysDBAPassword oracle -addDBOption DV -dvConfiguration true -dvUserName dvowne -dvUserPassword oracle -dvAccountManagerName dvacctmngr -dvAccountManagerPassword oracle

Preparing to Configure Database
2% complete
5% complete
28% complete
Adding Oracle Database Vault
85% complete
Completing Database Configuration
100% complete
Look at the log file “/u01/app/oracle/cfgtoollogs/dbca/LBTCI/LBTCI0.log” for further details.

–Restart database

srvctl stop database -db LBTCI
srvctl start database -db LBTCI

–Let’s check

SQL> SELECT parameter,value
FROM gv$OPTION
WHERE PARAMETER in ( ‘Oracle Database Vault’,’Oracle Label Security’);
PARAMETER VALUE
—————————————————————- —————————————————————-
Oracle Label Security TRUE
Oracle Database Vault TRUE

 

Advertisements

ORACLE-BASE: Network ACL ddl generator script

After enabling Database Vault in our database there was a chance that this option may have changed ACL entries , so decided to save old entries and generated as a script.

If you have many ACLs than this job becomes time consuming.

I found the simple script that does it for us.

ORACLE-BASE site : https://oracle-base.com/dba/script?category=script_creation&file=network_acls_ddl.sql

SET SERVEROUTPUT ON FORMAT WRAPPED LINESIZE 300
DECLARE
 l_last_acl dba_network_acls.acl%TYPE := '~';
 l_last_principal dba_network_acl_privileges.principal%TYPE := '~';
 l_last_privilege dba_network_acl_privileges.privilege%TYPE := '~';
 l_last_host dba_network_acls.host%TYPE := '~';

FUNCTION get_timestamp (p_timestamp IN TIMESTAMP WITH TIME ZONE)
 RETURN VARCHAR2
 AS
 l_return VARCHAR2(32767);
 BEGIN
 IF p_timestamp IS NULL THEN
 RETURN 'NULL';
 END IF;
 RETURN 'TO_TIMESTAMP_TZ(''' || TO_CHAR(p_timestamp, 'DD-MON-YYYY HH24:MI:SS.FF TZH:TZM') || ''',''DD-MON-YYYY HH24:MI:SS.FF TZH:TZM'')';
 END;
BEGIN
 FOR i IN (SELECT a.acl,
 a.host,
 a.lower_port,
 a.upper_port,
 b.principal,
 b.privilege,
 b.is_grant,
 b.start_date,
 b.end_date
 FROM dba_network_acls a
 JOIN dba_network_acl_privileges b ON a.acl = b.acl
 ORDER BY a.acl, a.host, a.lower_port, a.upper_port)
 LOOP
 IF l_last_acl <> i.acl THEN
 -- First time we've seen this ACL, so create a new one.
 l_last_host := '~';

DBMS_OUTPUT.put_line('-- -------------------------------------------------');
 DBMS_OUTPUT.put_line('-- ' || i.acl);
 DBMS_OUTPUT.put_line('-- -------------------------------------------------');
 DBMS_OUTPUT.put_line('BEGIN');
 DBMS_OUTPUT.put_line(' DBMS_NETWORK_ACL_ADMIN.drop_acl (');
 DBMS_OUTPUT.put_line(' acl => ''' || i.acl || ''');');
 DBMS_OUTPUT.put_line(' COMMIT;');
 DBMS_OUTPUT.put_line('END;');
 DBMS_OUTPUT.put_line('/');
 DBMS_OUTPUT.put_line(' ');
 DBMS_OUTPUT.put_line('BEGIN');
 DBMS_OUTPUT.put_line(' DBMS_NETWORK_ACL_ADMIN.create_acl (');
 DBMS_OUTPUT.put_line(' acl => ''' || i.acl || ''',');
 DBMS_OUTPUT.put_line(' description => ''' || i.acl || ''',');
 DBMS_OUTPUT.put_line(' principal => ''' || i.principal || ''',');
 DBMS_OUTPUT.put_line(' is_grant => ' || i.is_grant || ',');
 DBMS_OUTPUT.put_line(' privilege => ''' || i.privilege || ''',');
 DBMS_OUTPUT.put_line(' start_date => ' || get_timestamp(i.start_date) || ',');
 DBMS_OUTPUT.put_line(' end_date => ' || get_timestamp(i.end_date) || ');');
 DBMS_OUTPUT.put_line(' COMMIT;');
 DBMS_OUTPUT.put_line('END;');
 DBMS_OUTPUT.put_line('/');
 DBMS_OUTPUT.put_line(' ');
 l_last_acl := i.acl;
 l_last_principal := i.principal;
 l_last_privilege := i.privilege;
 END IF;

IF l_last_principal <> i.principal 
 OR (l_last_principal = i.principal AND l_last_privilege <> i.privilege) THEN
 -- Add another principal to an existing ACL.
 DBMS_OUTPUT.put_line('BEGIN');
 DBMS_OUTPUT.put_line(' DBMS_NETWORK_ACL_ADMIN.add_privilege (');
 DBMS_OUTPUT.put_line(' acl => ''' || i.acl || ''',');
 DBMS_OUTPUT.put_line(' principal => ''' || i.principal || ''',');
 DBMS_OUTPUT.put_line(' is_grant => ' || i.is_grant || ',');
 DBMS_OUTPUT.put_line(' privilege => ''' || i.privilege || ''',');
 DBMS_OUTPUT.put_line(' start_date => ' || get_timestamp(i.start_date) || ',');
 DBMS_OUTPUT.put_line(' end_date => ' || get_timestamp(i.end_date) || ');');
 DBMS_OUTPUT.put_line(' COMMIT;');
 DBMS_OUTPUT.put_line('END;');
 DBMS_OUTPUT.put_line('/');
 DBMS_OUTPUT.put_line(' ');
 l_last_principal := i.principal;
 l_last_privilege := i.privilege;
 END IF;

IF l_last_host <> i.host||':'||i.lower_port||':'||i.upper_port THEN
 DBMS_OUTPUT.put_line('BEGIN');
 DBMS_OUTPUT.put_line(' DBMS_NETWORK_ACL_ADMIN.assign_acl (');
 DBMS_OUTPUT.put_line(' acl => ''' || i.acl || ''',');
 DBMS_OUTPUT.put_line(' host => ''' || i.host || ''',');
 DBMS_OUTPUT.put_line(' lower_port => ' || NVL(TO_CHAR(i.lower_port),'NULL') || ',');
 DBMS_OUTPUT.put_line(' upper_port => ' || NVL(TO_CHAR(i.upper_port),'NULL') || ');');
 DBMS_OUTPUT.put_line(' COMMIT;');
 DBMS_OUTPUT.put_line('END;');
 DBMS_OUTPUT.put_line('/');
 DBMS_OUTPUT.put_line(' ');
 l_last_host := i.host||':'||i.lower_port||':'||i.upper_port;
 END IF;
 END LOOP;
END;
/

Drop multiple columns faster in Oracle

From documentation: http://docs.oracle.com/database/122/ADMIN/managing-tables.htm#GUID-74A86E52-E2D2-405E-B888-94164E3973B9

“If you are concerned about the length of time it could take to drop column data from all of the rows in a large table, you can use the ALTER TABLE...SET UNUSED statement.

This statement marks one or more columns as unused, but does not actually remove the target column data or restore the disk space occupied by these columns. However, a column that is marked as unused is not displayed in queries or data dictionary views, and its name is removed so that a new column can reuse that name. In most cases, constraints, indexes, and statistics defined on the column are also removed. The exception is that any internal indexes for LOB columns that are marked unused are not removed.

To mark the hiredate and mgr columns as unused, execute the following statement:

ALTER TABLE hr.admin_emp SET UNUSED (hiredate, mgr);

You can later remove columns that are marked as unused by issuing an ALTER TABLE...DROP UNUSED COLUMNS statement. Unused columns are also removed from the target table whenever an explicit drop of any particular column or columns of the table is issued.

The data dictionary views USER_UNUSED_COL_TABS, ALL_UNUSED_COL_TABS, or DBA_UNUSED_COL_TABS can be used to list all tables containing unused columns. The COUNT field shows the number of unused columns in the table.

SELECT * FROM DBA_UNUSED_COL_TABS;

OWNER                       TABLE_NAME                  COUNT
--------------------------- --------------------------- -----
HR                          ADMIN_EMP                       2

The ALTER TABLE...DROP UNUSED COLUMNS statement is the only action allowed on unused columns. It physically removes unused columns from the table and reclaims disk space.

In the ALTER TABLE statement that follows, the optional clause CHECKPOINT is specified. This clause causes a checkpoint to be applied after processing the specified number of rows, in this case 250. Checkpointing cuts down on the amount of undo logs accumulated during the drop column operation to avoid a potential exhaustion of undo space.

ALTER TABLE hr.admin_emp DROP UNUSED COLUMNS CHECKPOINT 250;

Disabling/enabling general job execution before/after maintenance

During the maintenance you may need to disable jobs.

To disable jobs created by dbms_jobs set job_queue_processes to zero.

–Save old value

show parameter job_queue_processes
1000

–disable

alter system set job_queue_processes=0;

If you are using dbms_scheduler, this parameter does not work for you.

You will have to run the following:

dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED','TRUE');

After finishing maintenance, enable them:

alter system set job_queue_processes=1000;
dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED','FALSE');

 

SYS_OP_C2C internal function, implicit data type converstion

Our database is heavily loaded. In addition, developers are writing codes that make even Exadata to hang 🙂  They are the best hackers ever :):)

Investigating database performance using 13c Cloud Control found one very interesting SQL.
At glance everything is fine, but there is one thing that is important and makes SQL  heavy.

Top SQL:

SELECT nvl(max(bl.id), 0) 
FROM schemaname.eb_restriction_balance bl 
WHERE bl.restrictcode = :b1

SCHEMANAME.EB_RESTRICTION_BALANCE table structure:

SQL> DESC schemaname.eb_restriction_balance

Name Type Nullable Default Comments 
-------------- ------------ -------- ------- -------- 
ID NUMBER 
RESTRICTCODE VARCHAR2(30) 
CURRENCY VARCHAR2(3) 
RESTRICTAMOUNT NUMBER(14) 0 
BALANCE NUMBER(14) 0 
STATE NUMBER(5) 5 
INSERTDATE DATE sysdate 
UPDATEDATE DATE Y 
INN VARCHAR2(30) Y

RESTRICTCODE column is indexed.

So in ideal way the above select should use the index on RESTRICTCODE.

Cloud Control shows that CBO chooses TABLE ACCESS STORAGE FULL.

You can run SQL Tuning Advisor from Cloud Control easily. Advisor generated the following recommendation:

The execution plan of this statement can be improved by creating one or more indices. Consider running the Access Advisor to improve the physical schema design or creating the recommended index.schemaname.EB_RESTRICTION_BALANCE(SYS_OP_C2C(“RESTRICTCODE”))

SYS_OP_C2C means that there happened implicit data type conversion.
So we must find the exact bind value that was used at the time sql was run.

SQL> SELECT name, datatype_string, value_string
 2 FROM v$sql_bind_capture
 3 WHERE sql_id='dnb1771sbm98x';

NAME DATATYPE_STRING VALUE_STRING
------- ------------------ -------------
:B1 NVARCHAR2(128) BR16215493
:B1 NVARCHAR2(128) BR16213680

As you see bind value type was NVARCHAR, that is why oracle converted varchar2 to nvarchar2 and did not use index on RESTRICTCODE.

Solution is to make developer change code and pass the parameter with the type varchar2.

 

How to find remote session executing over a database link

Select /*+ ORDERED */
substr(s.ksusemnm,1,10)||'-'|| substr(s.ksusepid,1,10) "ORIGIN",
substr(g.K2GTITID_ORA,1,35) "GTXID",
substr(s.indx,1,4)||'.'|| substr(s.ksuseser,1,5) "LSESSION" ,
substr(decode(bitand(ksuseidl,11),1,'ACTIVE',0,
decode(bitand(ksuseflg,4096),0,'INACTIVE','CACHED'),
2,'SNIPED',3,'SNIPED', 'KILLED'),1,1) "S",
substr(event,1,10) "WAITING"
from x$k2gte g, x$ktcxb t, x$ksuse s, v$session_wait w
where g.K2GTDXCB =t.ktcxbxba
and g.K2GTDSES=t.ktcxbses
and s.addr=g.K2GTDSES
and w.sid=s.indx;

GTXID is the same on both databases.

################################### Sample output ###################################

##Destination

3   LBREPDB01-51715  LBREP.aa2c0b4f.94.11.4694801  5447.62951   I   SQL*Net me

##Source

2   LB\MARIAMI-41196:4058  LBREP.aa2c0b4f.94.11.4694801 87.36231  I  SQL*Net me

More Details:

SID – 87
SERIAL – 36231

RMAN: Displaying current backup progress

To check the progress of your current RMAN backup use this script:

#This is my favorite script

select recid
 , output_device_type
 , dbsize_mbytes
 , input_bytes/1024/1024 input_mbytes
 , output_bytes/1024/1024 output_mbytes
 , (output_bytes/input_bytes*100) compression
 , (mbytes_processed/dbsize_mbytes*100) complete
 , to_char(start_time + (sysdate-start_time)/(mbytes_processed/dbsize_mbytes),'DD-MON-YYYY HH24:MI:SS') est_complete
 from v$rman_status rs
 , (select sum(bytes)/1024/1024 dbsize_mbytes from v$datafile) 
 where status='RUNNING'
 and output_device_type is not null