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 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]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
