3.4 Configure Data Roles

Create data roles for the employee and manager job functions.

1. First, create the data roles, then create a standard database role with the CREATE SESSION privilege and grant it to each data role that must support direct logon. In this scenario, grant it to the employee_role data role, which you'll assign to the end users in this example.

   -- Create data roles that are managed locally in the database
   CREATE DATA ROLE employee_role;
   CREATE DATA ROLE manager_role;
    
   -- Create a standard database role for connection privileges
   CREATE ROLE db_role;
   GRANT CREATE SESSION TO db_role;
    
   -- Grant the connection privileges to the data role used for direct logon
   GRANT db_role TO employee_role;

2. Grant the data roles to the local end users. Marvin receives both the manager and employee roles. Emma receives only the employee role.

   -- Grant data roles to Marvin (manager and employee)
   GRANT DATA ROLE manager_role TO "manderson";
   GRANT DATA ROLE employee_role TO "manderson";
    
   -- Grant the employee data role to Emma
   GRANT DATA ROLE employee_role TO "ebaker";