Proxy User in Oracle Database

 

Proxy User in Oracle Database

 

A user that is allowed to connect on behalf of other user called proxy user.

 

Types of Proxy Connections

 

You can create proxy connections using any one of the following options:

 

·         USER NAME

·         DISTINGUISHED NAME

·         CERTIFICATE

 

 

In this case, KRISHNA is the user name and dbaocm is the proxy for KRISHNA.

 

 

The password option exists for additional security. Having no authenticated clause implies default authentication, which is using only the user name without the password. The SQL statement for specifying default authentication is:

 

Syntax : ALTER USER KRISHNA GRANT CONNECT THROUGH DBAOCM

 

 

DISTINGUISHED NAME

 

 

This is a global name in lieu of the password of the user being proxied for.  This can be done using Enterprise user security EUS.

 

CREATE USER dbaocm IDENTIFIED GLOBALLY AS 'CN=dbaocm,OU=americas,O=oracle,L=redwoodshores,ST=ca,C=us';

 

The string that follows the identified globally as clause is the distinguished name. It is then necessary to authenticate using this distinguished name. The corresponding SQL statement to specify authentication using distinguished name is:

 

ALTER USER dbaocm GRANT CONNECT THROUGH SCOTT AUTHENTICATED USING DISTINGUISHED NAME;

 

 

CERTIFICATE

 

For more encrypted way of passing the credentials of the user, who is to be proxied, to the database. The certificate contains the distinguished name encoded in it. Generating the certificate is by creating a wallet and then decoding the wallet to get the certificate. The wallet can be created using runutl mkwallet. It is then necessary to authenticate using the generated certificate. The SQL statement for specifying authentication using certificate is:

 

 

ALTER USER dbaocm GRANT CONNECT THROUGH SCOTT AUTHENTICATED USING CERTIFICATE;

 

 

 

Note:

 

The use of certificates for proxy authentication will be desupported in future Oracle Database releases.

 

 

Demonstration with Example

 

We are creating to user KRISHNA (main) and DBAOCM (proxy).  Where proxy user is connection to main schema using his password but accessing all privileges for mail schema (KRISHNA)

 

SQL> create user dbaocm identified by  dbaocm ;

 

User created.

 

SQL>  grant connect, resource to dbaocm ;

 

Grant succeeded.

 

 

SQL> create user krishna identified by krishna ;

 

User created.

 

SQL> grant dba to krishna ;

 

Grant succeeded.

 

 

Now connect to main user and create some object under there

 

SQL> connect krishna/krishna

Connected.

 

SQL> create table abc as select * from user_extents ;

 

Table created.

 

SQL> create table emp as select * from hr.employees ;

 

Table created.

 

SQL> create table dept as select * from hr.departments ;

 

Table created.

 

SQL> select * from tab ;

 

TNAME      TABTYPE        CLUSTERID

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

ABC        TABLE

DEPT       TABLE

EMP        TABLE

 

3 rows selected.

 

 

Now connect as sysdba and setup proxy user priviliges to dbaocm using tyoe of "USER NAME"

 

SQL> conn / as sysdba

Connected.

SQL>  alter user  krishna grant connect through dbaocm ;

 

User altered.

 

 

SQL> col proxy for a10

SQL> col client for a8

SQL> select * from proxy_users ;

 

PROXY      CLIENT   AUT FLAGS

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

DBAOCM     KRISHNA  NO  PROXY MAY ACTIVATE ALL CLIENT ROLES

 

2 rows selected.

 

 

Proxy user setup has completed. Now time to validate the proxy connection.

 

 

[oracle@vm217 scripts]$ sqlplus dbaocm[KRISHNA]/dbaocm

 

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Oct 2 23:33:48 2020

Version 19.3.0.0.0

 

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

 

Last Successful login time: Fri Oct 02 2020 23:29:18 +05:30

 

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

 

SQL> show user

USER is "KRISHNA"

 

Now we can see my current user is KRISHNA, even after connection with dbaocm user password.  Now query and apply DML on KRISHNA schema objects.

 

SQL> col tname for a8

SQL> set lin290 pages 200

SQL> select * from tab ;

 

TNAME   TABTYPE   CLUSTERID

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

ABC     TABLE

DEPT    TABLE

EMP     TABLE

 

SQL> select count(*) from emp ;

 

  COUNT(*)

----------

       107

 

SQL> select count(*) from dept ;

 

  COUNT(*)

----------

        27

 

SQL> insert into emp select * from emp ;

 

107 rows created.

 

SQL> /

 

214 rows created.

 

SQL> /

 

428 rows created.

 

SQL> commit ;

 

Commit complete.

 

SQL> select count(*) from emp ;

 

  COUNT(*)

----------

       856

 

 

Now validate the KRISHNA schema and check the data in table which have inserted rows under this schema in emp tables…

 

 

[oracle@vm217 scripts]$ sqlplus krishna/krishna

 

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Oct 2 23:35:11 2020

Version 19.3.0.0.0

 

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

 

Last Successful login time: Fri Oct 02 2020 23:29:18 +05:30

 

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

 

SQL> select count(*) from emp ;

 

  COUNT(*)

----------

       856

 

 

 

Hope this help…