Hello, Sergei!
2008/5/20 Sergei Golubchik <serg@stripped>:
> Hi!
>
> On May 20, Sergey Kudriavtsev wrote:
>> [skipped]
>> To distinguish roles from users I propose to use "Host" field of
>> mysql.user table - If this field is empty then we should consider the
>> specified record to be a role.
>> Now empty field is equivalent to '%'. I will change the behaviour of
>> parser to always fail host identity check when the checked field has
>> empty value. I will also change mysql_fix_privilege_tables script to
>> replace all existing empty "Host" field values with '%'.
>
> This is fine, but what are you going to do with mysql.host table ? In
> there empty host is not equivalent to '%' :(
>
I'm not sure whether I have to do something with mysql.host table at all.
Anyway, I'm going to store roles in mysql.user/mysql.xxx_priv only and I don't
see how mysql.host table will interfere with roles' privileges.
Please, explain this
issue in more detailed way.
BTW, both 5.1 and 6.0 manuals say that
[quote ]A '%' or blank Host value in the host table means "any host." [/quote]
If community find useful to create additional isRole field instead
then I'll do it. But in this
case I will also have to add isRole fields to mysql.xxx_priv tables to
be able to
distinguish users from roles.
[skipped]
>> 4.1 Modify acl_load
>
> and grant_load
>
OK, got it.
>> routine so it will handle new privileges
>> CREATE/DROP ROLE correctly
>
> Where will you load mysql.roles table ?
>
Right now I will not load it at all, I just wish to finish modifying
mysql.xxx tables and move forward to the next step. Later I will create
specialized structure to hold it in memory and modify acl_load/grant_load
routines so they work correctly with this table.
Consider it to be just a part of necessary changes in `mysql` DB, I don't want
to get back to it at some point.
>> 4.2 Modify acl_getroot routine so that default role's privileges are
>> loaded and stored together with `personal` user privileges.
>
> This means that if role's global privileges are changed or a default
> role of a user is changed the changes will not affect already existing
> session, a user will need to reconnect for changes to take an effect.
>
> It's ok (at least consistent with existent behavior), but make sure it's
> documented.
>
OK
>> I'm not going to implement SET ROLE statement on this stage so I'll
>> not need to worry right now about changing role of user.
>
> Hmm, ok.
>
>> 4.3 acl_getroot_no_password routine will be left unchanged as in
>> WL#988 it's declared that implicit SET ROLE NONE statement must be
>> called when getting inside SP.
>
> Explain this in a comment around acl_getroot_no_password().
> Note that SET ROLE NONE should most probably not be called for SP with
> SQL SECURITY INVOKER. But they, I guess, don't call
> acl_getroot_no_password() anyway.
>
> Please, make sure you have tests for the above. That is:
>
> create a procedure with SQL SECURITY INVOKER. Run it as a user with some
> privileges granted via a role. Verify that there were no SET ROLE NONE.
>
> create a procedure with SQL SECURITY DEFINER. Run it as the same user as
> definer. Run it as a different user. Verify that there was SET ROLE NONE
> in both cases.
>
> See http://dev.mysql.com/doc/mysqltest/en/index.html
> and existing tests in mysql-test/t/
>
OK, it will be done.
--
Best regards,
Sergey Kudriavtsev
