November 22, 2004 at 6:24 am
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
November 22, 2004 at 9:11 am
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.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
November 23, 2004 at 10:31 am
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.
Randy
Helpdesk: Perhaps Im not the only one that does not know what you are doing. 😉
November 23, 2004 at 12:03 pm
You can limit the view by including a WHERE clause.
select * from myview where username = 'x'
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
November 23, 2004 at 3:33 pm
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.
Randy
Helpdesk: Perhaps Im not the only one that does not know what you are doing. 😉
November 24, 2004 at 11:27 am
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply