• 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

    • Can you point out where his walkthrough of Mozilla's finances are lies?
    • Advanced Renamer 4.12 by Razvan Serea Advanced Renamer is a program for renaming multiple files and folders at once. By configuring renaming methods the names can be manipulated in various ways. It is easy to set up a batch job using multiple methods on a large amount of files. The 14 different methods enables you to change the names, attributes, and timestamps of files in one go. Free for personal use. You can download and use Advanced Renamer for FREE for any personal use. If you use Advanced Renamer for a business you can download and try it out for free. To continue using it, you need to buy a life time license. Image files This mass file renamer is a great utility for organising digital pictures for both professionals and beginners. The thumbnail mode lets you display thumbnails directly in the file list giving you maximum control of the renaming process. With this program you can rename all your photos in a snap. GPS data If your image files contain GPS data you can add the name of the city and the country where the picture was taken. Coordinates are used to lookup city, country, and state names from a database containing more than 100,000 cities around the globe. Music files MP3 and other music files often have messed up names and contain weird characters. With Advanced Renamer you can change the names of your favourite music files to more suitable names using the built-in ID3 functions. Video files Ever wanted to add the codec or the resolution of a video to the filename? With the video tags you can add various information about video and audio content to the names. TV shows Add episode title or airdate to video files containing TV Shows after importing show information from the tvmaze.com website. Advanced Renamer 4.12 changelog: Upgraded regular expression engine for use in renaming methods Replace method: Named group substitution is now supported in regular expressions (e.g., (?.*) and ${name}) Program is now less likely to crash when config file is corrupted Fixed an edge case bug in List Replace method Fixed large file support in ExifTool integration Improved reading XMP metadata from MP4 files ExifTool field names sometimes showed up in lists where they were not supported Will no longer show error "Extension changed" when new name is blank Disc and DiscCount metadata now correctly recognized for MP3 files Item details would sometimes show the same fields multiple times Additional metadata fields is now supported for MP4 files: AudioFormat, AudioChannels, AudioSampleType, AudioSampleRate, CompressionID, CompressionName, BitDepth, XResolution, YResolution More robust handling of MP4 files with corrupted data Added support for extracting metadata from some older QuickTime .mov files Fixed an issue reading GPS metadata from image and video files, when formattet in a certain way Improved MKV file metadata support Added support for metadata fields AudioFormat, AudioChannels, and AudioSampleRate for AVI files Import from CSV did not remember the last used column index for original filename Fixed name collision rule "Append image sub second" When using name collision rule "Append image sub second", the rule will now be applied to all items in the list with the same name Improved performance for JPEG files containing long XMP Extended metadata MacOS: Item preview panel will now use embedded thumbnails for JPEGs for better performance Download: Advanced Renamer 4.12 | Portable ~12.0 MB (Free for personal use) Link: Advanced Renamer Home Page | Advanced Renamer Screenshot Get alerted to all of our Software updates on Twitter at @NeowinSoftware
    • I had that problem, too. I sold my motherboard on eBay. After a user bought it, he was complaining that the RAM pins were bent. I'm like, how tf did that happen. He hasn't replied in 2 weeks or ever, eBay gave me back my money.
    • it looks the same only smaller icons and more clutter
    • PDF Shaper 15.2 by Razvan Serea PDF Shaper is a set of feature-rich PDF software that makes it simple to split, merge, watermark, sign, optimize, convert, encrypt and decrypt your PDF documents, also delete and move pages, extract text and images. The program is optimized for low CPU resource usage and operates in batch mode, allowing users to process multiple PDF files while doing other work on their computers. PDF Shaper is available in three editions - Free, Premium and Ultimate. Compare and pick edition which is suitable for you. Compatible with Windows 7, 8, 10, 11. Features: Extract text, images of other objects from single or multiple PDFs Convert PDF to Word RTF or image, or convert image to PDF Extract pages from PDF and save them as separate PDF files Combine several PDF files into single PDF Encrypt and decrypt PDF with password, set user permissions Rotate, crop and normalize pages, set meta data Add watermark or remove images from PDF Benefits: Easy-to-use, intuitive user interface Low CPU resource usage during any process, including conversion Free for personal use and for any non-commercial organization Supports Unicode characters Supports batch processing for any operation Small installation size PDF Shaper 15.2 changelog: Updated translations. Improved table positioning for better layout accuracy (DOC to PDF). Enhanced image resizing to more accurately reflect DPI settings (Image to PDF). Improved text positioning for consistent formatting (TXT to PDF). Optimized overall performance on 64-bit systems. Enhanced support for Unicode text documents. Fixed an issue where font settings were not applied correctly to text files Download: PDF Shaper 15.2 | 7.9 MB (Free for personal use only) Link: PDF Shaper Home Page | Screenshot Get alerted to all of our Software updates on Twitter at @NeowinSoftware
  • Recent Achievements

    • Reacting Well
      Alan- earned a badge
      Reacting Well
    • Week One Done
      IAMFLUXX earned a badge
      Week One Done
    • One Month Later
      Æhund earned a badge
      One Month Later
    • One Month Later
      CoolRaoul earned a badge
      One Month Later
    • First Post
      Kurotama earned a badge
      First Post
  • Popular Contributors

    1. 1
      +primortal
      493
    2. 2
      ATLien_0
      267
    3. 3
      +FloatingFatMan
      224
    4. 4
      +Edouard
      199
    5. 5
      snowy owl
      141
  • Tell a friend

    Love Neowin? Tell a friend!