-1

I've got a schema in MySQL that stores information for various office functions. I want to make sure warehouse folks can't get into customer service tables and vice-versa. Each user has a login on Windows Server, and they belong to user groups like "Warehouse" and "Customer Service".

MySQL is not using Windows authentication (because you have to pay for that extension). Instead it has users like "warehouse" and "customerservice" which have access to their respective table sets.

Intranet php pages give access to the MySQL tables through forms, etc.

To accomplish the goal of giving the warehouse folks access to only their data, and the same with the customer service folks, I'm considering the following:

  • Create a "warehouse" directory, and put the applicable intranet pages in it; do the same with a "customerservice" directory.
  • Give only the "warehouse" user group access the "warehouse" directory. Same idea with "customerservice".
  • Have all php pages in the "warehouse" directory hit MySQL using the "warehouse" sign-on in MySQL. Same idea with "customerservice".

This way, I can add users to groups in Windows and they will have access to only the data they need. Basically, I'm correlating MySQL users with Windows groups.

What other ways have you used to handle this?

Jonathan M
  • 17,145
  • 9
  • 58
  • 91
  • It is the *web application* that connects/authenticates to the RDBMS, not the users themselves; it's rarely sensible for its sessions with MySQL to be conducted using different credentials depending on the user—a single database login for the application should normally suffice. The application itself would then authenticate the users (perhaps using [Windows authentiation](https://msdn.microsoft.com/en-us/library/ff647405.aspx)) and restrict access to its resources accordingly. – eggyal Feb 16 '15 at 23:28

1 Answers1

1

I come from a MS shop, so I'd grab the current user with a native .NET function (like this), and reach out to AD, then serve partials that apply only to that user.

Since you're on the PHP side, I'd use these to do something similar. The first comment seems to be doing identity and group membership verification, too.

Community
  • 1
  • 1
Michael McPherson
  • 469
  • 6
  • 17