* 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
