• 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

    • Hello, The thing about Thunderbolt 2/3/4 PCIe cards is that in additional to the signals carried over the PCIe slot they are plugged into they need to send additional signals to the motherboard for which there is no standardized connection set of connections on the PCIe bus.  To get around this limitation, motherboard manufacturers can include a separate Thunderbolt header. which is sometimes labeled as JTBT1 or TBT1 on the motherboard.  The Thunderbolt PCIe card has a corresponding header on it, and comes with a proprietary cable to connect between the card and motherboard. While the additional signals that need to be sent over the Thunderbolt header are somewhat standardized, motherboard manufacturers are also free to implement whatever custom vendor-specific additions like like, like allowing a the system to recognize an on/off button on a Thunderbolt dock they alone offer to power up the system, or recognize what an external peripheral has been plugged in or unplugged from the Thunderbolt port.  Features like this, plus the lack of requirements for standardized features, or even a standard physical layout for the Thunderbolt header, mean that manufacturers can implement what are Thunderbolt PCIe cards that are essentially proprietary in that all features only work with their motherboard and even then only when their custom cable is used between their motherboard and their Thunderbolt PCIe card.  There may be additional cables required in order to route a video card's signals through the Thunderbolt port as well. Because of this, you need to go with the motherboard manufacturer's Thunderbolt PCIe card, unless you want to get a different brand, build a custom cable to connect it to your motherboard, and potentially give up features like hot-plugging devices to it. I believe @Nik Louch identified a card which will work with your motherboard.  You can always double-check with the motherboard manufacturer just to be sure, or find out if there are any issues or limitations to the combination of your motherboard and the card. If you purchase a used Thunderbolt PCIe card, make sure to check with the seller if it comes with all of the cabling necessary to use it, otherwise you may end up having to purchase that separately as well. Regards, Aryeh Goretsky  
    • ChromeOS M137 goes stable, bringing new Face Control policy and more by David Uzondu Google has brought ChromeOS M137 to the stable channel, and it includes a few focused updates for users and IT admins. A key change is a new policy for managing big groups of Chromebooks. The face control accessibility tool, which Google also updated back in ChromeOS M135, now gets a vital control for managers. A new policy called FaceGazeEnabled lets them switch the feature on or off across a whole school or company. The update also brings a new audio feature called crosstalk cancellation. It aims to create a better sound experience using just the Chromebook's built-in speakers. The software processes audio to make it seem like it is surrounding your head, not just coming from two small points. This tries to copy the feel of a surround sound system or good headphones. Any audio gets a boost, but you will notice it most when watching movies or playing games with directional sound. More accessibility tools have arrived, too. ChromeVox now has a direct keyboard shortcut, Search + O + C, that displays spoken text as braille captions on a connected display. For the poor souls in IT, troubleshooting got a little less painful as well. A new event-based log collection system, when enabled by an admin, will automatically upload relevant logs when something specific fails, like an OS crash or a botched update. Instead of digging through mountains of data, administrators get targeted reports sent straight to them. Here's how to enable it: Turn on the Device system log upload setting. Turn on OS update status reporting—For the Report device OS information setting, select OS update status. Turn on device telemetry reporting on crash information—For the Report device telemetry setting, select Crash information. Google also keeps things sane by limiting these targeted uploads to just twice a day per device. As usual, the update is rolling out slowly. If you do not see ChromeOS M137 for your machine yet, just be patient. This phased release lets Google find and address any issues before the update gets to everyone.
    • LG gram Book 15U50T: Is this lightweight laptop the right upgrade for you? by Paul Hill If you’re in the UK looking for a new mid-range laptop that won’t feel underpowered, check out the LG gram Book 15U50T now because it’s at its all-time lowest price on Amazon UK thanks to a 14% discount from its £699.99 RRP. You can get it now for just £599.99 (Buying link at the end). At this price, the laptop definitely makes this mid-range option much more appealing, it’s also pretty new having only come out in January 2025, so you’re definitely getting more value for your money. The delivery is free and will take a few days to arrive unless you take advantage of a Prime member trial and get it next-day in time for Father’s Day. LG gram Book 15U50T: Key features and who it's for The LG gram Book 15U50T features a 15.6-inch Full-HD (1920x1080) anti-glare IPS display, making it ideal for use in well-lit areas as you won’t see yourself staring back. It’s powered by an Intel Core i5 processor (1334U), 16GB of RAM, and has a very fast 512GB NVMe Gen4 SSD. In my opinion, the storage might be a bit tight for some users; however, the device comes with two M.2 slots if you want to upgrade the storage. The LG gram Book 15U50T is ideal for students or professionals who need a device to carry with them out and about. It has an ultra-lightweight design and weighs just 1.65kg - that’s not too far off a similarly sized MacBook Air, but for a fraction of the cost. In terms of ports, there is an HDMI port, two USB-A ports, and two USB-C ports. There's also a 3.5mm headphone jack if you need to plug in headphones. Other noteworthy details about this laptop include that it's running Windows 11 Home with Copilot integration, it has a HD webcam with a privacy shutter, it uses Dolby Atmos audio for immersive sound, and it has a unique feature called gram Link for multi-device (including Android and iOS) connectivity. Should you buy it? If you are a student or a professional that won’t be doing heavy gaming, or using other super intensive applications, this laptop is a solid pick. It’s lightweight - so easy to carry around, it has an anti-reflective screen - so good in well-lit environments; and it features upgradeable storage slots if 512GB is not enough space. On the downside, this laptop has a mid-range processor that could limit your ability to use high-end professional tools. Another thing I’m not really a fan of here is how opaque LG has been with the battery life. As a portable laptop, you’re obviously going to want to take it on the go where you don’t have a charger handy, but all LG says about the battery is that it has a capacity of 51Wh. According to some online sources, variants of this laptop manage about 7 to 10 hours, so if you need a super long battery life, you might be better off with something like a MacBook Air. So should you buy it? If you’re not going to be doing anything super intensive, but can’t stand underpowered and slow budget laptops then this could be the ideal laptop for you. The £100 discount makes it even more appealing! LG gram Book 15U50T: £599.99 (Amazon UK) / RRP £699.99 This Amazon deal is U.K. specific, and not available in other regions unless specified. If you don't like it or want to look at more options, check out the Amazon UK deals page here. Get Prime, Prime Video, Music Unlimited, Audible or Kindle Unlimited, free for the first 30 days As an Amazon Associate we earn from qualifying purchases.
    • Totally different vehicles. Uber has partnered with Waymo for level 5 autonomous vehicles. Waymo has completed 10 million trips and to date, there have been 696 accidents in 4 years and of those 16 of them appear to have been due to an error by the car. In total airbags have only been deployed 38 times. The technology should always be under review and continued to be improved on, but this is a totally different animal to Tesla FSD PS. no I don't work for them etc. I am an analyst for a market intelligence firm and we have a lot of interest from clients looking at the connected car space for advertising etc. so I have studied them
    • Just seems ridiculous that my 2019 imac is no longer getting the latest macOS. Nowadays a 6 year old PC is still a fairly powerful computer. It could easily run the new OS. I also have a 2015 macbook pro with a 4th gen Intel cpu. Its running sequoia (via OCLP) and can still cope not too bad. My imac is way more powerful. Really puts me off ever buying a mac again, with such short support. Open Core legacy again for me then.
  • Recent Achievements

    • Week One Done
      somar86 earned a badge
      Week One Done
    • One Month Later
      somar86 earned a badge
      One Month Later
    • Apprentice
      Adrian Williams went up a rank
      Apprentice
    • Reacting Well
      BashOrgRu earned a badge
      Reacting Well
    • Collaborator
      CHUNWEI earned a badge
      Collaborator
  • Popular Contributors

    1. 1
      +primortal
      504
    2. 2
      ATLien_0
      260
    3. 3
      +Edouard
      186
    4. 4
      +FloatingFatMan
      174
    5. 5
      snowy owl
      132
  • Tell a friend

    Love Neowin? Tell a friend!