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
