2

So I was creating an application that required user log in, with each user having some privilege. Now this model closely resembled that of mysql's database where there can be many user, with each user having their own privileges.

So currently, for each user of my application, i am running a create user query and then grant them required permission and its working perfectly fine as of now

My Question

How good is my current approach?

I know, I can create my own user table and privilege table and then write queries accordingly, but is there any problem with my current approach,(after all thats what mysql does behind the scene, right?)

Ayush choubey
  • 606
  • 6
  • 23
  • If I understood it correctly, you're creating a DB user for every user of your application! IMO, You approach is horribly wrong! An application user should not be equated with the concept of user in databases. – Incognito Mar 03 '14 at 10:03
  • I guessed something like that, but i want to know, how exactly it is bad. Bad in terms of performance or something else? I would be doing the samething the mysql does with create user command right? – Ayush choubey Mar 03 '14 at 10:08
  • great question I've been wondering the same thing. You saved me so much trouble! – Clocker Jun 01 '14 at 15:58

1 Answers1

2

There can be so many dis-advantages of this approach, few are below-

  1. As user rights are managed in mysql in myisam tables, so if you will insert users very frequently then locking can be here.

  2. Privileges will not be effective till you flush them, and flush command lock the tables and can block all you application queries for some time.

  3. You can't save additional important information of user in these system tables, while in your own user table you can save user name, email, creation time, password update time etc.

  4. Easy management as you can create limited profile in mysql user table and map a group of users with these predefined profiles.

  5. By this you can allow a user to connect db but restrict to do some specific task in applicatioin as you can join this tables with active/inactive status in you queries.

Zafar Malik
  • 6,734
  • 2
  • 19
  • 30