Users, Roles & Privileges

To follow along with the examples in this lesson, use the course dataset from Lesson 8.

Access control is how Oracle decides who can connect to the database and what they are allowed to do once connected. Every action in Oracle — querying a table, running a procedure, creating a user — requires a privilege. Privileges can be granted directly to a user or bundled into a role that is then granted to many users at once. Getting access control right is one of the most important responsibilities of a database administrator.

Users and Schemas

In Oracle, a user and a schema are the same thing. When you create a user, Oracle creates a schema of the same name to hold that user's objects — tables, views, procedures, and sequences. A user who owns no objects still has a schema; a schema always belongs to exactly one user. This is different from other databases where schemas and users are separate concepts.

Every user needs at least the CREATE SESSION privilege to connect to the database. Without it the account exists but cannot be used. A newly created user with only CREATE SESSION can connect but cannot create any objects or query any tables until further privileges are granted.

-- Create a new user with a password
-- The user account and schema are created simultaneously
CREATE USER reporting_user IDENTIFIED BY SecurePass123;
-- Grant the minimum privilege needed to connect
GRANT CREATE SESSION TO reporting_user;
-- Grant the ability to create tables and views in their own schema
GRANT CREATE TABLE, CREATE VIEW TO reporting_user;
-- Assign a tablespace quota -- without this the user cannot store any data
ALTER USER reporting_user QUOTA 100M ON users;
-- Lock an account without dropping it -- prevents login while preserving the schema
ALTER USER reporting_user ACCOUNT LOCK;
-- Unlock the account
ALTER USER reporting_user ACCOUNT UNLOCK;
-- Drop a user and all objects they own
DROP USER reporting_user CASCADE;  -- CASCADE removes all schema objects first

• A user without a tablespace quota can connect and query other schemas but cannot create any objects that store data in their own schema
• DROP USER CASCADE drops the user and every object in their schema — use with care, this is irreversible
• ACCOUNT LOCK is safer than dropping a user when you want to temporarily disable access — the schema and all objects are preserved
• In Oracle, CREATE USER requires the DBA role or the CREATE USER system privilege

System Privileges vs Object Privileges

Oracle divides privileges into two categories. System privileges allow a user to perform a class of action anywhere in the database — CREATE TABLE, CREATE SESSION, ALTER ANY TABLE. They are broad and powerful. Object privileges allow a user to perform a specific action on a specific object owned by another user — SELECT ON hr.employees, EXECUTE ON store.get_customer_total. Object privileges are precise and follow the principle of least privilege.

-- System privilege: allows the user to create tables in their OWN schema
GRANT CREATE TABLE TO reporting_user;
-- System privilege with ANY: allows the user to create tables in ANY schema
-- Grant these sparingly -- they are very powerful
GRANT CREATE ANY TABLE TO reporting_user;
-- Object privilege: SELECT on a specific table in another schema
GRANT SELECT ON dataplexa_store.orders TO reporting_user;
-- Object privilege: execute a specific procedure
GRANT EXECUTE ON dataplexa_store.update_category_price TO reporting_user;
-- Grant multiple object privileges at once
GRANT SELECT, INSERT, UPDATE ON dataplexa_store.products TO reporting_user;
-- Revoke a privilege -- removes it immediately
REVOKE SELECT ON dataplexa_store.orders FROM reporting_user;
-- WITH GRANT OPTION: allows the recipient to grant the same privilege to others
GRANT SELECT ON dataplexa_store.customers TO reporting_user WITH GRANT OPTION;

• System privileges with ANY — DROP ANY TABLE, SELECT ANY TABLE — are extremely powerful and should only ever be granted to DBAs
• REVOKE takes effect immediately — the user loses the privilege the moment it is revoked, even in an active session
• WITH GRANT OPTION allows privilege chains — if reporting_user grants SELECT to another user and reporting_user's privilege is later revoked, the downstream grant is also revoked automatically
• Object privileges on a view do not require privileges on the underlying tables — this is how views are used for secure access

Roles

A role is a named collection of privileges. Instead of granting dozens of privileges to each user individually, you grant them to a role once and then grant the role to users. When the privileges needed by a job function change, you update the role and every user with that role inherits the change automatically. Oracle provides several built-in roles — CONNECT, RESOURCE, and DBA are the most commonly referenced — but creating your own roles is almost always better practice in production.

-- Create a custom role for read-only reporting access
CREATE ROLE reporting_role;
-- Grant privileges to the role
GRANT CREATE SESSION                             TO reporting_role;
GRANT SELECT ON dataplexa_store.orders           TO reporting_role;
GRANT SELECT ON dataplexa_store.customers        TO reporting_role;
GRANT SELECT ON dataplexa_store.products         TO reporting_role;
GRANT SELECT ON dataplexa_store.customer_order_summary TO reporting_role;
-- Grant the role to users -- each user inherits all role privileges
GRANT reporting_role TO reporting_user;
GRANT reporting_role TO analyst_user;
-- Revoke the role from a user
REVOKE reporting_role FROM reporting_user;
-- Drop a role -- automatically revoked from all users who held it
DROP ROLE reporting_role;

• Granting a role to a user is identical in syntax to granting a privilege — GRANT role_name TO user_name
• When a role is dropped every user who held it loses all the privileges that came with it immediately
• The built-in DBA role grants almost every system privilege — never grant it to application users or reporting accounts
• Roles cannot be granted to themselves or create circular grants — Oracle prevents this

Viewing Privileges and Roles

Oracle's data dictionary stores every privilege grant and role assignment. Use USER_SYS_PRIVS to see system privileges granted to the current user, USER_TAB_PRIVS for object privileges, and USER_ROLE_PRIVS for roles. DBAs can query the equivalent DBA_ views to see grants across all users in the database.

-- System privileges granted to the current user
SELECT privilege, admin_option
FROM   user_sys_privs
ORDER  BY privilege;
-- Object privileges granted TO the current user by others
SELECT owner, table_name, privilege, grantable
FROM   user_tab_privs
WHERE  grantee = USER        -- USER is a function returning the current username
ORDER  BY owner, table_name;
-- Roles granted to the current user
SELECT granted_role, default_role, admin_option
FROM   user_role_privs
ORDER  BY granted_role;
-- DBA view: see all object privileges granted on a specific table (requires DBA access)
SELECT grantee, privilege, grantable
FROM   dba_tab_privs
WHERE  owner      = 'DATAPLEXA_STORE'
AND    table_name = 'ORDERS'
ORDER  BY grantee;

• USER is an Oracle function that returns the current session's username — useful in queries that should always reflect the caller's own grants
• GRANTABLE = YES means the privilege was granted WITH GRANT OPTION — the user can pass it on to others
• ADMIN_OPTION = YES on a system privilege means the user can grant that system privilege to others
• The DBA_ prefix views require the DBA role or SELECT ANY DICTIONARY privilege — use them to audit grants across the entire database

Lesson Summary

Practice Questions

Quiz

Next up — Transactions & Concurrency — How Oracle manages transactions with COMMIT and ROLLBACK, what savepoints do, and how Oracle's multi-version read consistency keeps queries accurate without blocking writers.