Re: Using SET CURRENT_SCHEMA for DDL and DCL

From: MacGregor, Ian A. <ian_at_slac.stanford.edu>
Date: Wed, 19 Nov 2014 17:24:32 +0000
Message-ID: <909392AD-A293-48DC-83AC-5C471D973531_at_slac.stanford.edu>

I am extremely uncomfortable with the need for a user to give the DBA his password. You don’t need to “connect as sysdba” to use SET_CURRENT_SCHEMA. It carries no privileges. You do have to have elevated privileges to create objects in another schema. This can be done with simple DBA privileges. The gotcha with SET_CURRENT_SCHEMA is when you have an object in your “dba” schema with the same name as the an object in the schema to which SET_CURRENT_SCHEMA is pointing, actions taken against an object not qualified by owner will take place against the object in the schema to which SET_CURRENT_SCHEMA points.

Another problem with set_current_schema is that you cannot use it to create database links. You can however do this through

Alter user B grant connect through A;
and
connect A[B]

This allows user A to masquerade as user B and create a database link for user B.

Another use of SET_CURRENT_SCHEMA is to avoid synonym creation. For Instance I have a schema C which owns a set of objets and a user C_READ_ONLY which has even given select access to a subset of those object; I can create a login trigger for user C_READ_ONLY which invokes SET_CUURENT_USER=C

No synonyms are then needed for C_READ_ONLY to access the objects to which C has granted privileges.

Ian MacGregor
SLAC NATIONAL Accelerator Laboratory

On Nov 19, 2014, at 7:07 AM, Stephens, Chris <Chris.Stephens_at_adm.com<mailto:Chris.Stephens_at_adm.com>> wrote:

I’m probably in the minority here but I’ve never understood the reluctance of connecting as “sysdba” if you have the privilege to do so. Granted (ha!) you shouldn’t have the privilege if you haven’t earned it but if you are competent, it doesn’t seem at all likely that you will get yourself into more trouble as “sysdba” than connected as a user with “dba” role or something.

I’ve been connecting as “sysdba” on a daily basis for years without issue.

From: oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Hemant K Chitale Sent: Wednesday, November 19, 2014 8:59 AM To: ORACLE-L
Subject: Fwd: Using SET CURRENT_SCHEMA for DDL and DCL

Thank you for the responses.

A few observations :
1. "HEMANT" is an active application account -- one that is used by the application to connect to the schema and query / DML / run batches. So it is not a "no-session" account. The password for this account is (expectedly) maintained by the application and vaulted for use for upgrades / releases / patches. I would expect the DBA to retrieve the password from the vault and use it only for the purpose of executing the DDL scripts for the application / patch.

2. I would be unhappy if a DBA were to frequently use "/ AS SYSDBA". That privilege is to be used only in the rarest cases.

3. The ALTER SESSION SET CURRENT_SCHEMA is not mandated to be included in the script that the development team provides to the DBA. The DBA issues this command at the sqlplus interactive prompt. There are a number of possible dangers if (a) he sets it to the wrong schema or (b) he forgets to issue it.

Call me unhappy. But I am uncomfortable with the agreement to use AS SYSDBA.

Hemant K Chitale
---------- Forwarded message ----------
From: Hemant K Chitale <hemantkchitale_at_gmail.com<mailto:hemantkchitale_at_gmail.com>> Date: Sun, Nov 16, 2014 at 11:23 PM
Subject: Using SET CURRENT_SCHEMA for DDL and DCL To: ORACLE-L <oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org>>

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://hemantoracledba.blogspot.com/> http://hemantscribbles.blogspot.com<http://hemantscribbles.blogspot.com/>

CONFIDENTIALITY NOTICE:
This message is intended for the use of the individual or entity to which it is addressed and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If the reader of this message is not the intended recipient or the employee or agent responsible for delivering this message to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by email reply.

--

http://www.freelists.org/webpage/oracle-l Received on Wed Nov 19 2014 - 18:24:32 CET

This message: [ Message body ]
Next message: Rich Jesse: "Re: Fwd: Using SET CURRENT_SCHEMA for DDL and DCL"
Previous message: Karth Panchan: "Re: How identify failed logon audit records"
In reply to: Stephens, Chris: "RE: Using SET CURRENT_SCHEMA for DDL and DCL"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message