Re: [PATCHES] Roles - SET ROLE Updated

* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Stephen Frost <sfrost@snowman.net> 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