3.2 Create Sample Data

Create a sample HR schema with an employees table to test data access control.

1. Connect to the database as a named user with the DBA role and switch to the target pluggable database.

   ALTER SESSION SET CONTAINER = <your-target-PDB>;

2. Create a sample HR schema and populate it with an employees table. If you already have an hr.employees table, drop it before proceeding.

   Note:

   The CREATE USER statement below uses the USERS tablespace. If this tablespace does not exist in your database, create it.

   CREATE TABLESPACE users
     DATAFILE '<data_file_path>' SIZE 100M
     AUTOEXTEND ON;

   Replace <data_file_path> with the full path for the data file.

   -- Create the HR user
   CREATE USER hr NO AUTHENTICATION
     DEFAULT TABLESPACE users
     QUOTA UNLIMITED ON users;
    
   -- Create the employees table
   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)
   );
   

3. Populate the table with sample employee records.

   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;

After loading the data, the hr.employees table contains the following records:

EMPLOYEE_ID  FIRST_NAME  LAST_NAME  EMAIL       MANAGER     SSN          SALARY  PHONE
-----------  ----------  ---------  ----------  ----------  -----------  ------  --------
100          Victoria    Williams   vwilliams               219-09-9999  13000   555-0100
200          Marvin      Anderson   manderson   vwilliams   457-55-5462  12030   555-0200
300          Chris       Evans      cevans      vwilliams   321-12-4567   6900   555-0300
400          Emma        Baker      ebaker      manderson   733-02-9821   8200   555-0400
500          Taylor      Mills      tmills      manderson   558-76-1243   9000   555-0500