ORA-01031 Insufficient Privileges - Permission and Access Control

ORA-01031: Insufficient Privileges

Error Overview

Error Text: ORA-01031: insufficient privileges

This error occurs when a user attempts to perform an operation for which they lack the necessary privileges. It’s a security-related error that can occur with database operations, administrative commands, or object access attempts. Understanding Oracle’s privilege system is crucial for resolving this error.

Understanding Oracle Privileges

Privilege Types

Oracle Privilege System
├── System Privileges - Database-wide permissions
│   ├── CREATE TABLE, CREATE SESSION, etc.
│   ├── Administrative (SYSDBA, SYSOPER)
│   └── Object management privileges
├── Object Privileges - Specific object permissions
│   ├── SELECT, INSERT, UPDATE, DELETE
│   ├── EXECUTE (procedures, functions)
│   └── ALTER, INDEX, REFERENCES
└── Role-Based Privileges - Grouped permissions
    ├── Predefined roles (DBA, CONNECT, RESOURCE)
    ├── Custom roles
    └── Password-protected roles

Common Privilege Scenarios

• Missing system privileges - CREATE TABLE, CREATE VIEW, etc.
• Missing object privileges - SELECT, UPDATE on specific tables
• Administrative operations - SYSDBA required operations
• Cross-schema access - Accessing objects in other schemas
• Role activation - Required roles not enabled

Diagnostic Steps

1. Identify Required Operation and Privileges

-- Check current user and session privileges
SELECT
  SYS_CONTEXT('USERENV', 'SESSION_USER') as session_user,
  SYS_CONTEXT('USERENV', 'CURRENT_USER') as current_user,
  SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') as current_schema
FROM dual;

-- Check user's system privileges
SELECT privilege, admin_option
FROM user_sys_privs
ORDER BY privilege;

-- Check user's object privileges
SELECT
  owner,
  table_name,
  privilege,
  grantable
FROM user_tab_privs
ORDER BY owner, table_name, privilege;

-- Check user's role privileges
SELECT
  granted_role,
  admin_option,
  default_role
FROM user_role_privs
ORDER BY granted_role;

-- Check currently enabled roles
SELECT role FROM session_roles ORDER BY role;

2. Analyze Specific Object Access

-- Check privileges on specific object
SELECT
  grantor,
  privilege,
  grantable,
  hierarchy
FROM user_tab_privs
WHERE table_name = UPPER('specific_table_name')
  AND owner = UPPER('schema_name');

-- Check all privileges available on an object (as DBA)
SELECT
  grantee,
  privilege,
  grantable,
  grantor
FROM dba_tab_privs
WHERE table_name = UPPER('specific_table_name')
  AND owner = UPPER('schema_name')
ORDER BY grantee, privilege;

-- Check column-level privileges
SELECT
  owner,
  table_name,
  column_name,
  privilege,
  grantable
FROM user_col_privs
WHERE table_name = UPPER('specific_table_name')
ORDER BY table_name, column_name, privilege;

-- Check privileges through roles
SELECT
  role,
  owner,
  table_name,
  privilege
FROM role_tab_privs
WHERE role IN (SELECT granted_role FROM user_role_privs)
  AND table_name = UPPER('specific_table_name')
ORDER BY role, privilege;

3. Examine Administrative and System Access

-- Check if user has DBA privileges
SELECT
  grantee,
  granted_role,
  admin_option
FROM dba_role_privs
WHERE granted_role = 'DBA'
  AND grantee = UPPER('username')
UNION
SELECT
  grantee,
  privilege as granted_role,
  admin_option
FROM dba_sys_privs
WHERE privilege IN ('SYSDBA', 'SYSOPER')
  AND grantee = UPPER('username');

-- Check powerful system privileges
SELECT
  grantee,
  privilege,
  admin_option
