Hi All,
Just to keep this thread alive, a somewhat crazy idea I had during dinner:
It would be interesting, and possibly cleaner, to situate things this way:
mysql.user: User_ID, User, Host, Password, Anonymous_role
mysql.role: Role, Creator, Timestamp
mysql.user_role: User_ID, Role, Grantor, Timestamp
mysql.role_role: Role, Role_granted, Grantor, Timestamp
mysql.global: Role, Select_priv, Insert_priv, ...
mysql.db: Role, ...
mysql.tables_priv: Role, ...
mysql.columns_priv: Role, ...
mysql.procs_priv: Role, ...
That is to say, {User, Host} is factored out of all privilege tables in
favor of {Role}. Then, {User, Host} is kept only in mysql.user, to
continue to support the current authentication mechanism. So ALL
privileges are dealt with at the role level rather than the user level.
Any privileges specific to a user would be granted by way of an
"anonymous" role for that user: e.g. a role named "@" plus UUID(), which
is inserted into role and user_role and Anonymous_role is set, upon the
first user-specific GRANT issued. If a user is only part of a named
role, they would not need any anonymous roles.
On a performance/implementation note: I would expect that very little
change is needed in the current privilege cache system, as for
efficiency in evaluating privileges, it is likely the best idea to
pre-populate the permissions on a user-basis. That is, traverse the
roles that a user participates in, and logical-OR all permissions
together to generate a single set of user-based permissions in the
caches. Agree?
Advantages:
* Much cleaner implementation.
* Automatically makes it easy to support external (e.g. LDAP)
authentication mechanisms, since the authentication mechanism (and even
the username etc.) are completely separate from the permissions system.
Disadvantages:
* Much more code to change to clean things up.
* Fairly tricky migration script would have to be written to upgrade to
whatever version of MySQL this would be included in.
Feel free to add your thoughts. :)
Regards,
Jeremy
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 '%'.
>
> 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 routine so it will handle new privileges
> CREATE/DROP ROLE correctly
>
> 4.2 Modify acl_getroot routine so that default role's privileges are
> loaded and stored together with `personal` user privileges. 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.
>
> 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.
>
> The result of all those changes will be an ability to work with roles
> directly via mysql.xxx tables and even with existing CREATE USER/DROP
> USER/GRANT ... TO user etc. routines. The possibility to deal with
> roles via USER SQL statements will be disabled later. Also server will
> be able to handle default role's privileges and use them in privilege
> checks.
>
> --
> Best regards,
> Sergey Kudriavtsev
>
--
high performance mysql consulting
www.provenscaling.com
