Logon to Oracle using proxy authentication

proxy user

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.

ottobre 18, 2018

Tag:
  • Pingback: buy cialis cheap
  • Pingback: ivermectin 2mg
  • Pingback: buy cialis
  • Pingback: online viagra
  • Pingback: sildenafil citrate
  • Pingback: pharmacies
  • Pingback: flccc
  • Pingback: Anonimo
  • Pingback: flccc ivermectin
  • Pingback: ivermectin corona
  • Pingback: ivermectin 0.08
  • Pingback: Anonimo
  • Pingback: ivermectin 0.5
  • Pingback: ivermectin ireland
  • Pingback: ivermectin otc
  • Pingback: ivermectin drug
  • Pingback: Anonimo
  • Pingback: ivermectin
  • Pingback: ivermectin price
  • Pingback: ivermectin kaufen
  • Pingback: ignition casino us
  • Pingback: ivermectin india
  • Pingback: cialis best price
  • Pingback: buy viagra qld
  • Pingback: lasix 40 mg price
  • Pingback: cialis walmart
  • Pingback: tadalafil pills
  • Pingback: 2stilled
  • Pingback: 2compared
  • Pingback: cialis best price
  • Pingback: sildenafil tab
  • Pingback: viagra on line
  • Pingback: what is cialis
  • Pingback: cialis cost 20mg
  • Pingback: ivermectin msds
  • Pingback: ivermectin gold
  • Pingback: buy stromectol
  • Pingback: cialis pill
  • Pingback: ivermectin nahdi
  • Pingback: stromectol
  • Pingback: ivermectin 2mg
  • Pingback: madridbet
  • Pingback: meritking
  • Pingback: eurocasino
  • Pingback: meritroyalbet
  • Pingback: eurocasino
  • Pingback: ivermectin history
  • Pingback: madridbet
  • Pingback: coupons for cialis
  • Pingback: ivermectin trials
  • Pingback: hydroxychloroquine
  • Pingback: ivermectine usa
  • Pingback: ivermectin 3mg tab
  • Pingback: ivermectin virus
  • Pingback: ivermectin fda
  • Pingback: stromectol price
  • Pingback: child porn
  • Pingback: mazhor4sezon
  • Pingback: filmfilmfilmes
  • Pingback: gRh9UPV
  • Pingback: buy ivermectin
  • Pingback: 9-05-2022
  • Pingback: kinoteatrzarya.ru
  • Pingback: TopGun2022
  • Pingback: Xvideos
  • Pingback: XVIDEOSCOM Videos
  • Pingback: ivanesva
  • Pingback: meritroyalbet
  • Pingback: cialis
  • Pingback: Netflix
  • Pingback: meritroyalbet
  • Pingback: FILM
  • Pingback: designchita.ru
  • Pingback: YA-krasneyu
  • Pingback: design-human.ru
  • Pingback: designmsu.ru
  • Pingback: vkl-design.ru
  • Pingback: irida-design.ru
  • Pingback: projectio
  • Pingback: psy online
  • Pingback: Gz92uNNH
  • Pingback: do-posle-psihologa
  • Pingback: uels ukrain
  • Pingback: ivermectin 20 mg
  • Pingback: bahis siteleri
  • Pingback: Shkala tonov
  • Pingback: 3NOZC44
  • Pingback: 01211
  • Pingback: hdorg2.ru
  • Pingback: JXNhGmmt
  • Pingback: Psikholog
  • Pingback: netstate.ru
  • Pingback: Link
  • Pingback: stromectol msd
  • Pingback: psy
  • Pingback: bit.ly
  • Pingback: meritking
  • Pingback: cleantalkorg2.ru
  • Pingback: bucha killings
  • Pingback: War in Ukraine
  • Pingback: Ukraine
  • Pingback: site
  • Pingback: stromectol pills
  • Pingback: stats
  • Pingback: Ukraine-war
  • Pingback: movies
  • Pingback: gidonline
  • Pingback: stromectol 5 mg
  • Pingback: web
  • Pingback: film.8filmov.ru
  • Pingback: video
  • Pingback: levitra vs cialis
  • Pingback: filmgoda.ru
  • Pingback: rodnoe-kino-ru
  • Pingback: confeitofilm
  • Pingback: stat.netstate.ru
  • Pingback: sY5am
  • Pingback: Dom drakona
  • Pingback: JGXldbkj
  • Pingback: aOuSjapt
  • Pingback: ìûøëåíèå
  • Pingback: psikholog moskva
  • Pingback: A片
  • Pingback: Dim Drakona 2022
  • Pingback: TwnE4zl6
  • Pingback: psy 3CtwvjS
  • Pingback: lalochesia
  • Pingback: film onlinee
  • Pingback: 3qAIwwN
  • Pingback: video-2
  • Pingback: sezons.store
  • Pingback: psy-news.ru
  • Pingback: 000-1
  • Pingback: 3SoTS32
  • Pingback: 3DGofO7
  • Pingback: rftrip.ru
  • Pingback: dolpsy.ru
  • Pingback: kin0shki.ru
  • Pingback: 3o9cpydyue4s8.ru
  • Pingback: mb588.ru
  • Pingback: newsukraine.ru
  • Pingback: edu-design.ru
  • Pingback: tftl.ru
  • Pingback: brutv
  • Pingback: site 2023
  • Pingback: sitestats01
  • Pingback: 1c789.ru
  • Pingback: cttdu.ru
  • Pingback: 1703
  • Pingback: hdserial2023.ru
  • Pingback: serialhd2023.ru
  • Pingback: matchonline2022.ru
  • Pingback: bit.ly/3OEzOZR
  • Pingback: bit.ly/3gGFqGq
  • Pingback: bit.ly/3ARFdXA
  • Pingback: bit.ly/3ig2UT5
  • Pingback: bit.ly/3GQNK0J
  • Pingback: grandpashabet
  • Pingback: bep5w0Df
  • Lascia un commento