• 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

    • Microsoft has some PC VR games that could be played with it.
    • As such, many developers will start dropping Windows 10 support in their products Hi! Actual developer here. No we won't. It really doesn't work that way simply because most Windows devs don't target to a specific release of Windows unless we're using a feature that only exists IN a specific version, and that's pretty unusual. The biggest example would be MSFT killing off Windows Mixed Reality in Win 11, but most stuff we write for Win 10 will just work fine in Win 11 and vice versa. The vast majority of software doesn't rely on these things and will continue working on any recent version of Windows. Heck some of my software still runs on WinXP. Where Win 10 users will be left behind is software that relies on new features in Win 11 but again, we tend not to use those unless we're writing specific apps that need those features. In fact, the biggest danger area isn't apps, it's drivers as hardware makers focus on new machines more than supporting legacy devices.
    • Google Chrome finally lets you change the position of the address bar on Android by Aditya Tiwari Google is rolling out a small but useful update to Chrome. The update makes the address bar in the web browser more customizable and accessible. You can now place it at the top or bottom of the screen, as you prefer. The ability to change the position of the address bar in Google Chrome has started rolling out and will be available to all users over the coming weeks. You can long-press on the address bar and select the "Move address bar to bottom" option. Alternatively, you can also go to Settings > Address bar to customize it. "Depending on the size of your hand and your device, one address bar position may feel more comfortable than the other," the company said in a blog post. "We designed this update to give you the flexibility to choose your preferred location — so you can browse with more ease." The address bar is where you can type website names you want to visit. It's a crucial part of our browsing experience and also doubles as the search bar in Google Chrome. The browser's Android version has been around since 2012, and the address bar's top position has remained the default for most of its time. Over the years, Google has enhanced the address bar with additional features, such as the ability to automatically hide when scrolling up on a web page, thereby offering more screen real estate. A bottom-mounted address bar could be a better option for users with small hands or those who prefer to use their device with one hand. Interestingly, this is not the first time Chrome has had a bottom address bar, as Google briefly experimented with the feature in the past. The bottom address bar even made its way to Chrome for iOS, where you can long-press the address bar to toggle between the two options or change it in the settings. It's hard to digest that it took Google so long to add a simple feature. The defunct Windows Phone had this feature as far back as 2012, and Chrome's rival Safari added a Bottom layout option in 2021. Nonetheless, the latest update brings Chrome in line with other browsers that offer a bottom address bar on Android.
    • That's just my understanding of the courts situation with the law with of AI and when could be copyrighted. Found this article about how different area's of the world are handling copyrights - https://www.cooley.com/news/in...uts-varies-around-the-world AI seems to be a disruptive technology so far, like the internet was. Whenever a disruptive tech comes out, it takes a while on what societies accepts, adapts, rejects, and how it ultimately pans out.
  • Recent Achievements

    • Week One Done
      DrRonSr earned a badge
      Week One Done
    • Week One Done
      Sharon dixon earned a badge
      Week One Done
    • Dedicated
      Parallax Abstraction earned a badge
      Dedicated
    • First Post
      956400 earned a badge
      First Post
    • Week One Done
      davidfegan earned a badge
      Week One Done
  • Popular Contributors

    1. 1
      +primortal
      616
    2. 2
      ATLien_0
      227
    3. 3
      +FloatingFatMan
      170
    4. 4
      Michael Scrip
      166
    5. 5
      Som
      148
  • Tell a friend

    Love Neowin? Tell a friend!