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