Re: discuss: user management: conclusion
Danny Stolle wrote: hi, i would like to discuss 'user management' in mysql. Working with Oracle you can assign users to roles giving them privileges provided by that role. MySql doesn't have Roles. I have read (Managing and Using MySql, O'Reilly) 3 options on managing users having multiple roles in a MySql environment: 1. Giving the user a Single user ID and assign the privileges to that user ID 2. Create role-bases users and have different people share the same user ID for a given role. 3. Create multiple user IDs for each role played by each user (dannys_arch as an architect, dannys_dev as a developer). Which of these 3 options is the most preferable one or are there more options which you can use. What are the advantages and disadvantages on working with one of these 3 options? how do you handle hostnames when working with random ip-addresses on your site. Or just plain simple (or stupid) what are your experiences on user management in a MySql environment. Best regards, Danny Stolle Netherlands Thanx everybody for sharing some cool information on User Management. I have read some cool ideas and arguments on the options which i suggested. I guess there is no best or a most prefered method on user management. The method that is being used is the most suitable in the environment the database is used and the database administrator's prefered working method offcourse. But i must conclude that by reading the messages carefully the most prefered method was option 3: creating multiple user IDs for each task that the user would perform on the database. Creating these roles and assigning the user(s) to this specific role (=RBAC). The advantage is that you can create custom roles for specific tasks and communicate these roles to the users who will use them. You would also get a small amount of connections which can leed to high performance tuning from your MySql database. Still it would take a lot of time consuming thinking when you design and configure your database in dealing with User Management. Hope you all don't mind if i come up with more topic discussions like this one. I must say it is a real mind breaker. Hope you enjoyed this discussion; please feel free to continue. I shall share my opinion on your comments. Best Regards, Danny Stolle EmoeSoft, Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: discuss: user management
It always helpd me to change MySQL's "user" to connection in my head
when I begin to think about access control. Then in most database
designs that I have seen, row access control is just as important as
database/table/column. Then the question becomes does the "user" have
direct access to the database or is there an intervening filter
[application code].
Now on a development environment I typically group the developers {i.e.
roles} and let each "user" {person} in a group use a common connection.
Even then the # of connections has to be small and relatively generic
{Select on these 20 tables, Select/ Update on these 45 tables, Select/
Insert/ Update/ Delete on these 5 tables}. I have yet to find the DBA
that can define unique MySQL "users" for 500 people.
In an "Accountiing" environment I still will have groups/roles but much
of the identification/enforcement will be done through a combination of
application code and the use of specific database connections. This way
I can enforce things like population of last changed by and timestamp
fields, application navigation recording as well as row level access
control. {i.e. I should only be able to see my own employee data or some
parts of the data for people reporting to me.} Direct access via SQL
would be extremely limited.
-Original Message-
From: Danny Stolle [mailto:[EMAIL PROTECTED]
Sent: Tuesday, June 14, 2005 2:05 PM
To: [EMAIL PROTECTED]; mysql@lists.mysql.com
Subject: Re: discuss: user management
Hi Kevin,
i started this discussion to find out, how most database administrators
or users involved in managing MySql, would deal with a topic as User
Management. So the question(s) is(are) more hypothetical, e.g. "What if
(...) 'you would have a development site and an accounting site'" how
would you plan your user management?
I like the way you state your opinion on User Management and the
examples you give. It is not so that i would stick on these options, if
there are other ideas, please let us discuss them.
but if you have given some examples, i would like to give an example on
the 3th option: it is not so that you have to create a user with these
prefixes (_dev, _arch); why not having departmentnames as userID's or
perhaps fantasynames as userID's (which could be uses as role names).
your question on the role-part: 'why would somebody create roles?' is an
interesting question. i have no direct answer to this question. the only
thing i would come up with is: when you have a lot of tables and you
have to change a privilege on several tables. you have the choice for
changing that for 40 users each or 5 roles each.
Best Regards,
Danny Stolle
EmoeSoft, Netherlands
Kevin Struckhoff wrote:
> Danny,
>
> I would stay away from option 3 for exactly the example you provided.
> You have 1 user with 2 roles. What if you had 30 users with 2 roles? I
> would choose option 2 because I would only have to maintain 2 users in
> MySQL, not 60 as you would in option 3. For option 1, you would have
30
> users, but then you would to give them the 'most permissible'
privileges
> of the 2 roles.
>
> What I don't know is why you need to have roles in the first place. Do
> you have a large number of users and a large number of roles?
>
> Kevin Struckhoff
> Customer Analytics Mgr.
> NewRoads West
>
> Office 818.253.3819 Fax 818.834.8843
> [EMAIL PROTECTED]
>
>
> -----Original Message-
> From: Danny Stolle [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, June 14, 2005 11:12 AM
> To: [EMAIL PROTECTED]; mysql@lists.mysql.com
> Subject: Re: discuss: user management
>
> Hi Kevin,
>
> yes it is a complex matter, i agree completely. but how would you plan
> this as a dba or the person involved on administrating MySql. For
> instance: You would choose option 2 as the preferable one. But what
> would you do if somebody would change its role or that the person
would
> get other privileges? he will get a new or already created role
userID,
> but would still be able to logon using the previous user id.
>
> why wouldn't you choose for the 3th option or 1st option? what
> disadvantages do you think would option 1 and 3 have?
>
> Best regards,
>
> Danny Stolle
> EmoeSoft, Netherlands
>
>
>
> Kevin Struckhoff wrote:
>
>>Danny,
>>
>>Although my experience with MySQL user management is limited to just
>>maintaining a handful of users, I find it rather overly-complex
>
> because
>
>>of the need to maintain a table of users and 'from where' they can
>
> have
>
>>access, and to what databases they can have access to. For example, I
>>just installed MySQL Administrator on my laptop and then I had to add
>>rows a
Re: discuss: user management
Danny, >that is an interesting formulation which you gave. can you please comment >that with an example? would you create roles for users who are assigned the >same privileges and individual users who have multiple roles? In this fragment from an example, a role has a name (roles.name), eg 'payments clerk', a role is referenced in one or more rows in usecases (eg usecases.name='enter/edit payments'), so a role defines access to multiple jobs (the list of usecases rows which reference that roleID), a users.username must exist as a mysql.user.User, and a user may have multiple roles, whence the users-roles bridge table userroles. CREATE TABLE IF NOT EXISTS roles( roleID INT NOT NULL AUTO_INCREMENT, name CHAR(50) NOT NULL, roleRank SMALLINT NOT NULL, PRIMARY KEY (roleID), UNIQUE UC_roleRank (roleRank)); CREATE TABLE IF NOT EXISTS usecases( usecaseID INT NOT NULL AUTO_INCREMENT, name CHAR(50) NOT NULL, roleID INT NOT NULL, PRIMARY KEY (usecaseID), INDEX roleID (roleID)) ENGINE=InnoDb; CREATE TABLE IF NOT EXISTS users( userID INT NOT NULL AUTO_INCREMENT, username CHAR(16) NOT NULL, status SMALLINT NOT NULL, date_created DATETIME NOT NULL, date_edited DATETIME, entered_by INT, PRIMARY KEY (userID) ); CREATE TABLE IF NOT EXISTS userroles( userroleID INT NOT NULL AUTO_INCREMENT PRIMARY KEY, roleID INT NOT NULL , userID INT NOT NULL , INDEX ur_roleID( roleID ) , INDEX ur_userID( userID ) , ); For more flexibility, define metaroles as collections of roles, and/or model the hierachy as nodes and edges. PB - Danny Stolle wrote: Peter, that is an interesting formulation which you gave. can you please comment that with an example? would you create roles for users who are assigned the same privileges and individual users who have multiple roles? Best Regards, Danny Stolle Netherlands Peter Brawley wrote: Danny />1. Giving the user a Single user ID and assign the privileges to that user ID >2. Create role-bases users and have different people share the same user ID >for a given role. >3. Create multiple user IDs for each role played by each user (dannys_arch >as an architect, dannys_dev as a developer). / #2 has a name (role-based user access, RBAC) and is widely used, but its formulation above needs a correction: create roles, and users who can be assigned different and possibly multiple roles. PB - Danny Stolle wrote: hi, i would like to discuss 'user management' in mysql. Working with Oracle you can assign users to roles giving them privileges provided by that role. MySql doesn't have Roles. I have read (Managing and Using MySql, O'Reilly) 3 options on managing users having multiple roles in a MySql environment: 1. Giving the user a Single user ID and assign the privileges to that user ID 2. Create role-bases users and have different people share the same user ID for a given role. 3. Create multiple user IDs for each role played by each user (dannys_arch as an architect, dannys_dev as a developer). Which of these 3 options is the most preferable one or are there more options which you can use. What are the advantages and disadvantages on working with one of these 3 options? how do you handle hostnames when working with random ip-addresses on your site. Or just plain simple (or stupid) what are your experiences on user management in a MySql environment. Best regards, Danny Stolle Netherlands No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.323 / Virus Database: 267.7.2 - Release Date: 6/14/2005 No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.323 / Virus Database: 267.7.2 - Release Date: 6/14/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: discuss: user management
Danny Stolle <[EMAIL PROTECTED]> writes: [...] > 3. Create multiple user IDs for each role played by each user > (dannys_arch as an architect, dannys_dev as a developer). An interesting argument in favor of the much maligned "option 3" is that it allows users to select which of their privileges they want for a particular task. For example, even if you have developer privileges which allow you to delete tables, you may want to log in as "dannys_search" if you're testing a program or using a GUI frontend, to avoid accidentally damaging anything. I often use a technique like this when doing Web development; I'll create a "cust_select" for use in scripts that should only be reading the data, and a "cust_update" for scripts that should be updating. That way bugs in one script don't allow it to do too much damage. This is in general good security practice, much like only logging in as root when you need to. If you choose this route, I think you could manage it by maintaining your own tables with usernames and roles, then generating the actual MySQL user table. Your frontends could handle automatically appending the appropriate role. Even if you decide on a more traditional RBAC system, this still might be a useful technique. Good luck, ScottG. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: discuss: user management
Peter, that is an interesting formulation which you gave. can you please comment that with an example? would you create roles for users who are assigned the same privileges and individual users who have multiple roles? Best Regards, Danny Stolle Netherlands Peter Brawley wrote: Danny />1. Giving the user a Single user ID and assign the privileges to that user ID >2. Create role-bases users and have different people share the same user ID >for a given role. >3. Create multiple user IDs for each role played by each user (dannys_arch >as an architect, dannys_dev as a developer). / #2 has a name (role-based user access, RBAC) and is widely used, but its formulation above needs a correction: create roles, and users who can be assigned different and possibly multiple roles. PB - Danny Stolle wrote: hi, i would like to discuss 'user management' in mysql. Working with Oracle you can assign users to roles giving them privileges provided by that role. MySql doesn't have Roles. I have read (Managing and Using MySql, O'Reilly) 3 options on managing users having multiple roles in a MySql environment: 1. Giving the user a Single user ID and assign the privileges to that user ID 2. Create role-bases users and have different people share the same user ID for a given role. 3. Create multiple user IDs for each role played by each user (dannys_arch as an architect, dannys_dev as a developer). Which of these 3 options is the most preferable one or are there more options which you can use. What are the advantages and disadvantages on working with one of these 3 options? how do you handle hostnames when working with random ip-addresses on your site. Or just plain simple (or stupid) what are your experiences on user management in a MySql environment. Best regards, Danny Stolle Netherlands No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.323 / Virus Database: 267.7.2 - Release Date: 6/14/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: discuss: user management
George, from a MySql point of view, how would you deal with security on a site? would you than create individual users? Best Regards, Danny Stolle EmoeSoft, Netherlands [EMAIL PROTECTED] wrote: Danny, My 2 cents (American dollars): From a security standpoint (if you are a financial institution) you'd would never get away with #2 because there is no audit trail with multiple users using a "generic" user id. The MySQL implementation of security is not implemented with auditing and standard role based assignments in mind. I am hopeful that they will correct this issue down the road. Regards, George Danny Stolle <[EMAIL PROTECTED]> wrote: hi, i would like to discuss 'user management' in mysql. Working with Oracle you can assign users to roles giving them privileges provided by that role. MySql doesn't have Roles. I have read (Managing and Using MySql, O'Reilly) 3 options on managing users having multiple roles in a MySql environment: 1. Giving the user a Single user ID and assign the privileges to that user ID 2. Create role-bases users and have different people share the same user ID for a given role. 3. Create multiple user IDs for each role played by each user (dannys_arch as an architect, dannys_dev as a developer). Which of these 3 options is the most preferable one or are there more options which you can use. What are the advantages and disadvantages on working with one of these 3 options? how do you handle hostnames when working with random ip-addresses on your site. Or just plain simple (or stupid) what are your experiences on user management in a MySql environment. Best regards, Danny Stolle Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] __ Switch to Netscape Internet Service. As low as $9.95 a month -- Sign up today at http://isp.netscape.com/register Netscape. Just the Net You Need. New! Netscape Toolbar for Internet Explorer Search from anywhere on the Web and block those annoying pop-ups. Download now at http://channels.netscape.com/ns/search/install.jsp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: discuss: user management
Hi Kevin, i started this discussion to find out, how most database administrators or users involved in managing MySql, would deal with a topic as User Management. So the question(s) is(are) more hypothetical, e.g. "What if (...) 'you would have a development site and an accounting site'" how would you plan your user management? I like the way you state your opinion on User Management and the examples you give. It is not so that i would stick on these options, if there are other ideas, please let us discuss them. but if you have given some examples, i would like to give an example on the 3th option: it is not so that you have to create a user with these prefixes (_dev, _arch); why not having departmentnames as userID's or perhaps fantasynames as userID's (which could be uses as role names). your question on the role-part: 'why would somebody create roles?' is an interesting question. i have no direct answer to this question. the only thing i would come up with is: when you have a lot of tables and you have to change a privilege on several tables. you have the choice for changing that for 40 users each or 5 roles each. Best Regards, Danny Stolle EmoeSoft, Netherlands Kevin Struckhoff wrote: Danny, I would stay away from option 3 for exactly the example you provided. You have 1 user with 2 roles. What if you had 30 users with 2 roles? I would choose option 2 because I would only have to maintain 2 users in MySQL, not 60 as you would in option 3. For option 1, you would have 30 users, but then you would to give them the 'most permissible' privileges of the 2 roles. What I don't know is why you need to have roles in the first place. Do you have a large number of users and a large number of roles? Kevin Struckhoff Customer Analytics Mgr. NewRoads West Office 818.253.3819 Fax 818.834.8843 [EMAIL PROTECTED] -Original Message- From: Danny Stolle [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 14, 2005 11:12 AM To: [EMAIL PROTECTED]; mysql@lists.mysql.com Subject: Re: discuss: user management Hi Kevin, yes it is a complex matter, i agree completely. but how would you plan this as a dba or the person involved on administrating MySql. For instance: You would choose option 2 as the preferable one. But what would you do if somebody would change its role or that the person would get other privileges? he will get a new or already created role userID, but would still be able to logon using the previous user id. why wouldn't you choose for the 3th option or 1st option? what disadvantages do you think would option 1 and 3 have? Best regards, Danny Stolle EmoeSoft, Netherlands Kevin Struckhoff wrote: Danny, Although my experience with MySQL user management is limited to just maintaining a handful of users, I find it rather overly-complex because of the need to maintain a table of users and 'from where' they can have access, and to what databases they can have access to. For example, I just installed MySQL Administrator on my laptop and then I had to add rows allowing me to access MySQL from my laptop. The ODBC connection setup should suffice. For every instance of MySQL, you have to have an entry in the user table for every user from every access point. Then multiply that by the number of databases in each instance and you can see that administration of the users can get out of hand. If I had to choose between the 3 methods listed below, I would choose #2 if there was a large number of roles and users. I would definitely stay away from option #3 no matter what. HTH. Kevin Struckhoff Customer Analytics Mgr. NewRoads West Office 818.253.3819 Fax 818.834.8843 [EMAIL PROTECTED] -Original Message- From: Danny Stolle [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 14, 2005 10:08 AM To: mysql@lists.mysql.com Subject: [SPAM] - discuss: user management - Bayesian Filter detected spam hi, i would like to discuss 'user management' in mysql. Working with Oracle you can assign users to roles giving them privileges provided by that role. MySql doesn't have Roles. I have read (Managing and Using MySql, O'Reilly) 3 options on managing users having multiple roles in a MySql environment: 1. Giving the user a Single user ID and assign the privileges to that user ID 2. Create role-bases users and have different people share the same user ID for a given role. 3. Create multiple user IDs for each role played by each user (dannys_arch as an architect, dannys_dev as a developer). Which of these 3 options is the most preferable one or are there more options which you can use. What are the advantages and disadvantages on working with one of these 3 options? how do you handle hostnames when working with random ip-addresses on your site. Or just plain simple (or stupid) what are your experiences on user management in a MySql environment.
RE: discuss: user management
Danny, My 2 cents (American dollars): From a security standpoint (if you are a financial institution) you'd would never get away with #2 because there is no audit trail with multiple users using a "generic" user id. The MySQL implementation of security is not implemented with auditing and standard role based assignments in mind. I am hopeful that they will correct this issue down the road. Regards, George Danny Stolle <[EMAIL PROTECTED]> wrote: >hi, > >i would like to discuss 'user management' in mysql. Working with Oracle >you can assign users to roles giving them privileges provided by that >role. MySql doesn't have Roles. I have read (Managing and Using MySql, >O'Reilly) 3 options on managing users having multiple roles in a MySql >environment: >1. Giving the user a Single user ID and assign the privileges to that >user ID >2. Create role-bases users and have different people share the same user >ID for a given role. >3. Create multiple user IDs for each role played by each user >(dannys_arch as an architect, dannys_dev as a developer). > >Which of these 3 options is the most preferable one or are there more >options which you can use. What are the advantages and disadvantages on >working with one of these 3 options? how do you handle hostnames when >working with random ip-addresses on your site. > >Or just plain simple (or stupid) what are your experiences on user >management in a MySql environment. > >Best regards, > >Danny Stolle >Netherlands > >-- >MySQL General Mailing List >For list archives: http://lists.mysql.com/mysql >To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > __ Switch to Netscape Internet Service. As low as $9.95 a month -- Sign up today at http://isp.netscape.com/register Netscape. Just the Net You Need. New! Netscape Toolbar for Internet Explorer Search from anywhere on the Web and block those annoying pop-ups. Download now at http://channels.netscape.com/ns/search/install.jsp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: discuss: user management
Danny >1. Giving the user a Single user ID and assign the privileges to that user ID >2. Create role-bases users and have different people share the same user ID >for a given role. >3. Create multiple user IDs for each role played by each user (dannys_arch >as an architect, dannys_dev as a developer). #2 has a name (role-based user access, RBAC) and is widely used, but its formulation above needs a correction: create roles, and users who can be assigned different and possibly multiple roles. PB - Danny Stolle wrote: hi, i would like to discuss 'user management' in mysql. Working with Oracle you can assign users to roles giving them privileges provided by that role. MySql doesn't have Roles. I have read (Managing and Using MySql, O'Reilly) 3 options on managing users having multiple roles in a MySql environment: 1. Giving the user a Single user ID and assign the privileges to that user ID 2. Create role-bases users and have different people share the same user ID for a given role. 3. Create multiple user IDs for each role played by each user (dannys_arch as an architect, dannys_dev as a developer). Which of these 3 options is the most preferable one or are there more options which you can use. What are the advantages and disadvantages on working with one of these 3 options? how do you handle hostnames when working with random ip-addresses on your site. Or just plain simple (or stupid) what are your experiences on user management in a MySql environment. Best regards, Danny Stolle Netherlands No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.323 / Virus Database: 267.7.2 - Release Date: 6/14/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: discuss: user management
Danny, I would stay away from option 3 for exactly the example you provided. You have 1 user with 2 roles. What if you had 30 users with 2 roles? I would choose option 2 because I would only have to maintain 2 users in MySQL, not 60 as you would in option 3. For option 1, you would have 30 users, but then you would to give them the 'most permissible' privileges of the 2 roles. What I don't know is why you need to have roles in the first place. Do you have a large number of users and a large number of roles? Kevin Struckhoff Customer Analytics Mgr. NewRoads West Office 818.253.3819 Fax 818.834.8843 [EMAIL PROTECTED] -Original Message- From: Danny Stolle [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 14, 2005 11:12 AM To: [EMAIL PROTECTED]; mysql@lists.mysql.com Subject: Re: discuss: user management Hi Kevin, yes it is a complex matter, i agree completely. but how would you plan this as a dba or the person involved on administrating MySql. For instance: You would choose option 2 as the preferable one. But what would you do if somebody would change its role or that the person would get other privileges? he will get a new or already created role userID, but would still be able to logon using the previous user id. why wouldn't you choose for the 3th option or 1st option? what disadvantages do you think would option 1 and 3 have? Best regards, Danny Stolle EmoeSoft, Netherlands Kevin Struckhoff wrote: > Danny, > > Although my experience with MySQL user management is limited to just > maintaining a handful of users, I find it rather overly-complex because > of the need to maintain a table of users and 'from where' they can have > access, and to what databases they can have access to. For example, I > just installed MySQL Administrator on my laptop and then I had to add > rows allowing me to access MySQL from my laptop. The ODBC connection > setup should suffice. For every instance of MySQL, you have to have an > entry in the user table for every user from every access point. Then > multiply that by the number of databases in each instance and you can > see that administration of the users can get out of hand. > > If I had to choose between the 3 methods listed below, I would choose #2 > if there was a large number of roles and users. I would definitely stay > away from option #3 no matter what. HTH. > > Kevin Struckhoff > Customer Analytics Mgr. > NewRoads West > > Office 818.253.3819 Fax 818.834.8843 > [EMAIL PROTECTED] > > > -Original Message- > From: Danny Stolle [mailto:[EMAIL PROTECTED] > Sent: Tuesday, June 14, 2005 10:08 AM > To: mysql@lists.mysql.com > Subject: [SPAM] - discuss: user management - Bayesian Filter detected > spam > > hi, > > i would like to discuss 'user management' in mysql. Working with Oracle > you can assign users to roles giving them privileges provided by that > role. MySql doesn't have Roles. I have read (Managing and Using MySql, > O'Reilly) 3 options on managing users having multiple roles in a MySql > environment: > 1. Giving the user a Single user ID and assign the privileges to that > user ID > 2. Create role-bases users and have different people share the same user > > ID for a given role. > 3. Create multiple user IDs for each role played by each user > (dannys_arch as an architect, dannys_dev as a developer). > > Which of these 3 options is the most preferable one or are there more > options which you can use. What are the advantages and disadvantages on > working with one of these 3 options? how do you handle hostnames when > working with random ip-addresses on your site. > > Or just plain simple (or stupid) what are your experiences on user > management in a MySql environment. > > Best regards, > > Danny Stolle > Netherlands > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: discuss: user management
Hi Kevin, yes it is a complex matter, i agree completely. but how would you plan this as a dba or the person involved on administrating MySql. For instance: You would choose option 2 as the preferable one. But what would you do if somebody would change its role or that the person would get other privileges? he will get a new or already created role userID, but would still be able to logon using the previous user id. why wouldn't you choose for the 3th option or 1st option? what disadvantages do you think would option 1 and 3 have? Best regards, Danny Stolle EmoeSoft, Netherlands Kevin Struckhoff wrote: Danny, Although my experience with MySQL user management is limited to just maintaining a handful of users, I find it rather overly-complex because of the need to maintain a table of users and 'from where' they can have access, and to what databases they can have access to. For example, I just installed MySQL Administrator on my laptop and then I had to add rows allowing me to access MySQL from my laptop. The ODBC connection setup should suffice. For every instance of MySQL, you have to have an entry in the user table for every user from every access point. Then multiply that by the number of databases in each instance and you can see that administration of the users can get out of hand. If I had to choose between the 3 methods listed below, I would choose #2 if there was a large number of roles and users. I would definitely stay away from option #3 no matter what. HTH. Kevin Struckhoff Customer Analytics Mgr. NewRoads West Office 818.253.3819 Fax 818.834.8843 [EMAIL PROTECTED] -Original Message- From: Danny Stolle [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 14, 2005 10:08 AM To: mysql@lists.mysql.com Subject: [SPAM] - discuss: user management - Bayesian Filter detected spam hi, i would like to discuss 'user management' in mysql. Working with Oracle you can assign users to roles giving them privileges provided by that role. MySql doesn't have Roles. I have read (Managing and Using MySql, O'Reilly) 3 options on managing users having multiple roles in a MySql environment: 1. Giving the user a Single user ID and assign the privileges to that user ID 2. Create role-bases users and have different people share the same user ID for a given role. 3. Create multiple user IDs for each role played by each user (dannys_arch as an architect, dannys_dev as a developer). Which of these 3 options is the most preferable one or are there more options which you can use. What are the advantages and disadvantages on working with one of these 3 options? how do you handle hostnames when working with random ip-addresses on your site. Or just plain simple (or stupid) what are your experiences on user management in a MySql environment. Best regards, Danny Stolle Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
