SQL Server: Best way to deny sql user access to old data

Question

Using SQL Server, what is a simple but effective means of denying access to data older than a certain date, for some users?

We can do this at the application level (a web application) but this leaves us vulnerable to scenarios such as IIS being hacked or bugs in our application. Ideally only certain SQL users should have access to certain data older than a couple of months. What is a good way of achieving this with minimal fuss?

If it makes a difference, our application uses NHibernate.

Answer 1

SQL Server does not offer row level security. Meaning you cannot grant SELECT to certain rows (newer than a date) while denying to other. All GRANT/DENY/REVOKE work at the whole table level.

There are some proposed workarounds like using views,see Granting Row-Level Permissions in SQL Server, but they're cumbersome and difficult to use (specially if the data is also to be updated).

Your best bet is to move the data into different tables, if possible.

Answer 2

The pure SQL solution would be to create a view something like:-

CREATE USERVIEW AS (SELECT * FROM REALTABLE WHERE CREATE_DATE > CURRENT_DATE() - 30 DAYS )

Not sure how well this plays with Hibernate and of course the "CURRENT_DATE() - 30 DAYS " would only be valid in DB2 you would need a differnet function and syntax for each DBMS.

Answer 3

Can you archive older data to tables that have more restricted access rights?

Answer 4

The proper way to achieve that is to archive the old data by using the SQL Server business intelligence mechanisms, but it is not an immediate task and requires some planning (and these mechanisms are not available in the low-end versions of SQL Server).