Hi Sergei, On Fri, Apr 26, 2013 at 1:01 AM, Sergei Golubchik <[email protected]> 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 _______________________________________________ Mailing list: https://launchpad.net/~maria-developers Post to : [email protected] Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
