Execute privileges on PUBLIC role in Oracle RDS

Is It Recommended to Revoke EXECUTE from PUBLIC?

Yes, it is a recognized security best practice — recommended by Oracle's own security hardening guides (CIS Oracle Database Benchmark) and compliance frameworks (PCI-DSS, HIPAA, SOX).

The packages in question are categorized by risk:

Risk Level Packages Why High UTL_HTTP, UTL_TCP, UTL_SMTP, UTL_FILE, DBMS_LDAP Enable network access, file I/O, and external communication from within the database Medium DBMS_JOB, DBMS_SCHEDULER, DBMS_SQL Allow code execution, scheduling, and dynamic SQL that can escalate privileges Lower UTL_INADDR DNS resolution — information disclosure risk Potential Risks & Impacts of Revoking

Before revoking, you must assess application dependencies - Important Application Breakage — Many Oracle applications (APEX, Oracle E-Business Suite, custom PL/SQL apps) depend on these packages. Revoking from PUBLIC will break any code that relies on the implicit PUBLIC grant. Stored Procedures with AUTHID CURRENT_USER — Procedures running as the invoker will fail if the invoking user no longer has access.

Dependencies to check:

-- Find objects that depend on these packages

SELECT owner, name, type FROM dba_dependencies WHERE referenced_name IN ('UTL_HTTP','UTL_TCP','UTL_SMTP','UTL_FILE', 'UTL_INADDR','DBMS_JOB','DBMS_SCHEDULER', 'DBMS_SQL','DBMS_LDAP') AND referenced_owner = 'SYS';

Oracle-installed schemas — Some Oracle internal schemas (APEX_, FLOWS_, XDB, MDSYS, CTXSYS) depend on these packages. Revoking from PUBLIC can break Oracle features.

Best Practices for Securing These Packages

1. Principle of Least Privilege — Grant Directly to Required Users/Roles:

For Example: -- Revoke from PUBLIC REVOKE EXECUTE ON UTL_HTTP FROM PUBLIC; REVOKE EXECUTE ON UTL_TCP FROM PUBLIC; REVOKE EXECUTE ON UTL_SMTP FROM PUBLIC; REVOKE EXECUTE ON UTL_FILE FROM PUBLIC; REVOKE EXECUTE ON UTL_INADDR FROM PUBLIC; REVOKE EXECUTE ON DBMS_LDAP FROM PUBLIC;

-- Grant only to users/roles that need it GRANT EXECUTE ON UTL_HTTP TO app_network_role; GRANT EXECUTE ON UTL_FILE TO app_file_role;

PLEASE FIRST TEST IN A TEST ENVIRONMENT SIMILAR TO PROD AND VALIDATE.

Test in non-production — Revoke in a test environment identical to production Identify dependencies — Run the dependency query above Create a custom role — Grant packages to a role, then assign the role to specific users Revoke from PUBLIC — Only after confirming no breakage

Document exceptions — For any packages that must remain PUBLIC, document the business justification

For RDS Oracle specifically:

You cannot directly revoke from PUBLIC (SYS-owned). Use rdsadmin.rdsadmin_util.revoke_sys_object if available, or rely on Network ACLs as the primary control. On self-managed Oracle, the REVOKE commands above work directly.

MOS note 247093.1 has a hefty warning when it comes to revoking privileges from public:

*** WARNING ***

If you revoke any privilege from PUBLIC it becomes your own responsibility to ascertain that all applications keep working, this can often be accomplished by replacing the privileges formerly granted to PUBLIC to individual users or roles. Oracle support can only assist you in accomplishing this task, however Oracle support cannot help you answer the general question of what will happen if you revoke default privileges as this depends greatly on the implementation details of any application running on a specific database.

Note: Please raise a AWS Support case to check more details specific to your database. If your RDS Oracle is supported under BYOL please open a case with Oracle Support.