ROLES, PRIVILEGES & PROFILES
USER SECURITY & RESOURCE MANAGEMENT
Overview
- Privileges, Roles, and Profiles together form Oracle's user-security model.
- Privilege — a right to perform a specific action or access a specific object.
- Role — a named group of privileges, granted as a single unit.
- Profile — a named set of resource limits and password rules, assigned to a user.
| Privilege | Role |
|---|---|
| A single right to perform an action or access an object | A named group of privileges (and/or other roles) bundled together |
| Granted directly to a user | Granted to a user, who then "inherits" every privilege in the role |
| Hard to manage for many users | Easy to manage — change the role once, applies to everyone who has it |
1) Privileges
- A privilege is the right to execute a particular SQL statement or access another user's object.
- Two categories: System Privileges and Object Privileges.
System Privileges
- Right to perform an action on the database as a whole — e.g. CREATE SESSION, CREATE TABLE, CREATE USER, DROP ANY TABLE.
- Usually granted by a DBA (SYS/SYSTEM) to administrators or application owners.
Object Privileges
- Right to perform an action on a specific schema object — e.g. SELECT, INSERT, UPDATE, DELETE, EXECUTE, REFERENCES on a table/view/procedure.
- Granted by the object owner, or anyone who has the privilege WITH GRANT OPTION.
Quick DBA Commands — Privileges
SQL
-- Grant system privileges GRANT CREATE SESSION, CREATE TABLE TO raja; GRANT CREATE SESSION TO raja WITH ADMIN OPTION; -- Grant object privileges GRANT SELECT, INSERT ON hr.employees TO raja; GRANT SELECT ON hr.employees TO raja WITH GRANT OPTION; GRANT ALL ON hr.employees TO raja; -- Revoke privileges REVOKE CREATE TABLE FROM raja; REVOKE SELECT, INSERT ON hr.employees FROM raja; -- Check privileges SELECT * FROM dba_sys_privs WHERE grantee = 'RAJA'; SELECT * FROM dba_tab_privs WHERE grantee = 'RAJA'; SELECT * FROM session_privs; -- privileges of current session SELECT * FROM user_sys_privs; -- privileges of current user
ADMIN OPTION applies to system privileges (lets the grantee re-grant it). GRANT OPTION applies to object privileges (same purpose, for objects).
2) Roles
- A role is a named collection of privileges (and/or other roles) that can be granted to users as a single unit.
- Simplifies privilege management — grant/revoke the role instead of managing many individual privileges per user.
- A role itself owns no objects; it is just a container for privileges.
- Roles can be granted to other roles (nested roles), but not circularly.
Common Predefined (Oracle-Supplied) Roles
- CONNECT — basic privileges to connect to the database (CREATE SESSION).
- RESOURCE — privileges to create objects (tables, procedures, triggers etc.).
- DBA — full administrative privileges (almost all system privileges).
- SELECT_CATALOG_ROLE — read access to data dictionary views.
- EXP_FULL_DATABASE / IMP_FULL_DATABASE — used for full database export/import.
Quick DBA Commands — Roles
SQL
-- Create a role CREATE ROLE app_dev; CREATE ROLE app_dev IDENTIFIED BY dev123; -- password-protected role -- Grant privileges to the role GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW TO app_dev; -- Grant the role to a user GRANT app_dev TO raja; -- Grant a role to another role (nested) GRANT app_dev TO senior_dev; -- Enable / disable a role for the current session SET ROLE app_dev; SET ROLE app_dev IDENTIFIED BY dev123; SET ROLE ALL; SET ROLE NONE; -- Set a default role for a user (auto-enabled at login) ALTER USER raja DEFAULT ROLE app_dev; ALTER USER raja DEFAULT ROLE ALL; ALTER USER raja DEFAULT ROLE ALL EXCEPT app_dev; -- Revoke a role REVOKE app_dev FROM raja; -- Drop a role DROP ROLE app_dev; -- Check roles SELECT * FROM dba_roles; SELECT * FROM dba_role_privs WHERE grantee = 'RAJA'; SELECT * FROM role_sys_privs WHERE role = 'APP_DEV'; SELECT * FROM session_roles; -- roles active in current session
3) Profile
- A profile is a named set of resource limits and password policy settings that can be assigned to a user.
- Used to control resource usage (CPU, sessions, idle time) and enforce password security.
- Every user is assigned a profile — if none is specified, the DEFAULT profile applies.
- Resource limits only take effect when the RESOURCE_LIMIT initialization parameter is set to TRUE.
Password Parameters
| Parameter | Purpose |
|---|---|
| FAILED_LOGIN_ATTEMPTS | No. of failed login attempts before the account is locked |
| PASSWORD_LOCK_TIME | No. of days the account stays locked after exceeding failed attempts |
| PASSWORD_LIFE_TIME | No. of days a password is valid before it must be changed |
| PASSWORD_GRACE_TIME | Grace period (days) to change password after expiry, before locking |
| PASSWORD_REUSE_TIME | No. of days before an old password can be reused |
| PASSWORD_REUSE_MAX | No. of password changes required before an old password can be reused |
| PASSWORD_VERIFY_FUNCTION | PL/SQL function that enforces password complexity rules |
Resource Parameters
| Parameter | Purpose |
|---|---|
| SESSIONS_PER_USER | Max concurrent sessions per user |
| CPU_PER_SESSION | Max CPU time (in hundredths of a sec) per session |
| CPU_PER_CALL | Max CPU time per SQL call (parse/execute/fetch) |
| CONNECT_TIME | Max total connection time (minutes) per session |
| IDLE_TIME | Max continuous idle time (minutes) before session is killed |
| LOGICAL_READS_PER_SESSION | Max data blocks read (memory + disk) per session |
| LOGICAL_READS_PER_CALL | Max data blocks read per SQL call |
| PRIVATE_SGA | Max private space in the Shared Global Area (for shared server) |
| COMPOSITE_LIMIT | Weighted combination of the above resource costs |
Quick DBA Commands — Profile
SQL
-- Enable resource limits (needed for resource params to take effect) ALTER SYSTEM SET RESOURCE_LIMIT = TRUE; -- Create a profile CREATE PROFILE app_profile LIMIT FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LOCK_TIME 1 PASSWORD_LIFE_TIME 90 PASSWORD_GRACE_TIME 7 PASSWORD_REUSE_TIME 365 PASSWORD_REUSE_MAX 5 SESSIONS_PER_USER 3 IDLE_TIME 30 CONNECT_TIME 480; -- Assign a profile to a user ALTER USER raja PROFILE app_profile; CREATE USER raja IDENTIFIED BY raja123 PROFILE app_profile; -- Modify a profile (add/change a limit) ALTER PROFILE app_profile LIMIT PASSWORD_LIFE_TIME 60; -- Set unlimited / default for a specific parameter ALTER PROFILE app_profile LIMIT SESSIONS_PER_USER UNLIMITED; ALTER PROFILE app_profile LIMIT SESSIONS_PER_USER DEFAULT; -- Manually unlock / lock a user account ALTER USER raja ACCOUNT UNLOCK; ALTER USER raja ACCOUNT LOCK; -- Force immediate password expiry ALTER USER raja PASSWORD EXPIRE; -- Drop a profile DROP PROFILE app_profile CASCADE; -- CASCADE needed if users are assigned to it -- Check profiles SELECT * FROM dba_profiles WHERE profile = 'APP_PROFILE'; SELECT username, profile, account_status FROM dba_users;
Easy Memory
USER SECURITY
|
+-- PRIVILEGE --> System (DB-wide) / Object (SELECT, INSERT...)
|
+-- ROLE --> Bundle of privileges --> Granted to Users/Roles
| +-- Predefined: CONNECT, RESOURCE, DBA
|
+-- PROFILE --> Resource Limits + Password Policy --> Assigned to User
+-- Password: LIFE_TIME, LOCK_TIME, REUSE, VERIFY_FUNCTION
+-- Resource: SESSIONS, CPU, IDLE_TIME, CONNECT_TIME