* Tom Lane ([EMAIL PROTECTED]) wrote:
> Stephen Frost <[EMAIL PROTECTED]> writes:
> > Perhaps the specification isn't but I'm pretty sure other
> > implementations follow the SET ROLE -> current authorization
> > identifier (and thus dropping other rights granted to the CURRENT_USER).
> 
> My current reading of 4.31 is that SET ROLE *doesn't* drop rights, which
> means we need to rethink all of this.  However, on this point:

Reviewing:
http://www.psoug.org/reference/roles.html

(Top link in Google - Oracle Roles):

Oracle allows a 'SET ROLE all;' syntax, which is essentially what we're
currently automatically doing.  You can't deactivate a specific role,
but you can deactivate all roles using 'SET ROLE none;'.  Interestingly,
on at least one Oracle setup it appears that it also has an implicit
'SET ROLE all;'.  Check this out:

-----------------------------------------------------------------
melkor> sqlplus

SQL> select * from session_roles;

ROLE
------------------------------
CONNECT
NORMAL

SQL> SET ROLE none;

Role set.

SQL> select * from session_roles;

no rows selected

SQL>
-----------------------------------------------------------------

Doing this doesn't seem entirely unreasonable but we don't currently
have a way of handling 'SET ROLE none;'.  We'd need to make some changes
but I think we could handle it, and correctly handle a specific 
'SET ROLE <role>', which under Oracle does appear to drop any other
roles you currently have.

        Thanks,

                Stephen

Attachment: signature.asc
Description: Digital signature

Reply via email to