Re: Per-Database Roles

On 22 May 2012 14:04, Stephen Frost <sfrost(at)snowman(dot)net> wrote:

> What would the semantics of that look like though?  Which is "preferred"
> when you do a 'grant select' or 'grant role'?  Or do we just disallow
> overlaps between per-DB roles and global roles?  If we don't allow
> duplicates, I suspect a lot of the other questions suddenly become a lot
> easier to deal with, but would that be too much of a restriction?  How
> would you handle migrating an existing global role to a per-database
> role?

Perhaps:

CREATE [ GLOBAL | LOCAL ] ROLE name [ LIKE role_name ] [ [ WITH ]
option [ ... ] ]

Then:

CREATE LOCAL ROLE localrolename LIKE globalrolename;

REASSIGN OWNED BY globalrolename TO localrolename;

Conflicts would occur where localrolename matches an existing local
role name within the same database, or a global role name, but not a
local role name within another database. The problem with this,
however, is that creating global roles would need conflict checks
against local roles in every database, unless a manifest of all local
roles were registered globally.

--
Thom