maria-developers team mailing list archive

[Vicentiu Ciorbaru <cvicentiu@xxxxxxxxx>]

Hi Sergei,

On Fri, Apr 26, 2013 at 1:01 AM, Sergei Golubchik <serg@xxxxxxxxxxxx> wrote:
> Nice, you've done a lot of work, apparently!

Thank you for your response, I have proposed changes to address your
concerns and have raised a couple more issues:
>
>> 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.

This is what I wanted to say. I worded it wrongly by forgetting to
mention the ability to set it during the session. When the user logs
in, according to the user table, it _could_ have a role assigned to it
but it is not final after the login process.

> 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.

Alright, but I see one more problem here:
This might be because I don't fully understand how the login process
works, but if we use the user table to get the user id (user@hostname)
and use that to acknowledge the login credentials, does that not
enable someone to be able to login using a "role" instead of a
username?
If there are no more relevant checks except the user table during the
login process, I propose the following approach:

Create an extra column in the user table that links to another row in
the user table. The column can be NULL and represents the ROLE that a
user has. A "role" row can not link to another role. (or should we
allow it?)
The CURRENT_ROLE() function just returns the value in this column or
the one it points to.

Regarding the ability to login using a role:
The first idea is to add another column to the table (named Is_role or
something similar) that tells if a row represents a user or a role, a
boolean value. However I also have a second approach:

In order to not add another column to the table that tells if a row
represents a user or a role, we could just make every ROLE point to a
"bogus" user that is created in such a manner that it can not be used
to login (invalid encrypted password for example). In order to check
if a row in the user table is a ROLE or a USER, we just check the link
column and see if it points to the bogus user or not. (Kind of like a
NULL terminator in a linked list, if it points to "bogus", it's a
role, if it points to nothing or to something else, it's a user)

The advantage of this approach is that it scales slightly better with
a large number of users and roles as there is no need for a second
extra column.


I have already began writing the proposal and will submit a draft
shortly with what I mentioned in this email. If there are mistakes
please let me know.

Vicențiu