Re: Using SET CURRENT_SCHEMA for DDL and DCL

From: Hans Forbrich <fuzzy.graybeard_at_gmail.com>
Date: Mon, 17 Nov 2014 10:15:09 -0700
Message-ID: <546A2D1D.5040001_at_gmail.com>



This has the great advantage of allowing a schema owner that does not need or have 'create session' priv.

Which means that no one can log on to the schema owner and make nonrepudiated changes. And closes other doors that might have been opened by allowing userids that have passwords.

/Hans

On 16/11/2014 8:23 AM, Hemant K Chitale wrote:
>
> 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 Mon Nov 17 2014 - 18:15:09 CET

Original text of this message