* 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
signature.asc
Description: Digital signature