Re: Are there features on other RDBMS's that it would be good for Oracle to have?

Part of the problem is I don't want to have to code thousands of private synonyms in my multiple use database, one for each table/login pair. And public synonyms are really not a good option, for a great many reasons including those to do with table naming standards. I do also recall Steve Adams showing many moons ago they are not really good for parsing performance.

The ideal is to allow a login to implicitly do a "alter session set current_schema=[schema owner]" while still being under whatever role has been assigned that login.

That way the application does not have to be coded with <owner>.<table> syntax all over the place: owner will be implicit and whatever it is installed as - including multiple copies in the same instance under separate owners - while access control is defined uniquely by roles.

The problem is that in this day and age of third party "hidden" code I can't go around my externaly supplied apps and modify their code to assign roles and modify login processes as I see fit.

All I get to define is a login id - that is most likely shared by multiple pooled connections. And in some cases it's multiple login ids from multiple downstream applications that access my main app. For example: the case of a DW that is accessed by various maker's BI tools. Each will have a subset of access rights, dependent on what they can be allowed to do.

Each initial login id has to be under control of a role that says: "you can only see this" - granted by the owner of the schema involved.

And it has to do the equivalent of the alter session above, so it can see the required schema without requiring coding everything with <owner>.<table>.

Proxy logins don't let me do that: the target automatically assumes any roles assigned to the target login. Which in most cases is the schema owner. Not exactly what I wanted: I don't want everyone able to drop a table!

To change that - as you explained - I have to modify the app code to change roles after the login. That is seldom, if ever possible. Unless apps are coded in-house - a rarity, nowadays.

Hence why I use a login trigger: to set a current_schema. The native role of the login - assigned at creation time - defines of all the possible objects of current_schema, which are visible and how.

Ideally I'd like a simple process, eg:

create initial_user identified by Wh4t3v3r profile site_profile
context app_context;
grant read_only_role to initial_user;

where app_context defines current_schema for that context and any other session parameters that might be needed, and that's about it. No need for pesky login triggers and/or synonyms unless we have a cross-schema app - a special case which will of course require dedicated "access" logins.

Even better: allow me to assign not only a current_schema but also grant a role to a given context. Then all I have to do is assign the context to any given login id at create login time and bingo: all is done.

Need more/different access rights? Create another role, grant it to a new context, give that context the required current_schema and use the new context for any logins requiring such. Nice and simple, no need to access/modify native app code, no need to subvert any login process with triggers.

-- 
Cheers
Nuno Souto
in sunny Sydney, Australia
dbvision_at_iinet.net.au


--
http://www.freelists.org/webpage/oracle-l