📋Steps in this guide1/5

Proxy User Authentication and Connect Through in Oracle Databases

Connect to other database users without knowing their passwords using proxy users.

oracle miscconfigurationintermediate

by OracleDba

53 views

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 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 clause on the destination user. Create some test users. Allow the user to make a proxy connection to the user. We can now connect to the user, using the credentials of the proxy user. The proxy authentication can be revoked using the following command. 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.

Code/Command (click line numbers to comment):

-- 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;

alter user schema_owner grant connect through my_user_1;

SQL> conn my_user_1[schema_owner]/MyPassword1@//localhost:1521/pdb1
SQL> show user
USER is "SCHEMA_OWNER"
SQL>

alter user schema_owner revoke connect through my_user_1;

-- 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 view. The view only reports the destination user in the column, so we can't see which users are direct connections and which users are proxy connections. Joining to the view gives us access to the 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. 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. When connected as a proxy user the function gives you access to the following parameters.

Code/Command (click line numbers to comment):

select * from proxy_users;

PROXY                          CLIENT                         AUT FLAGS
------------------------------ ------------------------------ --- -----------------------------------
MY_USER_1                      SCHEMA_OWNER                   NO  PROXY MAY ACTIVATE ALL CLIENT ROLES

SQL>

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';

select dbusername,
       dbproxy_username
from   unified_audit_trail
where  dbproxy_username is not null;

DBUSERNAME           DBPROXY_USERNAME
-------------------- --------------------
SCHEMA_OWNER         MY_USER_1

SQL>

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.

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. In 11g the column was removed from the view, but you can still retrieve it from the table. 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: - Creating Proxy User Accounts and Authorizing Users to Connect Through Them - Proxy Authentication Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

-- 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';

select password
from   sys.user$
where  name = 'SCOTT';

PASSWORD
------------------------------
F894844C34402B67

1 row selected.

SQL>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!