One of the things that gets unnoticed or (overlooked) when securing Oracle Database Infrastructure is securing oracle database binaries by updating JDK build or updating SQL Developer.
When installing Oracle database binaries the version in a database install is always going to be behind the latest JDK so you should update to the latest version since latest version will include security fixes.
to check your current JDK build:
Procedure to replace JDK:
shutdown your oracle database and listener.
mv jdk jdk.orig
// you can download the Latest Java SE Patches/Update Releases on MOS (Doc ID 1414485.1) OR All Java SE Downloads on MOS (Doc ID 1439822.1)
// copy the downloaded JDK to $ORACLE_HOME
scp jdk-6u181-linux-x64.bin $ORACLE_HOME
mv jdk1.6.0_181 jdk
rm -rf jdk-6u181-linux-x64.bin
Startup the database and listener.
Run utlrp.sql script and check that all database components are valid:
SQL> select * from dba_registry;
Also, check database alert log file …just in case.
If the database has “JAVA” component you can follow the steps described in this procedure for “verification”
How To Determine The JDK Version Used by the Oracle JVM in the Database (Doc ID 131872.1)
The idea of Virtual Private Database is to enable users having access to a table to see ONLY subset of
data within the table. This technique is based on a function that will enable SQL execution to append a WHRE clause predicate. The subset of data can be from row-level or column-level perspective.
For demonstration, I will be using a table called EMP under SCOTT schema.
I will also create 2 accounts:
SQL> create user manager identified by mono670;
SQL>grant create session to to manager;
SQL>grant select on SCOTT.EMP to manager;
SQL>create user clerk identified by clerk332;
SQL>grant create session to clerk;
SQL>grant select on SCOTT.EMP to clerk;
connecting as accounts MANAGER & CLERK I can see the full table data !!!
Now I will create security administrator account:
SQL> alter session set container=CDB$root;
SQL> create user security_admin identified by zorro3_g;
SQL> grant create session to security_admin;
SQL> grant create procedure to security_admin;
Now I am going to create the function:
SQL> CREATE OR REPLACE FUNCTION VPD_FUNC
(schema_v IN VARCHAR2, tbl_v IN VARCHAR2) RETURN VARCHAR2 IS
RETURN ‘upper(ename) = SYS_CONTEXT(”USERENV”, ”CURRENT_USER”) OR upper(job) =
Then as a sys user in the pluggable database PDB_ORIGIN configure the policy:
statement_types => ‘SELECT’);
Connecting as account “manager” I can see data related to “manager” records:
Same to “CLERK”
This is a very nice security feature that many organizations can use to restrict access to data within the table itself.