How to test user's permissions to the database

Question

I have a simple .NET 3.5 app for changing some database fields using an ODBCDataSet. Now the Feature Creep is asking if I can hide or show tabs and other controls based on the user's database permissions.

Ideally, I would like to control the permissions only on the SQL Server using Windows user groups, and the app would not have any built-in authentication or permission system--it simply uses the logged-in user's Windows account for the database connection. So thus it would have to "test" the permissions to determine whether or not to show the tabs for the user. For example, if they have "write" permissions to a certain table, then the tab for editing it would be visible; if not, the tab never loads for them.

That's really the part I need help with: how can I list or test the user's permissions to the ODBCDataSet?

Answer 1

You could always Try/Catch the call to the DB. This would authenticate the user to the DB, then create a Stored Procedure to return all tables accessible.

Answer 2

Are you opposed to having your app aware of the Windows user groups? Generally we use AD groups all the time for security like you speak of on both the database AND in the .NET code. Showing/hiding features is exactly the point. Additionally, even if for some reason they manage to get the feature to show, the database additionally checks their role and can prevent actions.

Personally, I think checking the role membership in .NET code is the easiest solution (you can do this with the IsInRole method).

However, if there are reasons why you cannot or do not want to have the app aware of group names, in case they change, I understand. There's probably not an ODBC method of checking, as any method would most likely be proprietary and/or database dependent (SQL Server, etc.)... other than that, you'd have to write code to attempt an insert/update command on a known test record and see if it comes back with an SqlException I guess.