Setting ROLES in LOGON Trigger (Oracle9i)

Hi,
Has anybody tried using SET ROLE in LOGON trigger? What i want to do is to set appropriate role depending on the user's information stored in a table. I tried this in a stored procedure but then gave up when understood that roles are disabled in procedures. So, now i am disabling all the roles with SET ROLE NONE and granting appropriate role in database LOGON trigger. Result? nothing is happening atall. The default role is getting enabled everytime i log in. Books says that Oracle enables default roles and privileges at the time of logon. So, which one happens first? this one or the one i am setting in logon trigger?

Here is the code from LOGON trigger.

      p_sql_revoke := 'SET ROLE NONE';
      p_cursor := DBMS_SQL.OPEN_CURSOR;
      DBMS_SQL.PARSE(p_cursor,p_sql_revoke,1);
      p_result := DBMS_SQL.EXECUTE(p_cursor);

      IF p_role IS NOT NULL THEN
         p_sql_grant  := 'SET ROLE ' || p_role ;
         DBMS_SQL.PARSE(p_cursor,p_sql_grant,1);
         p_result := DBMS_SQL.EXECUTE(p_cursor);
      END IF;
      DBMS_SQL.CLOSE_CURSOR(p_cursor);

If i execute the same code from sqlplus, it sets the role correctly.

Thanks in advance

Prasad Received on Tue Jan 15 2002 - 22:02:47 CET