Hi Sergei, On Fri, Apr 26, 2013 at 1:01 AM, Sergei Golubchik <serg@askmonty.org> 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