Hi Ken,
Lively discussion today!
I don't think hierarchical role treatment would necessitate any change
to the issue at discussion at the moment... since the storage of the
specific privileges associated with a given role would remain the same.
It would just mean additional supplementary logic in the reading code
to handle the hierarchy.
For example, you could imagine a hierarchical role system for a company:
Finance (role)
-> Finance Region 3 (role)
-> Bob @ bob-desktop
May create the following rows in mysql.user:
user, host, ...
finance, (role), ...
finance_region_3, (role), ...
bob, bob-desktop, ...
And the following rows in mysql.role:
user, host, role
finance_region_3, (role), finance
bob, bob-desktop, finance_region_3
So, in essence, a role is part of another role, and bob is part of the
child role.
(Not that I would be unhappy with a complete rewrite and refactoring of
the grant tables in MySQL, but I don't think anyone is wanting to
include such a thing in the scope of this project. Yet.:-) )
Regards,
Jeremy
Ken Jacobs wrote:
> As the guy who brought the (Oracle) spec of roles to the ANSI SQL
> committee, I'll just remind everyone that (in the standard) ROLES can be
> GRANTED to ROLES. This may argue for a more orthogonal treatment of
> roles and users, and a complete disentanglement of roles and host names ...
>
> Ken
>
> Jeremy Cole wrote:
>> Hi Roy,
>>
>>> I do not think this is quite intuitive. A role is defined for all
>>> hosts, so the most explicit way of expressing this is to provide a
>>> wildcard-value in the host column (and having a user/role switch
>>> column to distinguish roles from users).
>>
>> Correct, but a "user" never sees the exact place that this is stored
>> internally, and they would use the role-based commands to add, modify,
>> and drop roles, so they would never encounter the fact that it's
>> really stored as a user internally. The ability to modify user grants
>> using the current GRANT and REVOKE syntax could be left as a feature,
>> or disabled through checking for the specific value in the hostname
>> passed to GRANT and REVOKE.
>>
>> I think overall, treating a role as a special-case user would be the
>> right approach and quite simple in code and in implementation to make
>> work, and make work correctly. (For instance, it would automatically
>> work with the current privilege-caching code, comparisons, etc.)
>>
>> After that, it's just a matter of wrapping it up in role-specific
>> CREATE ROLE, GRANT ... TO ROLE, etc. commands to make it nice for the
>> user. The code for all of those should be VERY simple since the
>> underlying code already exists, and the new code just has to wrap it,
>> passing in a specific value for host to determine that this is really
>> a role.
>>
>> Regards,
>>
>> Jeremy
>>
--
high performance mysql consulting
www.provenscaling.com
