If you have required privileges, it’s possible to connect as a user account without knowing or changing his password. This is called proxy connection. To authorize a user account to connect using a proxy account, use the GRANT CONNECT THROUGH clause of the ALTER USER statement.
Consider, we have a Application User called ‘APPUSR‘ and it has following objects.
SQL> select owner,object_name,object_type,created from dba_objects where owner='APPUSR'; OWNER OBJECT_NAME OBJECT_TYPE CREATED --------------- -------------------- ----------------------- ------------------ APPUSR TEST_DEMO TABLE 19-SEP-16 15:02:10
Now consider that you do not know the password for the APPUSR and you have been asked to execute some scripts as APPUSR.
Here is what you can do to accomplish the task.
Create a new user of your choice in the database to which the APPUSR belongs to
-- create a user of your choice SQL>create user proxy_user identified by proxy_user; User created.
Now, you can direct the APPUSR to connect through (via) the new user being created as follows
— Here you can also mention which roles from the APPUSR to use
— alter user APPUSR grant connect through proxy_user with role [role_name];
— If no role is specified all the roles belonging to APPUSR would be inherited
SQL>alter user APPUSR grant connect through proxy_user; User altered.
Once the permission is granted to APPUSR to connect through the new user, you can connect to the APPUSR without having it’s own password as follows
— Connect as APPUSR through the proxy user with proxy user’s password
SQL>conn proxy_user[APPUSR]/proxy_user Connected.
Validate that you are indeed connected as the APPUSR and not as the new user.
— Validate the connected user
SQL>show user USER is "APPUSR"
— Validate the connected User’s Object. These objects belongs to the APPUSR
SQL>select object_name,object_type,created from user_objects; OBJECT_NAME OBJECT_TYPE CREATED -------------------- ----------------------- -------------------- TEST_DEMO TABLE 19-SEP-2016 15:02:10
Now, you can create objects in the APPUSR (without even knowing it’s password and without resetting it)
— Connect as APPUSR through the proxy user with proxy user’s password
SQL>conn proxy_user[APPUSR]/proxy_user Connected. SQL>show user USER is "APPUSR"
— Check if you can view objects of the APPUSR
SQL>select object_name,object_type,created from user_objects; OBJECT_NAME OBJECT_TYPE CREATED -------------------- ----------------------- -------------------- TEST_DEMO TABLE 19-SEP-2016 15:02:10
— Create a new object
SQL>create table TEST_DEMO_from_proxy as select * from all_objects; Table created. SQL>select object_name,object_type,created from user_objects; OBJECT_NAME OBJECT_TYPE CREATED -------------------- ----------------------- -------------------- TEST_DEMO_FROM_PROXY TABLE 19-SEP-2016 15:31:41 TEST_DEMO TABLE 19-SEP-2016 15:02:10
— Connecting as SYSDBA to check and validate the owner for the created object
SQL>conn / as sysdba Connected. SQL>select owner,object_name,object_type,created from dba_objects where owner='APPUSR'; OWNER OBJECT_NAME OBJECT_TYPE CREATED --------------- -------------------- ----------------------- -------------------- APPUSR TEST_DEMO TABLE 19-SEP-2016 15:02:10 APPUSR TEST_DEMO_FROM_PROXY TABLE 19-SEP-2016 15:31: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>select * from proxy_users; PROXY CLIENT AUT FLAGS ---------- ---------- --- ----------------------------------- PROXY_USER APPUSR NO PROXY MAY ACTIVATE ALL CLIENT ROLES
To revoke the proxy authentication
Alter user APPUSR revoke connect through proxy_user;
