Re: SET ROLE in PL/SQL

Armando Marcotulli <a.marcotNSP_at_srd.it> wrote in article <3497B05A.D989A8DB_at_srd.it>...
> Stefan Knopp wrote:
>
> > We use dbms_session.set_role in a Developer2000 application with an
> > Oracle 7.3.3 Server. The roles we programmatically set are non-default
> >
> > roles (excluded via alter user ... default role). It works quite well.
> >
> > Users have access to sensitive Objects only via the application - and
> > not via sqlplus.
> >
> > S.Knopp /CSC Ploenzke Consulting, Germany
>
> What does it mean?
> Users connect to Oracle and then execute the dbms_session.set_role.
> Do you use this method?
>
>
>

The described method works great.
THe users have little or no privileges when they log on with sql*plus of via access/odbc so that they cannot do serious damage to the data. When they log on with the application the (password protected) role is enabled and the users have privileges to alterr any of the data from the app. Because the SET_ROLE procedure is part of DBMS_SESSION this means that only for this session this privilege is set. So a 'smart' user logging on with the app and trying to log on with sql*plus at the same time will still have only the standard privs in his plus session; Works great, more people should use it. Received on Fri Dec 19 1997 - 00:00:00 CST