2

I would like to know how I would go about restricting access to a table so that only a certain application can access the data within.

Where I work, we have a database application that rather stupidly stores user logon details (including passwords) in plaintext. There is a vulnerability in the way the software works that allows any user to run a SELECT query on the users table, and return all this information. This has been reported to the application developers, and should hopefully be fixed in a future version.

For now however, would I be able to change security settings so that only this application can access that table, and users of the application would be denied access? Users should still be allowed to run UPDATE queries to change their passwords, but SELECT is a big no.

  • 1
    How does the application login to SQL Server? Do the users login as themselves, or does the application handle security? – datagod Sep 11 '15 at 15:21
  • Application handles security. Users log on as users stored in this application-specific users table. – MrDetonia Sep 11 '15 at 15:22
  • 1
    If you're able to explicit deny the SELECT but still allow the UPDATE access, then does the vulnerability allow UPDATE on records which doesn't below to that user. – Pimp Juice IT Sep 11 '15 at 15:27
  • BTW "hopefully fixed in a future version" could be seen as "criminally negligent", depending on the sensitivity of the data. Does this application store personal information about the users? Credit card information? Put all this in writing and get your manager / product owner to sign off on it. Put a fire under their butt to get this fixed ASAP. – datagod Sep 11 '15 at 15:32
  • Luckily no personal employee information is stored. However, this potentially gives any user with a bit of SQL knowledge access to the sysop account. They have a "development suggestions" forum to which this has also been brought up. I'll definitely be taking your suggestion into action if this isn't dealt with soon. – MrDetonia Sep 11 '15 at 15:43
2

For the security credential/context which the application uses to authenticate to the DB, set an EXPLICIT DENY to it on the table to not allow it to access it at all (e.g. SELECT, UDPATE, DELETE, etc.).

You should really have the application credential locked down in the DB which it uses anyway to ONLY allow it to ONLY have the EXPLICIT level of access it needs to each DB object specifically in the name of security.

So why the user table is a concern now, wonder what other tables, etc. it has access to due to poor security design.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.