Proxy user as the name suggests provides the capability to login as another account without knowing the original password but can inherit its permission. Though this may sound a bit scary there might be some conditions where this account comes in handy or is used by Oracle by default without us even knowing. For example, ORDS uses proxy users.
Reasons?
Now one can imagine in what cases such account are required? Here could be some possible known reasons
- In order to avoid sharing the a common account password, proxy accounts can be created.
- Cases where main account credentials can’t be shared but user need to login with same permissions.
- Oracle creates them itself like ORDS etc.
How to create Proxy User?
Creating a proxy user is very straightforward, it first requires a main account as normal and then another account which will allow login as a main account as a proxy. Let us see one example code:
-- Create the main account
create user main_user identified by <password>;
grant connect to main_user;
-- Create the proxy account
create user proxy_user identified by <password1>;
grant connect to proxy_user;
-- Grant permission to proxy_user to login as main_user as proxy
alter user main_user grant connect through proxy_user;
-- Making connection with proxy user
sqlplus proxy_user[main_user]/<password1>@<tns_alias>
How to identify proxy users?
Next would be to determine whether a user is a proxy or a normal standard user in an Oracle database. There are various ways we can achieve the same.
-- User view proxy_users
Select * from proxy_users;
-- Combination of v$session and V$session_connection_info
select a.sid,a.serial#,a.username,a.osuer,b.authentication_type
from v$session a,
v$session_connect_info b
where a.sid=b.sid
and a.serial#=b.serial#
and b.authentication_type='PROXY';
-- Unified audit trail
select dbusername,dbproxy_username
from unified_audit_trail
where dbproxy_username is not null;
-- When connected through proxy user use sys_context
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;
Conclusion
In cases where the main account can’t be exposed, proxy users come in handy. It should also be kept in mind various organization doesn’t find proxy users as secure due to third party but in conditions where it has more benefits than challenges this technology comes in handy and is used appropriately.
