Hi!
On May 20, Sergey Kudriavtsev wrote:
> Hello, everyone.
>
> So, I'm going to implement some basic role functionality in another two weeks.
>
> Exactly, I'm planning to do the following:
>
> 1. Add new necessary fields to mysql.user table:
>
> Default_role char(16) binary;
> Comment varchar(50);
> Create_role_priv enum('N','Y') character set utf8 NOT NULL default 'N';
> Drop_role_priv enum('N','Y') character set utf8 NOT NULL default 'N'.
>
> 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 will also have to fix all related scripts and functions to work
> properly with new mysql.user fields.
>
> 2. I will create new mysql.roles table to hold user-role relationships
> like this:
>
> CREATE TABLE mysql.roles(
> `Host` char(60) collate utf8_bin NOT NULL default ",
> `User` char(16) collate utf8_bin NOT NULL default ",
> `Role` char(16) collate utf8_bin NOT NULL default ",
> PRIMARY KEY (`Host`,`User`,`Role`)
> ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users
> and roles relationships';
>
> 3. I will add a variable to hold assigned role name to
> Security_context class from file sql_class.cc
>
> 4. I will make the following changes to sql_acl.cc:
>
> 4.1 Modify acl_load
and grant_load
> routine so it will handle new privileges
> CREATE/DROP ROLE correctly
Where will you load mysql.roles table ?
> 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.
> 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/
Regards / Mit vielen Grüssen,
Sergei
--
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Sergei Golubchik <serg@stripped>
/ /|_/ / // /\ \/ /_/ / /__ Principal Software Engineer/Server Architect
/_/ /_/\_, /___/\___\_\___/ Sun Microsystems GmbH, HRB München 161028
<___/ Sonnenallee 1, 85551 Kirchheim-Heimstetten
Geschäftsführer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer
Vorsitzender des Aufsichtsrates: Martin Häring
