[Oracle] Proxy Authentication

You are the DBA of your organisation managing a cluster of Oracle databases. You want to give full access to a schema (say hr) to multiple database users (say bob and alice). How can you do it?

One approach is to give schema name and password to the users. Do you see any problem with that approach? I can think of at least a few:
1. Accountability - multiple users using the same username/password makes tracing actions to database users impossible.
2. Security - shared password is more vulnerable to exposure risks.
3. Revocation - in order to revoke access to the hr schema, you need to change the password of hr user.

Another approach would be to grant system privileges to bob and alice so that they can do anything on the hr schema. However, this does not follow the principle of least privileges and is prone to misuse as users are given more privileges than they require to do the job. Therefore, it is not a good idea to grant system privileges. In fact, as a rule of thumb, system privileges should be granted sparingly in order to improve security.

An improved approach would be to grant all privileges on the hr schema to bob and alice. This requires granting object privileges on each object in hr schema. Managing many object privileges is a nightmare. Further, this does not allow to create new objects in the hr schema. Therefore, this solution also has limitations.

So, what can we do about it? Having identified the above limitations, Oracle actually introduced what is called proxy authentication for such use cases. You simply grant proxy authentication to the users. The following toy script shows how to:  (You can also further restrict access to the schema by specifying what roles can be used during the proxy session. For simplicity, I am omitting such fine tuning here.)


SQL> conn / as sysdba
Connected.

SQL> alter user hr grant connect through bob;
User altered.
SQL> alter user hr grant connect through alice;
User altered.

SQL> 
SQL> conn hr
Password:
SQL> create table emp(name varchar2(100), sal int);
Table created.

SQL> insert into emp values ('tim', 125000);
1 row created.
SQL> insert into emp values ('eve', 150000);
1 row created.

SQL> -- bob connecting as proxy user

SQL> conn bob[hr]

Password:

SQL> select sys_context('userenv', 'current_user') from dual;

SYS_CONTEXT('USERENV','CURRENT_USER')

-------------------------------------

HR

SQL> select sys_context('userenv', 'proxy_user') from dual;

SYS_CONTEXT('USERENV','PROXY_USER')

-----------------------------------

BOB

SQL> -- bob can do anything on hr schema

SQL> select * from hr.emp;

NAME      SAL

------------------

tim       125000

eve       150000