We all know that SQL allows object level permissions down till the table, some of even know that it allows permissions at the column level too. But recently I was asked how to implement row level security at the table instead of column level security. In order to answer this I need to first explain the context. A user asked me how to implement security on a payslip table where the user is able to see only his payslip and not anybody else’s. Now the assumption here is that we can grant select or update permissions against a particular row just like how we can grant select or update permission against a table.
For the first thing to note here is , we can do this only if we can view the data for that user , in other words when the query
FETCH PAYSLIPDATA FROM PAYSLIPTABLE FOR USERNAMECOLUMN = @USERNAME
So essentially the where condition acts like a filter to prevent the user from being able to view (or Update) another person’s data. The only question is how to populate the variable @username , we can store the login credentials in a session variable and pass that as the input at the time of login , or if the database has a windows account for every user in the company then we can replace
Replace the @username variable with the function SUSER_SNAME()
Naturally this implementation requires that the payslip table store the domain account name for the user in order to do lookup and to extend this example all tables would need the username to be available in order filter at the row level.
Please Consider Subscribing
