Implementing Row Level Security (RLS)

We have implemented Row Level Security using the Virtual Private database Policy (VPD).What VPD will do is to add where conditions on user SQLs based on the policies defined.

For example, in the simplest manner, suppose you want to restrict the users to access the records created only by themselves in the table "TEST_TAB". You can define and apply a policy "POLICY1" as "CREATED_BY = USER" for the table "TEST_TAB".

The steps in implementing VPD is as follows:

1. Create a function which will return the where condition to be applied. The argumnets to this function should be schema and object name.

function policy1 (p_schema varchar2,p_object varchar2) return varchar2


2. Add the policy using DBMS_RLS.ADD_POLICY
Below is a simple example to add a policy on a table RETAILDM.TEST_RLS.

First We will see the data in RETAILDM.TEST_RLS.It has two records created by RETAILDM and one record created by another user.

SQL>; show user
USER is "RETAILDM"

SQL>; select *  
2  from test_rls;
C1                   CREATED_BY<
--------------------------------
XXXXX                RETAILDM
YYYYY                USER2
ZZZZZ                RETAILDM

Now, we have to define the Policy function to return the "where' condition.

SQL>; create or replace function test_rls_fn(p_schema varchar2,p_object varchar2)  
2          return varchar2 is  
3      lc_where varchar2(500);  
4  begin  
5      lc_where := ' created_by = user';  
6      return lc_where;
7  end;
8  /
Function created.

next step is to add the policy using DBMS_RLS.ADD_POLICY. The arguments are

object schema, object name, policy name, policy function schema and policy function name.

SQL>; exec dbms_rls.ADD_POLICY('RETAILDM','TEST_RLS','TEST_RLS_POLICY','RETAILDM','TEST_RLS_FN');
PL/SQL procedure successfully completed.

Now, if we select data from TEST_RLS, we will get only those records created by RETAILDM.

SQL>; select *  
2  from test_rls;
C1                   CREATED_BY
-------------------- ----------
XXXXX                RETAILDM
ZZZZZ                RETAILDM

If you check the explain plan, the where condition applied can be spotted out.

SQL>; explain plan for  
2  select *  
3  from test_rls;
Explained

SQL>; select *
2        from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 636636236

------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
------------------------------------------------------------------------------
0 SELECT STATEMENT 2 38 3 (0) 00:00:01 *
1 TABLE ACCESS FULL TEST_RLS 2 38 3 (0) 00:00:01
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

1 - filter("CREATED_BY"=USER@!)

Note
-----
- dynamic sampling used for this statement (level=2)

17 rows selected.

Tags: RLS ORACLE, Row level security ORACLE

Sessions Marked for Kill

Some times when we kill an ORACLE session with the “alter system kill” statement, it will return a message that the session is marked for kill. In most of the cases, it will be rolling back a huge transaction that you executed.

As an example, if you killed a session which was supposed to update/delete 1Million rows on half the way , it will definitely take some time to roll the changes back. This can be verified by checking the v$transaction table.

SQL> select count(*) cnt from test_tab1;
     
CNT
----------  

769538

SQL> update test_tab1 set c1 = c1+1;

Huge update is going on now.In between, if you kill the session and check the v$transaction table you will see the change in the values of "Number of undo blocks used" and "Number of undo records used"

SQL> alter system kill session '49,27133';
alter system kill session '49,27133'
*
ERROR at line 1:
ORA-00031: session marked for kill


SQL> select status
2  from v$session
3  where sid = 49;


STATUS
--------

KILLED


SQL> select used_ublk,used_urec
2  from v$transaction
3  where ses_addr=(
4                  select saddr
5                  from v$session
6                  where sid = 49
7                  );


 USED_UBLK  USED_UREC
---------- ----------  

 24769     161040


SQL> /
 USED_UBLK  USED_UREC
---------- ----------   

24081     155939


SQL> /
 USED_UBLK  USED_UREC
---------- ----------   

22605     145321


SQL> /
 USED_UBLK  USED_UREC
---------- ----------   

10487      66038


SQL> /
 USED_UBLK  USED_UREC
---------- ----------    

6637      41384


SQL> /

no rows selected

The rollback is over. We can check V$SESSION now.

