Users, Roles and Security

Security is a core strength of Oracle Database. Oracle provides a comprehensive security model that includes authentication, authorisation, encryption, auditing, and fine-grained access control. This lesson covers the essential security features every Oracle professional should know.

---

Users and Schemas

In Oracle, a user and a schema are closely related:

• A user is an account that can connect to the database
• A schema is the collection of objects (tables, views, etc.) owned by that user
• When you create a user, a schema of the same name is automatically created

-- Create a user
CREATE USER app_user IDENTIFIED BY SecurePass123
  DEFAULT TABLESPACE users
  TEMPORARY TABLESPACE temp
  QUOTA 500M ON users;

-- Change a password
ALTER USER app_user IDENTIFIED BY NewSecurePass456;

-- Lock/unlock an account
ALTER USER app_user ACCOUNT LOCK;
ALTER USER app_user ACCOUNT UNLOCK;

-- Drop a user and all their objects
DROP USER app_user CASCADE;

---

Authentication Methods

Method	Description
Password	Traditional username/password authentication
External	OS-level authentication (identified externally)
Global	LDAP / Active Directory authentication
Proxy	One user connects on behalf of another
Certificate-based	TLS/SSL client certificates

Password Profiles

Control password policies:

CREATE PROFILE strict_profile LIMIT
  PASSWORD_LIFE_TIME 90          -- expire after 90 days
  PASSWORD_GRACE_TIME 7          -- 7-day grace period
  FAILED_LOGIN_ATTEMPTS 5        -- lock after 5 failures
  PASSWORD_LOCK_TIME 1/24        -- lock for 1 hour
  PASSWORD_REUSE_MAX 10          -- cannot reuse last 10 passwords
  PASSWORD_VERIFY_FUNCTION ora12c_verify_function;

ALTER USER app_user PROFILE strict_profile;

---

Privileges

System Privileges

Allow users to perform administrative actions:

-- Grant system privileges
GRANT CREATE SESSION TO app_user;        -- ability to connect
GRANT CREATE TABLE TO app_user;          -- ability to create tables
GRANT CREATE VIEW TO app_user;           -- ability to create views
GRANT CREATE PROCEDURE TO app_user;      -- ability to create procedures
GRANT UNLIMITED TABLESPACE TO app_user;  -- no quota limits

-- Revoke
REVOKE CREATE TABLE FROM app_user;

Object Privileges

Allow access to specific objects:

-- Grant access to a specific table
GRANT SELECT, INSERT, UPDATE ON hr.employees TO app_user;

-- Grant with the ability to pass the privilege to others
GRANT SELECT ON hr.employees TO app_user WITH GRANT OPTION;

-- Grant execute on a procedure
GRANT EXECUTE ON hr.raise_salary TO app_user;

-- Revoke
REVOKE INSERT, UPDATE ON hr.employees FROM app_user;

---

Roles

Roles simplify privilege management by grouping privileges together:

-- Create a role
CREATE ROLE app_readonly;
CREATE ROLE app_readwrite;

-- Grant privileges to roles
GRANT SELECT ON hr.employees TO app_readonly;
GRANT SELECT ON hr.departments TO app_readonly;

GRANT app_readonly TO app_readwrite;
GRANT INSERT, UPDATE, DELETE ON hr.employees TO app_readwrite;

-- Assign roles to users
GRANT app_readonly TO report_user;
GRANT app_readwrite TO app_user;

Pre-defined Roles

Role	Purpose
CONNECT	Basic connection privilege (CREATE SESSION)
RESOURCE	Create tables, sequences, procedures, triggers
DBA	Full administrative privileges (use with caution)
SELECT_CATALOG_ROLE	Read access to data dictionary views
EXECUTE_CATALOG_ROLE	Execute privileges on data dictionary packages

Tip: Avoid granting DBA to application users. Create custom roles with the minimum required privileges.

---

Principle of Least Privilege

Always grant the minimum privileges necessary:

-- Bad: overly broad
GRANT DBA TO app_user;

-- Good: specific privileges
GRANT CREATE SESSION TO app_user;
GRANT SELECT, INSERT ON orders TO app_user;
GRANT EXECUTE ON process_order TO app_user;

---

Views for Security