Re: SET ROLE

[Quoted] > Anyone knows how can I Set a Role From a Database Procedure or a
> Database Function ???

You don't really 'set' a role in Oracle. You can grant a role to a user, though. A user can have multipe roles, so role privileges are cumulative.

Create function or procedure that uses dynamic SQL, the dbms_sql package. You may have to do a bit of reading there. Pass parameters that contain the username and the new, additional rolename. The role must already exist in the database. The SQL statement you generate will look something like:

GRANT RoleName TO UserName;

Then you call the dbms_sql.parse procedure, which will execute this statement. Note that this is DDL, not DML, therefore the dbms_sql package will execute on a parse, you do not call the execute procedure for DDL.

The owner of the stored proc/func must have GRANT privs explicitly granted to it. Even if the procedure owner has DBA granted to it, you will get an 'insufficient privs' error unless you explicity grant the GRANT privilege to it. This is because roles are disabled during stored procedure execution. Interesting twist, there. Looks like Oracle actually thought about this stuff first.

[Quoted] As I said, you may have to do a bit of reading here, but I just did this last week and it works fine. You can also create new users and change passwords this way. Handy dandy stuff to do with Forms 4.5, eh?

Roger Received on Mon Feb 17 1997 - 00:00:00 CET