How to Grant Read Only Access On Packages, Stored Procedures and Triggers?

Hello Oracle DBA Experts,

Is it possible to grant read-only access to packages, stored procedures, and triggers? If it is possible then please let me know how it will be done and what steps are involved in it.

Thanks in Advance!

Solution

Yes, it is possible to grant read-only access to packages, stored procedures, and triggers in Oracle. However, Oracle does not provide direct privileges to allow users to "view only" PL/SQL objects such as packages, procedures, or triggers.

In order to achieve this, you can grant SELECT privileges on the underlying views in the ALL_ or DBA_ schema views containing these objects' definitions. Please follow the steps below:

1. Grant Access to Object Definitions

You can grant SELECT access to the following dictionary views:

• ALL_OBJECTS: To view object metadata.
• ALL_SOURCE: To view the source code of the objects.
• ALL_TRIGGERS: To view trigger definitions.

GRANT SELECT ON ALL_OBJECTS TO <username>;
GRANT SELECT ON ALL_SOURCE TO <username>;
GRANT SELECT ON ALL_TRIGGERS TO <username>;

2. Grant Access Using a Database Role

Create a role for read-only access and assign it to the user:

CREATE ROLE READ_ONLY_PL_SQL;
GRANT SELECT ON ALL_OBJECTS TO READ_ONLY_PL_SQL;
GRANT SELECT ON ALL_SOURCE TO READ_ONLY_PL_SQL;
GRANT SELECT ON ALL_TRIGGERS TO READ_ONLY_PL_SQL;
GRANT READ_ONLY_PL_SQL TO <username>;

3. Grant Access to Specific Objects

Allow users to extract object definitions using the DBMS_METADATA package:

BEGIN
    GRANT EXECUTE ON DBMS_METADATA TO <username>;
END; 

Users can retrieve object definitions like this:

SELECT DBMS_METADATA.GET_DDL('PACKAGE', '<package_name>', '<schema_name>') FROM DUAL;

4. Grant Access to Triggers

Use the following command to grant access to triggers:

GRANT SELECT ON ALL_TRIGGERS TO <username>; 

5. Restrict Access to Modifications

Ensure users cannot modify objects by revoking specific privileges:

REVOKE CREATE ANY PROCEDURE FROM <username>;
REVOKE ALTER ANY PROCEDURE FROM <username>;
REVOKE DROP ANY PROCEDURE FROM <username>;

Note:

• Access to the complete dictionary can be obtained by granting SELECT ANY DICTIONARY or SELECT_CATALOG_ROLE. Use with caution.
• To protect critical object definitions, use data masking or particular access controls.