Hello. One of the issues we often deal with as DBAs is the number of developers working on the main database schema. One way to track what each of them is doing and to monitor their activities is by using a proxy user. Apart from providing auditing capabilities, this method offers the benefit that if someone leaves the team, there’s no need to change passwords—just locking the user is enough. To clarify, I’ll walk you through an example:
The first step is to create a user who owns the main schema and then create a table, a sequence, and a procedure within that schema. We’ll also grant it the create session privilege:
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB READ WRITE NO
SQL> alter session set container=orclpdb;
Session altered.
SQL> create user owneruser identified by securepass;
User created.
SQL> grant create table to owneruser;
Grant succeeded.
SQL> grant create procedure to owneruser;
Grant succeeded.
SQL> grant unlimited tablespace to owneruser;
Grant succeeded.
SQL> grant create sequence to owneruser;
Grant succeeded.
SQL> grant create session to owneruser;
Grant succeeded.
Now we connect to this user and create a table, a procedure, and a sequence.
[oracle@oracle9-19 ~]$ sqlplus owneruser@orclpdb
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Sep 29 04:42:23 2024
Version 19.23.0.0.0
Copyright (c) 1982, 2023, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.23.0.0.0
SQL> show user;
USER is "OWNERUSER"
SQL> CREATE TABLE my_table (
id NUMBER,
created_date DATE
);
Table created.
SQL> CREATE SEQUENCE my_seq
START WITH 1000
INCREMENT BY 1
NOCACHE
NOCYCLE;
Sequence created.
SQL> CREATE OR REPLACE PROCEDURE insert_into_my_table IS
new_id NUMBER;
BEGIN
new_id := my_seq.NEXTVAL;
INSERT INTO my_table (id, created_date)
VALUES (new_id, SYSDATE);
COMMIT;
END;
/
Procedure created.
To be sure, let’s call the procedure and select data from the table:
SQL> exec insert_into_my_table;
PL/SQL procedure successfully completed.
SQL> select * from my_table;
ID CREATED_D
---------- ---------
1000 29-SEP-24
That was just the introduction. Now, let’s move to the main topic. Suppose we have multiple users who need to work on the owneruser schema simultaneously. We create a user named user1 and grant it the create session privilege and the ability to connect via the owneruser.
SQL> create user user1 identified by passworduser1;
User created.
SQL> grant create session to user1;
Grant succeeded.
SQL> alter user owneruser grant connect through user1;
User altered.
Now, we’ll connect to the database as a proxy user using this account:
[oracle@oracle9-19 ~]$ sqlplus user1[owneruser]@orclpdb
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Sep 29 04:59:51 2024
Version 19.23.0.0.0
Copyright (c) 1982, 2023, Oracle. All rights reserved.
Enter password:
Last Successful login time: Sun Sep 29 2024 04:42:35 -04:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.23.0.0.0
SQL> show user;
USER is "OWNERUSER"
As we can see, although we connected using user1 and its password, the user displayed and the one we have access to is owneruser.
Let’s see the session details:
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;
SESSION_USER SESSION_SCHEMA CURRENT_SCHEMA PROXY_USER
-------------------- -------------------- -------------------- --------------------
OWNERUSER OWNERUSER OWNERUSER USER1
As shown, owneruser is connected, but through user1, which is displayed in the proxy_user field.
Let’s check the permissions:
SQL> exec insert_into_my_table;
PL/SQL procedure successfully completed.
SQL> select * from my_table;
ID CREATED_D
---------- ---------
1000 29-SEP-24
1001 29-SEP-24
Now, let’s move to how we can audit this user. I suggest the following script to activate in the database. Please share your thoughts if you have any suggestions for improvement:
CREATE AUDIT POLICY proxy_user_audit
ACTIONS ALL
WHEN 'SYS_CONTEXT(''USERENV'', ''PROXY_USER'') = ''USER1'''
EVALUATE PER SESSION;
AUDIT POLICY proxy_user_audit;
And its result:
SQL> select OS_USERNAME,DBUSERNAME,DBPROXY_USERNAME,UNIFIED_AUDIT_POLICIES,sql_text
from unified_audit_trail
where EVENT_TIMESTAMP>sysdate-1/48 AND UNIFIED_AUDIT_POLICIES='PROXY_USER_AUDIT';
OS_USERNAME DBUSERNAME DBPROXY_USERNAM UNIFIED_AUDIT_POLICI SQL_TEXT
--------------- --------------- --------------- -------------------- -----------------------------------------------------
oracle OWNERUSER USER1 PROXY_USER_AUDIT
oracle OWNERUSER USER1 PROXY_USER_AUDIT SELECT MY_SEQ.NEXTVAL FROM "SYS"."DUAL"
oracle OWNERUSER USER1 PROXY_USER_AUDIT INSERT INTO MY_TABLE (ID, CREATED_DATE) VALUES (:B1, SYSDATE)
oracle OWNERUSER USER1 PROXY_USER_AUDIT COMMIT
oracle OWNERUSER USER1 PROXY_USER_AUDIT BEGIN insert_into_my_table; END;