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.

Nuno Souto
in wet Sydney, Australia

Other related posts: