Single Sign On is a useful method to authenticate, but how do i differentiate SQL access per application in an SSO environment?
Let me give you an example that regularly pops up in PenTests:
App A uses the SSO to get to SQL DB and has full access to i.e. the Customers table, and uses in-app roles that prevents user A to access the customers of user B, and the security officers are is happy.
But if user A starts up SSMS with SSO, he can access the entire Customers table which makes the security officers rather unhappy.
Besides going for column based encryption, abandoning SSO and move to SQL security per app and possibly setting up the rarely used application roles, what tools or methodologies do i have to prevent this collateral damage whilst keeping SSO?
Am i overthinking this, and am i missing a simple trick here?
A somewhat grumpy DBA.