| Author |
Topic |
|
kitty2012
Starting Member
8 Posts |
 Posted - 2011-12-08 : 02:57:12
|
| Hi, I have a problemet about select and permission: I have a table in SQL 2008 that contains several columns, and one contains text of more secret content. I want users to be able to select posts from the table, but when user A selects, only posts with value A in column User vill be selected, and when user B etc... Is there a way to solve this? Is a view the way to go?I don´t want to build a view for each user.Even if user A runs select * from table he/she must only get posts that he/she have permission to read. Sorry about my bad engling, hope you understand what I'm after. /Daniel. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-12-08 : 07:03:34
|
| looks like what you're asking is a security model which needs to be built into business logic of application------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
lappin
Posting Yak Master
182 Posts |
Posted - 2011-12-08 : 11:48:32
|
| You can set column level security using: USE PUBS GRANT SELECT (stor_id, ord_num, ord_date, qty, title_id) ON sales TO b_smithhttp://www.techrepublic.com/article/locking-down-microsoft-sql-server/5031954but a select * fails rather than only showing columns with permission.It is also possible to check which roles a user belongs to inside a procedure and build them dynamic SQL. If it was me, I would build different Views /Stored Procedures and set the permission to different Roles and add users to these Roles. |
|
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2011-12-08 : 17:36:24
|
| Would a view satisfy your needs?[CODE]CREATE VIEW vMyTableasselect *from MyTablewhere User = User_name()[/CODE]User_name() may not be the function that you want to use but there are others that return similar results (e.g., SYSTEM_USER, SESSION_USER, et al).=======================================Faced with the choice between changing one's mind and proving that there is no need to do so, almost everyone gets busy on the proof. -John Kenneth Galbraith |
|
|
|
kitty2012
Starting Member
8 Posts |
Posted - 2011-12-09 : 01:32:31
|
quote:
Originally posted by visakh16
looks like what you're asking is a security model which needs to be built into business logic of application------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
Ofcourse I could use a "security" account in my application, presenting only the data the user have permission to se, but what if someone used Reverse Engineering on the application (if its possible) then it wouldn´t be safe, would it? |
|
|
|
kitty2012
Starting Member
8 Posts |
Posted - 2011-12-09 : 01:38:44
|
quote:
Originally posted by lappin
You can set column level security using: USE PUBS GRANT SELECT (stor_id, ord_num, ord_date, qty, title_id) ON sales TO b_smithhttp://www.techrepublic.com/article/locking-down-microsoft-sql-server/5031954but a select * fails rather than only showing columns with permission.It is also possible to check which roles a user belongs to inside a procedure and build them dynamic SQL. If it was me, I would build different Views /Stored Procedures and set the permission to different Roles and add users to these Roles.
Its not columns I want to lock out from the user, that is easy, but certain rows based on a value in one column. Setting up permanent views for each usergroup or user sounds a bit tedious because I will have quite a lot of groups. |
|
|
|
kitty2012
Starting Member
8 Posts |
Posted - 2011-12-09 : 01:43:39
|
quote:
Originally posted by kitty2012
Hi, I have a problemet about select and permission: I have a table in SQL 2008 that contains several columns, and one contains text of more secret content. I want users to be able to select posts from the table, but when user A selects, only posts with value A in column User vill be selected, and when user B etc... Is there a way to solve this? Is a view the way to go?I don´t want to build a view for each user.Even if user A runs select * from table he/she must only get posts that he/she have permission to read. Sorry about my bad engling, hope you understand what I'm after. /Daniel.
To make things little clearer; I have no probbs solving permissions in my application, what I am worried about is if a user installs SSMS localy and with his acount runs a simple SELECT on the table, then he can read records he wouldnt´t be able to read from the application. Im sorry if I ask stupid questions... but I want to secure the data in the table as much as possible. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-12-09 : 01:48:46
|
quote:
Originally posted by kitty2012
quote:
Originally posted by lappin
You can set column level security using: USE PUBS GRANT SELECT (stor_id, ord_num, ord_date, qty, title_id) ON sales TO b_smithhttp://www.techrepublic.com/article/locking-down-microsoft-sql-server/5031954but a select * fails rather than only showing columns with permission.It is also possible to check which roles a user belongs to inside a procedure and build them dynamic SQL. If it was me, I would build different Views /Stored Procedures and set the permission to different Roles and add users to these Roles.
Its not columns I want to lock out from the user, that is easy, but certain rows based on a value in one column. Setting up permanent views for each usergroup or user sounds a bit tedious because I will have quite a lot of groups.
how will you dtermine which all rows can be viewed by which user? is there a field which gives this info?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
kitty2012
Starting Member
8 Posts |
Posted - 2011-12-09 : 02:14:24
|
quote:
how will you dtermine which all rows can be viewed by which user? is there a field which gives this info?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
I will eventually join it with some kind of xref-table, but for now I have a column named group with just letters; A, B, C to test. If I belong to group A, I will only have permission to se posts where group = A. :) |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-12-09 : 03:32:33
|
| then isnt it easier to implement it with a procedure?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
kitty2012
Starting Member
8 Posts |
Posted - 2011-12-09 : 04:04:44
|
quote:
Originally posted by visakh16
then isnt it easier to implement it with a procedure?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
Well, hadn´t thought of SP, feels like a possible solution, but if you pass the users group (A or B etc) as a parameter in the application to the SP, the user, if he/she had SSMS could run the SP and just pass a different users group (who hase right permissions) and still read info that isn´t for him/her? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-12-09 : 04:07:51
|
| nope you can restrict the sp to take user value based on suser_sname function rather than asking user to input the value------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
kitty2012
Starting Member
8 Posts |
Posted - 2011-12-09 : 04:09:20
|
quote:
Originally posted by kitty2012
quote:
Originally posted by visakh16
then isnt it easier to implement it with a procedure?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
Well, hadn´t thought of SP, feels like a possible solution, but if you pass the users group (A or B etc) as a parameter in the application to the SP, the user, if he/she had SSMS could run the SP and just pass a different users group (who hase right permissions) and still read info that isn´t for him/her?
Maybe Im focusing wrong, perhaps I should try to make it harder to reverse my VB-application? ... hm. |
|
|
|
kitty2012
Starting Member
8 Posts |
Posted - 2011-12-09 : 04:10:55
|
quote:
Originally posted by visakh16
nope you can restrict the sp to take user value based on suser_sname function rather than asking user to input the value------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
Ah! Perfect, that was the missing piece in the puzzle!Thanks alot for all your help. :) /Daniel. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-12-09 : 04:12:25
|
| wc------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
Filter selection based on user