Need PL/SQL - Alter User

[kburrows]

Hi all,

I need to create a package with 2 procedures that are executed with the definers privs(alter user), when a user logs into the application.

-----one procedure would execute upon login
issue the alter user command to set all db roles to default.

-----the other would execute upon logout
issue the alter user command to only set the connect role as default.

Does anyone have a pkg/proc like this? Or other suggestions to accomplish this same task.

Thanks,
Kathy

[balajiyes]

Create a procedure and call them in a trigger during login and logout

[rajabalachandra]

-----one procedure would execute upon login
issue the alter user command to set all db roles to default.

-----the other would execute upon logout
issue the alter user command to only set the connect role as default.

can i know

what is the advantage of toggling like this?

what is the problem of leaving the roles to what you want?

what is the effect of setting it to something else,
when the user is logged out?

-Raja

[dharma]

If you are using alter session set role why do you have to reset it?
-dharma