LATEST TOPICS

Proxy Authentication in Oracle: How to act as a different database user

As a Oracle DBA, very often we face situations where we are asked to execute certain scripts as an Application User in the database. This task is quite simple provided we have the Application user’s password handy.

The moment the password is lost (unknown to DBA, Application team), we start scratching our head. If the scripts are simple one, we can append the SCHEMA identifier and get the job done.

However, there are certain PL/SQL blocks that needs to be executed as the Application user only. In those cases, we may change the Application user’s password temporarily and get the job done (by Keeping the old Password’s HASH values) . Once the scripts are executed reset the password back to the original value (Using the password HASH values retained prior to the password change).

However, sometime we are not allowed to change the Application user’s password at all. In that scenario ….. WHAT TO DO !!!! WHY ITS ME!!! (this thoughts comes to our mind).

I was in such a situation, when I learned something new (Probably it is already known to you) and I am sharing my learning here.

Consider, we have a Application User called ‘APP_USER‘  and it has following objects.


SQL:labpa >select owner,object_name,object_type,created from dba_objects where owner='APP_USER';

OWNER           OBJECT_NAME          OBJECT_TYPE             CREATED
--------------- -------------------- ----------------------- ------------------
APP_USER        TEST_TAB             TABLE                   09-SEP-14 19:12:10

 

Now consider that you do not know the password for the APP_USER and you have been asked to execute some scripts as APP_USER.

Here is what you can do to accomplish the task.

Create a new user of your choice in the database to which the APP_USER belongs to

-- create a user of your choice
SQL:labpa >create user proxy_user identified by proxy_user;

User created.

Now, you can direct the APP_USER to connect through (via) the new user being created as follows

 

-- Here you can also mention which roles from the APP_USER to use
-- alter user app_user grant connect through proxy_user with role [role_name];
-- If no role is specified all the roles belonging to APP_USER would be inherited

SQL:labpa >alter user app_user grant connect through proxy_user;

User altered.

 

Once the permission is granted to APP_USER to connect through the new user, you can connect to the APP_USER without having it’s own password as follows

--- Connect as APP_USER through the proxy user with proxy user's password
---
SQL:labpa >conn proxy_user[app_user]/proxy_user
Connected.

 

Validate that you are indeed connected as the APP_USER and not as the new user.

 

-- Validate the connected user
SQL:labpa >show user
USER is "APP_USER"

-- Validate the connected User's Object. These objects belongs to the APP_USER

SQL:labpa >select object_name,object_type,created from user_objects;

OBJECT_NAME          OBJECT_TYPE             CREATED
-------------------- ----------------------- --------------------
TEST_TAB             TABLE                   09-SEP-2014 19:12:10

 

Now, you can create objects in the APP_USER (without even knowing it’s password and without resetting it)

 

--- Connect as APP_USER through the proxy user with proxy user's password
---
SQL:labpa >conn proxy_user[app_user]/proxy_user
Connected.

SQL:labpa >show user
USER is "APP_USER"

-- Check if you can view objects of the APP_USER
--
SQL:labpa >select object_name,object_type,created from user_objects;

OBJECT_NAME          OBJECT_TYPE             CREATED
-------------------- ----------------------- --------------------
TEST_TAB             TABLE                   09-SEP-2014 19:12:10

-- Create a new object
--
SQL:labpa >create table test_tab_from_proxy as select * from all_objects;

Table created.

SQL:labpa >select object_name,object_type,created from user_objects;

OBJECT_NAME          OBJECT_TYPE             CREATED
-------------------- ----------------------- --------------------
TEST_TAB_FROM_PROXY  TABLE                   09-SEP-2014 19:22:41
TEST_TAB             TABLE                   09-SEP-2014 19:12:10

--- Connecting as SYSDBA to check and validate the owner for the created object
---
SQL:labpa >conn / as sysdba
Connected.

SQL:labpa >select owner,object_name,object_type,created from dba_objects where owner='APP_USER';

OWNER           OBJECT_NAME          OBJECT_TYPE             CREATED
--------------- -------------------- ----------------------- --------------------
APP_USER        TEST_TAB             TABLE                   09-SEP-2014 19:12:10
APP_USER        TEST_TAB_FROM_PROXY  TABLE                   09-SEP-2014 19:22:41

 

You can also query the database to get to know which all are the proxy user as follows

 

---
-- Query the proxy_users table to get the details of proxy user
-- PROXY : Name of the proxy user
-- CLIENT : Name of the actual user to which the proxy user is mapped
-- AUTHENTICATION : Authentication for accessing actual user's roles
-- FLAGS: Display privileges that are inherited from the actual user
---

SQL:labpa >select * from proxy_users;

PROXY      CLIENT     AUT FLAGS
---------- ---------- --- -----------------------------------
PROXY_USER APP_USER   NO  PROXY MAY ACTIVATE ALL CLIENT ROLES

 

 

2 Comments
  1. vinay v
  2. Abu Fazal Abbas
%d bloggers like this:
Visit Us On LinkedinVisit Us On TwitterVisit Us On Google PlusCheck Our Feed