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:
- SQL Login which can execute SELECT to the table. (i.e. FoobarLoginRead (
- 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?
Best Answer
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.