Re: security permissions of roles\users on table rows
From: Russell Fields (rlfields@sprynet.com)
Date: 07/05/02
- Next message: R Thomann: "Sproc Permissions on Broken Ownership Chain"
- Previous message: Sriram Ganapathy[MS]: "RE: xp_runwebtask permission"
- In reply to: Panagiotis Konstantinidis: "security permissions of roles\users on table rows"
- Next in thread: Erland Sommarskog: "Re: security permissions of roles\users on table rows"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
From: "Russell Fields" <rlfields@sprynet.com> Date: Fri, 5 Jul 2002 13:35:53 -0400
Panagiotis,
The classic method of providing row level access to contents of a table is
to create a view that filters the rows and provide access to the view. E.g.
Create View OnlyLowPay
as
Select Name, Salary
>From Employee
Where Salary <= 15000
If you want to dynamically filter rows in a single view rather than managing
many views a couple of ways exist.
One is to create a mapping table for Users to data, such as:
Create Table UserRowRights
(UserName sysname,
Value money)
Insert into UserRowRights ('UserOne',15000)
Create View LimitPayAccess
as
Select Name, Salary
from Employee Join UserRowRights
on Salary <= Value
Where UserName = Current_User
Another way to do the same thing is to use IS_MEMBER or IS_SRVROLEMEMBER to
filter rows.
Russell Fields
"Panagiotis Konstantinidis" <panagiotis@melonweb.gr> wrote in message
news:OqiDkuAJCHA.2748@tkmsftngp13...
> We want to be able to grant permissions to roles and\or users on table
rows
> (not on columns). For example, when user A selects all rows, only those
rows
> should be returned that user A has been granted permission to see. User A
> may have another set of rows from the same table, that user A will be able
> to update, another set for deletion, etc.
>
> However, some of the rows of the above mentioned table will be able to be
> selected by other users as well, according to each user's individual
> permissions on each row.
>
> Can the above mentioned system be implemented with the help of the
built-in
> role\user system of SQL2K and how? I know that permissions may be assigned
> to roles and users with respect to tables and tabvle columns. But what
> happens with table rows?
>
> If the built-in permissions system canot be employed, any ideas about how
to
> do it, i.e. have groups and users with individual permissions on table
rows
> (like the NTFS file system)?
>
> Thanks in advance for any replies.
>
> Regards,
> Panagiotis
>
>
>
- Next message: R Thomann: "Sproc Permissions on Broken Ownership Chain"
- Previous message: Sriram Ganapathy[MS]: "RE: xp_runwebtask permission"
- In reply to: Panagiotis Konstantinidis: "security permissions of roles\users on table rows"
- Next in thread: Erland Sommarskog: "Re: security permissions of roles\users on table rows"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Relevant Pages
|
