🔐 Module 13: Security & User Management

CREATE USER username
  IDENTIFIED BY password
  [DEFAULT TABLESPACE tablespace_name]
  [TEMPORARY TABLESPACE temp_tablespace]
  [QUOTA size ON tablespace_name];

-- Example: Create application user
CREATE USER app_user
  IDENTIFIED BY SecurePass123
  DEFAULT TABLESPACE users
  TEMPORARY TABLESPACE temp
  QUOTA 100M ON users;

-- Create user with password expiration
CREATE USER secure_user
  IDENTIFIED BY password
  PASSWORD EXPIRE;

-- Alter user password
ALTER USER username IDENTIFIED BY new_password;

-- Lock/Unlock user account
ALTER USER username ACCOUNT LOCK;
ALTER USER username ACCOUNT UNLOCK;

-- Set password expiration
ALTER USER username PASSWORD EXPIRE;

-- Drop user
DROP USER username [CASCADE];

-- View user information
SELECT username, account_status, lock_date
FROM dba_users;

-- Create a custom role
CREATE ROLE reporting_analyst;

-- Create role with password protection
CREATE ROLE secure_role IDENTIFIED BY role_password;

-- Grant privileges to role
GRANT CREATE SESSION TO reporting_analyst;
GRANT SELECT ON employees TO reporting_analyst;

-- Assign role to user
GRANT reporting_analyst TO app_user;

-- Drop role
DROP ROLE role_name;

-- Grant system privilege
GRANT CREATE TABLE TO app_user;
GRANT UNLIMITED TABLESPACE TO app_user;

-- Grant object privilege
GRANT SELECT, INSERT, UPDATE 
  ON employees 
  TO app_user;

-- Grant with ADMIN OPTION (can grant others)
GRANT CREATE TABLE TO app_user 
  WITH ADMIN OPTION;

-- Revoke privilege
REVOKE SELECT ON employees FROM app_user;

-- View user privileges
SELECT grantee, privilege
FROM dba_sys_privs
WHERE grantee = 'APP_USER';

-- Create VPD policy function
CREATE OR REPLACE FUNCTION emp_security_policy(
  schema_name IN VARCHAR2,
  table_name IN VARCHAR2
) RETURN VARCHAR2 AS
  v_department_id NUMBER;
BEGIN
  SELECT NVL(department_id, 0)
  INTO v_department_id
  FROM user_profile
  WHERE username = USER;
  
  RETURN 'department_id = ' || v_department_id;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RETURN '1=0'; -- No access
END emp_security_policy;
/

-- Add policy to table
BEGIN
  DBMS_RLS.ADD_POLICY(
    object_schema => 'HR',
    object_name => 'EMPLOYEES',
    policy_name => 'emp_dept_policy',
    function_schema => 'HR',
    policy_function => 'emp_security_policy',
    statement_types => 'SELECT,UPDATE,DELETE'
  );
END;
/