maria-developers team mailing list archive

[Sergei Golubchik <serg@xxxxxxxxxxxx>]

Hi, Vicentiu!

On Apr 25, Vicentiu Ciorbaru wrote:
> On Wed, Apr 17, 2013 at 8:08 PM, Sergei Golubchik <serg@xxxxxxxxxxxx> wrote:
> > On Apr 17, Vicențiu Ciorbaru wrote:
> >>
> >> The project that interests me the most is MDEV-4397: Roles.
> >>
> >> I want to ask you what would be a good place to start in the
> >> codebase to help me get an idea what I need to do for this project?
> >
> > sql/sql_acl.cc
> >
> > Check functions like acl_load(), acl_get(), grant_load(), check_grant().
> > There are more functions that deal with privileges, but those four
> > should be enough to understand how the system works.
> 
> Thank you for the answer and I apologize for the late response. I had
> some trouble following the code in those functions that you mentioned
> but I think I got the gist on how the system works.

Nice, you've done a lot of work, apparently!

> I have come up with a preliminary plan to implement roles and would
> like to discuss it here before I begin to write my proposal.
> 
> As I see it so far, the privileges of a user are stored in the
> mysql.user table with a privilege per column, such as Select_priv,
> Insert_priv etc.

correct

> My first idea is to create a separate table that describes a role,
> with the columns identical to the privilege columns in user and a name
> for the role. This way a row in the table can  represent a subset of
> privileges.

There's a problem with that, see below.

> Since a user can only have one role at any one time, there would be a
> one to one mapping between the mysql.user table and mysql.roles?
> table. This could be done with a simple foreign key in the user table.

right

> NOTE: I am not quite sure what a session means in the context
> presented in this paragraph: Quote:"Only one role can be set to a user
> at any specific point in any given session. In other words,
> CURRENT_ROLE (see below) can never return a list."
> I took the meaning of a session as the context present at the moment
> the user logged in and that the role is set at the moment of login in
> the session context based on the aforementioned tables mysql.user and
> mysql.roles.

Not exactly. When a user is logged in it might have no roles set at all.
It can set a role later. But if he sets a new role, a previous role is
"unset" automatically. Only one role can be set at any time.

> To get the privileges of a user one would have to check the user table
> for all Yes ticked privileges and also add the Yes ticked privileges
> in the new mysql.roles table row corresponding to the user (if any)
> based on the foreign key in the user table.
> 
> The queries:
> REVOKE privilege FROM { user | role }
> GRANT privilege TO { user | role }
> will either modify the user table or the roles table.
> 
> Regarding the ambiguity between a user and a role, would it not be
> easier to have an extra keyword TO ROLE or FROM ROLE?

Yes, it would. But we cannot do it. The syntax is specified by the SQL
standard, and we should follow the standard syntax, when there is one.

We can, of course, invent our own non-standard syntax for our own
non-standard features. But roles are defined by the SQL standard, and
the syntax for roles is standard too.

Here's the issue, see - there is no ambiguity. role names and user names
live in the same namespace. There can be no role name with the same name
as a user name.

Which also means, we can store roles and users in the same mysql.user
table, there is no need to have a special table for roles.

> I am not sure what these tables should contain exactly:
> INFORMATION_SCHEMA.APPLICABLE_ROLES
> INFORMATION_SCHEMA.ENABLED_ROLES

This is all defined in the SQL standard. I can show you later, don't
bother about it, these information_schema tables are trivial, it won't
take more than a few days to add them both. They practically do not
increase the complexity of the project.

Regards,
Sergei