• 0

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


Question

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

14 answers to this question

Recommended Posts

  • 0
  On 04/01/2013 at 14:01, SuperKid said:

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.

  • 0
  On 04/01/2013 at 14:06, Jose_49 said:

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.

  • 0

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.

  • 0

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.

  • 0

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

  On 04/01/2013 at 14:15, n_K said:

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 :)

  On 04/01/2013 at 14:28, technikal said:

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

  • 0
  On 04/01/2013 at 15:48, Jose_49 said:

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.

  • 0

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.

  • 0

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.

  • 0
  On 05/01/2013 at 06:34, mollick2 said:

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.

  On 05/01/2013 at 06:44, The_Decryptor said:

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

This topic is now closed to further replies.
  • Posts

    • Xbox June Update brings unsynced save management, publisher browsing, and more by Pulasthi Ariyasinghe June has been a busy month for Microsoft, bringing major software upgrades across its product stack. It even announced new hardware for its Xbox lineup, finally entering the handheld gaming space. The company's roundup for this month's new features has now been published, and it's touting a great deal of changes. To start off on the PC side, the Xbox app on the platform now has a section to browse by publishers. The company says that this will let players easily discover more games made by their favorite developers and franchises. Copilot for Gaming also landed in beta form recently, letting users ask AI for help when a game gets too difficult. It's only available for iOS and Android for now, with ROG Xbox Ally support coming later this year. Another feature that will hit the Xbox Ally is the new universal launcher feature for the Xbox app on PC. Microsoft just kicked off Xbox Insider testing for this functionality earlier today. Get all the details here. Over on consoles, the ability to hide system apps, pin favorites to the list, and reduce the number of tiles displayed are now available. Game Hubs also arrived as a fresh feature to easily display relevant information when selecting a game to play, offering data on player stats, achievements, friends currently playing, recent captures, available add-ons, events, and more. Double-tapping the play button will quick-launch the game instead. On both Xbox consoles and in the cloud, a new progress bar will now appear when a save has been left behind on a device in an offline state. "A new progress bar, device names, timestamps, and additional details are now displayed when you have previous game saves on another device in an unsynced state," says the company. Microsoft has also added mouse and keyboard controls as well as touch controls for more cloud games this month. These join the fresh additions that have landed on the 'Stream your own game' collection and the Retro Classics app. Check out the full lists on the announcement page here. On top of all this, Microsoft has also announced that Xbox will be at Gamescom this year. While no details have been announced yet, more announcements from Xbox Game Studios may happen at the major gaming event.
    • Hopefully this is a precurser to them linking other launchers to the Xbox console. With this current gen the Xbox has had dismal sales compared to the competition. If they did support Steam, Epic, Ubisoft Connect, etc etc they'd crush on the next gen battle.
    • My update. Didn't see much point in the top panel since global menu isn't there, so going with a win/kde layout now. Overall, I would say Gnome is a disappointment - it's been 15 years and you still have to rely on a bunch of extensions to get anything useful out of it. At the same time, the way Universal Blue / Bluefin is approaching the desktop feels like what Ubuntu should have started doing five years ago (no wonder the guy I learned about this from used to work for Canonical). Maybe I should have gone with Aurora (the KDE variant), or Bazzite with KDE, but I think I have Gnome where it works for me now.       
    • GOG Galaxy offers connecting of other launchers into theirs. And while idea is interesting, I always had issues with Steam that would just lose connection and just not work. Which was annoying.
    • I remember that during the earlier development of Windows 10 a big deal was made about the Recycle Bin icon(s). https://www.neowin.net/news/wi...pdated-icons-in-this-build/
  • Recent Achievements

    • Dedicated
      Camlann earned a badge
      Dedicated
    • Week One Done
      fredss earned a badge
      Week One Done
    • Dedicated
      fabioc earned a badge
      Dedicated
    • One Month Later
      GoForma earned a badge
      One Month Later
    • Week One Done
      GoForma earned a badge
      Week One Done
  • Popular Contributors

    1. 1
      +primortal
      650
    2. 2
      Michael Scrip
      225
    3. 3
      ATLien_0
      220
    4. 4
      +FloatingFatMan
      146
    5. 5
      Xenon
      136
  • Tell a friend

    Love Neowin? Tell a friend!