Row level security

Question

Row level security

John C. Marx

Mr or Mrs. 500

Points: 549
More actions
November 22, 2004 at 6:24 am

#91535

We have a database table with about 25 divisions. There is one entry per division per day. We have a security table that we use to set what the user has access to. For example,
Security
Username

John C Marx

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 1

You haven't really provided very much detail here as to what you are looking for. If you want to limit access to data based on username, then a view might help you.

rstone SSCertifiable Points: 6209 More actions · Answer 2

If access is by username, the user name can be passed to a stored procedure (returning a rowset) or user-defined function (returning a TABLE data_type). The T-SQL code can then use the parameter to limit columns and rows returned (via CASE, SELECT, WHERE, etc).

Views don't have parameters. If integrated security is used, then perhaps some form of join with a derived table "mask" can be used in a view. For example, select a "masking" record based on suser_sname and use mask bits in CASE statements to either return a column value or a special value (e.g., null, 'No Access', etc.). Might work - never tried it.

RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 3

You can limit the view by including a WHERE clause.

select * from myview where username = 'x'

rstone SSCertifiable Points: 6209 More actions · Answer 4

Because views do not have parameters, a separate view for each division will be required. If this is not acceptable, then a stored procedure or udf will be the way to go. You will either have logic to select the correct view by user or logic in a stored procedure to select the correct data based on the user passed.

RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉

Tom Goltl SSCertifiable Points: 5815 More actions · Answer 5

We are doing something similar. I'll customize the solution for you.

Make a table (Security) that has 2 columns: username and division.

Then create a view that join your new Security table to the data table, along with a parameter.

Create view row_level_View as select * from tbldata a join security b on a.division = b.division where b.username = system_user.

This will limit what division each user can see.

Hope that helps

Tom