Oracle Virtual Private Database (VPD) lets you to create security policies that's control database access at the row and column level.
Let's see how we can implement Column level security in 19c database .
RAC Database : RENODBPR
GRID Home : /u01/app/19.3.0.0/grid
RDBMS Home : /u01/app/oracle/product/19.3.0.0/dbhome_1
Version : Oracle Database 19c EE - Production Version 19.17.0.0.0
hosts : primhost01 & primhost02
Our LAB environment is a 2 node RAC cluster with 19c version and with patch set 19.17 applied.
CDB name : RENODBPR
PDB name : ONEPDB
Lets say we have a requirement where we want few users not see the SSN column of the above table.
In this case, lets say USER3 can view all the rows/columns of USER1.TABLE1 except the SSN column and let's say USER2 can view all the rows/columns of USER1.TABLE1 including the SSN column
To achieve this, we need to create a VPD function and VPD policy as shown below.
Step 1: Grant 'EXECUTE' privilege on DBMS_RLS package to USER1.
Login to the Database as SYSDBA and grant above privilege.
[oracle@labhost01 admin]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jan 19 09:59:48 2023
Version 19.17.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.17.0.0.0
SQL> alter session set container=onepdb;
Session altered.
SQL> GRANT EXECUTE ON dbms_rls TO user1;
Step 2: Create the VPD Policy function.
Login as USER1 to the PDB and create the function as shown below.
SQL> alter session set container=onepdb;
Session altered.
SQL> create or replace function ssn_secure_fnc (p_schema varchar2, p_obj varchar2)
return varchar2 as
l_user varchar2(20);
l_predicate varchar2(100);
begin
select user into l_user from dual;
if l_user!='USER2' then
l_predicate:='1=2';
end if;
return l_predicate;
end ssn_secure_fnc;
/
In Above function, we are telling that if user connect is USER2, it can return the data or else return nothing.
Step 3: Create the VPD Policy.
create below policy as USER1.
SQL> alter session set container=onepdb;begin
dbms_rls.add_policy
(object_schema=>'USER1' -- specify the schema containing the object
,object_name=>'TABLE1' -- specify the object name within the schema.
,policy_name=>'ssn_secure' -- specify the policy name. Policy name is unique for an object.
,function_schema=>'USER1' -- specify the schema in which the policy function is created
,policy_function=>'ssn_secure_fnc' -- specify the name of the policy function
,statement_Types=>'SELECT' -- Operations when this policy applies. SELECT ,sec_relevant_cols=>'SSN' -- ALL relevant columns to be hidden from users
,sec_relevant_cols_opt=> dbms_rls.ALL_ROWS
);
end;
/
Step 4. Grant select on user1.table1 to the users user2 and user3
SQL> grant select on table1 to user2;
Grant succeeded.
SQL> grant select on table1 to user3;
Grant succeeded.
SQL>
Step 5 . Run the below select statement by connecting to the user USER2 and USER3 and see the results.
select * from user1.table1;
As we can see from above, that when we run the SELECT statement with USER2, it can see the SSN column data because we created VPD policy and added this user in it ensuring that USER2 can view all data including SSN column data.
But when we run the same select statement with USER3 or in fact any other user, we can see that SSN data is not displayed.
Thanks
Sambaiah Sammeta
Patch 34762026 - GI Release Update 19.18.0.0.230117
Patch 34765931 - DATABASE RELEASE UPDATE 19.18.0.0.0
Patch 34773489 - COMBO OF OJVM RU COMPONENT 19.18.0.0.230117 + DB RU 19.18.0.0.230117
In this post, we will see how we can apply the Grid and Database RUs to 19c RAC environment.
My Lab environment which is a 2 node RAC cluster is of version 19c with patch set 19.17 applied. Below is the current RU version applied to the existing GI and DB homes
In this post we will see how we can apply the 19.18 RU to the GRID home.
Download the Grid Patch , Database patch and the latest OPatch from Oracle and stage it on all the nodes of the cluster(if its RAC)
p34762026_190000_Linux-x86-64.zip - Grid Infrastructure Release Update 19.18.0.0.230117
p34765931_190000_Linux-x86-64.zip - Database Release Update 19.18.0.0.0
p6880880_190000_Linux-x86-64.zip - Latest Opatch for 19c
The Oracle Grid Infrastructure and Database patches are cumulative and include the database CPU program security content.
1. Opatch utility version information : You must use the OPatch utility version 12.2.0.1.34 or later to apply this patch. Oracle recommends that you use the latest released OPatch version for 12.2 which is available for download from My Oracle Support note 6880880 by selecting the ARU link for the 12.2.0.1.0 OPatch release.
Check the current OPatch version on the server
[oracle@primhost01 software]$ $ORACLE_HOME/OPatch/opatch version
OPatch Version: 12.2.0.1.33
OPatch succeeded.
[oracle@primhost01 software]$
Download and unzip the latest OPatch utility to both the Grid and Database homes.
Node 1
[root@primhost01 ~]# mv /u01/app/19.3.0.0/grid/OPatch /u01/app/19.3.0.0/grid/OPatch.orig1
[root@primhost01 ~]# unzip p6880880_190000_Linux-x86-64.zip -d /u01/app/19.3.0.0/grid
[root@primhost01 ~]# chown -Rf oracle:dba /u01/app/19.3.0.0/grid/OPatch
let's check the OPatch version to see if its updated.
[oracle@primhost01 software]$ $ORACLE_HOME/OPatch/opatch version
OPatch Version: 12.2.0.1.35
OPatch succeeded.
[oracle@primhost01 software]$
Okay, we now have the latest recommended OPatch version. lets do the same step in node 2.
Node2
[root@primhost02 ~]# mv /u01/app/19.3.0.0/grid/OPatch /u01/app/19.3.0.0/grid/OPatch.orig1
[root@primhost02 ~]# unzip p6880880_190000_Linux-x86-64.zip -d /u01/app/19.3.0.0/grid
[root@primhost02 ~]# chown -Rf oracle:dba /u01/app/19.3.0.0/grid/OPatch
let's check the OPatch version to see if its updated.
[oracle@primhost02 software]$ $ORACLE_HOME/OPatch/opatch version
OPatch Version: 12.2.0.1.35
OPatch succeeded.
[oracle@primhost02 software]$
2. Check the consistency for the Oracle inventory of both Grid and Database homes
Before we begin apply any patches, it is always strongly recommended to check the consistency of the inventory information for both the Grid home and Oracle homes where we are applying the patch.
3. Run the Opatch conflict check
Determine whether any currently installed one-off patches conflict with this patch 34416665 as follows:
Run OPatch Conflict Check
For Grid Infrastructure Home, as home user:
export ORACLE_HOME=/u01/app/19.3.0.0/grid
export PATH=$ORACLE_HOME/bin:$PATH
$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /u01/software/34762026/34765931
$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /u01/software/34762026/34768559
$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /u01/software/34762026/34768569
$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /u01/software/34762026/34863894
$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /u01/software/34762026/33575402
Node 1:
Node2
Run the below command to check the system space for GRID home.
Run the system space check on both the nodes
Check if we have enough free space available in both GRID and DB homes for the patch to be applied.
Create two files, one with Grid patch info and another one with DB patch info as shown below
For Grid home, create below file.
oracle@oraprim01 software]$ cat /tmp/patch_list_gihome.txt
/u01/software/34416665/34419443
/u01/software/34416665/34444834
/u01/software/34416665/34428761
/u01/software/34416665/34580338
/u01/software/34416665/33575402
[oracle@oraprim01 software]$
Run the below command to check the system space for GRID home.
$ORACLE_HOME/OPatch/opatch prereq CheckSystemSpace -phBaseFile /tmp/patch_list_gihome.tx
Node1
Node2
Check the One-off Patch Conflict Detection and Resolution
As ROOT user, run this step in all the nodes of the cluster. This will check conflicts for both the grid and database homes.
export ORACLE_HOME=/u01/app/19.3.0.0/grid
export PATH=$ORACLE_HOME/bin:$PATH
$ORACLE_HOME/OPatch/opatchauto apply /u01/software/34762026 -analyze
We have to check the same in other nodes of the cluster.
As root user, apply the patch for the GRID home using 'opatchauto',
export ORACLE_HOME=/u01/app/19.3.0.0/grid
export PATH=$ORACLE_HOME/bin:$PATH
$ORACLE_HOME/OPatch/opatchauto apply /u01/software/34762026 -oh /u01/app/19.3.0.0/grid
Please note that if we dont give the Oracle home(-oh /u01/app/19.3.0.0/grid) in above command, the patch will get applied to the grid home and the database home.
In this case, since we are specificying the Oracle home as the Grid home, the patch will be applied only to the Grid home.
On Node1
Check the lspatches to see if the patch has been successfully applied.
we have top apply the patch in all the nodes of the cluster.
In this post, we saw how to apply the latest Oracle RU to both Grid home using 'opatchauto' . we can use the same command to patch the database home, we just need to include the database home in -oh' option as shown below.
To Patch just the database home (/u01/app/oracle/product/19.3.0.0/dbhome_1), we can use the below command.
$ORACLE_HOME/OPatch/opatchauto apply /u01/software/34762026 -oh /u01/app/oracle/product/19.3.0.0/dbhome_1
As mentioned earlier and as we know, we can apply the patch to both Grid infra home and database home using the same command(mentioned below) except that we don't mentioned any specific ORACLE_HOME with -oh option.
$ORACLE_HOME/OPatch/opatchauto apply /u01/software/34762026
Thanks
Sambaiah Sammeta
In this post we will see how we can apply December 2022 MRP to 19c RAC database home.
MRPs are a collection of one-off patches bundled together. Starting with update 19.17, Oracle will be providing these Monthly recommended patches for Linux x86-64 version.
This MRPs will include to provide proactive patching between Release Updates and will include the fixes documented in "Oracle Database Important Recommended Patches" (My Oracle Support Doc ID 555.1),
MRPs are cumulative patches and they don't change the release number and they are deployed using Opatchauto and as of now , MRPs are available only on the Linux x86-64 platform.
MRP version | Bug Fixes |
Database MRP 19.17.0.0.221220 | MLR 34789241(34060122, 33896423, 34310304, 34377917, 34485380, 34562023), 34715072, 34545238, 32295794 |
Database MRP 19.17.0.0.221115 | 33896423, 34333986, 30691454, 34538232, 34574048, 34366627, 34724125 |
