User and Role Management in Oracle

Overview

In this workshop you will:
- Build a small HR schema (departments, employees, salaries).
- Create common users for admins, managers and employees.
- Protect data with views and privileges, then simplify with roles.
- Verify that each user sees only what they should.

All commands target Oracle Database (for example via SQL*Plus on Linux).

Notation
- Terminal: commands in bash blocks (prompt example: [oracle@dbserver ~]$).
- SQL*Plus: commands in sql blocks (run after connecting with sqlplus).

---

1. Scenario and data model

The HR team needs three access levels:
- Administrator (ADMIN): manages users/roles and has full access to HR data.
- Manager (MANAGER): can view and update basic employee info only inside their department (name, email, department). No salaries. Cannot manage users.
- Employee (EMPLOYEE): can only view employee names.

Each manager/employee is an Oracle user (sufficient for a small lab).

Tables:
- DEPARTMENTS(DEPT_ID, DEPT_NAME) - list of departments.
- EMPLOYEES(EMP_ID, NAME, DEPT_ID, MANAGER_OF, USERNAME, EMAIL, JOIN_DATE) - staff and which department they manage (MANAGER_OF is null for non-managers).
- SALARIES(EMP_ID, SALARY) - salary isolated for tighter control.

---

2. Prepare the admin account and schema

2.1 Connect as SYSDBA

Terminal ([oracle@dbserver ~]$):

sqlplus / AS SYSDBA

2.2 Create the admin user (adjust the password to your policy)

CREATE USER c##admin IDENTIFIED BY admin
  DEFAULT TABLESPACE users
  QUOTA UNLIMITED ON users;

GRANT DBA TO c##admin;

Connect as admin:

CONNECT c##admin/admin

2.3 Create the HR tables

CREATE TABLE departments (
  dept_id    NUMBER PRIMARY KEY,
  dept_name  VARCHAR2(100)
);

CREATE TABLE employees (
  emp_id      NUMBER PRIMARY KEY,
  name        VARCHAR2(50),
  dept_id     NUMBER REFERENCES departments(dept_id),
  manager_of  NUMBER REFERENCES departments(dept_id), -- which department this user manages
  username    VARCHAR2(30),                           -- Oracle username
  email       VARCHAR2(100),
  join_date   DATE
);

CREATE TABLE salaries (
  emp_id  NUMBER PRIMARY KEY,
  salary  NUMBER,
  FOREIGN KEY (emp_id) REFERENCES employees(emp_id)
);

2.4 Load sample data

INSERT INTO departments VALUES (20, 'Sales');
INSERT INTO departments VALUES (10, 'IT');

INSERT INTO employees (emp_id, name, dept_id, manager_of, email, join_date, username)
VALUES (1, 'Ahmed', 20, 20, 'ahmed@sales.company.com', DATE '2020-05-10', 'C##MANAGER1');

INSERT INTO employees (emp_id, name, dept_id, manager_of, email, join_date, username)
VALUES (2, 'Ali', 10, NULL, 'ali@it.company.com', DATE '2021-01-15', 'C##EMP1');

INSERT INTO employees (emp_id, name, dept_id, manager_of, email, join_date, username)
VALUES (3, 'Amir', 10, 10, 'amir@it.company.com', DATE '2019-03-22', 'C##MANAGER2');

INSERT INTO employees (emp_id, name, dept_id, manager_of, email, join_date, username)
VALUES (4, 'Imad', 20, NULL, 'imad@sales.company.com', DATE '2022-07-01', 'C##EMP2');

INSERT INTO employees (emp_id, name, dept_id, manager_of, email, join_date, username)
VALUES (5, 'Raouf', 10, NULL, 'raouf@it.company.com', DATE '2023-09-05', 'C##EMP3');

INSERT INTO salaries (emp_id, salary) VALUES (1, 8500.00);
INSERT INTO salaries (emp_id, salary) VALUES (2, 4200.00);
INSERT INTO salaries (emp_id, salary) VALUES (3, 9200.00);
INSERT INTO salaries (emp_id, salary) VALUES (4, 4800.00);
INSERT INTO salaries (emp_id, salary) VALUES (5, 3900.00);

Managers C##MANAGER1 and C##MANAGER2 manage departments 10 and 20 via the MANAGER_OF column.

---

3. Create the Oracle users

Run as C##ADMIN:

CREATE USER c##manager1 IDENTIFIED BY manager1;
GRANT CREATE SESSION TO c##manager1;

CREATE USER c##manager2 IDENTIFIED BY manager2;
GRANT CREATE SESSION TO c##manager2;

