Re: Using SET CURRENT_SCHEMA for DDL and DCL
- From: "MacGregor, Ian A." <ian@xxxxxxxxxxxxxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 19 Nov 2014 17:24:32 +0000
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@xxxxxxx<mailto:Chris.Stephens@xxxxxxx>> 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@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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@xxxxxxxxx<mailto:hemantkchitale@xxxxxxxxx>>
Date: Sun, Nov 16, 2014 at 11:23 PM
Subject: Using SET CURRENT_SCHEMA for DDL and DCL
To: ORACLE-L <oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>>
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/>
--
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.
Other related posts:
