Re. Setting ROLES in LOGON Trigger (Oracle9i)

So, if you say that SET ROLE cannot be used in triggers also, what are the other options to set the roles dynamically from server side.

Thanks

From: pmahale_at_yahoo.com (Prasad Mahale) Newsgroups: comp.databases.oracle
Subject: Setting ROLES in LOGON Trigger (Oracle9i) NNTP-Posting-Host: 192.250.112.134
Message-ID: <44a6df49.0201151302.39b98e2e_at_posting.google.com>

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:49:43 CET