Re: Synonyms
- From: Nuno Souto <dbvision@xxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Thu, 02 Dec 2010 21:28:01 +1100
Dunbar, Norman wrote,on my timestamp of 2/12/2010 8:47 PM:
Me too. I love the "alter session set current_schema" ability. And, even
better, in a logon trigger, the user logging on doesn't need alter
session rights to execute the statement. Brilliant!
Yeah, it's kinda handy. Proxy users do something similar, but they do the
equivalent of a "become user" in that the initial login assumes the roles and
auths of the target login. That rules out making the target the app owner
schema: you simply can't have everyone acting as "owner", role or no role.
Of course, one can setup an "access user" with the required rights and then
proxy everyone through it. But that creates other problems in that it is not
easy to track who is running what SQL, when.
I prefer the alter session set current_schema. That way I don't need private
synonyms, the role I give to a login user is still active and all the PL/SQL
executes fine. Love it!
We've now expanded to putting the alter session in a global table, together with
things like the authorized IP address and a few other flags. Login trigger gets
the info from SYS_CONTEXT, uses that to check against the login table, then uses
the alter schema that corresponds to that login in that IP address. Works like
a charm and doesn't incur much overhead.
Of course: I wouldn't use this for a system where users do mostly
"login-select/insert from/to one table-logout".
But that is not the case in my shop: DW users login in the morning and stay
logged in until nightfall - any overhead at login time is completely irrelevant.
--
Cheers
Nuno Souto
in wet Sydney, Australia
dbvision@xxxxxxxxxxxx
--
//www.freelists.org/webpage/oracle-l
Other related posts:
