revoke public privileges

Introduction: The Importance of Revoking Permissions from PUBLIC

In Oracle databases, the "PUBLIC" role refers to all database users, including those who do not have specific access or privileges granted to them. This role is recognized as a convenient way to manage access, but if not properly controlled, it can create significant security risks. According to recommendations from the Center for Internet Security (CIS), default privileges granted to "PUBLIC" should be revoked, especially when they pertain to sensitive packages and system functions.

One of the major issues with leaving permissions for "PUBLIC" is that it allows any user, whether malicious or benign, to access system functions, potentially causing security problems. For example, certain Oracle packages like UTL_FILE, DBMS_JOB, and DBMS_SCHEDULER enable users to read and write files on the operating system, create scheduled jobs, and even execute SQL commands at the system level.

According to CIS standards, EXECUTE privileges to "PUBLIC" should be avoided for critical packages. Specifically, the following actions should be taken:

• Revoke the 'EXECUTE' privilege from "PUBLIC" for sensitive packages like UTL_FILE, DBMS_SQL, DBMS_RANDOM, and DBMS_JOB.
• Limit access to system-level packages that can perform sensitive operations such as reading/writing files, scheduling jobs, or interacting with operating system resources.

By revoking these privileges, organizations can reduce the risk of exploitation and ensure that only authorized users with specific roles and privileges can execute these sensitive operations. This is an important part of maintaining Oracle database security and reducing the attack surface.

In the following sections, we will explore how these changes can improve the security posture of the database.

Steps to Identify Existing Privileges:

Review EXECUTE Privileges for Packages and System Functions: The first step is to check which packages and system functions have been granted to PUBLIC. We can use Oracle system tables and views for this purpose. The following query can show us which packages and functions have EXECUTE permissions granted to PUBLIC:

 

SELECT PRIVILEGE, TABLE_NAME
FROM DBA_TAB_PRIVS
WHERE GRANTEE = 'PUBLIC'
AND TABLE_NAME IN (
    'UTL_FILE', 'UTL_SMTP', 'UTL_TCP', 'UTL_HTTP', 'DBMS_RANDOM',
    'DBMS_LOB', 'DBMS_SQL', 'DBMS_SYS_SQL', 'DBMS_IJOB', 'DBMS_JOB',
    'DBMS_BACKUP_RESTORE', 'DBMS_OBFUSCATION_TOOLKIT', 'DBMS_LDAP',
    'DBMS_SCHEDULER', 'DBMS_STREAMS_ADM_UTL', 'DBMS_AQADM_SYS',
    'DBMS_STREAMS_RPC', 'DBMS_PRVTAQIM', 'DBMS_ADVISOR', 'DBMS_CRYPTO',
    'DBMS_JAVA', 'DBMS_JAVA_TEST', 'DBMS_XMLGEN', 'DBMS_XMLQUERY',
    'DBMS_FILE_TRANSFER'
);

 

Identifying and Storing INVALID Objects in the Oracle Database:

INVALID objects are those that cannot be executed or function properly due to changes in dependencies or issues during compilation. These objects may include packages, functions, procedures, views, or triggers.

To begin, we need to identify all INVALID objects in the database and store them in a table so we can differentiate them from objects affected by our work:

 

CREATE TABLE sys.pre_invalid_objects AS
SELECT * FROM dba_objects WHERE status = 'INVALID';

 

Explanation of the Command:

• CREATE TABLE sys.pre_invalid_objects: This part of the command creates a new table named pre_invalid_objects in the SYS schema. This table will store the status of INVALID objects.
• SELECT * FROM dba_objects WHERE status = 'INVALID': This section selects all records from the DBA_OBJECTS table where the status is INVALID.

Identifying and Managing PUBLIC Permissions in Oracle:

In this section of the article, we aim to identify all packages that have been granted EXECUTE permissions to PUBLIC using a new table named vahiddb_public_privilege.

Steps for Creating the Table and Storing Privileges:

Creating the vahiddb_public_privilege Table: First, we create a new table called vahiddb_public_privilege to store all packages and functions granted EXECUTE to PUBLIC:

 

