📋

Description

The ORA-28156 error indicates that a proxy user is attempting to set a role for a client user without proper authorization. This typically occurs when the necessary `CONNECT THROUGH` privileges haven't been granted.

💬

Error Message

ORA-28156: Proxy user ' string ' not authorized to set role ' string ' for client ' string '

🔍

Known Causes

3 known causes

⚠️

Missing CONNECT THROUGH

The proxy user lacks the `CONNECT THROUGH` privilege for the client user, preventing role assumption.

⚠️

Incorrect Role Grant

The necessary role hasn't been granted to the client user through the proxy user.

⚠️

Privilege Revocation

A previously granted `CONNECT THROUGH` privilege has been revoked from the proxy user.

🛠️

Solutions

3 solutions available

1. Grant Proxy Role to Proxy User easy

Explicitly grant the proxy role to the user acting as the proxy.

1

Connect to the Oracle database as a user with sufficient privileges (e.g., SYS, SYSTEM, or a DBA user).

2

Execute the following SQL statement, replacing 'PROXY_USER', 'ROLE_TO_BE_PROXIED', and 'CLIENT_USER' with the actual names from the ORA-28156 error message. Note: The 'ROLE_TO_BE_PROXIED' is the role the proxy user is trying to set for the client. If the error message refers to a role not explicitly set, it might be a default role granted to the client user.

GRANT "ROLE_TO_BE_PROXIED" TO PROXY_USER WITH ADMIN OPTION;
-- If the role is not directly granted but is a default role:
-- ALTER USER CLIENT_USER DEFAULT ROLE "ROLE_TO_BE_PROXIED";
-- If the proxy user needs to set a role for themselves which is then inherited by the client:
-- GRANT "ROLE_TO_BE_PROXIED" TO PROXY_USER;
-- Then ensure PROXY_USER can proxy for CLIENT_USER:
-- ALTER USER CLIENT_USER GRANT PROXY = PROXY_USER;
-- This last step is usually for direct proxy authentication, but worth checking in complex scenarios.

3

Verify the grant. You can query the `DBA_ROLE_PRIVS` view.

SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE = 'PROXY_USER' AND GRANTED_ROLE = 'ROLE_TO_BE_PROXIED';
SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE = 'CLIENT_USER' AND GRANTED_ROLE = 'ROLE_TO_BE_PROXIED';

2. Configure Proxy Authentication for the Client User medium

Enable the client user to be proxied by the specified proxy user.

1

Connect to the Oracle database as a user with `ALTER USER` privilege (e.g., SYS, SYSTEM, or a DBA user).

2

Execute the `ALTER USER` statement to grant proxy rights. Replace 'CLIENT_USER' with the user that is being connected to, and 'PROXY_USER' with the user that is performing the proxy connection.

ALTER USER CLIENT_USER GRANT PROXY = PROXY_USER;
-- To allow any user to proxy for CLIENT_USER (use with caution):
-- ALTER USER CLIENT_USER GRANT PROXY = ANY;
-- To revoke proxy rights:
-- ALTER USER CLIENT_USER REVOKE PROXY = PROXY_USER;

3

Ensure the 'PROXY_USER' has the necessary role(s) granted to it that it intends to set for the 'CLIENT_USER'. If the 'PROXY_USER' itself doesn't have the role, it cannot grant it.

GRANT "ROLE_TO_BE_PROXIED" TO PROXY_USER;

3. Review and Correct Role Assignments for Proxy User medium

Ensure the proxy user has the role it's attempting to set for the client.

1

Identify the role that the 'PROXY_USER' is trying to set for the 'CLIENT_USER' from the ORA-28156 error message.

2

Connect to the database as a user with sufficient privileges (e.g., SYS, SYSTEM, or a DBA user).

3

Grant the identified role to the 'PROXY_USER'. If the 'PROXY_USER' itself does not possess the role, it cannot set it for another user.

GRANT "ROLE_TO_BE_PROXIED" TO PROXY_USER;
-- If the role needs to be set with admin option for the proxy user:
-- GRANT "ROLE_TO_BE_PROXIED" TO PROXY_USER WITH ADMIN OPTION;

4

Confirm the role is granted to the 'PROXY_USER' by querying `DBA_ROLE_PRIVS`.

SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE = 'PROXY_USER' AND GRANTED_ROLE = 'ROLE_TO_BE_PROXIED';

Oracle ORA-28156: Proxy Role Unauthorized