List:General Discussion« Previous MessageNext Message »
From:Gordon Bruce Date:June 14 2005 8:21pm
Subject:RE: discuss: user management
View as plain text  
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:d.stolle@stripped] 
Sent: Tuesday, June 14, 2005 2:05 PM
To: kevin.struckhoff@stripped; mysql@stripped
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
> users, but then you would to give them the 'most permissible'
> 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
> kevin.struckhoff@stripped
> -----Original Message-----
> From: Danny Stolle [mailto:d.stolle@stripped] 
> Sent: Tuesday, June 14, 2005 11:12 AM
> To: kevin.struckhoff@stripped; mysql@stripped
> 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
> get other privileges? he will get a new or already created role
> 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:
>>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
>>-----Original Message-----
>>From: Danny Stolle [mailto:d.stolle@stripped] 
>>Sent: Tuesday, June 14, 2005 10:08 AM
>>To: mysql@stripped
>>Subject: [SPAM] - discuss: user management - Bayesian Filter detected
>>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
>>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

MySQL General Mailing List
For list archives:
To unsubscribe:

discuss: user managementDanny Stolle14 Jun
  • Re: discuss: user managementPeter Brawley14 Jun
    • Re: discuss: user managementDanny Stolle14 Jun
      • Re: discuss: user managementPeter Brawley14 Jun
  • Re: discuss: user managementScott Gifford14 Jun
  • Re: discuss: user management: conclusionDanny Stolle15 Jun
RE: discuss: user managementTheRefUmp14 Jun
  • Re: discuss: user managementDanny Stolle14 Jun
RE: discuss: user managementGordon Bruce14 Jun