Implementing Row-Level
Security (RLS) in Oracle
This document outlines the steps to implement Row-Level Security (RLS) in
Oracle for the user PRACTICE1. It includes creating a new user, setting up an
EMP table with sample data, defining the necessary context, procedure, and
function, and applying the RLS policy. Testing steps are also included to validate
the implementation.
Step 1: Create the User
Create the user PRACTICE1 and grant the required privileges.
CREATE USER PRACTICE1 IDENTIFIED BY practice1;
GRANT CONNECT, RESOURCE TO PRACTICE1;
ALTER USER PRACTICE1 QUOTA UNLIMITED ON USERS;
GRANT CREATE SESSION, CREATE TABLE, CREATE PROCEDURE,
CREATE VIEW, CREATE TRIGGER TO PRACTICE1;
GRANT EXECUTE ON DBMS_RLS TO PRACTICE1;
www.linkedin.com/in/daniyaldba/ +923408801269 danishjee05@gmail.com
Step 2: Create the EMP Table
Create the EMP table under the PRACTICE1 schema.
CREATE TABLE EMP (
EMPNO NUMBER(4) PRIMARY KEY,
ENAME VARCHAR2(20),
JOB VARCHAR2(20),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(10, 2),
COMM NUMBER(10, 2),
DEPTNO NUMBER(4),
USERNAME VARCHAR2(20)
);
Step 3: Insert Sample Data
Populate the EMP table with sample records.
INSERT INTO EMP VALUES (222, 'ANNAR', 'MANAGER', NULL,
SYSDATE, 3000, NULL, 10, 'ALI');
INSERT INTO EMP VALUES (7788, 'SCOTT', 'ANALYST', 7566, SYSDATE,
3000, NULL, 20, 'DANISH');
www.linkedin.com/in/daniyaldba/ +923408801269 danishjee05@gmail.com
INSERT INTO EMP VALUES (7698, 'BLAKE', 'MANAGER', 7839, SYSDATE,
2850, NULL, 30, 'SYSTEM');
INSERT INTO EMP VALUES (7566, 'JONES', 'MANAGER', 7839, SYSDATE,
2975, NULL, 20, 'DANISH');
INSERT INTO EMP VALUES (7839, 'KING', 'PRESIDENT', NULL, SYSDATE,
5000, NULL, 10, 'ALI');
INSERT INTO EMP VALUES (7499, 'ALLEN', 'SALESMAN', 7698, SYSDATE,
1900, 300, 30, 'SYSTEM');
INSERT INTO EMP VALUES (7782, 'CLARK', 'MANAGER', 7839, SYSDATE,
2450, NULL, 10, 'ALI');
INSERT INTO EMP VALUES (7521, 'WARD', 'SALESMAN', 7698, SYSDATE,
1250, 500, 30, 'SYSTEM');
INSERT INTO EMP VALUES (7934, 'MILLER', 'CLERK', 7782, SYSDATE,
1300, NULL, 10, 'ALI');
INSERT INTO EMP VALUES (7902, 'FORD', 'ANALYST', 7566, SYSDATE,
3000, NULL, 20, 'DANISH');
INSERT INTO EMP VALUES (7654, 'MARTIN', 'SALESMAN', 7698,
SYSDATE, 1250, 1400, 30, 'SYSTEM');
Step 4: Create a Context
Define a context named TEST that will store user-specific attributes.
create context test using set_deptno;
www.linkedin.com/in/daniyaldba/ +923408801269 danishjee05@gmail.com
Step 5: Define a Procedure to Set Context
Create a procedure to set the context for the TEST namespace.
create or replace procedure set_deptno(p1 varchar2) as
begin
dbms_session.set_context(
namespace => 'test',
attribute => 'username',
value => p1
);
end;
/
www.linkedin.com/in/daniyaldba/ +923408801269 danishjee05@gmail.com
Step 6: Define a Function for RLS Predicate
Create a function that defines the RLS predicate.
create or replace function dept_predicate(schema_name in varchar2,
object_name in varchar2)
return varchar2
is
lv_predicate varchar2(1000);
begin
lv_predicate := 'username = sys_context(''test'', ''username'')';
return lv_predicate;
exception
when others then
raise_application_error(-20001, 'Error in dept_predicate: ' || sqlerrm);
end;
/
Step 7: Apply the RLS Policy
Attach the RLS policy to the EMP table.
BEGIN
DBMS_RLS.ADD_POLICY(
object_schema => 'PRACTICE1',
www.linkedin.com/in/daniyaldba/ +923408801269 danishjee05@gmail.com
object_name => 'EMP',
policy_name => 'EMP_CL_POLICY',
function_schema => 'PRACTICE1',
policy_function => 'dept_predicate'
);
END;
/
Step 8: Test the Implementation
Set the Context for ALI:
begin
set_deptno('ALI');
end;
/
Verify the Context Setting:
SELECT SYS_CONTEXT('TEST', 'USERNAME') FROM DUAL;
www.linkedin.com/in/daniyaldba/ +923408801269 danishjee05@gmail.com
OUTPUT:
SYS_CONTEXT('TEST', 'USERNAME')
------------------------------------
ALI
Query the EMP Table:
SELECT * FROM EMP;
www.linkedin.com/in/daniyaldba/ +923408801269 danishjee05@gmail.com
Change the Context to DANISH:
begin
set_deptno('DANISH');
end;
/
Verify the Context Setting:
SELECT SYS_CONTEXT('TEST', 'USERNAME') FROM DUAL;
OUTPUT:
SYS_CONTEXT('TEST', 'USERNAME')
------------------------------------
DANISH
SELECT * FROM EMP;
OUTPUT:
www.linkedin.com/in/daniyaldba/ +923408801269 danishjee05@gmail.com
======================GOOD LUCK==========================
www.linkedin.com/in/daniyaldba/ +923408801269 danishjee05@gmail.com
