Wednesday, April 24, 2013

Proxy User in Oracle

If you find yourself with the requirement to allow a user to create objects in another user's schema, you may want to take a look at Proxy User functionality.


As a test, I created a new user (abtest) and, as expected, was not able to create an object in my regular (aballo) Schema. You can perhaps grant ‘EXECUTE PROCEDURE’ and do the DDL in the statement (which is messy)….

I then tried logging is as “ABTEST” as a Proxy User for “ABALLO” and this DID appear to work. A test is below…  maybe this will work for your requirements? This is also useful when you don't want to give out a particular user's password.


16:43:11 ODST> create user abtest
16:43:31   2  identified by pwd4ab
16:43:44   3  default tablespace users
16:44:35   4  temporary tablespace temp_group1
16:45:11   5  quota 1m on users;

User created.

17:00:48 ODST> grant create session,create table to abtest;

Grant succeeded.

17:00:53 ODST> connect abtest/pwd4ab@odst;
Connected.

17:01:46 ODST> CREATE TABLE ABALLO.T2
17:01:47   2  (
17:01:47   3    N1  NUMBER,
17:01:47   4    N2  NUMBER
17:01:47   5  )
17:01:47   6  TABLESPACE USERS
17:01:47   7  RESULT_CACHE (MODE DEFAULT)
17:01:47   8  PCTUSED    0
17:01:47   9  PCTFREE    1
17:01:47  10  INITRANS   1
17:01:47  11  MAXTRANS   255
17:01:47  12  STORAGE    (
17:01:47  13              PCTINCREASE      0
17:01:47  14              BUFFER_POOL      DEFAULT
17:01:47  15              FLASH_CACHE      DEFAULT
17:01:47  16              CELL_FLASH_CACHE DEFAULT
17:01:47  17             )
17:01:47  18  LOGGING
17:01:47  19  NOCOMPRESS
17:01:47  20  NOCACHE
17:01:47  21  NOPARALLEL
17:01:47  22  MONITORING;
CREATE TABLE ABALLO.T2
*
ERROR at line 1:
ORA-01031: insufficient privileges

17:13:02 ODST> connect ###mgr/##########@odst
Connected.

17:24:39 ODST> alter user aballo grant connect through abtest;

User altered.

17:28:44 ODST> connect abtest[aballo]/pwd4ab@odst;
Connected.

17:29:22 ODST> CREATE TABLE ABALLO.T2
17:29:27   2  (
17:29:27   3    N1  NUMBER,
17:29:27   4    N2  NUMBER
17:29:27   5  )
17:29:27   6  TABLESPACE USERS
17:29:27   7  RESULT_CACHE (MODE DEFAULT)
17:29:27   8  PCTUSED    0
17:29:27   9  PCTFREE    1
17:29:27  10  INITRANS   1
17:29:27  11  MAXTRANS   255
17:29:27  12  STORAGE    (
17:29:27  13              PCTINCREASE      0
17:29:27  14              BUFFER_POOL      DEFAULT
17:29:27  15              FLASH_CACHE      DEFAULT
17:29:27  16              CELL_FLASH_CACHE DEFAULT
17:29:27  17             )
17:29:27  18  LOGGING
17:29:27  19  NOCOMPRESS
17:29:27  20  NOCACHE
17:29:27  21  NOPARALLEL
17:29:27  22  MONITORING;

Table created.

17:51:44 ODST> select user from dual;

USER
------------------------------
ABALLO

17:51:59 ODST> select sys_context('userenv','proxy_user') from dual;

SYS_CONTEXT('USERENV','PROXY_USER')
--------------------------------------------------------------------------
ABTEST

17:52:05 ODST>


Another way to view current Proxy Users logged into the instance:


SELECT *
  FROM v$session JOIN V$SESSION_CONNECT_INFO USING (sid, serial#)
 WHERE authentication_type = 'PROXY'
 and network_service_banner = 'TCP/IP NT Protocol Adapter for Linux: Version 12.1.0.1.0 - Production'

No comments:

Post a Comment