Error
Error Code: ORA-30187

Oracle Error ORA-30187: Access Denied

📦 Oracle Database
📋

Description

This error indicates that a client attempted to access the `DBMS_INTERNAL_CLKM.GET_KEY` procedure without proper authorization. It occurs when a user or application, outside of Oracle GoldenGate, attempts to invoke this procedure.
💬

Error Message

ORA-30187: Permission denied for ' string ' to access procedure GET_KEY.
🔍

Known Causes

2 known causes
⚠️
Unauthorized Access
The client does not have the necessary privileges to execute the `DBMS_INTERNAL_CLKM.GET_KEY` procedure. Access to this procedure is restricted.
⚠️
GoldenGate Dependency
`DBMS_INTERNAL_CLKM.GET_KEY` is intended for internal use by Oracle GoldenGate and should not be directly invoked by other applications.
🛠️

Solutions

4 solutions available

1. Grant EXECUTE Privilege to the User easy

Provide the necessary execute privilege on the GET_KEY procedure to the user encountering the error.

1
Connect to the Oracle database as a user with sufficient privileges (e.g., SYS, SYSTEM, or the owner of the GET_KEY procedure).
2
Grant the EXECUTE privilege on the GET_KEY procedure to the user who is receiving the ORA-30187 error. Replace 'TARGET_USER' with the actual username.
GRANT EXECUTE ON GET_KEY TO TARGET_USER;
3
Commit the transaction to make the grant effective.
COMMIT;

2. Grant EXECUTE Privilege on the Schema Owner easy

If GET_KEY is owned by a specific schema, grant execute on the procedure to that schema owner.

1
Identify the owner of the GET_KEY procedure. This is often the schema that owns the package or standalone procedure. Let's assume the owner is 'SCHEMA_OWNER'.
2
Connect to the Oracle database as a user with sufficient privileges (e.g., SYS, SYSTEM, or SCHEMA_OWNER).
3
Grant the EXECUTE privilege on the GET_KEY procedure to the 'SCHEMA_OWNER'. Replace 'TARGET_USER' with the actual username that needs to execute GET_KEY.
GRANT EXECUTE ON SCHEMA_OWNER.GET_KEY TO TARGET_USER;
4
Commit the transaction.
COMMIT;

3. Grant Privileges via a Role medium

Assign execute privilege to a role and then grant that role to the user.

1
Connect to the Oracle database as a user with DBA privileges (e.g., SYS, SYSTEM).
2
Create a new role or identify an existing role that is granted to the user encountering the error. Let's create a new role named 'GET_KEY_ACCESS_ROLE'.
CREATE ROLE GET_KEY_ACCESS_ROLE;
3
Grant the EXECUTE privilege on the GET_KEY procedure to the newly created role.
GRANT EXECUTE ON GET_KEY TO GET_KEY_ACCESS_ROLE;
4
Grant the role to the user who needs access. Replace 'TARGET_USER' with the actual username.
GRANT GET_KEY_ACCESS_ROLE TO TARGET_USER;
5
Commit the changes.
COMMIT;

4. Grant Privileges on the Package Containing the Procedure easy

If GET_KEY is part of a package, grant execute privilege on the entire package.

1
Identify the package that contains the GET_KEY procedure. For example, if GET_KEY is in a package named 'MY_PACKAGE', the owner might be 'SCHEMA_OWNER'.
2
Connect to the Oracle database as a user with sufficient privileges (e.g., SYS, SYSTEM, or SCHEMA_OWNER).
3
Grant the EXECUTE privilege on the package to the user. Replace 'TARGET_USER' and 'SCHEMA_OWNER' with the appropriate names.
GRANT EXECUTE ON SCHEMA_OWNER.MY_PACKAGE TO TARGET_USER;
4
Commit the transaction.
COMMIT;