Re: ***Security via enabling roles?***

Michael Shapiro wrote:
>
> I have 5 groups of users and I've assigned a role for each group.
> I have granted corresponding privelages to each role on the Server 7.3.2.
>
> My application on the client machine uses store procedures along with
> the embeded SQL.
>
> I'd like to set the role when my application starts so that the
> privelages will be avilable via store procedure and embeded SQL on the
> client and expect it to be disabled automatically when my application
> terminates.

Yes and no. The answer is no in that roles are disabled inside a stored procedure. The answer is yes if you are willing to handle the role checking yourself. This is the way we do it.

We have a package User which, during the initiation phase, read the users' roles directly from the dictionary and stores them in a PL/SQL table (actually, we have two -- when we "disable" and "enable" a role, we are simply moving it from one list to another). The package has a function HasRole which returns a boolean indication of whether or not the current user has the role. Procedures within other packages check to see "if User.HasRole('role_name')" when they need to know whether or not to proceed to a critical operation.

You may narrow the field somewhat by granting execute to certain packages to certain roles, but this can quickly become a logistical nightmare.

-- 
Tomm Carr
--
"Can you describe your assailant?"
"No problem, Officer.  That's exactly what I was doing when he hit me!"