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:

How to Run the Scripts

Run these scripts as SYS from a shell on the database host, in the following order.

  1. 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: SYS password for the target PDB.

    • dbapasswd: Password for the DEEPSEC_DBA administrative user that the script creates.

    • passwd: Password for the manderson and ebaker end users.

    • target_pdb: Service name of the target PDB.

  2. 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: SYS password for the target PDB.

    • target_pdb: Service name of the target PDB.

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.

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

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

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.

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

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