4

This is a question for SQL Server but could probably apply to nearly any SQL implementation.

A client will be consuming a web service API. From a security standpoint, my idea was to ETL all the pertinent data into one table and let the client pull from that table. Let's call the table FoobarTbl. I also am locking down the SQL Login to only have access to this one table and nothing else.

I took it further by creating two SQL Logins:

  1. SQL Login which can execute SELECT to the table. (i.e. FoobarLoginRead (
  2. SQL Login which can execute INSERT and UPDATE to the table. (i.e. FoobarLoginWrite).

Is this a good design for security or is it overkill and problematic? I observed that the UPDATE operation didn't function without adding SELECT, which kinda defeats the purpose of having separate logins for reading from and writing to the same table.

My thought process is: If somehow a nefarious user got the creds for FoobarLoginWrite, they would only have visibility to the one table but also they couldn't actually grab any data. A user nefariously updating or inserting data is an acceptable loss, since the source data is being ETL'd to this table. The bigger concern is grabbing the data from the table.

Am I overthinking this or is this a reasonable approach to locking down SQL Logins?

By "locking down SQL Logins", I mean effective permissions for what resources a SQL Login can access such as tables, views, and so on. In my case, I created two SQL logins. Both have permissions to access one table. And my idea was to give one login SELECT permissions to this one table and the other login gets INSERT and UPDATE permissions to this same table.

In short, what I'm asking is is having two separate logins for the same table, with what I outlined above, a good approach for securing SQL data or am I overdoing it and this would be considered poor design?

  • 1
    >>>is this a reasonable approach to locking down SQL Logins<<< Your terminology is very strange: the whole post is talking about USERS, not logins, only USERS can have DML permissions, but at least one can understand what you want to do. But at the end it's not clear at all what do you mean saying "locking down SQL Logins". Can you explain it, please? – sepupic Sep 14 '17 at 20:15
  • Sure, no problem. Sorry about the terminology. I don't mean users in SQL parlance. "Locking down" meaning I created a couple of SQL logins that have permissions to only one table. I'll try to clarify in the original post. Thank you. – user3621633 Sep 14 '17 at 20:31
  • 1
    Should the web API only have select permissions? Or are you also allowing them to insert/update via web API? – Sir Swears-a-lot Sep 18 '17 at 9:07
  • Only allowing them to SELECT with API. – user3621633 Sep 18 '17 at 13:38
  • 1
    You could have different roles but it makes it a complex thing to manage and you would have to deal with multiple connection pools. This makes only sense if you really have strict requirements (like no modifications or append only tables). If your app knows both logins the usefulness of the separation is limited. BTW: you could also look into proxy users or using the end users as database accounts. – eckes Sep 30 '17 at 14:04
3

You simply need to follow the Principle of Least Privilege.

If you have one entity, whether it be a user or an application, that entity should have an account with the minimum permissions required to do its work. So if you will have a web API that will only read from one table, then its website should be using an account that only has permission to read that one table.

Your question doesn't seem to indicate who or what will be using the read/write account. If nobody will be writing to the table (which I'm guessing is correct, based on the fact that the data came from another source), then only the ETL process should have permission to write to the table.

2

I would go with 2 separate stored procedures, one to read and a second procedure to update; if insert is needed then a third procedure will do that.

This way you grant permission to the relevant procedure only and nothing on the table itself.

This is not always possible but I found this a suitable approach to give least privilege while having the needed flexibility to allow all the required operations.

More work is required to build robust stored procedures though and maybe this is not a burden easily handled if other constraint/requirements are present (eg: project manager defining specs, customer requiring XYZ approach)

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.