Dynamic SET ROLE Question

Can anyone tell me how I might be able to dynamically set a single role based on the result of a query of user_role_privs for the non-default role for a given user?

We are establishing our security for our systems such that any one user will have only one non-default role assigned to him/her for update purposes. I would like to be able to upon succesful query (no problem with the query for finding and counting roles) to set the role to the variable value found. However, I have so far been unable to find a method whereby I might be able to do a "set role " to a variable value or an expression.

 Is this possible? Or will I have to do a 'set role all' to get the non-default role set?

(Note that a given user may have more than one non default role, but only one per application. On a per application basis we are prefixing our roles with the 4 character application mnemonic (acronym)). Received on Mon Jan 15 1996 - 11:40:28 CST