roles and stored procedures -Reply
Andrew:
Yes.
We assign users to different roles for different applications. When the user first
executes an app, the security module takes the USER, and Application name to determine
from our security table the user's role. This value is then used to execute the 'set role'
statement. Code sample from our Security Stored Procedure is below. Works like a charm...
select userid, activecd, inactiversn, rolename
into l_UserID, l_ActiveCd, l_InactiveRsn, l_RoleName
from t_user, t_security, t_role
where userid = USER
AND t_user.userkey = t_security.userkey
AND applicationfilter = a_AppFilter
and t_role.rolekey = t_security.rolekey;. . . (error checking code snipped)--
if (l_roleName = "caprasb")
then
set role caprasb;
elif (l_RoleName = "capradmn")
then
set role capradmn;
elif (l_RoleName = "caprupdt")
then
set role caprupdt;
elif (l_RoleName = "caprcmte")
(etc. more snip)
--
HTH.
Sky
+-------(southwel@smtpgw.maricopa.gov)-------+- Maricopa County -+
| Schuyler L. Southwell | Attorney's Office |
| Data Management Consultant | 301 W. Jefferson |
| Voice: 602-506-1111 **** FAX: 602-506-3970 | Phoenix, AZ 85003 |
+--------------------------------------------+--------USA--------+
>>> Andrew Simpson <guides@mail.tammany.com> 04/28/97 05:43pm >>>
Has anyone tried to use a set role statement in a stored procedure?
If so, did it work?