Thursday, November 04, 2010

The classic Oracle user security model

Following from comments on my last post, I thought I'd put into words my thoughts on the Oracle security model.

Oracle has database users with passwords. Each database user is, at least nominally, a schema with its own objects. Each user can be granted system privileges (such as CREATE SESSION), object privileges (such as INSERT into a particular table) and roles. Roles are a bunch of system and object privileges that come into effect for that user's sessions but which can't be leveraged for objects such as stored procedures in the user's schema.

I think that there are problems with this.

Firstly, having the database being the 'point of authentication' really only works if one single database underlies all the applications in the enterprise. In practice there's a bunch of other applications too, down to email and calendars, and you really want a unified authentication mechanism. Single sign-on or LDAP or something dedicated to the job. And of course Oracle provides for an array of external authentications (discussed here) and the simple 'password in the database' will one day be totally redundant.

Secondly, the one-to-one relationship between users and schemas doesn't quite work. If I have two end users, Fred and Barney, doing the same jobs with all the same rights and privileges, then I will never want FRED owning some tables in his schema and BARNEY having others in his schema. I don't even want the possibility of FRED having access to an object that BARNEY doesn't. Because of the potential for that, every sql you look at in V$SQL has a parsing schema id. Because there is the chance that the grants authorizing FRED can be revoked, requiring the query to invalidated and re-parsed, the SQL has to be separate from BARNEY's identical SQL on the same tables.

If you worked in the client/server days, you can probably remember seeing dozens of identical SQLs differentiated only by the logged in user. A pain.

Thirdly, the client/server days also had this idea of having a database session for every logged in user. It seems a bit scary these days and architects will come in and shout about how you can't scale to a million users in that sort of model. Its not that big a deal though, at least for most business apps. The number of users for your business application hasn't changed that much since the 80s. Smaller businesses may have ten or twenty, large businesses maybe a few hundred or thousands. Where an application is 'front and centre' of some-ones job, they will be using it steadily. If there is some sort of 'state', it needs preserving, whether that is in a database tier or application tier. 

The problem with this model is if you extend your application outside your business, to your customers or the world in general. Then you might get thousands of users, perhaps only using the application for a few seconds, perhaps not logging on and off properly so resources can't be released immediately.

Some form of connection pooling with generic users is generally considered the way to go. Looking down a step, this is pretty much what a database does anyway, with the all the writes to data and log files typically being done by a few processes under an abstracted 'oracle' user. 

I understand why the Apex security model is separate from the classic Oracle model. It generally follows the typical model for an application technology. Indeed, my opinion is that Application Express is an 'applications' technology or framework, not a database one. It just happens to be tied closer to the database.

What I don't like is the use of DBMS_SYS_SQL. This is a powerful, and undocumented, package. There's bits of code in Apex that aim to prevent the creation of workspaces on a number of standard schemas. I'd prefer it the other way, that schemas should need to be explicitly permitted to be used as Apex schemas. Network ACLs came into Oracle with 11g so that those packages that communicated over the internet/intranet (UTL_HTTP etc) be more tightly controlled, down to individual sites if necessary. A similar setup could be established to control access through DBMS_SYS_SQL.


Tim... said...


One clarification (and I know you know this). Privileges via roles are applicable to stored procedures provided they use invoker rights, rather than owner rights.

Obviously, the relevance of this depends on the task you are trying to do.



Noons said...

For the so-called "scalable" applications - whatever that means nowadays: apparently "Amazon/Ebay/Google" seems to be a universal synonym for such... - access control has to be completely different.
These are the ones that will support tens of thousands of users. Clearly, some form of login pooling is needed, if not a complete bypass of db logins.

(How many in that class have you come across in the last few years, in Australia? Me too.
But let's not stop "architects" from claiming that every 100-user departmental application needs to be as "scalable" as Ebay is...)

Then again, 90% of enterprise applications support at most a few thousand logins, with the vast majority having a lot less.

That's reality at least here in Australia, regardless of what the "million logged-in users" brigade might like it to be...

Which brings me back to users and schemas for the db: the "vanilla" case most dbas and designers cope with.

At this level, I setup security so that logins are separated from schemas.

It is indeed a problem with Oracle that it never managed to truly separate logins from schemas...
But I have to live with what I have, not what I'd like to have.

If an application requires individual logins in Oracle db, I usually set them up as db proxy users to a single application login that has all the required authorizations/synonyms/whatever to the required application schema.

That basically means any of those proxy logins causes its session to "become" the application access login for all intents and purposes as far as the db security is concerned: makes for an easy life in terms of setting up roles/grants and synonyms.

Another method I use when more than one role is needed - or if roles cannot be used in the application login itself - is the login trigger method: at login time, the session does a "set current_schema" to the required schema. Which can be the application schema or an "access" schema.

The difference between a proxy user or a "set current_schema" is this:

- with a proxy user, the login inherits the roles and grants of the target login.
- with a "set current_schema", the login "becomes" the target schema as far as SQL and object visibility is concerned BUT the roles and authorizations are the ones of the original login user.

Subtle difference, but very important.

What rattles me with Apex is that it doesn't follow a consistent model:

- if you are using workspaces, then you become the workspace owner as far as the db is concerned.

- if you are using worksheet, you are APEX_PUBLIC_USER as far as the db is concerned and that's it.