Assume SQL Server Rights for apps, any cons?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I’ve got a requirement from one of my clients and I am not sure if there are
any cons. I am developing a data management software for him. He
administrates the underlying SQL Server 2000 database by himself. Now he
wants that my software assumes the user rights automatically from the SQL
Server. Please let me give you a short example:

Lets say we have a table called Customers. User A has the following rights
- select (all columns)
- update (only address fields)
- insert (deny)
- delete (deny)

My application offers a GUI to manage customers. If user A opens the form
the app should disable/hide the delete and insert buttons and disable all
fields user A is not allowed to edit.

My client wants to centralize the rights management what is not a bad idea.
He wants to define roles and rights dynamically. He has some other
applications who also read and edit data of this database so he doesn’t want
to give users the right to edit all values of tables and fields.

Do you see any cons?

Look forward to hearing any cons :-)

Stephan
 
Since your customer is the sysadmin on his SQL Server box, you need to
make sure that he is creating roles in his database and assigning
permissions only to roles, not to individual users. That way users can
be added/removed from roles based on the level of access he wants them
to have. Even if the role has only one user, he needs to set it up
this way. At runtime when a user first connects, your application can
retrieve the user's role membership, which you can cache locally in
your app and then use to enable/disable UI. If you try to do it any
other way, you'll end up with a big, hairy, un-maintainable mess on
your hands.

--Mary
 
Back
Top