SQL> select status
2  from v$session
3  where sid = 49;

no rows selected

Logon Failure Auditing in ORACLE

We just enabled the Logon Failure Auditing in ORACLE. The steps folowed are as follows.

1. change the parammeter audit_trail=db in initSID.ora or ALTER SYSTEM SET audit_trail=db SCOPE=SPFILE;
2.Restart the db.
3.Connect as sysdba and run the below command.

audit create session whenever not successful;

4.Now failures will be logged in dba_audit_trail table.
5.Details of audit options are available in dba_priv_audit_opts table.

LISTAGG and PIVOT in ORACLE 11g

The two new features of ORACLE 11g - The LISTAGG function and the PIVOT SQL clause - are very handy.

The benefit with the same is actually visible when you compare these features with the "old" technique to get the same output.
We can see the differences with the sample EMP and DEPT created and populated as below.

drop table test_emp;
drop table test_dept;

create table test_emp(emp_id number(5),emp_name varchar2(150),
dept_id number(4));
create table test_dept(dept_id number(4),dept_name varchar2(150));
Insert into TEST_DEPT
(DEPT_ID, DEPT_NAME)
Values (30, 'Sales');
Insert
into TEST_DEPT
(DEPT_ID, DEPT_NAME)
Values (20, 'IT');
Insert into TEST_DEPT
(DEPT_ID, DEPT_NAME)
Values
(10, 'HR');
COMMIT;
Insert into TEST_EMP
(EMP_ID, EMP_NAME, DEPT_ID)
Values (6, 'Sabu Samuel', 30);
Insert into TEST_EMP
(EMP_ID, EMP_NAME, DEPT_ID)
Values (6, 'Chinthaka G', 30);
Insert into TEST_EMP
(EMP_ID, EMP_NAME, DEPT_ID)
Values (5, 'Margarat K Jack', 20);
Insert into TEST_EMP
(EMP_ID, EMP_NAME, DEPT_ID)
Values (4, 'Kennet Lane', 20);
Insert into TEST_EMP
(EMP_ID, EMP_NAME, DEPT_ID)
Values (3, 'Rama Murthy', 10);
Insert into TEST_EMP
(EMP_ID, EMP_NAME, DEPT_ID)
Values (2, 'Wilson Dslva', 10);
Insert into TEST_EMP
(EMP_ID, EMP_NAME, DEPT_ID)
Values (1, 'Lawrence Clinch', 10);
COMMIT;
Prior to 11g to get the Department name as a column and count as rows we need to use the SUM(DECODE()) technique.

select sum(decode(dept_name,'HR',1,0)) hr,
sum(decode(dept_name,'IT',1,0)) it,
sum(decode(dept_name,'Sales',1,0)) sales
from test_emp e,test_dept d
where e.DEPT_ID = d.DEPT_ID
group by dept_name; 

To Get a list of employees across departments in a Pivoted manner we need to use SUM(DECODE()) technique along with SYS_CONNECT_BY_PATH prior to 11g.

select max(decode(dept_name,'HR',emp_list)) hr,
max(decode(dept_name,'IT',emp_list)) it,
max(decode(dept_name,'Sales',emp_list)) sales
from (
select dept_name,ltrim(max(sys_connect_by_path(emp_name,',')),',') emp_list
from(
select dept_name,emp_name,row_number() over(partition by dept_name order by emp_name) rn
from test_emp e,test_dept d
where e.DEPT_ID = d.DEPT_ID
)
start with rn = 1
connect by prior rn = rn - 1 and prior dept_name = dept_name
group by dept_name
);

HR                                       IT                        SALES
---------------------------------------- ------------------------- -------------------------
Lawrence Clinch,Rama Murthy,Wilson Dslva Kennet Lane,Margarat K Ja Chinthaka G,Sabu Samuel

In 11g the same thing can be achieved by a simple and fast query as shown below.

select *
from(
select dept_name,listagg(emp_name) within group (order by emp_name) emp_list
from test_emp e,test_dept d
where e.DEPT_ID = d.DEPT_ID
group by dept_name
) pivot ( max(emp_list) for dept_name in ('HR','IT','Sales') ); 

Tags: LISTAGG ORACLE 11g , PIVOT ORACLE 11g