RE: Using SET CURRENT_SCHEMA for DDL and DCL
Date: Wed, 19 Nov 2014 09:07:24 -0600
Message-ID: <D95BD5AFADBB0F4E9BB6C53F14D3A0500716F43820_at_JRCEXC1V1.research.na.admworld.com>
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] 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://hemantscribbles.blogspot.com
--
Hemant K Chitale
http://hemantoracledba.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 - 16:07:24 CET