"SET ROLE" Help Requested

Has anyone developed stored procedures or other short cuts for enabling additional roles in a session, without disabling those roles already enabled? I am using Oracle 7.2 under HP/UX. The basic SET ROLE command uses the syntax:

  SET ROLE connect_role, dba_role, ..., role_x [IDENTIFIED BY password];

Any roles not explicitly included in the list are disabled. I am working with a database containing 10,000+ tables organized into roughly 100 projects. We would like to establish roles on a project by project basis. To keep things simple, I do not anticipate using passwords with the roles. The roles will be used to control which engineers and analysts are able to access each project. An analyst may work on several projects during a given database session. Some analysts are authorized to access all projects.

Several of the third party analysis tools we use, tend to display to the user, a complete list of all accessible tables (probably from the view ALL_TABLES). This makes it undesirable to enable all roles at the time a user logs on to our system. We would like to allow users to easily add and drop projects from their current working list as needed.

I know that the list of currently enabled roles can be queried from the view SESSION_ROLES. I also know that roles granted to a given user can be queried from USER_ROLE_PRIVS. Before I set about to write scripts to ENABLE_A_ROLE or DISABLE_A_ROLE, I would like to know if anyone has done this or can provide advice on how to best approach the problem. Thanks! Received on Thu Oct 05 1995 - 00:00:00 CET