roles, stored procedures, and frustration

Why is it every time I start trying to do something in Oracle, I end up wanting to bite things?

A while ago, Paul van Dijken <<paul.vandijken_at_sema.nl> said:

>On logging into the database, the program calls an Oracle stored
procedure.

>This procedure selects the username and an encrypted password from a
table

>with user-info. The stored procedure decodes the password and tries to
grant

>the update-role to the user.

I'd like to do something similar, but I want to enable a role which has been granted to a user, but is not part of their default role list. Stored procedures, of course, don't let you set a role. Urgh. For the record, this is ideally what I'd like to do:

<fontfamily><param>Courier New</param><smaller>create or replace procedure attain_role(drole in varchar2,dpassword in varchar2) is

  stmt varchar2(256);

begin

        stmt:=drole||' identified by '||dpassword;

        dbms_session.set_role(stmt);

end;

/

</smaller></fontfamily>Which of course, doesn't work. Any workaround?

---

Dennis Taylor

---

Don't worry about people stealing your ideas.  If your ideas

are any good, you'll have to ram them down people's throats.