FROM dba_sys_privs
WHERE grantee = UPPER('username')
  AND privilege IN (
    'CREATE ANY TABLE',
    'DROP ANY TABLE',
    'SELECT ANY TABLE',
    'INSERT ANY TABLE',
    'UPDATE ANY TABLE',
    'DELETE ANY TABLE',
    'GRANT ANY PRIVILEGE',
    'GRANT ANY ROLE'
  )
ORDER BY privilege;

-- Check user's profile and resource limits
SELECT
  username,
  profile,
  account_status,
  default_tablespace,
  temporary_tablespace
FROM dba_users
WHERE username = UPPER('username');

-- Check profile resource limits
SELECT
  profile,
  resource_name,
  limit
FROM dba_profiles
WHERE profile = (SELECT profile FROM dba_users WHERE username = UPPER('username'))
  AND resource_type = 'KERNEL'
ORDER BY resource_name;

4. Investigate Recent Privilege Changes

-- Check recent privilege grants/revokes (if auditing enabled)
SELECT
  username,
  obj_name,
  action_name,
  timestamp,
  sql_text
FROM dba_audit_trail
WHERE (action_name LIKE '%GRANT%' OR action_name LIKE '%REVOKE%')
  AND timestamp > SYSDATE - 7  -- Last week
  AND (username = UPPER('target_username') OR obj_name = UPPER('target_username'))
ORDER BY timestamp DESC;

-- Check system privilege audit trail
SELECT
  username,
  privilege,
  success,
  timestamp,
  userhost
FROM dba_priv_audit_opts
WHERE user_name = UPPER('username')
ORDER BY timestamp DESC;

-- Check role grants history
SELECT
  grantee,
  granted_role,
  grantor,
  grantable
FROM dba_role_privs
WHERE grantee = UPPER('username')
   OR granted_role IN (SELECT granted_role FROM dba_role_privs WHERE grantee = UPPER('username'))
ORDER BY granted_role;

Immediate Solutions

Solution 1: Grant Required System Privileges

Common System Privilege Grants

-- Grant basic connection privilege
GRANT CREATE SESSION TO username;

-- Grant common development privileges
GRANT CREATE TABLE TO username;
GRANT CREATE VIEW TO username;
GRANT CREATE SEQUENCE TO username;
GRANT CREATE PROCEDURE TO username;
GRANT CREATE TRIGGER TO username;
GRANT CREATE SYNONYM TO username;

-- Grant tablespace quota
ALTER USER username QUOTA UNLIMITED ON users;
-- Or specific quota
ALTER USER username QUOTA 100M ON users;

-- Grant resource role (includes common privileges)
GRANT RESOURCE TO username;

-- Grant connect role (basic connection privileges)
GRANT CONNECT TO username;

-- For application users needing broad access
GRANT CREATE ANY TABLE TO username;  -- Use carefully
GRANT SELECT ANY TABLE TO username;  -- Use carefully

-- Verify grants took effect
SELECT privilege FROM dba_sys_privs WHERE grantee = UPPER('username');

Administrative Privilege Grants

-- Grant DBA role (very powerful - use cautiously)
GRANT DBA TO username;

-- Grant specific administrative privileges
GRANT CREATE USER TO username;
GRANT ALTER USER TO username;
GRANT DROP USER TO username;
GRANT GRANT ANY PRIVILEGE TO username;
GRANT GRANT ANY ROLE TO username;

-- Grant SYSDBA privilege (requires OS authentication or password file)
GRANT SYSDBA TO username;

-- Grant SYSOPER privilege (less powerful than SYSDBA)
GRANT SYSOPER TO username;

-- Check if user now has required privileges
SELECT * FROM dba_sys_privs WHERE grantee = UPPER('username') ORDER BY privilege;

Solution 2: Grant Object-Specific Privileges

Table and View Privileges

-- Grant basic table privileges
GRANT SELECT ON schema.table_name TO username;
GRANT INSERT ON schema.table_name TO username;
GRANT UPDATE ON schema.table_name TO username;
GRANT DELETE ON schema.table_name TO username;

