A.1 Scripts for Direct Logon with Local End Users
Run these scripts to automate the configuration described in Configure Oracle Deep Data Security for Direct Logon with Local End Users.
Topics:
A.1.1 How to Run the Scripts
Run these scripts as SYS from a shell on the database host,
in the following order.
01_local_demo.sql: Sets up and runs the demo scenario. The script reads these parameters in order from either command-line arguments or interactive prompts:syspasswd:SYSpassword for the target PDB.dbapasswd: Password for theDEEPSEC_DBAadministrative user that the script creates.passwd: Password for themandersonandebakerend users.target_pdb: Service name of the target PDB.
01_local_cleanup.sql: Removes all objects the demo script created. Run this to reset the environment. The script reads these parameters in order from either command-line arguments or interactive prompts:syspasswd:SYSpassword for the target PDB.target_pdb: Service name of the target PDB.
A.1.2 Scripts
Review the source for both scripts used in the scenario.
| File | Purpose |
|---|---|
01_local_demo.sql |
Sets up and runs the demo scenario. |
01_local_cleanup.sql |
Removes all objects created by the demo script. |
A.1.2.1 01_local_demo.sql
The source for the 01_local_demo.sql script.
SET ECHO ON
SET FEEDBACK 1
SET NUMWIDTH 10
SET LINESIZE 200
SET TRIMSPOOL ON
SET TAB OFF
SET PAGESIZE 100
COL EMPLOYEE_ID FORMAT 9999
COL FIRST_NAME FORMAT A12
COL LAST_NAME FORMAT A12
COL EMAIL FORMAT A24
COL MANAGER FORMAT A24
COL SSN FORMAT A12
COL SALARY FORMAT 9999999
COL PHONE FORMAT A12
define syspasswd=&1
define dbapasswd=&2
define passwd=&3
define target_pdb=&4
spool 01_local_demo.log
----------------------------------------------------------------------
-- HR Security Demo - Local End User Scenario
----------------------------------------------------------------------
-- This script creates the demo environment:
-- * the administrative user
-- * the HR schema and employee table
-- * sample employee rows
-- * local end users for the SQL*Plus demo
-- * the session role, data roles, and role assignments
-- * the data grants that enforce row and column security
-- * row and column filtering for local end users
-- * end user connects directly to the database using their own identity
----------------------------------------------------------------------
-- 1. Connect as SYS to the target PDB
----------------------------------------------------------------------
connect sys/&syspasswd@&target_pdb as sysdba
----------------------------------------------------------------------
-- 2. Create the administration user
----------------------------------------------------------------------
-- This local user is used to manage the demo environment.
create user DEEPSEC_DBA identified by &dbapasswd;
grant DBA to DEEPSEC_DBA;
----------------------------------------------------------------------
-- 3. Connect as the administration user
----------------------------------------------------------------------
connect DEEPSEC_DBA/&dbapasswd@&target_pdb
----------------------------------------------------------------------
-- 4. Create tablespace and HR schema
----------------------------------------------------------------------
-- Create the tablespace used by the HR demo schema.
-- Oracle Managed Files (OMF) controls the datafile location.
CREATE TABLESPACE users DATAFILE SIZE 100M AUTOEXTEND ON;
-- Create the HR schema that owns the employee table.
-- NO AUTHENTICATION means this schema cannot be logged into directly;
-- it is accessed only through object grants.
CREATE USER hr NO AUTHENTICATION
DEFAULT TABLESPACE users
QUOTA UNLIMITED ON users;
----------------------------------------------------------------------
-- 5. Create the employee table
----------------------------------------------------------------------
-- Create the data model used by all demo scenarios.
CREATE TABLE hr.employees (
employee_id NUMBER PRIMARY KEY,
first_name VARCHAR2(50),
last_name VARCHAR2(50),
email VARCHAR2(128),
manager VARCHAR2(128),
ssn VARCHAR2(20),
salary NUMBER(10,2),
phone VARCHAR2(20)
);
----------------------------------------------------------------------
-- 6. Load sample data
----------------------------------------------------------------------
INSERT INTO hr.employees VALUES (100,'Victoria','Williams','vwilliams',NULL,'219-09-9999',13000,'555-0100');
INSERT INTO hr.employees VALUES (200,'Marvin','Anderson','manderson','vwilliams','457-55-5462',12030,'555-0200');
INSERT INTO hr.employees VALUES (300,'Chris','Evans','cevans','vwilliams','321-12-4567',6900,'555-0300');
INSERT INTO hr.employees VALUES (400,'Emma','Baker','ebaker','manderson','733-02-9821',8200,'555-0400');
INSERT INTO hr.employees VALUES (500,'Taylor','Mills','tmills','manderson','558-76-1243',9000,'555-0500');
COMMIT;
----------------------------------------------------------------------
-- 7. Create local end user identities
----------------------------------------------------------------------
-- Local end users whose names are matching the email column above
CREATE END USER "manderson" IDENTIFIED BY &passwd;
CREATE END USER "ebaker" IDENTIFIED BY &passwd;
----------------------------------------------------------------------
-- 8. Create data roles
----------------------------------------------------------------------
-- Employees can see only their own row.
CREATE OR REPLACE DATA ROLE employee_role;
-- Managers can see their direct reports (with SSN masked).
CREATE OR REPLACE DATA ROLE manager_role;
----------------------------------------------------------------------
-- 9. Create the session role
----------------------------------------------------------------------
-- A standard database role that carries the CREATE SESSION privilege.
-- Granting this role to a data role allows end users who hold that
-- data role to establish a database session.
CREATE ROLE db_role;
GRANT CREATE SESSION TO db_role;
----------------------------------------------------------------------
-- 10. Grant create session privilege to data roles
----------------------------------------------------------------------
GRANT db_role TO employee_role;
GRANT db_role TO manager_role;
----------------------------------------------------------------------
-- 11. Assign data roles to end users
----------------------------------------------------------------------
-- Marvin holds both roles: he can see his own record as an employee,
-- and see his direct reports (Emma, Taylor) as a manager.
GRANT DATA ROLE manager_role TO "manderson";
GRANT DATA ROLE employee_role TO "manderson";
-- Emma holds only the employee role: she can see only her own record.
GRANT DATA ROLE employee_role TO "ebaker";
----------------------------------------------------------------------
-- 12. Create data grants
----------------------------------------------------------------------
-- Employee access rule:
-- A user can see only the row where the email matches their identity.
CREATE OR REPLACE DATA GRANT hr.employees_own_record
AS SELECT
ON hr.employees
WHERE email = ORA_END_USER_CONTEXT.username
TO employee_role;
-- Manager access rule:
-- A manager can see rows where the manager column matches their identity.
-- The ALL COLUMNS EXCEPT clause masks SSN for those rows.
CREATE OR REPLACE DATA GRANT hr.manager_direct_reports
AS SELECT (ALL COLUMNS EXCEPT ssn)
ON hr.employees
WHERE manager = ORA_END_USER_CONTEXT.username
TO manager_role;
----------------------------------------------------------------------
-- 13. Query as Marvin Anderson (employee and manager)
----------------------------------------------------------------------
-- Marvin holds both employee_role and manager_role.
connect "manderson"/&passwd@&target_pdb
-- Confirm the active end user identity.
SELECT ORA_END_USER_CONTEXT.username FROM dual;
-- Expected results:
-- employee_role: Marvin's own row, including his SSN and salary.
-- manager_role: Emma Baker and Taylor Mills (direct reports),
-- with their SSN column masked.
SELECT * FROM hr.employees;
----------------------------------------------------------------------
-- 14. Query as Emma Baker (employee only)
----------------------------------------------------------------------
-- Emma holds only employee_role.
connect "ebaker"/&passwd@&target_pdb
-- Confirm the active end user identity.
SELECT ORA_END_USER_CONTEXT.username FROM dual;
-- Expected result: Emma's own row only.
-- No other rows are visible; SSN and salary are accessible
-- because employee_role grants full column access to her own record.
SELECT * FROM hr.employees;
spool off
A.1.2.2 01_local_cleanup.sql
The source for the 01_local_cleanup.sql script.
SET ECHO ON
SET FEEDBACK 1
SET NUMWIDTH 10
SET LINESIZE 200
SET TRIMSPOOL ON
SET TAB OFF
SET PAGESIZE 100
define syspasswd=&1
define target_pdb=&2
spool 01_local_cleanup.log
-- This script removes all objects created by the demo so the
-- environment can be reused cleanly.
----------------------------------------------------------------------
-- 1. Connect as SYS
----------------------------------------------------------------------
connect sys/&syspasswd@&target_pdb as sysdba
----------------------------------------------------------------------
-- 2. Remove local end users
----------------------------------------------------------------------
DROP END USER "manderson";
DROP END USER "ebaker";
----------------------------------------------------------------------
-- 3. Remove the HR schema
----------------------------------------------------------------------
DROP USER hr CASCADE;
----------------------------------------------------------------------
-- 4. Remove data roles
----------------------------------------------------------------------
DROP DATA ROLE employee_role;
DROP DATA ROLE manager_role;
----------------------------------------------------------------------
-- 5. Remove the database role
----------------------------------------------------------------------
DROP ROLE db_role;
----------------------------------------------------------------------
-- 6. Remove the administration user and tablespace
----------------------------------------------------------------------
DROP USER DEEPSEC_DBA CASCADE;
DROP TABLESPACE users INCLUDING CONTENTS AND DATAFILES;
spool off
A.1.3 Log Files
Review the output generated by running each script.
| File | Purpose |
|---|---|
01_local_demo.log |
Output generated by running
01_local_demo.sql.
|
01_local_cleanup.log |
Output generated by running
01_local_cleanup.sql.
|
A.1.3.1 01_local_demo.log
The output generated by running
01_local_demo.sql.
SQL>
SQL> ----------------------------------------------------------------------
SQL> -- HR Security Demo - Local End User Scenario
SQL> ----------------------------------------------------------------------
SQL> -- This script creates the demo environment:
SQL> -- * the administrative user
SQL> -- * the HR schema and employee table
SQL> -- * sample employee rows
SQL> -- * local end users for the SQL*Plus demo
SQL> -- * the session role, data roles, and role assignments
SQL> -- * the data grants that enforce row and column security
SQL> -- * row and column filtering for local end users
SQL> -- * end user connects directly to the database using their own identity
SQL>
SQL> ----------------------------------------------------------------------
SQL> -- 1. Connect as SYS to the target PDB
SQL> ----------------------------------------------------------------------
SQL>
SQL> connect sys/&syspasswd@&target_pdb as sysdba
Connected.
SQL>
SQL> ----------------------------------------------------------------------
SQL> -- 2. Create the administration user
SQL> ----------------------------------------------------------------------
SQL>
SQL> -- This local user is used to manage the demo environment.
SQL> create user DEEPSEC_DBA identified by &dbapasswd;
old 1: create user DEEPSEC_DBA identified by &dbapasswd
new 1: create user DEEPSEC_DBA identified by dba_pwd
User created.
SQL> grant DBA to DEEPSEC_DBA;
Grant succeeded.
SQL>
SQL> ----------------------------------------------------------------------
SQL> -- 3. Connect as the administration user
SQL> ----------------------------------------------------------------------
SQL>
SQL> connect DEEPSEC_DBA/&dbapasswd@&target_pdb
Connected.
SQL>
SQL> ----------------------------------------------------------------------
SQL> -- 4. Create tablespace and HR schema
SQL> ----------------------------------------------------------------------
SQL>
SQL> -- Create the tablespace used by the HR demo schema.
SQL> -- Oracle Managed Files (OMF) controls the datafile location.
SQL> CREATE TABLESPACE users DATAFILE SIZE 100M AUTOEXTEND ON;
Tablespace created.
SQL>
SQL> -- Create the HR schema that owns the employee table.
SQL> -- NO AUTHENTICATION means this schema cannot be logged into directly;
SQL> -- it is accessed only through object grants.
SQL> CREATE USER hr NO AUTHENTICATION
2 DEFAULT TABLESPACE users
3 QUOTA UNLIMITED ON users;
User created.
SQL>
SQL> ----------------------------------------------------------------------
SQL> -- 5. Create the employee table
SQL> ----------------------------------------------------------------------
SQL>
SQL> -- Create the data model used by all demo scenarios.
SQL> CREATE TABLE hr.employees (
2 employee_id NUMBER PRIMARY KEY,
3 first_name VARCHAR2(50),
4 last_name VARCHAR2(50),
5 email VARCHAR2(128),
6 manager VARCHAR2(128),
7 ssn VARCHAR2(20),
8 salary NUMBER(10,2),
9 phone VARCHAR2(20)
10 );
Table created.
SQL>
SQL> ----------------------------------------------------------------------
SQL> -- 6. Load sample data
SQL> ----------------------------------------------------------------------
SQL>
SQL> INSERT INTO hr.employees VALUES (100,'Victoria','Williams','vwilliams',NULL,'219-09-9999',13000,'555-0100');
1 row created.
SQL> INSERT INTO hr.employees VALUES (200,'Marvin','Anderson','manderson','vwilliams','457-55-5462',12030,'555-0200');
1 row created.
SQL> INSERT INTO hr.employees VALUES (300,'Chris','Evans','cevans','vwilliams','321-12-4567',6900,'555-0300');
1 row created.
SQL> INSERT INTO hr.employees VALUES (400,'Emma','Baker','ebaker','manderson','733-02-9821',8200,'555-0400');
1 row created.
SQL> INSERT INTO hr.employees VALUES (500,'Taylor','Mills','tmills','manderson','558-76-1243',9000,'555-0500');
1 row created.
SQL> COMMIT;
Commit complete.
SQL>
SQL> ----------------------------------------------------------------------
SQL> -- 7. Create local end user identities
SQL> ----------------------------------------------------------------------
SQL>
SQL> -- Local end users whose names are matching the email column above
SQL> CREATE END USER "manderson" IDENTIFIED BY &passwd;
old 1: CREATE END USER "manderson" IDENTIFIED BY &passwd
new 1: CREATE END USER "manderson" IDENTIFIED BY pwd
End user created.
SQL> CREATE END USER "ebaker" IDENTIFIED BY &passwd;
old 1: CREATE END USER "ebaker" IDENTIFIED BY &passwd
new 1: CREATE END USER "ebaker" IDENTIFIED BY pwd
End user created.
SQL>
SQL> ----------------------------------------------------------------------
SQL> -- 8. Create data roles
SQL> ----------------------------------------------------------------------
SQL>
SQL> -- Employees can see only their own row.
SQL> CREATE OR REPLACE DATA ROLE employee_role;
Data role created.
SQL>
SQL> -- Managers can see their direct reports (with SSN masked).
SQL> CREATE OR REPLACE DATA ROLE manager_role;
Data role created.
SQL>
SQL> ----------------------------------------------------------------------
SQL> -- 9. Create the session role
SQL> ----------------------------------------------------------------------
SQL>
SQL> -- A standard database role that carries the CREATE SESSION privilege.
SQL> -- Granting this role to a data role allows end users who hold that
SQL> -- data role to establish a database session.
SQL> CREATE ROLE db_role;
Role created.
SQL> GRANT CREATE SESSION TO db_role;
Grant succeeded.
SQL>
SQL> ----------------------------------------------------------------------
SQL> -- 10. Grant create session privilege to data roles
SQL> ----------------------------------------------------------------------
SQL>
SQL> GRANT db_role TO employee_role;
Grant succeeded.
SQL> GRANT db_role TO manager_role;
Grant succeeded.
SQL>
SQL> ----------------------------------------------------------------------
SQL> -- 11. Assign data roles to end users
SQL> ----------------------------------------------------------------------
SQL>
SQL> -- Marvin holds both roles: he can see his own record as an employee,
SQL> -- and see his direct reports (Emma, Taylor) as a manager.
SQL> GRANT DATA ROLE manager_role TO "manderson";
Grant succeeded.
SQL> GRANT DATA ROLE employee_role TO "manderson";
Grant succeeded.
SQL>
SQL> -- Emma holds only the employee role: she can see only her own record.
SQL> GRANT DATA ROLE employee_role TO "ebaker";
Grant succeeded.
SQL>
SQL> ----------------------------------------------------------------------
SQL> -- 12. Create data grants
SQL> ----------------------------------------------------------------------
SQL>
SQL> -- Employee access rule:
SQL> -- A user can see only the row where the email matches their identity.
SQL> CREATE OR REPLACE DATA GRANT hr.employees_own_record
2 AS SELECT
3 ON hr.employees
4 WHERE email = ORA_END_USER_CONTEXT.username
5 TO employee_role;
Data grant created.
SQL>
SQL> -- Manager access rule:
SQL> -- A manager can see rows where the manager column matches their identity.
SQL> -- The ALL COLUMNS EXCEPT clause masks SSN for those rows.
SQL> CREATE OR REPLACE DATA GRANT hr.manager_direct_reports
2 AS SELECT (ALL COLUMNS EXCEPT ssn)
3 ON hr.employees
4 WHERE manager = ORA_END_USER_CONTEXT.username
5 TO manager_role;
Data grant created.
SQL>
SQL> ----------------------------------------------------------------------
SQL> -- 13. Query as Marvin Anderson (employee and manager)
SQL> ----------------------------------------------------------------------
SQL>
SQL> -- Marvin holds both employee_role and manager_role.
SQL> connect "manderson"/&passwd@&target_pdb
Connected.
SQL>
SQL> -- Confirm the active end user identity.
SQL> SELECT ORA_END_USER_CONTEXT.username FROM dual;
USERNAME
--------------------------------------------------------------------------------
"manderson"
1 row selected.
SQL>
SQL> -- Expected results:
SQL> -- employee_role: Marvin's own row, including his SSN and salary.
SQL> -- manager_role: Emma Baker and Taylor Mills (direct reports),
SQL> -- with their SSN column masked.
SQL> SELECT * FROM hr.employees;
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL MANAGER SSN SALARY PHONE
----------- ------------ ------------ ------------------------ ------------------------ ------------ -------- ------------
200 Marvin Anderson manderson vwilliams 457-55-5462 12030 555-0200
400 Emma Baker ebaker manderson 8200 555-0400
500 Taylor Mills tmills manderson 9000 555-0500
3 rows selected.
SQL>
SQL> ----------------------------------------------------------------------
SQL> -- 14. Query as Emma Baker (employee only)
SQL> ----------------------------------------------------------------------
SQL>
SQL> -- Emma holds only employee_role.
SQL> connect "ebaker"/&passwd@&target_pdb
Connected.
SQL>
SQL> -- Confirm the active end user identity.
SQL> SELECT ORA_END_USER_CONTEXT.username FROM dual;
USERNAME
--------------------------------------------------------------------------------
"ebaker"
1 row selected.
SQL>
SQL> -- Expected result: Emma's own row only.
SQL> -- No other rows are visible; SSN and salary are accessible
SQL> -- because employee_role grants full column access to her own record.
SQL> SELECT * FROM hr.employees;
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL MANAGER SSN SALARY PHONE
----------- ------------ ------------ ------------------------ ------------------------ ------------ -------- ------------
400 Emma Baker ebaker manderson 733-02-9821 8200 555-0400
1 row selected.
SQL>
SQL> spool off
A.1.3.2 01_local_cleanup.log
The output generated by running
01_local_cleanup.sql.
SQL>
SQL> -- This script removes all objects created by the demo so the
SQL> -- environment can be reused cleanly.
SQL>
SQL> ----------------------------------------------------------------------
SQL> -- 1. Connect as SYS
SQL> ----------------------------------------------------------------------
SQL>
SQL> connect sys/&syspasswd@&target_pdb as sysdba
Connected.
SQL>
SQL> ----------------------------------------------------------------------
SQL> -- 2. Remove local end users
SQL> ----------------------------------------------------------------------
SQL>
SQL> DROP END USER "manderson";
End user dropped.
SQL> DROP END USER "ebaker";
End user dropped.
SQL>
SQL> ----------------------------------------------------------------------
SQL> -- 3. Remove the HR schema
SQL> ----------------------------------------------------------------------
SQL>
SQL> DROP USER hr CASCADE;
User dropped.
SQL>
SQL> ----------------------------------------------------------------------
SQL> -- 4. Remove data roles
SQL> ----------------------------------------------------------------------
SQL>
SQL> DROP DATA ROLE employee_role;
Data role dropped.
SQL> DROP DATA ROLE manager_role;
Data role dropped.
SQL>
SQL> ----------------------------------------------------------------------
SQL> -- 5. Remove the database role
SQL> ----------------------------------------------------------------------
SQL>
SQL> DROP ROLE db_role;
Role dropped.
SQL>
SQL> ----------------------------------------------------------------------
SQL> -- 6. Remove the administration user and tablespace
SQL> ----------------------------------------------------------------------
SQL>
SQL> DROP USER DEEPSEC_DBA CASCADE;
User dropped.
SQL> DROP TABLESPACE users INCLUDING CONTENTS AND DATAFILES;
Tablespace dropped.
SQL>
SQL> spool off