Re: Using SET CURRENT_SCHEMA for DDL and DCL
Date: Tue, 18 Nov 2014 07:31:49 -0700
Message-ID: <546B5855.4060100_at_gmail.com>
You could do that. As with nearly everything Oracle, there are multiple different ways to accomplish similar things.
It is a set of steps that would need to be done any time the dictionary needs to be modified.
The idea that the schema owner is exactly that, and can not create a session and is therefore not a userid, appeals to me personally.
/Hans
On 18/11/2014 7:00 AM, Jason Witte wrote:
> Couldn't you just allow proxy access to the account?
>
> ALTER USER hemant GRANT CONNECT THROUGH my_name;
>
> Then you would log in with your password to that account. For example
>
> SQL> conn my_name[HEMANT]
>
> This way the password for the HEMANT account remains unknown to you,
> but allows you to log in and create the table in that schema. Then, if
> that makes you uncomfortable, revoke the access
>
> ALTER USER hemant REVOKE CONNECT THROUGH my_name;
>
> Or have I missed something?
>
>
> On Mon, Nov 17, 2014 at 10:15 AM, Hans Forbrich
> <fuzzy.graybeard_at_gmail.com <mailto:fuzzy.graybeard_at_gmail.com>> wrote:
>
> 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
>
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Nov 18 2014 - 15:31:49 CET