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.
PrivilegeRole
A single right to perform an action or access an objectA named group of privileges (and/or other roles) bundled together
Granted directly to a userGranted to a user, who then "inherits" every privilege in the role
Hard to manage for many usersEasy 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
ParameterPurpose
FAILED_LOGIN_ATTEMPTSNo. of failed login attempts before the account is locked
PASSWORD_LOCK_TIMENo. of days the account stays locked after exceeding failed attempts
PASSWORD_LIFE_TIMENo. of days a password is valid before it must be changed
PASSWORD_GRACE_TIMEGrace period (days) to change password after expiry, before locking
PASSWORD_REUSE_TIMENo. of days before an old password can be reused
PASSWORD_REUSE_MAXNo. of password changes required before an old password can be reused
PASSWORD_VERIFY_FUNCTIONPL/SQL function that enforces password complexity rules
Resource Parameters
ParameterPurpose
SESSIONS_PER_USERMax concurrent sessions per user
CPU_PER_SESSIONMax CPU time (in hundredths of a sec) per session
CPU_PER_CALLMax CPU time per SQL call (parse/execute/fetch)
CONNECT_TIMEMax total connection time (minutes) per session
IDLE_TIMEMax continuous idle time (minutes) before session is killed
LOGICAL_READS_PER_SESSIONMax data blocks read (memory + disk) per session
LOGICAL_READS_PER_CALLMax data blocks read per SQL call
PRIVATE_SGAMax private space in the Shared Global Area (for shared server)
COMPOSITE_LIMITWeighted 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