CREATE USER c##emp1 IDENTIFIED BY emp1;
GRANT CREATE SESSION TO c##emp1;

CREATE USER c##emp2 IDENTIFIED BY emp2;
GRANT CREATE SESSION TO c##emp2;

CREATE USER c##emp3 IDENTIFIED BY emp3;
GRANT CREATE SESSION TO c##emp3;

At this point they can log in but cannot see tables.

---

4. Version 1: direct privileges via views

4.1 Department-scoped manager view

Limit managers to their own department based on the USERNAME in EMPLOYEES:

CREATE OR REPLACE VIEW VempManager AS
SELECT e.name, e.email, e.dept_id
FROM   employees e
WHERE  e.dept_id = (
  SELECT manager_of
  FROM   employees
  WHERE  username = USER
    AND  manager_of IS NOT NULL
);

Grant managers access:

GRANT SELECT, UPDATE ON VempManager TO c##manager1;
GRANT SELECT, UPDATE ON VempManager TO c##manager2;
GRANT SELECT ON departments TO c##manager1;
GRANT SELECT ON departments TO c##manager2;

4.2 Employee names view

Expose only names to employees:

CREATE OR REPLACE VIEW emp_names AS
SELECT name FROM employees;

Grant read-only access:

GRANT SELECT ON emp_names TO c##emp1;
GRANT SELECT ON emp_names TO c##emp2;
GRANT SELECT ON emp_names TO c##emp3;

Result so far:
- Managers can view/update basic data in their own department plus see departments.
- Employees can read only the list of names.
- Salaries remain hidden for everyone except C##ADMIN.

---

5. Version 2: wrap privileges in roles

Roles make it easy to onboard new users by granting a single object.

5.1 Manager role

CREATE ROLE C##r_manager;
GRANT SELECT, UPDATE ON VempManager TO C##r_manager;
GRANT SELECT ON departments TO C##r_manager;

GRANT C##r_manager TO c##manager1;
GRANT C##r_manager TO c##manager2;

5.2 Employee role

CREATE ROLE C##r_employee;
GRANT SELECT ON emp_names TO C##r_employee;

GRANT C##r_employee TO c##emp1;
GRANT C##r_employee TO c##emp2;
GRANT C##r_employee TO c##emp3;

---

6. Verify as SYSDBA

Run these checks to confirm the setup:

-- Tables
SELECT owner, table_name
FROM   dba_tables
WHERE  table_name IN ('EMPLOYEES','DEPARTMENTS','SALARIES');

-- Views
SELECT owner, view_name
FROM   dba_views
WHERE  view_name IN ('VEMPMANAGER','EMP_NAMES');

-- Users
SELECT username, account_status, created
FROM   dba_users
WHERE  username LIKE 'C##%';

-- Roles granted to users
SELECT grantee, granted_role
FROM   dba_role_privs
WHERE  grantee LIKE 'C##%';

-- Privileges of a specific role
SELECT * FROM dba_tab_privs WHERE grantee = 'C##R_MANAGER';
SELECT * FROM dba_tab_privs WHERE grantee = 'C##R_EMPLOYEE';

---

7. Test as each user

7.1 Admin (C##ADMIN)

CONNECT c##admin/admin
SELECT table_name FROM user_tables;
SELECT view_name  FROM user_views;
SELECT * FROM salaries; -- full access

7.2 Manager (C##MANAGER1 / C##MANAGER2)

CONNECT c##manager1/manager1
SELECT * FROM c##admin.VempManager;     -- only their department
SELECT * FROM c##admin.departments;

UPDATE c##admin.VempManager
SET    email = 'new.mail@company.com'
WHERE  name = 'Ali';

SELECT * FROM session_roles;   -- should list C##R_MANAGER
SELECT * FROM user_tab_privs;  -- check table/view privileges

SELECT * FROM c##admin.salaries; -- should fail (ORA-01031)

7.3 Employee (C##EMP1 / C##EMP2 / C##EMP3)

CONNECT c##emp1/emp1
SELECT * FROM c##admin.emp_names;

SELECT * FROM session_roles;   -- should list C##R_EMPLOYEE
SELECT * FROM user_tab_privs;

SELECT * FROM c##admin.employees; -- should fail

---

8. Takeaways

• Store Oracle usernames in the data model to drive view filters (department-scoped access).
• Use views (VempManager, emp_names) to hide sensitive columns.
• Granting through roles (C##R_MANAGER, C##R_EMPLOYEE) keeps onboarding and audits simple.
• Dictionary views (DBA_%, SESSION_ROLES, USER_TAB_PRIVS) let you verify exactly who can do what.