Re: GRANT role_name TO role_name ON database_name

On Wed, May 29, 2013, at 10:08 AM, Stephen Frost wrote:
> This capability might well come with a real way to have per-database
> roles in general, which has been asked for quite often as well.  You
> would then be able to have an 'auditor' role in each database and have
> them actually be different roles- would that match your needs..?

Yes, if we had per-database roles, it would work.  However, I don't 
think it's necessary.  We've already got role permissions specific toa database; so we're most of the way there.  The
mainpiece missing
 
is a way for me to assign a role to a user, but only for a specificdatabase.   Let me rephrase this, using a different
syntax...
 
  CAST <user> AS <role> ON <database>

This statement permits the <user> to execute "SET ROLE <role>"
when they are attached to <database>.   The <user> doesn't 
inherit from the role, it's only a permission that enables them to 
SET ROLE and only when attached to the permitted database.

I think this would solve my problem.  Suppose again I've got a
database cluster with a "sales" and an "hr" database.   In this
database cluster, I've got an "auditor" role which can read the
audit_table in the respective database.  Now, suppose I wish
for Tom to be an auditor for Sales, and not for HR.   I'd issue
"CAST tom AS auditor ON sales".   

When Tom normally joins the database, he wouldn't see auditor
tables (since this CAST isn't really role inheritance).  But, if he
wanted to see them, and he were in the Sales database, he'd
issue:  "SET ROLE auditor".   Then he could query audit_table.
On other hand, just because Tom joined the HR database to 
enter his timeslips, he'd not have access to audit_table; and if
he did a "SET ROLE auditor" it'd fail to escalate his permission.

I hope this makes sense and that it might be general enough.

Best,

Clark