Using SET CURRENT_SCHEMA for DDL and DCL

From: Hemant K Chitale <hemantkchitale_at_gmail.com>
Date: Sun, 16 Nov 2014 23:23:10 +0800
Message-ID: <CAMNBsZuaKRYGuxDHZbn1u07jueuV4xHU2WcrsiKwijg9BHV=uA_at_mail.gmail.com>

I am familiar with ALTER SESSION SET CURRENT_SCHEMA to define the scope for all queries and DML in a current session. Thus schema "OWNER" can grant privileges to account "USER" and account "USER" can login as himself and invoke ALTER SESSION SET CURRENT_SCHEMA to define the scope for object-resolution without using Synonyms.

Have you seen or would you condone this :

CONNECT / AS SYSDBA
ALTER SESSION SET CURRENT_SCHEMA = 'HEMANT' CREATE TABLE XYZ
GRANT SELECT ON XYZ TO 'CHITALE' Such that

a. The DBA does not need the password for 'HEMANT'
b.  The DBA expects the table XYZ to be created in the 'HEMANT' schema
c.  The DBA expects HEMANT to grant SELECT privilege to CHITALE

Frankly, I am uncomfortable with this as it doesn't seem proper. I would rather have the DBA get the password for the 'HEMANT' account from the password vault and login as HEMANT to execute the CREATE and GRANT commands.

What is your opinion ?

--

Hemant K Chitale
http://hemantoracledba.blogspot.com
http://hemantscribbles.blogspot.com

--

http://www.freelists.org/webpage/oracle-l Received on Sun Nov 16 2014 - 16:23:10 CET