• 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

    • Adobe Acrobat Reader DC 2025.001.20531 by Razvan Serea Adobe Acrobat Reader DC software is the free, trusted standard for viewing, printing, signing, and annotating PDFs. Its the only PDF viewer that can open and interact with all types of PDF content – including forms and multimedia. It’s connected to Adobe Document Cloud – so you can work with PDFs on computers and mobile devices. Adobe Document Cloud is a revolutionary, modern and efficient way to get work done with documents in the office, at home or on-the-go. At the heart of Document Cloud is the all-new Adobe Acrobat DC, which will take e-signatures mainstream by delivering free e-signing with every individual subscription. Document Cloud includes a set of integrated services that use a consistent online profile and personal document hub. With Adobe Document Cloud, people will be able to create, review, approve, sign and track documents whether on a desktop or mobile device. Businesses will be able to take advantage of Document Cloud for enterprise which provides enterprise-class document services that integrate into systems of record such as CRM, HCM, CLM, and CMS, adding speed, efficiency and transparency to getting business done with documents. Adobe Acrobat Reader DC new feature highlights: Work with PDFs from anywhere with the new, free Acrobat DC mobile app for Android or iOS. Select functionality is also available on Windows Phone. Use the new Fill & Sign tool in your desktop software to complete PDF forms fast with smart autofill. Download the free Adobe Fill & Sign mobile app to add the same option to your iPad or Android tablet device. Save money on ink and toner when printing from your Windows PC. Store and access files in Adobe Document Cloud with 5GB of free storage. Get instant access to recent files across desktop, web, and mobile devices with Mobile Link. Sync your Fill & Sign autofill collection across desktop, web, and iPad devices. Adobe PDF Pack premium features includes: Convert documents and images to PDF files. Use your mobile device camera to take a picture of a paper document or form and convert it to PDF. Turn PDFs into editable Microsoft Word, Excel, PowerPoint, or RTF files. Combine multiple files into a single PDF (web only). Get signatures from others with a complete e-signature service. Send, track, and confirm delivery of documents electronically instead of using fax or overnight services (tracking not available on mobile). Store and access files online with 20GB of storage. Download: Adobe Acrobat Reader DC 64-bit | 626.0 MB (Freeware) Download: Adobe Acrobat Reader DC 32-bit | 516.0 MB Link: Adobe Acrobat Reader DC Home Page | Release Notes | Screenshot Get alerted to all of our Software updates on Twitter at @NeowinSoftware
    • Do not make me report you. You've been warn
    • It's back online now
    • Audacity 3.7.4 by Razvan Serea Audacity is a free, open source digital audio editor and recording application. Edit your sounds using cut, copy, and paste features (with unlimited undo functionality), mix tracks, or apply effects to your recordings. The program also has a built-in amplitude-envelope editor, a customizable spectrogram mode, and a frequency-analysis window for audio-analysis applications. Built-in effects include bass boost, wah wah, and noise removal, and the program also supports VST plug-in effects. You can use Audacity to: Record live audio. Record computer playback on any Windows Vista or later machine. Convert tapes and records into digital recordings or CDs. Edit WAV, AIFF, FLAC, MP2, MP3 or Ogg Vorbis sound files. AC3, M4A/M4R (AAC), WMA and other formats supported using optional libraries. Cut, copy, splice or mix sounds together. Numerous effects including change the speed or pitch of a recording. Write your own plug-in effects with Nyquist. And more! See the complete list of features. Audacity 3.7.4 changelog: Fixed a crash when closing a large unsaved project Fixed a crash when using real-time effects that activate delay compensation Fixed issue where Studio Fade Out creates a new clip when applied at the end of a clip Fixed incorrect waveform rendering on clipped audio Fixed unintended deletion of a clip when joining two clips with pitch adjustment Effect preview now works when the track is muted Fixed possible incorrect calculations in the Hamming window derivative (Thanks, @witwald!) Fixed compilation issues on legacy macOS (Thanks, @barracuda156!) Download: Audacity 64-bit | Standalone ~20.0 MB (Open Source) Download: Audacity 32-bit | Standalone View: Audacity Home Page | Release Notes | Screenshot Get alerted to all of our Software updates on Twitter at @NeowinSoftware
    • Turn your book ideas into passive income with Youbooks AI Book Generator by Steven Parker Today's highlighted deal comes via our Apps + Software section of the Neowin Deals store, where you can save 90% off a lifetime subscription to Youbooks. Break free from writer's block and fast-track your publishing journey with Youbooks, the AI-powered platform that transforms your concepts into polished, publish-ready non-fiction books. Whether you're a subject matter expert, content creator, or entrepreneur, Youbooks empowers you to craft comprehensive manuscripts up to 300,000 words, tailored to your unique voice and backed by thorough research. Why choose Youbooks? Multi-AI Collaboration: Leverages the strengths of multiple AI models, including ChatGPT, Claude, Gemini, and Llama, to produce nuanced and coherent content. Extensive Word Count: Generate books up to 300,000 words, surpassing the limitations of other AI writing tools. Built-in Internet Research: Youbooks performs real-time web searches during composition, integrating up-to-date facts, statistics, and news to ensure your content is current and accurate. Customizable Tone and Style: Define your preferred writing style or provide samples for the AI to emulate, ensuring consistency throughout your book. Incorporate Personal Research: Upload your own documents, transcripts, or memos to guide the AI, grounding your book in factual information. Comprehensive Workflow: Each book undergoes over 1,000 steps, from ideation to refinement, resulting in a cohesive and professional manuscript. Full Commercial Rights: Retain complete ownership of your content, allowing you to publish, distribute, or sell your book without restrictions. What's Included in the Lifetime Deal? Lifetime Access: One-time payment for perpetual use of Youbooks. Monthly Credits: Receive 150,000 monthly credits, usable for writing and source uploads. Credit Usage: Approximately 1 credit per delivered word or uploaded source word. Customization Capacity: Store up to 100 style samples and 100 source documents. Future Updates: Access to all upcoming plan enhancements. Youbooks is ideal for Publishers: Create books on niche topics, emerging trends, and timely news stories. Subject Matter Experts: Transform your knowledge into authoritative books. Content Creators: Repurpose videos, blogs, or podcasts into comprehensive written content. Entrepreneurs: Establish thought leadership and generate leads with insightful publications. Educators and Coaches: Develop course materials or guides to enhance your offerings. How it works Input Your Idea: Provide a brief or detailed description of your book's subject. Customize Your Preferences: Set your desired tone, style, and upload any supporting documents. Generate Your Book: Let Youbooks' AI craft your manuscript, ready for download in formats like DOCX, EPUB, or Markdown. Get your book in different formats PDF: The best format for viewing on any device or printing. EPUB: Great for e-readers and most ebook platforms. Word (.docx): Allows easy editing and formatting adjustment. Markup (.md): Great for transforming to other formats. However, to get your book ready for distribution, you will have to convert it to a format specified by your Publisher. For POD titles, this usually involves using a MS Word template and printing it to PDF. For ebook titles, you might be able to edit and upload the EPUB file we will provide to you. Discover some of the premium books created using Youbooks NOTE: Codes are stackable up to 10X. Customers wanting more than 150K credits per month can buy multiple codes to get an additional 150K credits per month for each code purchased. Good to know Length of access: lifetime Redemption deadline: redeem your code within 30 days of purchase Access options: desktop or mobile Available to NEW and EXISTING users Updates included Have questions on how digital purchases work? Learn more here Learn more about our Lifetime deals here! A lifetime subscription to Youbooks normally costs $179, but you can pick this up for just $49 for a limited time - that represents a saving of $491 (90% off). For a full description, spec, and terms, click the link below. Get Youbooks (lifetime plan) for just $49 (was $540) Although priced in U.S. dollars, this deal is available for digital purchase worldwide. We post these because we earn commission on each sale so as not to rely solely on advertising, which many of our readers block. It all helps toward paying staff reporters, servers and hosting costs. Other ways to support Neowin Whitelist Neowin by not blocking our ads Create a free member account to see fewer ads Make a donation to support our day to day running costs Subscribe to Neowin - for $14 a year, or $28 a year for an ad-free experience Disclosure: Neowin benefits from revenue of each sale made through our branded deals site powered by StackCommerce.
  • 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
      494
    2. 2
      ATLien_0
      268
    3. 3
      +FloatingFatMan
      224
    4. 4
      +Edouard
      199
    5. 5
      snowy owl
      141
  • Tell a friend

    Love Neowin? Tell a friend!