Question Where should I check user permissions

[keb1965]

Should I be checking permissions in my stored procedures or should I be checking them in my application?

For example, lets say there is a permission for managing user accounts. The permissions might be: View, Create, Edit, Delete.

Is it better to check those permissions in my application and allow only stored procedures to be called that do the allowed actions or should I verify the user's permissions in the stored procedure before allowing the user to view, add, update or delete the record?

As an FYI, I have over 200 user permissions that will be managed in this way.

 

[jmcilhinney]

You should check permissions at the application level. You should then hide any functionality that the user does not have permissions to use. That way, they will never try to execute a sproc that they don't have permission to. You can leave all permission checking out at the database level and just let exceptions be thrown if the wrong procedure is executed. It then becomes the responsibility of each application to catch and handle those exceptions in an appropriate manner.

 

[keb1965]

great .. that will be much simpler to manage.

Does it matter that the users are not validated at the database level? The application is the only authenticated user and as a result, whatever stored procedure is called, it will likely run without exception.

 

[jmcilhinney]

great .. that will be much simpler to manage.

Does it matter that the users are not validated at the database level? The application is the only authenticated user and as a result, whatever stored procedure is called, it will likely run without exception.

If your application is the only interface to the database then, in theory, that's fine.

 

[keb1965]

Yes, the application is the only programmatic interface. Although, a root user logged in locally (with appropriate permissions) can manage the database without authentication .. because presumably a root user is already authenticated at the OS level.

Thanks for the insight.