Re: Question About Oracle Users

On Fri, 8 Jun 2001 12:45:52 +0100, "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote:

>The drawback to this is that roles are in the same
>table as users, and finding 20 roles out of 10,000
>users through the data dictionary views results in
>some very expensive and peculiar execution plans.
>
>It comes back to a discussion you were in a while
>back about optimizer_mode and data dictionary
>views. It is my opinion that application code should
>NEVER use data_dictionary views - views are things
>that you design as part of an application.

Yes, most unfortunate. Nevertheless, we must allow for *some* use of the dictionary views, no? I mean, light use. After all one of the most distinguishing features of ORACLE is that the dictionary itself is kept in tables. Very few relational databases do that.

The problem I had in that discussion was with the export/import views. Standard ORACLE, not a user-developed app. Hardly used by anything else. They simply die with FIRST_ROWS. Not nice.

Coming back to this case. Just about every application out there has some form of security built-in. Who can use such and such a screen or function, that kind of thing. Nothing to do with the data itself. I call that (for lack of a better definition) "application-specific security". As opposed to "data-specific security".

It's better to write pplication-specific security into the app itself rather than rely on roles and such to control it. As you well point out, looking up roles in dictionary views is bad karma. Which means the app has to lookup its own tables after a DB logon that just checks the uid/pwd. This means having to duplicate the user id somewhere in a table, why not do the lot then? Much faster that way. Of course, data-specific security still needs to be there but that's easily catered for with roles/general logon.

Another approach I've seen is where users logon with a DB uid/pwd, which basically checks their authentication with the DB, then the application does another logon to a general user and then checks the security levels by looking up application specific tables. That is used by Peoplesoft, for example. And I believe SAP as well. Don't particularly like it: too much overhead, two logons per connection and all it does is check a DB pwd. But if it does the work and if user sessions are long enough, then I guess it isn't a problem.

Probably the best, most efficient solution would be something like OLAP: keep all that external to the database, just go in for the data. Not a bad idea at all. I've never been a fan of use of DB roles to control application security. Data security, yes of course. But not application security. Try to explain this subtle distinction to an "architect", though!... :-D

>
>The views designed for one application (e.g. the
>application for managing the database) are not
>guaranteed to be efficient for use in another
>application (e.g. the end-user application).
>
>--

Agreed. Problem is when the "application for managing the database" dies when it uses its own views. It's not just exp/imp views that get affected by FIRST_ROWS... <Grrr>

Cheers
Nuno Souto
nsouto_at_bigpond.net.au.nospam
http://www.users.bigpond.net.au/the_Den/index.html Received on Sat Jun 09 2001 - 08:46:59 CDT