-- Grant all privileges on table
GRANT ALL ON schema.table_name TO username;

-- Grant with grant option (allows user to grant to others)
GRANT SELECT ON schema.table_name TO username WITH GRANT OPTION;

-- Grant column-specific privileges
GRANT UPDATE (column1, column2) ON schema.table_name TO username;
GRANT REFERENCES (column1) ON schema.table_name TO username;

-- Grant privileges on all tables in schema (use carefully)
BEGIN
  FOR table_rec IN (
    SELECT table_name FROM dba_tables WHERE owner = UPPER('schema_name')
  ) LOOP
    EXECUTE IMMEDIATE 'GRANT SELECT ON ' || UPPER('schema_name') || '.' || table_rec.table_name || ' TO ' || UPPER('username');
  END LOOP;
END;
/

-- Verify object privileges
SELECT * FROM dba_tab_privs
WHERE grantee = UPPER('username')
ORDER BY owner, table_name, privilege;

Procedure and Function Privileges

-- Grant execute privilege on procedures/functions
GRANT EXECUTE ON schema.procedure_name TO username;
GRANT EXECUTE ON schema.function_name TO username;
GRANT EXECUTE ON schema.package_name TO username;

-- Grant execute on all procedures in schema
BEGIN
  FOR proc_rec IN (
    SELECT object_name
    FROM dba_objects
    WHERE owner = UPPER('schema_name')
      AND object_type IN ('PROCEDURE', 'FUNCTION', 'PACKAGE')
  ) LOOP
    EXECUTE IMMEDIATE 'GRANT EXECUTE ON ' || UPPER('schema_name') || '.' || proc_rec.object_name || ' TO ' || UPPER('username');
  END LOOP;
END;
/

-- Check execute privileges
SELECT * FROM dba_tab_privs
WHERE grantee = UPPER('username')
  AND privilege = 'EXECUTE'
ORDER BY owner, table_name;

Solution 3: Use Role-Based Access Control

Create and Grant Custom Roles

-- Create application-specific role
CREATE ROLE app_read_role;
CREATE ROLE app_write_role;
CREATE ROLE app_admin_role;

-- Grant system privileges to roles
GRANT CREATE SESSION TO app_read_role;
GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW TO app_write_role;
GRANT DBA TO app_admin_role;

-- Grant object privileges to roles
GRANT SELECT ON schema.table1 TO app_read_role;
GRANT SELECT ON schema.table2 TO app_read_role;
GRANT SELECT ON schema.view1 TO app_read_role;

GRANT SELECT, INSERT, UPDATE, DELETE ON schema.table1 TO app_write_role;
GRANT SELECT, INSERT, UPDATE, DELETE ON schema.table2 TO app_write_role;
GRANT EXECUTE ON schema.package1 TO app_write_role;

-- Grant roles to users
GRANT app_read_role TO readonly_user;
GRANT app_read_role, app_write_role TO regular_user;
GRANT app_read_role, app_write_role, app_admin_role TO admin_user;

-- Make roles default (automatically enabled at login)
ALTER USER regular_user DEFAULT ROLE app_read_role, app_write_role;

-- Verify role grants
SELECT * FROM dba_role_privs WHERE grantee = UPPER('username');

Manage Role Activation

-- Enable specific roles in current session
SET ROLE app_read_role, app_write_role;

-- Enable all roles
SET ROLE ALL;

-- Disable all roles except defaults
SET ROLE NONE;

-- Enable password-protected role
SET ROLE secure_role IDENTIFIED BY role_password;

-- Check currently active roles
SELECT role FROM session_roles ORDER BY role;

-- Create procedure to manage role activation
CREATE OR REPLACE PROCEDURE enable_work_roles(p_username VARCHAR2) AS
BEGIN
  -- This would be called by application to enable required roles
  EXECUTE IMMEDIATE 'SET ROLE app_read_role, app_write_role';
  DBMS_OUTPUT.PUT_LINE('Work roles enabled for session');
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error enabling roles: ' || SQLERRM);
END;
/

Long-Term Solutions

1. Implement Least Privilege Principle

Design Role Hierarchy

-- Create hierarchical role structure
CREATE ROLE app_base_role;
CREATE ROLE app_user_role;
CREATE ROLE app_manager_role;
CREATE ROLE app_admin_role;

-- Grant basic privileges to base role
GRANT CREATE SESSION TO app_base_role;
GRANT SELECT ON shared_lookup_tables TO app_base_role;

-- Build role hierarchy
GRANT app_base_role TO app_user_role;
GRANT app_user_role TO app_manager_role;
GRANT app_manager_role TO app_admin_role;

-- Add specific privileges to each level
GRANT SELECT, INSERT ON user_data_tables TO app_user_role;
GRANT UPDATE, DELETE ON user_data_tables TO app_manager_role;
GRANT CREATE ANY TABLE, DROP ANY TABLE TO app_admin_role;

-- Document role purposes
COMMENT ON ROLE app_base_role IS 'Basic access for all application users';
COMMENT ON ROLE app_user_role IS 'Standard user operations - read/write own data';
COMMENT ON ROLE app_manager_role IS 'Manager operations - can modify user data';
COMMENT ON ROLE app_admin_role IS 'Administrative operations - full access';

-- Create role assignment procedure
CREATE OR REPLACE PROCEDURE assign_user_role(
  p_username VARCHAR2,
  p_role_level VARCHAR2
) AS
BEGIN
  CASE UPPER(p_role_level)
    WHEN 'USER' THEN
      EXECUTE IMMEDIATE 'GRANT app_user_role TO ' || p_username;
      EXECUTE IMMEDIATE 'ALTER USER ' || p_username || ' DEFAULT ROLE app_user_role';
    WHEN 'MANAGER' THEN
      EXECUTE IMMEDIATE 'GRANT app_manager_role TO ' || p_username;
      EXECUTE IMMEDIATE 'ALTER USER ' || p_username || ' DEFAULT ROLE app_manager_role';
    WHEN 'ADMIN' THEN
      EXECUTE IMMEDIATE 'GRANT app_admin_role TO ' || p_username;
      EXECUTE IMMEDIATE 'ALTER USER ' || p_username || ' DEFAULT ROLE app_admin_role';
    ELSE
      RAISE_APPLICATION_ERROR(-20001, 'Invalid role level: ' || p_role_level);
  END CASE;

  DBMS_OUTPUT.PUT_LINE('Assigned ' || p_role_level || ' role to ' || p_username);
END;
/

2. Privilege Monitoring and Auditing

Implement Privilege Tracking

-- Create privilege audit table
CREATE TABLE privilege_audit (
  audit_id NUMBER PRIMARY KEY,
  username VARCHAR2(30),
  privilege_type VARCHAR2(20), -- SYSTEM, OBJECT, ROLE
  privilege_name VARCHAR2(128),
  object_owner VARCHAR2(30),
  object_name VARCHAR2(128),
  action VARCHAR2(10), -- GRANT, REVOKE
  granted_by VARCHAR2(30),
  grant_time TIMESTAMP DEFAULT SYSTIMESTAMP,
  with_grant_option CHAR(1),
  notes VARCHAR2(500)
);

-- Create sequence for audit table
CREATE SEQUENCE privilege_audit_seq START WITH 1 INCREMENT BY 1;

-- Create trigger to audit privilege changes
CREATE OR REPLACE TRIGGER trg_privilege_audit
AFTER GRANT ON DATABASE
DECLARE
  v_sql_text ORA_NAME_LIST_T;
  v_sql VARCHAR2(4000);
  v_grantee VARCHAR2(30);
  v_privilege VARCHAR2(128);
BEGIN
  -- Get the SQL text
  FOR i IN 1..ora_sql_txt(sql_txt) LOOP
    v_sql := v_sql || ora_sql_txt(i);
  END LOOP;

  -- Parse grant statement to extract details
  -- This is a simplified version - would need more robust parsing
  IF UPPER(v_sql) LIKE '%GRANT%TO%' THEN
    INSERT INTO privilege_audit (
      audit_id, privilege_name, action, granted_by, grant_time, notes
    ) VALUES (
      privilege_audit_seq.NEXTVAL, 'PRIVILEGE_GRANTED', 'GRANT', USER, SYSTIMESTAMP, v_sql
    );
  END IF;

  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    NULL; -- Don't fail the grant operation
END;
/

-- Procedure to review privilege usage
CREATE OR REPLACE PROCEDURE review_privilege_usage AS
  CURSOR user_privs IS
    SELECT
      grantee,
      COUNT(CASE WHEN privilege LIKE '%ANY%' THEN 1 END) as powerful_privs,
      COUNT(*) as total_privs
    FROM dba_sys_privs
    WHERE grantee NOT IN ('SYS', 'SYSTEM', 'PUBLIC')
    GROUP BY grantee
    HAVING COUNT(CASE WHEN privilege LIKE '%ANY%' THEN 1 END) > 0
    ORDER BY powerful_privs DESC;

BEGIN
  DBMS_OUTPUT.PUT_LINE('=== Users with Powerful Privileges ===');

  FOR user_rec IN user_privs LOOP
    DBMS_OUTPUT.PUT_LINE(user_rec.grantee || ': ' ||
                        user_rec.powerful_privs || ' powerful privileges out of ' ||
                        user_rec.total_privs || ' total');

    -- List the powerful privileges
    FOR priv_rec IN (
      SELECT privilege
      FROM dba_sys_privs
      WHERE grantee = user_rec.grantee
        AND privilege LIKE '%ANY%'
    ) LOOP
      DBMS_OUTPUT.PUT_LINE('  - ' || priv_rec.privilege);
    END LOOP;
  END LOOP;
END;
/

3. Application Security Integration

Implement Application-Level Privilege Management

// Java example of privilege-aware database access
public class PrivilegeAwareDataAccess {

    private static final Map<String, Set<String>> REQUIRED_PRIVILEGES = new HashMap<>();

    static {
        // Define required privileges for operations
        REQUIRED_PRIVILEGES.put("SELECT_EMPLOYEES", Set.of("SELECT ON HR.EMPLOYEES"));
        REQUIRED_PRIVILEGES.put("UPDATE_SALARY", Set.of("UPDATE ON HR.EMPLOYEES", "HR_MANAGER_ROLE"));
        REQUIRED_PRIVILEGES.put("CREATE_DEPARTMENT", Set.of("INSERT ON HR.DEPARTMENTS", "HR_ADMIN_ROLE"));
    }

    public void executeOperation(String operation, Connection conn) throws SQLException {
        // Check if user has required privileges before attempting operation
        if (!hasRequiredPrivileges(operation, conn)) {
            throw new InsufficientPrivilegesException(
                "Operation " + operation + " requires privileges: " +
                REQUIRED_PRIVILEGES.get(operation)
            );
        }

        try {
            performOperation(operation, conn);
        } catch (SQLException e) {
            if (e.getErrorCode() == 1031) { // ORA-01031
                handleInsufficientPrivileges(operation, e);
            } else {
                throw e;
            }
        }
    }

    private boolean hasRequiredPrivileges(String operation, Connection conn) throws SQLException {
        Set<String> requiredPrivs = REQUIRED_PRIVILEGES.get(operation);
        if (requiredPrivs == null) {
            return true; // No specific requirements defined
        }

        for (String privilege : requiredPrivs) {
            if (!checkPrivilege(privilege, conn)) {
                return false;
            }
        }
        return true;
    }

    private boolean checkPrivilege(String privilege, Connection conn) throws SQLException {
        if (privilege.contains(" ON ")) {
            // Object privilege check
            String[] parts = privilege.split(" ON ");
            String privType = parts[0];
            String objectName = parts[1];

            String sql = "SELECT COUNT(*) FROM user_tab_privs WHERE privilege = ? AND table_name = ?";
            try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
                pstmt.setString(1, privType);
                pstmt.setString(2, objectName.substring(objectName.indexOf('.') + 1));

                try (ResultSet rs = pstmt.executeQuery()) {
                    rs.next();
                    return rs.getInt(1) > 0;
                }
            }
        } else if (privilege.endsWith("_ROLE")) {
            // Role check
            String sql = "SELECT COUNT(*) FROM session_roles WHERE role = ?";
            try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
                pstmt.setString(1, privilege.replace("_ROLE", ""));

                try (ResultSet rs = pstmt.executeQuery()) {
                    rs.next();
                    return rs.getInt(1) > 0;
                }
            }
        } else {
            // System privilege check
            String sql = "SELECT COUNT(*) FROM user_sys_privs WHERE privilege = ?";
            try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
                pstmt.setString(1, privilege);

                try (ResultSet rs = pstmt.executeQuery()) {
                    rs.next();
                    return rs.getInt(1) > 0;
                }
            }
        }
    }

    private void handleInsufficientPrivileges(String operation, SQLException e) {
        // Log the privilege issue
        logger.warn("Insufficient privileges for operation: " + operation +
                   ". Required: " + REQUIRED_PRIVILEGES.get(operation));

        // Notify administrator
        notifyPrivilegeIssue(operation, e);

        // Throw user-friendly exception
        throw new InsufficientPrivilegesException(
            "You don't have permission to perform this operation. " +
            "Please contact your administrator to request access."
        );
    }
}

Dynamic Privilege Management

-- Create dynamic privilege management system
CREATE TABLE application_privileges (
  app_privilege_id NUMBER PRIMARY KEY,
  app_operation VARCHAR2(100),
  required_system_privs VARCHAR2(1000),
  required_object_privs VARCHAR2(1000),
  required_roles VARCHAR2(1000),
  description VARCHAR2(500)
);

-- Create user privilege mappings
CREATE TABLE user_app_privileges (
  username VARCHAR2(30),
  app_privilege_id NUMBER,
  granted_date TIMESTAMP DEFAULT SYSTIMESTAMP,
  granted_by VARCHAR2(30),
  expiry_date DATE,
  status VARCHAR2(20) DEFAULT 'ACTIVE'
);

-- Function to check application privilege
CREATE OR REPLACE FUNCTION check_app_privilege(
  p_username VARCHAR2,
  p_operation VARCHAR2
) RETURN BOOLEAN AS
  v_has_privilege BOOLEAN := FALSE;
  v_system_privs VARCHAR2(1000);
  v_object_privs VARCHAR2(1000);
  v_roles VARCHAR2(1000);
  v_count NUMBER;
BEGIN
  -- Get required privileges for operation
  SELECT
    required_system_privs,
    required_object_privs,
    required_roles
  INTO v_system_privs, v_object_privs, v_roles
  FROM application_privileges ap, user_app_privileges uap
  WHERE ap.app_privilege_id = uap.app_privilege_id
    AND uap.username = UPPER(p_username)
    AND ap.app_operation = p_operation
    AND uap.status = 'ACTIVE'
    AND (uap.expiry_date IS NULL OR uap.expiry_date > SYSDATE);

  -- Check if user has required system privileges
  IF v_system_privs IS NOT NULL THEN
    FOR priv IN (
      SELECT TRIM(column_value) as privilege
      FROM TABLE(apex_string.split(v_system_privs, ','))
    ) LOOP
      SELECT COUNT(*) INTO v_count
      FROM user_sys_privs
      WHERE privilege = priv.privilege;

      IF v_count = 0 THEN
        RETURN FALSE;
      END IF;
    END LOOP;
  END IF;

  -- Check object privileges and roles similarly...

  RETURN TRUE;

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RETURN FALSE;
  WHEN OTHERS THEN
    RETURN FALSE;
END;
/

4. Automated Privilege Management

Privilege Reconciliation Process

-- Create privilege reconciliation procedure
CREATE OR REPLACE PROCEDURE reconcile_user_privileges AS
  CURSOR user_cursor IS
    SELECT username, profile
    FROM dba_users
    WHERE username NOT IN ('SYS', 'SYSTEM', 'ANONYMOUS', 'APEX_PUBLIC_USER')
      AND account_status = 'OPEN';

  v_expected_privs SYS.odcivarchar2list;
  v_current_privs SYS.odcivarchar2list;
  v_missing_privs SYS.odcivarchar2list;
  v_extra_privs SYS.odcivarchar2list;

BEGIN
  FOR user_rec IN user_cursor LOOP
    -- Get expected privileges based on user profile/role
    SELECT privilege
    BULK COLLECT INTO v_expected_privs
    FROM expected_user_privileges
    WHERE username = user_rec.username
       OR profile = user_rec.profile;

    -- Get current privileges
    SELECT privilege
    BULK COLLECT INTO v_current_privs
    FROM dba_sys_privs
    WHERE grantee = user_rec.username;

    -- Find missing privileges
    SELECT privilege
    BULK COLLECT INTO v_missing_privs
    FROM (
      SELECT column_value as privilege FROM TABLE(v_expected_privs)
      MINUS
      SELECT column_value as privilege FROM TABLE(v_current_privs)
    );

    -- Find extra privileges
    SELECT privilege
    BULK COLLECT INTO v_extra_privs
    FROM (
      SELECT column_value as privilege FROM TABLE(v_current_privs)
      MINUS
      SELECT column_value as privilege FROM TABLE(v_expected_privs)
    );

    -- Report discrepancies
    IF v_missing_privs.COUNT > 0 OR v_extra_privs.COUNT > 0 THEN
      INSERT INTO privilege_discrepancies (
        username, check_date, missing_privileges, extra_privileges
      ) VALUES (
        user_rec.username, SYSDATE,
        apex_string.join(v_missing_privs, ','),
        apex_string.join(v_extra_privs, ',')
      );
    END IF;

  END LOOP;

  COMMIT;
END;
/

Related Errors

• ORA-01017 - Invalid username/password
• ORA-00942 - Table or view does not exist
• ORA-00990 - Missing or invalid privilege
• ORA-01720 - Grant option does not exist

Quick Reference

Emergency Response Steps

1. ✓ Identify the specific operation causing the error
2. ✓ Check current user privileges and roles
3. ✓ Determine required privileges for the operation
4. ✓ Grant necessary system or object privileges
5. ✓ Enable required roles if applicable
6. ✓ Test the operation to verify access

Quick Commands

-- Check current user privileges
SELECT privilege FROM user_sys_privs ORDER BY privilege;
SELECT role FROM session_roles ORDER BY role;

-- Grant common privileges
GRANT CREATE SESSION TO username;
GRANT CREATE TABLE TO username;
GRANT SELECT ON schema.table_name TO username;

-- Grant role
GRANT role_name TO username;
GRANT DBA TO username;  -- Use carefully

-- Enable role in session
SET ROLE role_name;
SET ROLE ALL;

-- Check specific privilege
SELECT COUNT(*) FROM user_sys_privs WHERE privilege = 'CREATE TABLE';
SELECT COUNT(*) FROM user_tab_privs WHERE table_name = 'TABLE_NAME' AND privilege = 'SELECT';

Prevention Guidelines

• Follow least privilege principle - Grant minimum required access
• Use role-based access control - Organize privileges into roles
• Regular privilege reviews - Audit and clean up unnecessary privileges
• Document privilege requirements - Maintain clear access control documentation
• Implement application-level checks - Verify privileges before operations
• Monitor privilege usage - Track actual vs. granted privileges