Oracle Proxy User

Hi dear friends,

In this writing, we’re going to examine ‘Proxy user authentication’ feature which came with Oracle 9i Release. When do we need Proxy user?

As a DBA, if you want to create or drop Private Database Links via a schema or manage jobs with DBMS_JOB package, you need to know the password of that user. If you don’t, this is when do we need "Proxy user".

Our post has two parts, former is about using Proxy user and latter is about dropping private database link of a user whose password we don’t know.

1. USING PROXY USER

Create a user.

SQL> CREATE USER DATA4TECH IDENTIFIED BY 1234;
User created.

Grant privilege to the user you’ve created to connect as ‘HR user’.

SQL> ALTER USER HR GRANT CONNECT THROUGH DATA4TECH;
User altered.

As you see, since you didn’t grant ‘create session’ privilege to the user you’ve created, the user couldn’t connect.

SQL> conn DATA4TECH/1234;
ERROR:
ORA-01045: user DATA4TECH lacks CREATE SESSION privilege; logon denied
Warning: You are no longer connected to ORACLE.

You can connect to HR user through the user you’ve created. (still didn’t grant ‘create session’ privilege )

SQL> conn DATA4TECH[HR]/1234
Connected.
SQL> show user
USER is "HR"

Grant ‘create session’ privilege to the user you’ve created.

SQL> grant create session to DATA4TECH;
Grant succeeded.
SQL> conn data4tech/1234;
Connected.
SQL> show user
USER is "DATA4TECH"

Query "PROXY_USER" view.

SQL> conn / as sysdba
Connected.
SQL> SELECT * FROM PROXY_USERS;
PROXY        CLIENT    AUT    FLAGS 
----------   -------   ----   -----------------------------------DATA4TECH    HR        NO     PROXY MAY ACTIVATE ALL CLIENT ROLES                

Revoke Proxy user privilege .

SQL> ALTER USER HR REVOKE CONNECT THROUGH DATA4TECH;
User altered.
SQL> SELECT * FROM PROXY_USERS;
no rows selected.

2. DROPPING PRIVATE DATABASE LINK OF A USER WHOSE PASSWORD WE DON’T KNOW

In this example, we’re going to drop private database link of a user (‘HR) whose password we don’t know.

Look at the database links in the database from dba_db_links view.

SQL> select owner,db_link from dba_db_links;
OWNER      DB_LINK
-----      ----------------------------
SYS        SYS_HUB
HR         DENEME

Try to drop the database link of HR user.

SQL> DROP DATABASE LINK DENEME;
DROP DATABASE LINK DENEME
                   *
ERROR at line 1:
ORA-02024: database link not found

Grant privilege to the user you’ve created to connect as ‘HR user’.

SQL> ALTER USER HR GRANT CONNECT THROUGH DATA4TECH;
User altered.

Drop the link through Proxy user.

SQL> conn DATA4TECH[HR]/1234;
Connected.
SQL> show user
USER is "HR"
SQL> drop database link deneme;
Database link dropped.

Check the links again from View.

SQL> select owner,db_link from dba_db_links;
OWNER      DB_LINK
-----      ----------------------------
SYS        SYS_HUB

As you see, we could drop private database link of a user whose password we don’t know.

Hope to see you in new posts,

Take care.