Jump to content



Photo

Should I create a unique MySQL user per logged in person?

mysql

  • Please log in to reply
14 replies to this topic

#1 Jose_49

Jose_49

    Neowinian Senior

  • Tech Issues Solved: 2
  • Joined: 30-July 09

Posted 04 January 2013 - 13:58

Yo Neowin!
I want to know what do you suggest in terms of security, and speed, whether is recommended or not to create an individual user for each person that logs in to my site.

I mean. I usually verify a username on a table, and assign unique tables to each of my users with a General MySQL user account with limited privileges. But since I've been reading a little bit more about MySQL (I only know the basics), I've seen that to improve security I could assign certain limits on MySQL users and only allow access to certain tables.

So, what can you suggest me in terms of MySQL users?

Thanks :p


#2 SuperKid

SuperKid

    Im no superman

  • Joined: 21-April 08
  • Location: Birmingham, England, UK
  • OS: OS X 10.8, iOS 7
  • Phone: iPhone 4S

Posted 04 January 2013 - 14:01

What do you mean unique mysql user per logged in user, what type of site is this?

#3 OP Jose_49

Jose_49

    Neowinian Senior

  • Tech Issues Solved: 2
  • Joined: 30-July 09

Posted 04 January 2013 - 14:06

What do you mean unique mysql user per logged in user, what type of site is this?


I mean, to create a MySQL user. The default user on a MySQL server is root. I would like to know if it would improve security having a separate user like "John" which would only access Joh_products and John_clients table and will have limited privileges like SELECT, DROP, UPDATE, INSERT commands.


This site, is on development right now, so everything can be modified. It's a receipt management website, which each of the users will have their own clients stats, number of purchases, receipts, etc.

#4 Mr.XXIV

Mr.XXIV

    Shine bright like Iron Man.

  • Tech Issues Solved: 1
  • Joined: 30-April 11
  • Location: Durham, North Carolina
  • OS: OS X Yosemite
  • Phone: iPhone 5s

Posted 04 January 2013 - 14:09

I mean, to create a MySQL user. The default user on a MySQL server is root. I would like to know if it would improve security having a separate user like "John" which would only access Joh_products and John_clients table and will have limited privileges like SELECT, DROP, UPDATE, INSERT commands.


This site, is on development right now, so everything can be modified. It's a receipt management website, which each of the users will have their own clients stats, number of purchases, receipts, etc.


I truly would not recommend that at all.

#5 n_K

n_K

    Neowinian Senior

  • Tech Issues Solved: 3
  • Joined: 19-March 06
  • Location: here.
  • OS: FreeDOS
  • Phone: Nokia 3315

Posted 04 January 2013 - 14:15

NEVER use the root account AT ALL once you've configured the MySQL server, make another account and grant it root-like permissions and ONLY use the root account as a last resort if something breaks to restore everything.
Yes use different accounts for different sites, one account for all clients on one site should be fine i.e. one account for this receipt tracking site, another account for a control panel site, etc.

#6 +Nik L

Nik L

    Where's my pants?

  • Tech Issues Solved: 2
  • Joined: 14-January 03

Posted 04 January 2013 - 14:21

Do not create a MySql user for every site login, if that is what you are asking.

#7 Mr.XXIV

Mr.XXIV

    Shine bright like Iron Man.

  • Tech Issues Solved: 1
  • Joined: 30-April 11
  • Location: Durham, North Carolina
  • OS: OS X Yosemite
  • Phone: iPhone 5s

Posted 04 January 2013 - 14:26

I was recently recommended this, I would prefer something like Symfony as you will need to build for protection and added bonuses if you really need something like this.

#8 Sandor

Sandor

    Neowinian Senior

  • Joined: 28-November 03
  • OS: Win 8.1

Posted 04 January 2013 - 14:28

You should only really need one master user for the mysql database itself. Then use web based forms (in PHP for example) to allow the people to add/delete/update their data. They don't need to have direct access to the database tables to do this. I don't really see the point of having totally distinct tables for each user either. Seems like a lot of duplication and you'll end up with a massive amount of tables.

#9 OP Jose_49

Jose_49

    Neowinian Senior

  • Tech Issues Solved: 2
  • Joined: 30-July 09

Posted 04 January 2013 - 15:48

Thanks to all of the above. Now I have a clear mind.

