Post reply

App A uses SSO to get to SQL DB, how do i Prevent App B from querying that DB?

  • April 19, 2019 at 7:57 am

    #3463124

    Hi

    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.

    Theo

  • April 19, 2019 at 9:15 am

    #3464037

    Why there is SSO to DB? SSO should be only to app as you have access logic in app. In this configuration you should configure access logic on db level and create security roles and limit access to users using eg. views that will filter rows per users, row level security or create separated tables for different users groups.

  • April 19, 2019 at 11:41 am

    #3465724

    Ah, good point Ed!

    I forgot to mention 3 things:

    1: The app is a fat client that resides on the workstations or laptop of each user, and the DB is on a on-premises SQL server due to regulatory issues.

    2: The app is an ERP system for which we have no source code to tweak and has no support for row level security

    3: and even if we would have row level security, the security officer rightly points out that it would still be possible for a user to copy large sections of records to i.e. excel using a sql connection, and that is what we want to prevent. Only the ERP app should be able to access SQL.

    Hence the problem.

    t 🙂

    • This reply was modified 5 years, 7 months ago by  Theo Ekelmans.

  • April 19, 2019 at 8:45 pm

    #3471265

    So I don't see a solution, access logic is in wrong place 🙁

    You can't limit access on firewall only for app server, and you can't limit access by app name in connection string as this is easy to spoof.

    SSO is just logins/groups from Windows AD?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply