This tutorial provides a clear guide to managing users, roles, and privileges in Oracle 19c, covering Data Control Language (DCL) commands like CREATE USER, ALTER USER, DROP USER, CREATE ROLE, SET ROLE, GRANT, and REVOKE. Master these commands to implement robust database security and access control in your Oracle environment.
Managing Users, Roles, and Privileges (DCL)
CREATE USER
The CREATE USER statement is used to create new database users. Each user has a unique name and is authenticated by a password, externally, or globally.
Example 1: Create a Basic User
CREATE USER tutorial_user IDENTIFIED BY "SecurePassword123"
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp;
-- This command creates a new database user named 'tutorial_user'
-- with a specified password, default tablespace, and temporary tablespace.
Explanation This code creates a new user tutorial_user, setting their password and assigning them to the users default tablespace and temp temporary tablespace.
Example 2: Create User with Specific Quota
CREATE USER app_data_user IDENTIFIED BY "AppDataPass!2025"
DEFAULT TABLESPACE app_data
QUOTA 100M ON app_data;
-- Creates 'app_data_user' with a 100MB quota on the 'app_data' tablespace.
Explanation This statement creates app_data_user and grants them a specific storage quota of 100MB on the app_data tablespace.
Example 3: Create User with No Quota
CREATE USER reporting_user IDENTIFIED BY "ReportPass#987"
DEFAULT TABLESPACE users
QUOTA UNLIMITED ON users;
-- Creates 'reporting_user' with unlimited storage in the 'users' tablespace.
Explanation This example creates reporting_user and allows them to consume unlimited space in their assigned default tablespace.
Example 4: Create User with Password Expire
CREATE USER temp_user IDENTIFIED BY "TemporaryPwd2025"
PASSWORD EXPIRE;
-- Creates 'temp_user' and forces a password change on first login.
Explanation This command creates temp_user and immediately expires their password, requiring a change upon their initial login for enhanced security.
Example 5: Create Common User in CDB (for PDBs)
ALTER SESSION SET CONTAINER = CDB$ROOT;
CREATE USER C##COMMON_ADMIN IDENTIFIED BY "CDBAdminPwd!"
CONTAINER = ALL;
-- Creates a common user accessible across all pluggable databases (PDBs) in a CDB.
Explanation This example demonstrates creating a common user C##COMMON_ADMIN in a Container Database (CDB) that can be used across all Pluggable Databases (PDBs).
ALTER USER
The ALTER USER statement is used to modify existing user attributes, such as passwords, default tablespaces, temporary tablespaces, and account status (lock/unlock).
Example 1: Change User Password
ALTER USER tutorial_user IDENTIFIED BY "NewSecurePwd!2025";
-- Changes the password for 'tutorial_user'.
Explanation This command updates the password for tutorial_user to a new, more secure value.
Example 2: Lock a User Account
ALTER USER tutorial_user ACCOUNT LOCK;
-- Locks the 'tutorial_user' account, preventing login.
Explanation This statement locks the tutorial_user account, disallowing any further login attempts until unlocked.
Example 3: Unlock a User Account
ALTER USER tutorial_user ACCOUNT UNLOCK;
-- Unlocks the 'tutorial_user' account, allowing login.
Explanation This command unlocks the tutorial_user account, enabling the user to log in again.
Example 4: Change Default Tablespace
ALTER USER tutorial_user DEFAULT TABLESPACE new_data_ts;
-- Changes the default tablespace for 'tutorial_user' to 'new_data_ts'.
Explanation This SQL modifies tutorial_user's default tablespace, where their new objects will be created by default.
Example 5: Expire User Password
ALTER USER app_data_user PASSWORD EXPIRE;
-- Forces 'app_data_user' to change their password on the next login.
Explanation This command sets the password for app_data_user to expire immediately, prompting a password change upon their next authentication.
DROP USER
The DROP USER statement removes a user account from the database. The CASCADE option is crucial for removing all objects owned by the user.
Example 1: Drop a User (No Objects)
DROP USER temp_user;
-- Deletes the 'temp_user' account if they don't own any objects.
Explanation This command drops temp_user. It will fail if temp_user owns any database objects.
Example 2: Drop a User and Their Objects
DROP USER app_data_user CASCADE;
-- Deletes 'app_data_user' and all schema objects they own.
Explanation This crucial statement drops app_data_user and cascades the deletion to all tables, views, and other objects owned by them.
Example 3: Drop a User with Sessions (Requires Session Termination)
-- First, identify and kill user sessions if they are connected:
-- SELECT SID, SERIAL# FROM V$SESSION WHERE USERNAME = 'TUTORIAL_USER';
-- ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;
DROP USER tutorial_user CASCADE;
-- Drops the user after terminating any active sessions and their objects.
Explanation To drop a user with active sessions, those sessions must first be terminated; then, the user can be dropped, along with their objects.
Example 4: Drop a Common User (in CDB)
ALTER SESSION SET CONTAINER = CDB$ROOT;
DROP USER C##COMMON_ADMIN CASCADE;
-- Drops a common user and their common objects across all PDBs.
Explanation This example demonstrates dropping a common user (C##COMMON_ADMIN) from the CDB, which removes them from all associated PDBs.
Example 5: Confirm User Dropped
SELECT USERNAME FROM ALL_USERS WHERE USERNAME = 'APP_DATA_USER';
-- Verifies that the user 'APP_DATA_USER' no longer exists in the database.
Explanation This query is used to confirm the successful removal of a user from the database by checking ALL_USERS view.
CREATE ROLE
The CREATE ROLE statement is used to create a role, which is a collection of privileges. Roles simplify privilege management by allowing privileges to be granted to a role, which is then granted to users.
Example 1: Create a Simple Role
CREATE ROLE app_developer_role;
-- Creates a new role named 'app_developer_role'.
Explanation This command creates a new database role, app_developer_role, which can then be assigned privileges.
Example 2: Create a Password-Protected Role
CREATE ROLE sensitive_data_access IDENTIFIED BY "SecretRolePwd!";
-- Creates a role that requires a password to be enabled.
Explanation This statement creates sensitive_data_access, a role that users must activate with a password using SET ROLE to use its privileges.
Example 3: Create a Global Role (for Enterprise Users)
CREATE ROLE sales_global_role IDENTIFIED GLOBALLY;
-- Creates a global role for use with enterprise user authentication.
Explanation This example creates a global role sales_global_role, integrated with enterprise user management systems.
Example 4: Create a Role in a PDB (Local Role)
ALTER SESSION SET CONTAINER = pdb1; -- Switch to a specific PDB
CREATE ROLE local_pdb_reader;
-- Creates a role specific to the current pluggable database (PDB).
Explanation This command creates local_pdb_reader within a specific PDB (pdb1), making it a local role.
Example 5: Create a Common Role (in CDB)
ALTER SESSION SET CONTAINER = CDB$ROOT;
CREATE ROLE C##COMMON_AUDITOR CONTAINER = ALL;
-- Creates a common role accessible and grantable across all PDBs.
Explanation This example shows how to create C##COMMON_AUDITOR, a common role in the CDB, available across all PDBs for unified privilege management.
SET ROLE
The SET ROLE statement enables or disables roles for the current user. It is particularly useful for password-protected roles or managing multiple roles.
Example 1: Enable a Role
SET ROLE app_developer_role;
-- Enables the 'app_developer_role' for the current session.
Explanation This command activates the app_developer_role for the current user's session, making its granted privileges available.
Example 2: Enable a Password-Protected Role
SET ROLE sensitive_data_access IDENTIFIED BY "SecretRolePwd!";
-- Enables a password-protected role for the session.
Explanation This statement enables sensitive_data_access by providing its password, granting access to its associated privileges.
Example 3: Disable All Roles
SET ROLE NONE;
-- Disables all roles for the current session, leaving only privileges granted directly to the user.
Explanation This command revokes all roles from the current session, ensuring only privileges explicitly granted to the user are active.
Example 4: Enable Specific Roles
SET ROLE app_developer_role, local_pdb_reader;
-- Enables multiple specified roles for the session.
Explanation This statement activates both app_developer_role and local_pdb_reader simultaneously for the current session.
Example 5: Enable All Granted Roles
SET ROLE ALL;
-- Enables all roles granted to the current user (if not password-protected).
Explanation This command attempts to enable all roles that have been granted to the current user, excluding those that require a password.
GRANT (System Privileges)
The GRANT statement is used to assign system privileges to users or roles. System privileges allow users to perform database-wide actions, such as creating sessions, tables, or performing administrative tasks.
Example 1: Grant Create Session Privilege
GRANT CREATE SESSION TO tutorial_user;
-- Allows 'tutorial_user' to connect to the database.
Explanation This command grants tutorial_user the fundamental privilege to establish a connection to the Oracle database.
Example 2: Grant Resource and Connect Role
GRANT CONNECT, RESOURCE TO app_developer_role;
-- Grants common pre-defined roles (CONNECT and RESOURCE) to a custom role.
Explanation This statement assigns the CONNECT (for session creation) and RESOURCE (for object creation) roles to app_developer_role.
Example 3: Grant DBA Privilege (Highly Sensitive)
GRANT DBA TO C##COMMON_ADMIN WITH ADMIN OPTION;
-- Grants DBA role with the ability to grant it to others (use with extreme caution).
Explanation This highly powerful command grants C##COMMON_ADMIN the DBA role and ADMIN OPTION, allowing them to further grant DBA to other users.
Example 4: Grant Create Table Privilege
GRANT CREATE TABLE TO app_developer_role;
-- Allows users with 'app_developer_role' to create tables in their schema.
Explanation This privilege allows any user who has app_developer_role enabled to create tables within their own schema.
Example 5: Grant Select Any Table
GRANT SELECT ANY TABLE TO reporting_user;
-- Allows 'reporting_user' to select data from any table in the database.
Explanation This command grants reporting_user the ability to query data from any table in the entire database, regardless of its owner.
GRANT (Object Privileges)
Object privileges control access to specific database objects, such as tables, views, sequences, or procedures. They allow fine-grained control over data manipulation.
Example 1: Grant SELECT on a Table
GRANT SELECT ON hr.employees TO tutorial_user;
-- Allows 'tutorial_user' to query data from the 'employees' table owned by 'hr'.
Explanation This command grants tutorial_user the right to read data from the employees table, which is owned by the hr schema.
Example 2: Grant INSERT, UPDATE, DELETE on a Table
GRANT INSERT, UPDATE, DELETE ON app_schema.orders TO app_developer_role;
-- Allows role members to manipulate data in the 'orders' table.
Explanation This statement grants the app_developer_role the privileges to add, modify, and remove records in the orders table within the app_schema.
Example 3: Grant ALL Privileges on a View
GRANT ALL ON app_schema.customer_view TO reporting_user;
-- Grants all applicable object privileges (SELECT, INSERT, UPDATE, DELETE) on a view.
Explanation This grants reporting_user all standard object privileges on customer_view, including SELECT, INSERT, UPDATE, and DELETE.
Example 4: Grant EXECUTE on a Procedure
GRANT EXECUTE ON hr.calculate_bonus TO app_developer_role;
-- Allows role members to execute the 'calculate_bonus' stored procedure.
Explanation This command grants the app_developer_role the permission to run the calculate_bonus procedure in the hr schema.
Example 5: Grant REFERENCES on a Table
GRANT REFERENCES ON hr.departments TO app_schema.employees;
-- Allows 'app_schema.employees' to create foreign key constraints referencing 'hr.departments'.
Explanation This privilege enables the employees table in app_schema to define foreign key constraints that refer to the departments table in the hr schema.
REVOKE (System Privileges)
The REVOKE statement removes previously granted system privileges from users or roles. This is essential for maintaining a secure database by adhering to the principle of least privilege.
Example 1: Revoke Create Session Privilege
REVOKE CREATE SESSION FROM tutorial_user;
-- Removes the ability for 'tutorial_user' to connect to the database.
Explanation This command revokes the CREATE SESSION privilege, preventing tutorial_user from establishing new database connections.
Example 2: Revoke Resource Role
REVOKE RESOURCE FROM app_developer_role;
-- Removes the 'RESOURCE' role and its associated privileges from 'app_developer_role'.
Explanation This statement removes the RESOURCE role from app_developer_role, thereby revoking all privileges bundled within that role.
Example 3: Revoke DBA Privilege (with Admin Option)
REVOKE DBA FROM C##COMMON_ADMIN;
-- Removes the DBA role from 'C##COMMON_ADMIN'.
Explanation This command takes away the DBA role from C##COMMON_ADMIN, significantly reducing their administrative power.
Example 4: Revoke Create Table Privilege
REVOKE CREATE TABLE FROM app_developer_role;
-- Prevents users with 'app_developer_role' from creating new tables.
Explanation This privilege revocation ensures that users assigned app_developer_role can no longer create tables in their schemas.
Example 5: Revoke Select Any Table
REVOKE SELECT ANY TABLE FROM reporting_user;
-- Stops 'reporting_user' from being able to select data from any table.
Explanation This command removes the SELECT ANY TABLE privilege, restricting reporting_user's ability to query data from arbitrary tables.
REVOKE (Object Privileges)
The REVOKE statement also removes object privileges, controlling specific access rights to database objects.
Example 1: Revoke SELECT on a Table
REVOKE SELECT ON hr.employees FROM tutorial_user;
-- Removes 'tutorial_user's ability to query data from the 'employees' table.
Explanation This command revokes tutorial_user's permission to read data from the hr.employees table.
Example 2: Revoke INSERT on a Table
REVOKE INSERT ON app_schema.orders FROM app_developer_role;
-- Removes the ability for role members to insert data into the 'orders' table.
Explanation This statement specifically revokes the INSERT privilege on the app_schema.orders table from app_developer_role.
Example 3: Revoke ALL Privileges on a View
REVOKE ALL ON app_schema.customer_view FROM reporting_user;
-- Revokes all object privileges previously granted on the 'customer_view'.
Explanation This command removes all previously granted object privileges from reporting_user on the customer_view.
Example 4: Revoke EXECUTE on a Procedure
REVOKE EXECUTE ON hr.calculate_bonus FROM app_developer_role;
-- Prevents role members from executing the 'calculate_bonus' stored procedure.
Explanation This statement revokes the permission for app_developer_role to execute the hr.calculate_bonus procedure.
Example 5: Revoke REFERENCES with CASCADE CONSTRAINTS
REVOKE REFERENCES ON hr.departments FROM app_schema.employees CASCADE CONSTRAINTS;
-- Revokes REFERENCES privilege and drops any dependent foreign key constraints.
Explanation This command revokes the REFERENCES privilege and, importantly, drops any foreign key constraints in app_schema.employees that relied on hr.departments.