CODEWITHSUNDEEP
sql-server
stevenrcfox
stevenrcfox

Reputation: 1567

Preventing users from accessing Application Database

We have a problem where one of our customers is changing data directly in the database.

As we have an API, we'd prefer our customers to use this. We have threatened not to support their solution if they persist in manually changing data.

My query is, is there a technical way we can prevent access to the database from anything other than our application?

This is a sql server database, and our customers own the server and administer the DB server, So essentially we need a way to lock out SA.

Thanks

Upvotes: 1

Views: 1039

Answers (2)

gbn
gbn

Reputation: 432662

First things... you can't:

  • lock out sa or sysadmin rights at all at the server level
  • lock out db_owner/dbo at the database level

Now we've cleared that one up, who is accessing data directly?

  • If you mean end users are changing data, then you have a security issue: they should only be able to use the API and not even be able to connect.

  • If you mean sysadmin level users (eg DBAs or BOFH types) then there may be a legitimate reason. Does your API support all operations? As a DBA, I had to do open table surgery on badly written 3rd party apps now and then

  • If end users have sysadmin level rights, then you have a politics issue within the client company

Edit:

After comment by OP on their question... sysadmin users can disable triggers...

Upvotes: 3

jwueller
jwueller

Reputation: 31006

Is you API public? It shouldn't be if you can change data without any authentication. I recommend using some internal authentication mechanism. A basic challenge/response protocol that ensures that the incoming query is safe to execute, or something like that.

Upvotes: 1

Related Questions