Permission at the row level associated with user/login

Hi,
I have the following scenario:
I have a table X with 1000 rows. I want to allow select access to only 300
particular rows to a user/login while another user/login can see the other
700 (for example).
Is this granularity level possible in SQL Server 2005?
Thanks in advance,
Juan Dent, M.Sc.Juan Dent (juanjr@.nospam.nospam) writes:
> I have the following scenario:
> I have a table X with 1000 rows. I want to allow select access to only 300
> particular rows to a user/login while another user/login can see the other
> 700 (for example).
> Is this granularity level possible in SQL Server 2005?
Yes and no. In theory it is simple. You add a table that specifies which
keys that a certain user may see:
CREATE VIEW rowlevelsec_view AS
SELECT ...
FROM tbl t
JOIN accesscontrol c ON t.keycol = c.keycol
WHERE c.userid = SYSTEM_USER
You grant users access on the view, but not on the table. Users can then
only see the rows they are entitled to.
However, it is possible for crafty users to wrestle out information from
the view that they are not permitted to see. It's not that they can read
the rows, but they can infer things from query plans and error messages.
It's not really trivial, but this could matter if the data is very
sensitive.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thnaks, but I was thinking something perhaps new to SQLServer 2005 and at th
e
Transact-SQL level or the like, you know something declarative and part of
the language - not a construction.
Anyone?
Thanks in advance,
Juan Dent, M.Sc.
"Erland Sommarskog" wrote:

> Juan Dent (juanjr@.nospam.nospam) writes:
> Yes and no. In theory it is simple. You add a table that specifies which
> keys that a certain user may see:
> CREATE VIEW rowlevelsec_view AS
> SELECT ...
> FROM tbl t
> JOIN accesscontrol c ON t.keycol = c.keycol
> WHERE c.userid = SYSTEM_USER
> You grant users access on the view, but not on the table. Users can then
> only see the rows they are entitled to.
> However, it is possible for crafty users to wrestle out information from
> the view that they are not permitted to see. It's not that they can read
> the rows, but they can infer things from query plans and error messages.
> It's not really trivial, but this could matter if the data is very
> sensitive.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||Juan Dent (juanjr@.nospam.nospam) writes:
> Thnaks, but I was thinking something perhaps new to SQLServer 2005 and
> at the Transact-SQL level or the like, you know something declarative
> and part of the language - not a construction.
I'm afraid that what I presented is what SQL 2005 offers.
See also this white-paper on the topic:
http://www.microsoft.com/technet/pr.../multisec.mspx-
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Juan
http://vyaskn.tripod.com/ row_level...as
es.htm
"Juan Dent" <juanjr@.nospam.nospam> wrote in message
news:6D23828E-4AD9-4456-9E6E-B3836C1E0099@.microsoft.com...
> Hi,
> I have the following scenario:
> I have a table X with 1000 rows. I want to allow select access to only 300
> particular rows to a user/login while another user/login can see the other
> 700 (for example).
> Is this granularity level possible in SQL Server 2005?
> --
> Thanks in advance,
> Juan Dent, M.Sc.