Fwd: Using SET CURRENT_SCHEMA for DDL and DCL
- From: Hemant K Chitale <hemantkchitale@xxxxxxxxx>
- To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 19 Nov 2014 22:58:44 +0800
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>
Date: Sun, Nov 16, 2014 at 11:23 PM
Subject: Using SET CURRENT_SCHEMA for DDL and DCL
To: ORACLE-L <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://hemantscribbles.blogspot.com
--
Hemant K Chitale
http://hemantoracledba.blogspot.com
http://hemantscribbles.blogspot.com
Other related posts:
