8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Proxy User Authentication and Connect Through in Oracle Databases
Connect to other database users without knowing their passwords using proxy users.
- Why Proxy?
- Proxy User and Connect Through
- Identify Proxy Users
- Grants for Proxy Users
- Password Reset (Pre-9iR2)
Related articles.
Why Proxy?
There are a two main reasons for using proxy users.
- Some DBA tasks, like creating private database links or setting up jobs using the
DBMS_JOB
package, require the administrator to log in as a specific user. This can present a problem if the administrator doesn't know the password. - You have multiple developers working in a shared schema. Letting multiple people share the same credentials represents a security risk. Instead you create a separate proxy user for each individual, allowing them to connect to the schema owner with their own credentials. If a user leaves a project, you simply lock or drop their user, and they no longer have access to the shared schema.
Proxy User and Connect Through
Since Oracle 9i Release 2 it has been possible to create proxy users, allowing us to access a schema via a different username/password combination. This is done by using the GRANT CONNECT THROUGH
clause on the destination user.
Create some test users.
-- Connect to a privileged user. conn sys/SysPassword1@//localhost:1521/pdb1 as sysdba --drop user schema_owner cascade; --drop user my_user_1 cascade; -- Create SCHEMA_OWNER. create user schema_owner identified by SecretPassword1; grant create session to schema_owner; -- Create proxy user. create user my_user_1 identified by MyPassword1; grant create session to my_user_1;
All the MY_USER_1
user to make a proxy connection to the SCHEMA_OWNER
user.
alter user schema_owner grant connect through my_user_1;
We can now connect to the SCHEMA_OWNER
user, using the credentials of the proxy user.
SQL> conn my_user_1[schema_owner]/MyPassword1@//localhost:1521/pdb1 SQL> show user USER is "SCHEMA_OWNER" SQL>
The proxy authentication can be revoked using the following command.
alter user schema_owner revoke connect through my_user_1;
Using this method the administrator can now set up their privileged account to have connect through access to any other user, allowing them to perform tasks as that user, without having to alter the user's password.
There are a lot of other things we can do with proxy users. These may be useful for developers accessing the data via the middle tier, but may be less useful to DBAs.
-- Normal proxy. alter user schema_owner grant connect through my_user_1; -- Limit privileges to a specific role granted to the destination user. alter user schema_owner grant connect through my_user_1 with role test_role; -- Force authentication for authenticated roles. alter user schema_owner grant connect through my_user_1 with role test_role authentication required; -- Disable a specific role from the destination user. alter user schema_owner grant connect through my_user_1 with role all except test_role; -- Disable all roles from the destination user. alter user schema_owner grant connect through my_user_1 with no roles;
Identify Proxy Users
Proxy users can be identified using the PROXY_USERS
view.
select * from proxy_users; PROXY CLIENT AUT FLAGS ------------------------------ ------------------------------ --- ----------------------------------- MY_USER_1 SCHEMA_OWNER NO PROXY MAY ACTIVATE ALL CLIENT ROLES SQL>
The V$SESSION
view only reports the destination user in the USERNAME
column, so we can't see which users are direct connections and which users are proxy connections. Joining to the V$SESSION_CONNECT_INFO
view gives us access to the AUTHENITCATION_TYPE
column, which contains the value "PROXY" for proxy connections. This way we can query just the sessions that are using proxy authentication. Check out the proxy_sessions.sql script.
select s.sid, s.serial#, s.username, s.osuser, sci.authentication_type from v$session s, v$session_connect_info sci where s.sid = sci.sid and s.serial# = sci.serial# and sci.authentication_type = 'PROXY';
The proxy user details are part of the unified audit trail, so you can see both the client and the proxy user in the audit records.
select dbusername, dbproxy_username from unified_audit_trail where dbproxy_username is not null; DBUSERNAME DBPROXY_USERNAME -------------------- -------------------- SCHEMA_OWNER MY_USER_1 SQL>
When connected as a proxy user the SYS_CONTEXT
function gives you access to the following parameters.
column session_user format a20 column session_schema format a20 column current_schema format a20 column proxy_user format a20 select sys_context('userenv','session_user') as session_user, sys_context('userenv','session_schema') as session_schema, sys_context('userenv','current_schema') as current_schema, sys_context('userenv','proxy_user') as proxy_user from dual; SESSION_USER SESSION_SCHEMA CURRENT_SCHEMA PROXY_USER -------------------- -------------------- -------------------- -------------------- SCHEMA_OWNER SCHEMA_OWNER SCHEMA_OWNER MY_USER_1 SQL>
Grants for Proxy Users
Proxy users often confuse people when they are thinking about grants. Just remember that a proxy user is an alternate way to log into the destination user. Once logged in, you are running as the destination user, so all grants need to be applied to that user, not your user or the proxy user. For example.
Logged In As | Destination User | Apply Grants To |
---|---|---|
MY_USER_1 | MY_USER_1 | MY_USER_1 |
MY_USER_1[SCHEMA_OWNER] | SCHEMA_OWNER | SCHEMA_OWNER |
SCHEMA_OWNER | SCHEMA_OWNER | SCHEMA_OWNER |
Password Reset (Pre-9iR2)
Prior to introduction of proxy users in Oracle 9i Release 2, DBAs would often use the following trick to connect as other users when they didn't know the password. Please don't do this anymore, as proxy users solve this problem.
One workaround for this was to save the users current password hash, change the password, logon and perform the task, then change the password back to the hashed value.
-- Get the current password hash. conn / as sysdba select password from dba_users where username = 'SCOTT'; PASSWORD ------------------------------ F894844C34402B67 1 row selected. SQL> -- Reset the password to a known value. alter user scott identified by DummyPassword1; -- use the known password to connect to the user and perform the task. conn scott/DummyPassword1; -- #### Do the task now. #### -- Reset the password using the hash. conn / as sysdba alter user scott identified by values 'F894844C34402B67';
In 11g the PASSWORD
column was removed from the DBA_USERS
view, but you can still retrieve it from the USER$
table.
select password from sys.user$ where name = 'SCOTT'; PASSWORD ------------------------------ F894844C34402B67 1 row selected. SQL>
The logon_as_user_orig.sql and logon_as_user.sql scripts make it a little easier to retrieve the relevant information to perform these actions.
This method works fine, but it does mean there is a period of time when the password is incorrect, which may affect users and application servers trying to make new connections.
For more information see:
Hope this helps. Regards Tim...