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 accessALTER 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@xxxxxxxxx <mailto:fuzzy.graybeard@xxxxxxxxx>> 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 -- //www.freelists.org/webpage/oracle-l