CREATE TABLE sys.vahiddb_public_privilege (privilege VARCHAR2(255));

 

This table has one column named privilege, which will store the names of the packages and functions.

Inserting Data into the Table: After creating the table, we insert the names of all packages and functions granted EXECUTE to PUBLIC:

 

INSERT INTO sys.vahiddb_public_privilege (privilege) VALUES ('UTL_FILE');
INSERT INTO sys.vahiddb_public_privilege (privilege) VALUES ('UTL_SMTP');
INSERT INTO sys.vahiddb_public_privilege (privilege) VALUES ('UTL_TCP');
INSERT INTO sys.vahiddb_public_privilege (privilege) VALUES ('UTL_HTTP');
INSERT INTO sys.vahiddb_public_privilege (privilege) VALUES ('DBMS_RANDOM');
INSERT INTO sys.vahiddb_public_privilege (privilege) VALUES ('DBMS_LOB');
INSERT INTO sys.vahiddb_public_privilege (privilege) VALUES ('DBMS_SQL');
INSERT INTO sys.vahiddb_public_privilege (privilege) VALUES ('DBMS_SYS_SQL');
INSERT INTO sys.vahiddb_public_privilege (privilege) VALUES ('DBMS_IJOB');
INSERT INTO sys.vahiddb_public_privilege (privilege) VALUES ('DBMS_JOB');
INSERT INTO sys.vahiddb_public_privilege (privilege) VALUES ('DBMS_BACKUP_RESTORE');
INSERT INTO sys.vahiddb_public_privilege (privilege) VALUES ('DBMS_OBFUSCATION_TOOLKIT');
INSERT INTO sys.vahiddb_public_privilege (privilege) VALUES ('DBMS_LDAP');
INSERT INTO sys.vahiddb_public_privilege (privilege) VALUES ('DBMS_SCHEDULER');
INSERT INTO sys.vahiddb_public_privilege (privilege) VALUES ('DBMS_STREAMS_ADM_UTL');
INSERT INTO sys.vahiddb_public_privilege (privilege) VALUES ('DBMS_AQADM_SYS');
INSERT INTO sys.vahiddb_public_privilege (privilege) VALUES ('DBMS_STREAMS_RPC');
INSERT INTO sys.vahiddb_public_privilege (privilege) VALUES ('DBMS_PRVTAQIM');
INSERT INTO sys.vahiddb_public_privilege (privilege) VALUES ('DBMS_ADVISOR');
INSERT INTO sys.vahiddb_public_privilege (privilege) VALUES ('DBMS_CRYPTO');
INSERT INTO sys.vahiddb_public_privilege (privilege) VALUES ('DBMS_JAVA');
INSERT INTO sys.vahiddb_public_privilege (privilege) VALUES ('DBMS_JAVA_TEST');
INSERT INTO sys.vahiddb_public_privilege (privilege) VALUES ('DBMS_XMLGEN');
INSERT INTO sys.vahiddb_public_privilege (privilege) VALUES ('DBMS_XMLQUERY');
INSERT INTO sys.vahiddb_public_privilege (privilege) VALUES ('DBMS_FILE_TRANSFER');
COMMIT;

 

Purpose of this Operation: This table acts as a centralized resource to identify packages granted EXECUTE to PUBLIC. By populating this table, we can easily identify which packages should be removed from PUBLIC to improve Oracle database security.

Creating a Log Table for Revoking Permissions: Here is the SQL command to create a table called revoke_log that logs information about revoking permissions:

 

CREATE TABLE sys.revoke_log (
    action_time DATE DEFAULT SYSDATE,          
    action_type VARCHAR2(50),                
    object_name VARCHAR2(100),                
    granted_to VARCHAR2(100),                  
    status VARCHAR2(200)                     
);

 

Explanation of Fields:

• action_time: Stores the time of the revocation action. By default, the current time (SYSDATE) is recorded.
• action_type: Specifies the type of operation, in this case, it will be "REVOKE".
• object_name: Stores the name of the database object (e.g., package or function) from which the permission was revoked.
• granted_to: Stores the name of the user or role (here, "PUBLIC") to whom the permission was granted.
• status: Stores the status of the revocation operation, which can include "SUCCESS" or error messages in case of failure.

Purpose of Creating this Table: Creating the revoke_log table ensures that all revocation actions are recorded, providing traceability of security changes. This table can be useful for reviewing the history of security changes, reporting to administrators, and troubleshooting potential issues.

The rest of the document continues with PL/SQL procedures for dynamically revoking EXECUTE permissions from PUBLIC, handling exceptions, and logging actions.

The PL/SQL code provided here is designed to revoke EXECUTE privileges from PUBLIC on various Oracle database packages.

 

BEGIN
  FOR rec IN (SELECT privilege FROM sys.vahiddb_public_privilege) LOOP
    BEGIN
      EXECUTE IMMEDIATE 'REVOKE EXECUTE ON ' || rec.privilege || ' FROM PUBLIC';
      INSERT INTO sys.revoke_log (action_type, object_name, granted_to, status)
      VALUES ('REVOKE', rec.privilege, 'PUBLIC', 'SUCCESS');
    EXCEPTION
      WHEN OTHERS THEN
        INSERT INTO sys.revoke_log (action_type, object_name, granted_to, status)
        VALUES ('REVOKE', rec.privilege, 'PUBLIC', DBMS_UTILITY.FORMAT_ERROR_STACK);
    END;
  END LOOP;
END;

/

 

Creating a Loop:

This loop iterates over all privileges stored in the vahiddb_public_privilege table. Each privilege in this table is examined in turn within the loop.

 

EXECUTE IMMEDIATE 'REVOKE EXECUTE ON ' || rec.privilege || ' FROM PUBLIC';

 

Logging the Successful Operation:

After revoking the privilege, information about the successful operation (package name, time of execution, and success status) is logged in the revoke_log table.

 

INSERT INTO sys.revoke_log (action_type, object_name, granted_to, status) 
VALUES ('REVOKE', rec.privilege, 'PUBLIC', 'SUCCESS');

 

 

Exception Handling:

If an error occurs during the revocation process, it is automatically handled in the EXCEPTION section. The error message is logged in the revoke_log table using DBMS_UTILITY.FORMAT_ERROR_STACK.

 

EXCEPTION WHEN OTHERS THEN 
  INSERT INTO sys.revoke_log (action_type, object_name, granted_to, status) 
  VALUES ('REVOKE', rec.privilege, 'PUBLIC', DBMS_UTILITY.FORMAT_ERROR_STACK);

 

Benefits of this Operation:

• Automatic Documentation: All revocation actions are automatically logged in the revoke_log table, making all security changes accurately and traceably recorded.
• Error Management: With the exception management section, all errors are logged precisely, making it easy to find information to resolve any issues.
• Improved Security: By revoking extra privileges from PUBLIC, unauthorized access to the database is reduced, thus improving system security.

The following SQL query is used to check the status of revocation operations from the revoke_log table. It categorizes the number of revocation actions based on their status:

 

SELECT status , count(*) 
FROM sys.revoke_log 
GROUP BY status;

 

To populate the dba_errors table with errors that occurred after compilation, we execute the following

 

@?/rdbms/admin/utlrp

 

 

Now, let's move to the procedure for granting the necessary privileges.

Creating the grant_privileges_auto Procedure:

 

CREATE OR REPLACE PROCEDURE grant_privileges_auto AS
    CURSOR privilege_cursor IS
        SELECT DISTINCT privilege
        FROM vahiddb_public_privilege;

    CURSOR error_cursor(privilege_name VARCHAR2) IS
        SELECT DISTINCT owner
        FROM dba_errors
        WHERE UPPER(TEXT) LIKE '%' || privilege_name || '%';

    v_privilege_name VARCHAR2(128);

