• 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

    • Sounds like a tool is needed to backup drivers from Win Update for older devices. Anyone got a rough idea besides doing a manual backup of driverstore?
    • Stylish Samsung Galaxy S25 Edge gets 20% discount sending it to lowest price by Paul Hill If you’re in the market for a powerful, stylish phone, then you may be interested in the Galaxy S25 Edge, which is now reduced to $969.99, down 20% from the $1,219.99 list price. This is the lowest price that the device has been at on Amazon. You can get the device at this price if you go for the Titanium Icyblue or Titanium Silver editions. These models are the top-end S25 Edge variants with 512GB of storage. While this Samsung device may be about as thick as a pencil, it’s constructed from titanium and uses Corning Gorilla Glass Ceramic 2 on the display. This should ensure durability, despite its thin design. The thickness of this phone is just 5.8mm, and it weighs 163g. Camera capabilities and AI enhancements The Galaxy S25 Edge's camera system is impressive. It has a 200MP main camera with optical image stabilization and a 2x optical quality zoom. This feature magnifies the image using optical mechanisms in the camera lens so that the zoomed image retains its original clarity, detail, and resolution of the scene. On the back, there is also a 12MP ultrawide camera with autofocus and on the front, there’s a 12MP camera. Like many phones coming out now, there are AI-powered camera features such as Night Video, Best Face for group shots, Portrait Selfie, and Gallery Search by description. Notably, there is no dedicated telephoto lens like in the S25+ and S25 Ultra. This is a tradeoff that the company decided to make to achieve its ultrathin design. Telephoto lenses are camera lenses that are designed to make distant objects appear closer and larger in your photos and videos. The absence of this telephoto lens may be a deal breaker for those looking to use this camera for professional shots; otherwise, it’s still very impressive, especially with that 200MP main camera. Performance, display, and integrated AI features The S25 Edge is powered by the Snapdragon 8 Elite chipset, specifically the 3-nanometer Qualcomm SM8750-AB. It has an octa-core CPU (2x4.47 GHz Oryon V Phoenix L + 6x3.53 GHz Oryon V2 Phoenix M) and an Adreno 830 GPU. This model's display is a 6.7-inch QHD+ Dynamic AMOLED 2X with a 120Hz adaptive refresh rate and high brightness. This device has plenty of other AI features too, outside of the camera, including Cross App Action, Audio Eraser for videos, Now Brief/Now Bar for personalized information, and of course, Google Gemini. With 12GB of RAM, the specs on this phone are really very good. It should be capable of doing most tasks you throw at it, including gaming. You may wonder if it will handle games well, given that the thin design means it could overheat. Samsung has already thought about this and included a reconfigured vapor chamber that helps to keep the phone cool. Battery life and charging speed: The main compromise Aside from the removal of the telephoto lens, this phone also has a drawback in terms of battery life. Its 3,900 mAh battery capacity means it’s smaller than many other devices, even compared to budget phones. Another disappointing aspect is the charging, where the wired charging speed is limited to 25W—that’s lower than what you find on very affordable Xiaomi POCO devices. This battery limitation might make this device a bad choice if you’re away from a charger for long periods. One affordable way to mitigate this downside is by carrying a portable charger, but still, it’s ironic that you buy a thin phone to be lighter and then end up carrying a brick-sized charger around with you. You could also just bring your charger and plug it into an outlet. Is the Galaxy S25 Edge the smart buy for you? If you’re someone who wants a sleek, lightweight phone that has a premium build and can easily slice through tasks you throw at it, then the Galaxy S25 Edge is very likely for you. It’s also great for anyone looking for a standout camera that doesn’t need a telephoto lens. However, if you need a device with all-day battery life because you’re going camping or to the middle of nowhere, then you could probably find a better fit with a longer battery life. If this device is for you, check out the links below. Samsung Galaxy S25 Edge (Titanium Icyblue): $969.99 (Amazon US) / MSRP $1,219.99 Samsung Galaxy S25 Edge (Titanium Silver): $969.99 (Amazon US) / MSRP $1,219.99 This Amazon deal is US-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 US deals page here. Get Prime (SNAP), Prime Video, Audible Plus or Kindle / Music Unlimited. Free for 30 days. As an Amazon Associate, we earn from qualifying purchases.
    • I got too many apps I use actively. Theres been too often I completely blank on the name of the application and had to manually look through the list for it. Now I'm using Start11 and got my apps sorted in the Start menu by categories so that if I'm looking for Krita i can find it under "Art editors". If MS are going to change it, they should consider making automated categories. Seen some Linux distros do that. But if they do, theres probably gonna be a lot of wrongly categorized apps though, unfortunately.
    • LAV Filters 0.80.0 by Razvan Serea LAVFSplitter is a multi-format media splitter that uses libavformat (the demuxing library from ffmpeg) to demux all sorts of media files. LAV Splitter is a Souce Filter/Splitter required to demux the files into their separate elementary streams. LAV Audio and Video Decoder are powerful decoders with a focus on quality and performance, without any compromises. Supported Formats: MKV/WebM, AVI, MP4/MOV, MPEG-TS/PS (including basic EVO support), FLV, OGG, and many more that are supported by ffmpeg! LAV Filters are based on ffmpeg and libbluray and is aimed to offer a all-around solution to perfect playback of file-based Media as well as Blu-rays. LAV Filters 0.80.0 changelog: LAV Splitter NEW: Introduced the IURLSourceFilterLAV interface to allow opening URLs with custom user agent and referrer NEW: Added support for WebP images Changed: Increased the length of the advanced subtitle selection field, so its no longer cut off after 255 characters Changed: Improved buffering behavior on badly interleaved video files Fixed: Audio streams with an unknown/unsupported codec are no longer selected for playback, as long as others are present Fixed: Improved accuracy of reported FPS from AviSynth scripts LAV Video NEW: D3D11 support for HEVC 4:2:2 and 4:4:4 hardware decoding NEW: Dolby Vision extension metadata is exported for renderers to use Changed: Added additional media types to support more video streams Changed: Updated dav1d for significant AV1 decoding improvements Fixed: Improved handling of H.264 4:4:4 files encoded by certain versions of x264 Fixed: VP9 DXVA2/D3D11 decoding could result in artifacts on some clips Fixed: Decoding ProRes reports more accurate color details LAV Audio Changed: Added support for additional ADPCM audio codecs Download: LAV Filters 0.80.0 | 15.5 MB (Open Source) View: LAV Filters Website | Screenshot Get alerted to all of our Software updates on Twitter at @NeowinSoftware
  • Recent Achievements

    • First Post
      emptyother earned a badge
      First Post
    • Week One Done
      Crunchy6 earned a badge
      Week One Done
    • One Month Later
      KynanSEIT earned a badge
      One Month Later
    • One Month Later
      gowtham07 earned a badge
      One Month Later
    • Collaborator
      lethalman went up a rank
      Collaborator
  • Popular Contributors

    1. 1
      +primortal
      675
    2. 2
      ATLien_0
      277
    3. 3
      Michael Scrip
      220
    4. 4
      +FloatingFatMan
      168
    5. 5
      Steven P.
      161
  • Tell a friend

    Love Neowin? Tell a friend!