NEVER use the root account AT ALL once you've configured the MySQL server, make another account and grant it root-like permissions and ONLY use the root account as a last resort if something breaks to restore everything.
Yes use different accounts for different sites, one account for all clients on one site should be fine i.e. one account for this receipt tracking site, another account for a control panel site, etc.


I shall take this recommendation then :)

I don't really see the point of having totally distinct tables for each user either. Seems like a lot of duplication and you'll end up with a massive amount of tables.

:/ There was no other way my logic could function.

I Googled a bit and found that there wasn't any problem having multiple tables. The thing is that it allows flexibility. I didn't see a good way on putting the client info, the receipt #, the quantity, price of the product purchased (because it has a variable price), the current product id, the tax, and whether it was paid, delivered or not. So I could fetch it in a productive way later on....

Anyways, I'm open to suggestions :D

#10 mollick2

mollick2

    Neowinian

  • Tech Issues Solved: 2
  • Joined: 12-June 08

Posted 05 January 2013 - 06:34

I Googled a bit and found that there wasn't any problem having multiple tables. The thing is that it allows flexibility. I didn't see a good way on putting the client info, the receipt #, the quantity, price of the product purchased (because it has a variable price), the current product id, the tax, and whether it was paid, delivered or not. So I could fetch it in a productive way later on....


Multiple tables are fine, in fact you should be using multiple tables, but there's a much better and organized way of using them. You should be using different tables for storing types of data. If I have Users, Customers, and Receipts; I would create a separate table for each one of them. Then I would create two additional tables used for associations, one for Users->Receipts, and one for Customers->Receipts. These associative tables would only store the unique id's for the rows in the other tables.

Not sure if I explained clear enough or not, also not sure if it's quite the same idea as your system. Either way its best to have different table's for different types of data, since there's no sense in storing the same data multiple times.

#11 The_Decryptor

The_Decryptor

    STEAL THE DECLARATION OF INDEPENDENCE

  • Tech Issues Solved: 5
  • Joined: 28-September 02
  • Location: Sol System
  • OS: iSymbian 9.2 SP24.8 Mars Bar

Posted 05 January 2013 - 06:44

Certainly use multiple tables, but not for each user. Say you have 10 users and each user has a separate table, if you want to see all the data from all the users you have to search through 10 tables, vs. just the main table for the type of data you want.

So instead of userA_orders, userB_orders, etc. you just have a single orders table, and store what user created the order in the record you insert into the table.

#12 hjf288

hjf288

    Korean Crazy Man!

  • Joined: 19-April 03
  • Location: United Kingdom

Posted 05 January 2013 - 21:23

1 mysql user per database schema, have as many tables as you want .

#13 paxa

paxa

    Neowinian

  • Joined: 04-June 04
  • Location: so far away....nearly in the end of the world

Posted 05 January 2013 - 21:33

if i've read this right. you should create a function user. one user that can insert, update, or delete records, but not modify the database structure. use that user for any transaction, and the root as a last resort.

#14 OP Jose_49

Jose_49

    Neowinian Senior

  • Tech Issues Solved: 2
  • Joined: 30-July 09

Posted 05 January 2013 - 23:08

Multiple tables are fine, in fact you should be using multiple tables, but there's a much better and organized way of using them. You should be using different tables for storing types of data. If I have Users, Customers, and Receipts; I would create a separate table for each one of them. Then I would create two additional tables used for associations, one for Users->Receipts, and one for Customers->Receipts. These associative tables would only store the unique id's for the rows in the other tables.

Not sure if I explained clear enough or not, also not sure if it's quite the same idea as your system. Either way its best to have different table's for different types of data, since there's no sense in storing the same data multiple times.

Certainly use multiple tables, but not for each user. Say you have 10 users and each user has a separate table, if you want to see all the data from all the users you have to search through 10 tables, vs. just the main table for the type of data you want.

So instead of userA_orders, userB_orders, etc. you just have a single orders table, and store what user created the order in the record you insert into the table.


Now I get it! Yup. Indeed. I know my logic was failing somewhere.

I just need to create a separate column with the current logged in user, and bang it with a WHERE clause to identify the user (*poker face*)
Aaaargh.


Going to work on it right now

Thank you people :D

#15 OP Jose_49

Jose_49

    Neowinian Senior

  • Tech Issues Solved: 2
  • Joined: 30-July 09

Posted 06 January 2013 - 16:15

Just finished modifying everything. Now, I'll have 4 tables, instead of hundreds of them :D