BEGIN
    FOR priv IN privilege_cursor LOOP
        v_privilege_name := priv.privilege;
        
        -- Check for errors related to the privilege
        FOR err IN error_cursor(v_privilege_name) LOOP
            -- If an error is found for this privilege, grant EXECUTE
            BEGIN
                -- If the owner is 'MDSYS' or any other user who needs the privilege, grant EXECUTE
                EXECUTE IMMEDIATE 'GRANT EXECUTE ON ' || v_privilege_name || ' TO ' || err.owner;
                DBMS_OUTPUT.put_line('Granted EXECUTE on ' || v_privilege_name || ' to ' || err.owner);
            EXCEPTION
                WHEN OTHERS THEN
                    DBMS_OUTPUT.put_line('Error granting privilege ' || v_privilege_name || ' to ' || err.owner || ': ' || SQLERRM);
            END;
        END LOOP;
    END LOOP;
END grant_privileges_auto;

 

 

Explanation of the grant_privileges_auto Procedure:

This procedure is designed to grant EXECUTE privileges to various users based on errors in the DBA_ERRORS table. It automatically grants necessary privileges to users who are associated with specified errors in the database.

Steps of the grant_privileges_auto Procedure:

• Defining Cursors: The procedure uses two cursors:

  • privilege_cursor: This cursor retrieves different privileges from the vahiddb_public_privilege table, which contains packages and functions granted EXECUTE to PUBLIC.
  • error_cursor: This cursor checks for errors related to each privilege in the DBA_ERRORS table. If an error is found for a privilege, it returns the owner’s name (typically a user or specific role).

• Iterating Through Privileges and Checking Errors: The procedure first uses privilege_cursor to get all privileges from the vahiddb_public_privilege table. Then, for each privilege, it uses error_cursor to check if an error exists in relation to that privilege in the DBA_ERRORS table. If an error exists, the procedure considers the object's owner.

• Dynamically Granting Privileges: If an error is found, the procedure uses EXECUTE IMMEDIATE to grant EXECUTE privileges to the object's owner. This operation is done dynamically for each error.

• Reporting the Status: After granting each privilege, the procedure uses DBMS_OUTPUT.put_line to report the status of the operation, including the privilege, owner, and result.

• Exception Handling: If any error occurs while granting the privilege, it is captured and logged in the revoke_log table using DBMS_UTILITY.FORMAT_ERROR_STACK, which helps database administrators track and resolve the issues.

 

SET SERVEROUTPUT ON;
exec grant_privileges_auto;

 

At this stage, you can check the number of objects created after the operation by running the following 

 

SELECT * FROM dba_objects WHERE status = 'INVALID'
MINUS
SELECT * FROM pre_invalid_objects;

 

After running the procedure, compile again:

 

@?/rdbms/admin/utlrp

 

Then, re-run the procedure for granting privileges:

 

exec grant_privileges_auto;

 

Compile again:

 

@?/rdbms/admin/utlrp

 

run the grant_privileges_auto if any invalid objects remained and compile it again.

 

At this point, no invalid objects should remain in the system. If any package for your users still faces issues, you can grant all privileges to the user with the following procedure:

 

CREATE OR REPLACE PROCEDURE grant_privileges_to_user(p_username VARCHAR2) AS
    CURSOR privilege_cursor IS
        SELECT privilege
        FROM vahiddb_public_privilege;

    v_privilege_name VARCHAR2(128);

BEGIN
    -- Loop through each privilege in the table
    FOR priv IN privilege_cursor LOOP
        v_privilege_name := priv.privilege;

        -- Grant the privilege to the specified user
        BEGIN
            EXECUTE IMMEDIATE 'GRANT EXECUTE ON ' || v_privilege_name || ' TO ' || p_username;
            DBMS_OUTPUT.put_line('Granted EXECUTE on ' || v_privilege_name || ' to ' || p_username);
        EXCEPTION
            WHEN OTHERS THEN
                DBMS_OUTPUT.put_line('Error granting privilege ' || v_privilege_name || ' to ' || p_username || ': ' || SQLERRM);
        END;
    END LOOP;
END grant_privileges_to_user;

 

This procedure ensures that only the required privileges are granted to users. If you are unsure, you can grant all privileges as needed.