7 Replies - 427 Views - Last Post: 19 August 2013 - 03:21 PM

#1 Crimson Wings  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 13
  • View blog
  • Posts: 215
  • Joined: 11-July 09

Is it a good idea to create different MySQL users for different tasks?

Posted 19 August 2013 - 02:25 PM

Heya fellow DIC members.

I have a question and I'd really love your input on this. I want the opinion of people with much more experience than me on this.

Is it a good idea to create different MySQL users for different database-related tasks? Personally, I think it'd be a good extra security layer to add, provided I keep proper track of passwords. In that way I could create a database user with read-only related permissions, that would fetch all the data and do that only. Then I could have a MySQL user with a few higher permissions to insert new data into the database, and even another one for higher permissions. I think this would be a good security layer in the worst case scenario, which is getting compromised somehow.

But here's the thing - I could be crazy right now, and that's why I want your opinions. This is the first system I am designing and implementing on my own for a startup company I'm working with, so I want to get as many opinions as possible, either good or bad, or alternatives.

Thanks for your time guys!

Is This A Good Question/Topic? 0
  • +

Replies To: Is it a good idea to create different MySQL users for different tasks?

#2 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 9073
  • View blog
  • Posts: 34,115
  • Joined: 12-June 08

Re: Is it a good idea to create different MySQL users for different tasks?

Posted 19 August 2013 - 02:29 PM

Sure - and it's a good idea to track account resources!

https://dev.mysql.co...-resources.html

http://www.greensql....ning-mysql-tips
Was This Post Helpful? 1
  • +
  • -

#3 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3354
  • View blog
  • Posts: 11,353
  • Joined: 12-December 12

Re: Is it a good idea to create different MySQL users for different tasks?

Posted 19 August 2013 - 02:48 PM

I assume you mean on the front-end, rather than the back-end? That is, you have a few special friends that would log-in in the same way as anyone else but they just have a few more permissions? (As opposed to users being able to modify and administer the site via phpMyAdmin or similar.)

If so, I suppose a simple approach is to add a Role field in the users table. Then when someone attempts to delete a record, check their Role to see if they can do this. The alternative would be to present different versions of certain pages, with certain features present or hidden. (Even so, you would still need to double-check their role on every occasion.)

But I haven't got involved with this myself, so someone will hopefully clarify my interpretation.
Was This Post Helpful? 0
  • +
  • -

#4 Crimson Wings  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 13
  • View blog
  • Posts: 215
  • Joined: 11-July 09

Re: Is it a good idea to create different MySQL users for different tasks?

Posted 19 August 2013 - 02:56 PM

I'm actually talking about the MySQL users in the back-end.

The first app I'm building is to keep track of what actions an employee does with an external app. The thing is not all employees have permissions to update the external up with new data. Some of them will have their "account" in our company but they can only view their own personal data - to manage these users I'd like to create a back-end MySQL user with read-only permissions. Then for users that update the external app I need to log their actions in a new table - for this I want to have a MySQL user that has insertion rights only. I'm mostly talking about the database design and it's users rather than its front-end

I have figured out my permissions system, I just don't know if the managing MySQL users should be many or just one MySQL user with all permissions in the database.

Gah, I don't know if I'm being clear haha, but think of actual users who have their accounts as "Employees" and the MySQL users as the actual users only MySQL sees in the server.

This post has been edited by Crimson Wings: 19 August 2013 - 02:58 PM

Was This Post Helpful? 0
  • +
  • -

#5 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3354
  • View blog
  • Posts: 11,353
  • Joined: 12-December 12

Re: Is it a good idea to create different MySQL users for different tasks?

Posted 19 August 2013 - 03:04 PM

You have exhausted my knowledge on this subject (and I'm a little confused) but:

Quote

I have figured out my permissions system, I just don't know if the managing MySQL users should be many or just one MySQL user with all permissions in the database.

you should limit the number of people who have all permissions :surrender: so this, in my mind, necessitates creating at least 3-4 different roles. Good luck.
Was This Post Helpful? 1
  • +
  • -

#6 Crimson Wings  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 13
  • View blog
  • Posts: 215
  • Joined: 11-July 09

Re: Is it a good idea to create different MySQL users for different tasks?

Posted 19 August 2013 - 03:08 PM

View Postandrewsw, on 19 August 2013 - 06:04 PM, said:

You have exhausted my knowledge on this subject (and I'm a little confused) but:

Quote

I have figured out my permissions system, I just don't know if the managing MySQL users should be many or just one MySQL user with all permissions in the database.

you should limit the number of people who have all permissions :surrender:/> so this, in my mind, necessitates creating at least 3-4 different roles. Good luck.


Yeah, that's what I'm thinking, which is why I should have MySQL users with different permissions rather than one "master" MySQL user. My Employees themselves won't really have much "power" at all, but the underlying MySQL users they all share is what is worrying me. :)

That being said, you answered my question haha. Thanks. Hopefully more people will have more imput on this.
Was This Post Helpful? 0
  • +
  • -

#7 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3354
  • View blog
  • Posts: 11,353
  • Joined: 12-December 12

Re: Is it a good idea to create different MySQL users for different tasks?

Posted 19 August 2013 - 03:14 PM

Yes, hopefully someone else will add to this topic, but.. my approach - my starting point - would be to give no-one permissions at all! Then to decide what permissions they need. I hope you see where I'm coming from? :bigsmile:

That is, your current approach of considering giving everyone full permissions is upside-down :stuart:

This post has been edited by andrewsw: 19 August 2013 - 03:18 PM

Was This Post Helpful? 0
  • +
  • -

#8 Crimson Wings  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 13
  • View blog
  • Posts: 215
  • Joined: 11-July 09

Re: Is it a good idea to create different MySQL users for different tasks?

Posted 19 August 2013 - 03:21 PM

Yeah the thing is I'm not really giving everyone permissions haha. Man maybe I need to find a way to express myself better. :P/> Just the MySQL user(s) that will do the queries in behalf of the employees. Think of a MySQL user that I will grant "ALL PERMISSIONS" versus three different MySQL users that will have "SELECT', "INSERT", and "UPDATE" permissions separately, like.

dbuser_readonly. SELECT permissions.
dbuser_update. UPDATE permissions only.
dbuser_insert. INSERT permissions only.

In that way my employees who only have read only permissions would make the system issue their queries via the dbuser_readonly MysQL user.

Still I think you're right though. I just never heard of a system that has multiple MySQL users to begin with.

This post has been edited by Crimson Wings: 19 August 2013 - 03:24 PM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1