Using Roles for Security?

I have an application that needs to grant access to portions of a database when the user logs in, then revoke the access when the user logs out.

There has to be a better way to handle it aside from my solution to follow.

My solution was to create a very limited access user, one that could not normally see the database in question, then grant that user the GRANT_ANY_ROLE privilege. The user would log onto the system. An embedded SQL statement would then be executed that would grant a role to the user with the grantee being the same user (ie. GRANT APPLICATION_ROLE TO USER ). Basically, the statement is issued by the new user granting a role onto itself. The new grant does not take effect until the next user session, so a quick logoff and login are required. Now the user has access to the specific portion of the database. When the user logs out, the role is revoked. A series of public synonyms are also used to allow the user to reference these tables without having to use a schema prefix.

I have thought about instead of having the user grant the role to itself, having a separate user quietly connected when the real user logs in. The 'quiet' connection would handle granting the role to the real user, which would mean that standard user accounts would not have to have the GRANT ANY PRIVILEGE role.

Is there any way that this can be done in a more simpler fashion? Can I somehow invoke a stored procedure that handles this type of security feature?

Sure, this works, but it seems there should be a better way.

Any insight into this would be greatly appreciated! Daniel

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum Received on Wed Sep 02 1998 - 13:47:40 CDT