Logon to Oracle using proxy authentication
Preamble
Customers often ask me to create Oracle accounts that can act on multiple schemas, so I have to provide them potentially harmful grants, such as ‘drop any table‘.
This is because, otherwise, I should provide prompt grants on objects (eg all allowing ‘jane‘ to read and modify all the tables in the ‘john‘ schema), including those ones created later.
Scenario
I’m going to explain below a case study on the danger of those actions.
SQL> conn / as sysdba
Connected.
SQL> CREATE TABLE sys.dummy (id NUMBER);
Table created.
SQL> CREATE TABLE system.dummy (id NUMBER);
Table created.
SQL> CREATE USER jane IDENTIFIED BY "doe"
2 DEFAULT TABLESPACE users
3 TEMPORARY TABLESPACE temp
4 ACCOUNT UNLOCK;
User created.
SQL> GRANT CREATE SESSION TO jane;
Grant succeeded.
SQL> GRANT SELECT ANY TABLE TO jane;
Grant succeeded.
SQL> GRANT DROP ANY TABLE TO jane;
Grant succeeded.
SQL>This laboratory consists in a table on the ‘sys‘ scheme and one on the ‘system‘ scheme.
Now I logon as ‘jane‘ and I try to delete them.
SQL> conn jane/doe
Connected.
SQL> DROP TABLE sys.dummy;
DROP TABLE sys.dummy
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> SELECT * FROM sys.dummy;
SELECT * FROM sys.dummy
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> SELECT * FROM system.dummy;
no rows selected
SQL> DROP TABLE system.dummy;
Table dropped.
SQL>You can see the user ‘sys‘ is protected, but we can’t say the same for the other accounts, system accounts too.
Fortunately, we can avoid to get in trouble and, at the same time, meet the requests of customers to be able to access other schemes
We can take advantage of a feature introduced by Oracle in its rel. 9i: the proxy authentication.
SQL> conn / as sysdba
Connected.
SQL> REVOKE DROP ANY TABLE FROM jane;
Revoke succeeded.
SQL> CREATE USER john IDENTIFIED BY "doe"
2 DEFAULT TABLESPACE users
3 TEMPORARY TABLESPACE temp
4 ACCOUNT UNLOCK;
User created.
SQL> GRANT CREATE SESSION TO john;
Grant succeeded.
SQL> GRANT RESOURCE TO john;
Grant succeeded.
SQL> ALTER USER john QUOTA UNLIMITED ON users;
User altered.
SQL> CREATE TABLE john.dummy (id NUMBER);
Table created.
SQL> ALTER USER john GRANT CONNECT THROUGH jane;
User altered.
SQL> show parameter audit
NAME TYPE VALUE
------------------------------------ -------------------------------- --------------------------------
audit_file_dest string /u01/app/oracle/admin/orcl/adump
audit_sys_operations boolean TRUE
audit_syslog_level string
audit_trail string DB
unified_audit_sga_queue_size integer 1048576
SQL> AUDIT SESSION BY jane;
Audit succeeded.
SQL>Abbiamo creato lo schema ‘john‘ e abbiamo concesso a ‘jane‘ il permesso di collegarsi tramite proxy authentication, così da operare a tutti gli effetti come fosse ‘john‘.
Abbiamo anche verificato l’audit per l’utente ‘jane‘ (vedremo dopo il suo utilizzo).
You can see that when’jane‘ does a normal login, she can access to john’s table, thanks to the ‘select any table‘ grant, but she can’t drop it, because we revoked the ‘drop any table‘ grant.
SQL> conn jane/doe
Connected.
SQL> SELECT * FROM john.dummy;
no rows selected
SQL> DROP TABLE john.dummy;
DROP TABLE john.dummy
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL>Workaround
Don’t worry, it’s not a ‘behind the eight ball’ situation.
Now we do login by using the string ‘SQL> connect user[schema owner]/password‘ (write the password of the user account, not the password of the schema you want to act into).
SQL> conn jane[john]/doe
Connected.
SQL> show user
USER is "john"
SQL> DROP TABLE dummy;
Table dropped.
SQL>Someone moves me a remark, when I explained this method: “How can I understand who make what?”
In this scenario, he wants to know who drops the table, among ‘john‘ and ‘jane‘.
In the end of this exercice, we verify we can trace this action by querying the system table ‘dba_audit_trail‘.
SQL> conn / as sysdba
Connected.
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'yyyy-mm-dd hh24:mi:ss';
SQL> SET LINES 132 PAGES 60 TRIMS ON
SQL> COL username for a13
SQL> COL proxy_user for a13
SQL> COL obj_name for a13
SQL> COL timestamp for a20
SQL> SELECT a.username,
2 b.username proxy_user,
3 a.obj_name,
4 a.returncode,
5 a.timestamp,
6 a.sessionid,
7 a.proxy_sessionid
8 FROM dba_audit_trail a,
9 dba_audit_trail b
10 WHERE a.action_name = 'DROP TABLE'
11 AND a.proxy_sessionid = b.sessionid
12 AND b.action_name = 'PROXY AUTHENTICATION ONLY'
13 ORDER BY sessionid;
USERNAME PROXY_USER OBJ_NAME RETURNCODE TIMESTAMP SESSIONID PROXY_SESSIONID
------------- ------------- ------------- ---------- -------------------- ---------- ---------------
JOHN JANE DUMMY 0 2018-09-14 14:27:44 295832023 295832022
SQL>We can see that the self-join query allows us to verify that the ‘drop table‘ action was performed by ‘jane‘, connected by proxy authentication method.