| Author |
Topic |
|
robg69
Starting Member
41 Posts |
 Posted - 2003-06-26 : 10:59:52
|
| Hey guys, I'm not sure if this should be admin and/or developer, but I figured this was the best place. Does anyone know if you can limit data that users can view depending on, say, a flag IN the table. I can do this in the code, but it is a commercial app. that we will get updated versions for, so I would have to add this code everytime a new version comes out. We can also buy a license for another DB, but we would like to avoid that if possible. Anyone have a clue?Hypothetically, I have UserA and UserB. UserA can view data in the employee table ONLY IF the active flag is "Y" (cannot see "N" data). UserB can view data in the employee table ONLY IF the active flag is "N"(cannot see "Y" data). So I'd like to limit access depending on a flag. Thanks for any help!-Rob |
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2003-06-26 : 11:38:24
|
| The right way to do this is through view definitions.Jonathan{0} |
 |
|
|
robg69
Starting Member
41 Posts |
Posted - 2003-06-26 : 11:51:11
|
Thanks for your reply. I have never done anything with view definitions. I've found a little bit of documentation, but not much. Can you be a little more specific?  Thanks,-RobEdited by - robg69 on 06/26/2003 11:54:46 |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-06-26 : 11:54:02
|
| If all your access is via stored procs then it's simple.Otherwise you could do it via vews and remove access to table e.g.create view vwMyTblasselect MyTbl.*from MyTblwhere exists(select * from UserAccess where name = user_name() and TableName = 'MyTbl' and flg = 1)You could also rename the tables and make the view with the table name.You could also generate the views from the database structure.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy.Edited by - nr on 06/26/2003 11:55:37 |
|
|
|
robg69
Starting Member
41 Posts |
Posted - 2003-06-26 : 14:57:39
|
| I wish they were accessed with SP's, but no such luck.Correct me if I'm wrong, but even if I use a view (named the same as the table, so I don't have to change the code), won't I still have to have two different views (one for UserA "where flg = 'Y'" AND one for UserB "where flg = 'N'"), thus causing me to change the code anyway?Again, my main obj. is to not have to change any code in the app., just the DB.Thanks!-Rob |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2003-06-26 : 15:14:48
|
| The short answer is, you're out of luck if you can't change the application code. There is no such thing as data-driven security within SQL Server (or, for that matter, any enterprise RDBMS that I'm aware of).Jonathan{0} |
|
|
|
robg69
Starting Member
41 Posts |
Posted - 2003-06-26 : 15:37:39
|
| I'm able to change the code, but I was trying to avoid it. Oh well, I guess that's what I'm going to have to do.Thanks for the replies.-Rob |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-06-26 : 15:38:15
|
| Nope.The UserAccess table defines whether or not the user can see the data.The table is username, tablename, flg.flg = 1, can see data; 0, can't see data.Then you just populate that table with the users and tables. To prevent/enable a user seeing data just change the flag.The way I have written the view you need an entry to allow the user to see data. To disable viewing on an entry in the table =use 'not exists'.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
efelito
Constraint Violating Yak Guru
478 Posts |
Posted - 2003-06-27 : 09:02:50
|
Very nice nr.Rob should be able to take this one step further and apply his filter to the views. If he creates UserAccess with a filter field he could add rows to the UserAccess table to only allow users to see certian rows in a table. You could probably drop the flg field since not having a row that matches the filter will effectively disallow access. Now they should have the right to view rows containing specific values like Rob asked for. So combining your examples you get something like this...create view vwMyTbl as select MyTbl.* from MyTbl where exists (select * from UserAccess where name = user_name() and TableName = 'MyTbl' and UserAccess.filter = MyTbl.active) Jeff Banschbach, MCDBAEdited by - efelito on 06/27/2003 09:05:18 |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2003-06-27 : 09:40:53
|
| I see we've come full circle on the view idea ... Jonathan{0} |
|
|
|
robg69
Starting Member
41 Posts |
Posted - 2003-06-30 : 09:17:43
|
| Thanks for all your help guys. I think I'm following you, but I'm going to have to mess around with it.-Rob |
|
|
|
robg69
Starting Member
41 Posts |
Posted - 2003-07-01 : 16:02:47
|
| Alright, I finally got it working! I did it somewhat similar to what you guys said using a view and a usertable, but it works a little bit different. View:-----create view vwMyTbl as select MyTbl.* from MyTbl where MyTbl.fieldToChk = (select * from UserAccess where name = user_name() and TableName = 'MyTbl' and UserAccess.Desc1 = UserAccess.Desc2) UserTable:----------Create table UserAccess (UserName varchar(15)Desc1 varchar(5)Desc2 varchar(5))User Data:----------And the user data looks something like this:UserA, Y, YUserB, N, NSo the view just ends up retrieving the data value that I want to limit user access on (MyTbl.fieldToChk = Y) or:UserA SQL = select MyTbl.* from MyTbl where MyTbl.fieldToChk = YUserB SQL = select MyTbl.* from MyTbl where MyTbl.fieldToChk = NIt's kinda confusing, but it's working great!Again, thanks for your help, nr and efelito! Without your View SQL examples, I probably wouldn't have gotten it. -Rob |
|
|
|
|
security depending on data?