• 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

    • Maybe, just maybe... and it isn't you... there are some people who like the Windows 11 UI (for whatever reason) and want a better backend.
    • Gundams to arrive in Call of Duty: Mobile with new mech mode and unique third-person combat by Paul Hill Activision has announced that Call of Duty: Mobile will see the launch of Season 6 “Gundams Arrive” on July 2 at 5PM Pacific Time. This major collaboration between the world’s most popular FPS franchise and the Gundam franchise will introduce a new, limited-time mode called Gundam Team Deathmatch where 8 players will face off 4v4 and pilot Gundam-themed operators such as Ethan - Freedom Gundam, Reaper - Sazabi Gundam, Proton - v Gundamo, Deathscythe Gundam (EW). When playing in this game mode, players will notice a switch from the usual first-person view to the third-person perspective. The new game mode will also feature specific abilities and weapons that are unique to Gundam suits rather than player loadouts. The new Gundam Team Deathmatch mode will be played on a new map called interstellar space station, which has been designed for this mode. When playing, you’ll discover that the mech suits offer specialized mobility such as dodge, sprint, and vertical jets. In the post-match, players will be able to watch Gundam operator animations with unlockable rewards for viewing. You can unlock more animations by participating in Gundam Team Deathmatch, normal Multiplayer, and Battle Royale modes. There will also be Gundam-themed in-game events, such as Survival of the Fittest, which will give players free rewards like the new legendary weapon J358 — Fin Funnel v Gundam, Urban Tracker — Defense Force, Cyro Bomb — Haro (reskin), Emote — Haro Team, new camos, and more. Players will also be able to obtain a variety of items through Season 6 Battle Pass free and premium tiers, including sci-fi-themed Operators and Weapon Blueprints. Players on the free tier will get access to the bolt-action 3-Line Rifle based on a World War II design and is capable of inflicting high damage with high accuracy. Free tier players will also have the chance to earn other rewards such as Skins, Weapons Blueprints, Vault Coins, and more. Players looking to spend money can get the Premium Pass. These players will have a chance to get all of the content from Season 6 including tactical warriors like Silver — Chrome Dome Reskin, Misty — Science Pilot, Atlas — Dust Ranger, and The Marshal — Rock Hound; and Weapon Blueprints like the BP50 — Pathripper, Oden — Maevwat Technical, PDW-57 — Rocket Re-Entry, BY15 — Dark Moon, and the 3-Line Rifle — Geo Thermal Line, based on the new Season 6 weapon. There’s also Battle Pass Subscription which gives you additional monthly rewards along with a 10% boost to Player and Weapon XP, discount coupons, and limited discounts on 10x crate pulls. Activision also stated that Mythic Drops are returning to the Mythic store and that Battle Pass Vault is getting Season 9 — Zombies Are Back (2022) and Season 6 — Templar's Oath (2023).
    • I managed to buy the original Hellblade: Senua’s Sacrifice through the ps app on my phone even though it was not showing up in the ps store when browsing from my ps5 it was buggy but more or less O.K.ish to play… I wonder if this original added version is funny fixed up for ps5 play
    • Download old Windows Startup Sounds @ https://www.winhistory.de/more/winstart/winstart_en.htm
  • Recent Achievements

    • Week One Done
      pcdoctorsnet earned a badge
      Week One Done
    • Rising Star
      Phillip0web went up a rank
      Rising Star
    • One Month Later
      Epaminombas earned a badge
      One Month Later
    • One Year In
      Bert Fershner earned a badge
      One Year In
    • Reacting Well
      ChrisOdinUK earned a badge
      Reacting Well
  • Popular Contributors

    1. 1
      +primortal
      550
    2. 2
      ATLien_0
      205
    3. 3
      +FloatingFatMan
      173
    4. 4
      Michael Scrip
      151
    5. 5
      Som
      131
  • Tell a friend

    Love Neowin? Tell a friend!