more trouble with details...
I've said it before here and will say it again: the Oracle implementation of proxy logins is flawed from the start.
I've given at least one demonstration of why. And why using ALTER SESSION SET CURRENT_SCHEMA is a much more manageable and secure alternative.
Despite that, folks insist and persist on using proxies...
Consider Tom Kyte's article on the latest Oracle magazine, with the examples of login A and login B. I won't repeat the article here. Just go read it - or better yet, open the mag next to the screen and look here and there. Yeah, like that. Works for me! ;)
In the article our technologist claims that this is the correct way to get a login to impersonate another. Nothing wrong with the article, mind you!
It might be on paper and manuals, but day to day practicalities say it isn't, hence my disagreement.
For example of just a few glaring problems with the proxy login approach:
We would then be able to login to A - no need for fancy syntax - then we would be able to see all tables from login B that were granted to the role - and ONLY THOSE - as well as having whatever system privileges were granted to the role. And anyone using v$session/v$process to monitor who is logged in, would see a login A - not login B!
I still think if Oracle made CURRENT_SCHEMA one of the context-controlled variables, the whole process would even be simpler: just give the correct schema to a custom context - together with any other variables, roles, etc, then simply make a login use that context as a default, like it is already possible to do.
But I'll settle for a connect trigger for the time being, in the absence of a better solution.
There are likely a lot of other examples of why using a login trigger doing an alter session set current_schema is a much better solution than the machiavelic proxy login a-la Oracle.
But to me the above are more than enough to shoot down the whole concept and use instead my approach.
And I couldn't care less if "triggers are evil" - or whatever the latest crusade is.
Sorry, but like I said before: the campaign to eradicate trigger use is ill-guided and ill-directed!
Yes, without a shadow of a doubt: triggers can be dangerous.
That is NOT a synonym for always dangerous!
On another note:
A coupla years ago I posted a performance screenshot from one of our prod db servers, at a supposedly idle time - Xmas eve, lunch-time. In it was clearly shown the system was under quite a lot of I/O load, although not under any real stress.
That was enough for a lot of comments to start in the intervening time as to how "some DBAs never strive to improve their systems" and "always resist any change". From the usual FUD sources.
Coincidental of course with Oracle trying to flog a bunch of Exa* whatever boxes on us, against my recommendations... ("You're a *bad* dba, Nuno!" was the least that was invoked back then...).
Well, this was taken a few weeks ago during a restore from a backup into our development DW server, as part of a regular monthly refresh from the production backup. Bear in mind it's a dev server, with no particular emphasis on its overall performance.
OK, so in two years we went from 300MB/s overall aggregate in production to 800MB/s in a non-critical, non-production server, with 670MB/s of those being in WRITES - not READS! - and with an overall busy time for disk devices of 21% while the CPU is nearly flat out.
You should see the production box now...
Kevin Closson would - rightly so! - say we're CPU-starved. Of course. But then again this is a restore from a compressed backup, where CPU in user mode is being pushed to expand the data. And it is only run once a month, so I'm not really that worried about what it uses then - for the 3/4hour it lasts. Not worth any battles to fix that!
This was achieved with NO upgrade to hardware whatsoever. The OS was upgraded to Aix 7.1 and Oracle upgraded to 11.2.0.3, that's all. Plus a lot of work on how we split our Aix box into lpars and what is assigned to each in terms of memory, CPU, disk controllers, SAN LUNs, etcetc.
Cost? 3/4 of NULL == NULL. Other than my time of course, which is part of my normal salary - I'm not a contractor here.
We do not strive for improvements, eh? Sure... Any other pearls of idiocy?
Catchyalata, folks!
I've given at least one demonstration of why. And why using ALTER SESSION SET CURRENT_SCHEMA is a much more manageable and secure alternative.
Despite that, folks insist and persist on using proxies...
Consider Tom Kyte's article on the latest Oracle magazine, with the examples of login A and login B. I won't repeat the article here. Just go read it - or better yet, open the mag next to the screen and look here and there. Yeah, like that. Works for me! ;)
In the article our technologist claims that this is the correct way to get a login to impersonate another. Nothing wrong with the article, mind you!
It might be on paper and manuals, but day to day practicalities say it isn't, hence my disagreement.
For example of just a few glaring problems with the proxy login approach:
- If we use the construct exampled by Tom in the article, we must connect to the database as
connect a[b]/a.
Ie, a completely non-standard, non-intuitive connect command, with the login name defined as a[b] - how many tools/apps do you know of that simply won't accept that sort of syntax for a login name? I know a few, try any odbc connection and watch what happens. Worse yet: try to explain to an end user their login name is a[b] from now on? Good luck... - Once we are connected, access to system privileges - and ONLY those! - is controlled by a role that MUST be assigned to login B. Not login A! Ie, we want to restrict what login A can do - or indeed any other login that might also be a proxy - but we define that role and attach it to login B? It should be associated with A - and any other users that may need to be attached to login B. Not to login B.
- The role restriction is incomplete. It works fine for system privileges, but it does NOT stop login A for example from being able to see all rows in ALL tables of login B!
Try it - repeat the example from the Oracle magazine in your system, only this time add only SELECT privilege on a suitable table owned by login B. Then login as A[B].
You'll notice that regardless of what you put in the role, you'll be able to see ALL tables of login B, and SELECT/INSERT/UPDATE/DELETE from them all! - Worse: now go into v$session and v$process and see if you can see login A anywhere? It's simply not there! What you see is an additional login B. So much for making it easy to identify who is who logged on to the database, eh?
ALTER SESSION SET CURRENT_SCHEMA=B,We would then be able to login to A - no need for fancy syntax - then we would be able to see all tables from login B that were granted to the role - and ONLY THOSE - as well as having whatever system privileges were granted to the role. And anyone using v$session/v$process to monitor who is logged in, would see a login A - not login B!
I still think if Oracle made CURRENT_SCHEMA one of the context-controlled variables, the whole process would even be simpler: just give the correct schema to a custom context - together with any other variables, roles, etc, then simply make a login use that context as a default, like it is already possible to do.
But I'll settle for a connect trigger for the time being, in the absence of a better solution.
There are likely a lot of other examples of why using a login trigger doing an alter session set current_schema is a much better solution than the machiavelic proxy login a-la Oracle.
But to me the above are more than enough to shoot down the whole concept and use instead my approach.
And I couldn't care less if "triggers are evil" - or whatever the latest crusade is.
Sorry, but like I said before: the campaign to eradicate trigger use is ill-guided and ill-directed!
Yes, without a shadow of a doubt: triggers can be dangerous.
That is NOT a synonym for always dangerous!
On another note:
A coupla years ago I posted a performance screenshot from one of our prod db servers, at a supposedly idle time - Xmas eve, lunch-time. In it was clearly shown the system was under quite a lot of I/O load, although not under any real stress.
That was enough for a lot of comments to start in the intervening time as to how "some DBAs never strive to improve their systems" and "always resist any change". From the usual FUD sources.
Coincidental of course with Oracle trying to flog a bunch of Exa* whatever boxes on us, against my recommendations... ("You're a *bad* dba, Nuno!" was the least that was invoked back then...).
Well, this was taken a few weeks ago during a restore from a backup into our development DW server, as part of a regular monthly refresh from the production backup. Bear in mind it's a dev server, with no particular emphasis on its overall performance.
OK, so in two years we went from 300MB/s overall aggregate in production to 800MB/s in a non-critical, non-production server, with 670MB/s of those being in WRITES - not READS! - and with an overall busy time for disk devices of 21% while the CPU is nearly flat out.
You should see the production box now...
Kevin Closson would - rightly so! - say we're CPU-starved. Of course. But then again this is a restore from a compressed backup, where CPU in user mode is being pushed to expand the data. And it is only run once a month, so I'm not really that worried about what it uses then - for the 3/4hour it lasts. Not worth any battles to fix that!
This was achieved with NO upgrade to hardware whatsoever. The OS was upgraded to Aix 7.1 and Oracle upgraded to 11.2.0.3, that's all. Plus a lot of work on how we split our Aix box into lpars and what is assigned to each in terms of memory, CPU, disk controllers, SAN LUNs, etcetc.
Cost? 3/4 of NULL == NULL. Other than my time of course, which is part of my normal salary - I'm not a contractor here.
We do not strive for improvements, eh? Sure... Any other pearls of idiocy?
Catchyalata, folks!
posted by Noons at 4/02/2013 05:02:00 pm
5 Comments:
In my opinion, the inteded use of the proxy login is completely different than what Tom does with it in the oramag article. It's an abuse of a feature to achieve something else - which we see a lot of every day :-)
Hmmm, I think there is nothing wrong with what Tom showed in oramag.
I mean: proxy logins can be used, in some specific cases and do have a place. Tom's article is actually quite good IMHO and shows by great and simple examples how to use proxy for a specific problem.
I just don't think that use is the best strategic approach to the problem.
To me the ideal solution continues to be that Oracle for once LISTENS to what ACTUAL users are asking - as opposed to listening to marketeers - and extends the database context functionality to allow this type of session control.
And other more complex types I'm sure I'm missing.
Meanwhile, I'll stick with a login trigger and individual logins assigned to appropriate roles, as it does - simply - all I need with full access control, in a fashion that is totally transparent to the login process end users know and love.
I think the proxy can be a good solution to some problems.
A classic is the typical "I'm the DBA and need to log in to the database as user XYZ without knowing his password, and without changing his password."
That's exactly what the proxy is built to do - bypass the default authentication mechanism for an oracle user. It was never intended to address the problem of "we want to restrict what login A can do", rather to "allow login A to do ANYTHING login B can do". Or let multiple users (X,Y,Z), each identified by their own individual passwords, control / manage schema "B".
The problem isn't proxy authentication is broken, but that Oracle didn't distinguish between users and schemas when they first started.
The situation presented to Tom was unusual. Most production systems don't need to accommodate modifying PL/SQL code except through a formal patch release system. To modify PL/SQL when you are not the owner of the code AND don't have DBA privileges is irregular (though they could add support for GRANT REPLACE ON VIEW|TRIGGER .... and GRANT ALTER ON TABLE privs).
Absolutely, Gary. It solves some problems, of which the one you mentioned is the most obvious.
I tend to just grab the existing encrypted pwd, reset it to something else to do the work, then reset it back to what it was using the "identified by values" option.
But that's because my dbs rarely get users connecting as schema owners, so I can live with a different pwd for a little while.
The problem is indeed the difference between a schema and a login, which was never clear with Oracle.
I tend to separate the two, with a set of logins for application use and of course the one for the schema.
That lets me use the login trigger to set current_schema for the app logins, without the need for an army of synonyms for each login. While still maintaining the full role control.
But I'd much rather specify that in a context than through a login trigger.
Unfortunately, Oracle doesn't seem to be listening - as usual...
This is cool!
Post a Comment
<< Home