Row and Cell Security

I am trying to implement row-security in SQL 2005 but i make a query to make a view

CREATE VIEW vwVisibleLabels
AS

SELECT ID, Label.ToString()
FROM tblUniqueLabel WITH (NOLOCK)
WHERE
ID IN --Classification
(SELECT ID FROM tblUniqueLabelMarking WITH (NOLOCK)
WHERE CategoryID = 1 AND IS_MEMBER(MarkingRoleName) = 1)
AND --Compartments
1 = ALL(SELECT IS_MEMBER(MarkingRoleName) FROM tblUniqueLabelMarking
WHERE CategoryID = 2 AND UniqueLabelID = tblUniqueLabel.ID)
GO

And the error is

Msg 208, Level 16, State 1, Procedure vwVisibleLabels, Line 4
Invalid object name 'tblUniqueLabel'.

The tblUniquelabel does exist, what is going on?
Please someone help me!!!!

PS. I am following the white papper on Implementing Row and Cell Level Security from the microsoft site.

You may need to prefix the table name by its schema name.

Thanks
Laurentiu|||I think is the ToSrting that is giving the error do you know what this meens?

Thanks|||I think the problem is in the ToString(). I have the colum label with the data type nchar(20) do you think that could be the problem?|||Have you looked into prefixing the table name with the schema name? The message that you obtained indicates that the table name lookup failed. The SELECT didn't even get to process the "ID, Label.ToString()" part, so even if there would be errors in it, they're not the ones generating the message that you received.

Thanks
Laurentiu|||

But what do you mean by scheam? Is it the code that generates the database? were is it?

Thanks

Ps. I made a view only ti list the tbUniquelabel and it was fine so the problem isn't the table not existing or being badly written.

|||

A schema is a new concept that helps managing the contents of a database. See the "User-Schema Separation" topic in Books Online.

To find the schema name for this table, you can try the following query:

select schema_name(schema_id) from sys.objects where name = 'tblUniqueLabel'

This will list all schemas in which you can find objects named tblUniqueLabel.

But now that you mentioned that you could create another view on the table, I took a closer look at the create statement and it may be incorrect. Try executing the following:

SELECT ID, Label.ToString()
FROM tblUniqueLabel tUL WITH (NOLOCK)
WHERE
ID IN --Classification
(SELECT ID FROM tblUniqueLabelMarking WITH (NOLOCK)
WHERE CategoryID = 1 AND IS_MEMBER(MarkingRoleName) = 1)
AND --Compartments
1 = ALL(SELECT IS_MEMBER(MarkingRoleName) FROM tblUniqueLabelMarking
WHERE CategoryID = 2 AND UniqueLabelID = tUL.ID)
GO

I didn't notice that you had a second reference to tblUniqueLabel in the inner query. That may be the one generating the error. Let us know if this works. If this doesn't work, please also post the create table statements that you used to create the tblUniqueLabel and tblUniqueLabelMarking tables.

Thanks

Laurentiu

|||

When you run the code

SELECT ID, Label
FROM dbo.tblUniqueLabel WITH (NOLOCK)
WHERE (ID IN
(SELECT dbo.tblUniqueLabel.ID
FROM dbo.tblUniqueLabelMarking WITH (NOLOCK)
WHERE (CategoryID = 1) AND (IS_MEMBER(MarkingRoleName) = 1)))

No error but no right result because, i think that IS_MEMBER(MarkingRoleName) = 1 always is 0. so every time it gives me a empty Table with the label and id colum

So i need to convert Label to string so it can be compared in the IS_MEMBER(MarkingRoleName) = 1

But if i run

SELECT ID, Label.ToString() AS Expr1
FROM dbo.tblUniqueLabel WITH (NOLOCK)
WHERE (ID IN
(SELECT dbo.tblUniqueLabel.ID
FROM dbo.tblUniqueLabelMarking WITH (NOLOCK)
WHERE (CategoryID = 1) AND (IS_MEMBER(MarkingRoleName) = 1)))

It gives me the folowing messege:

Cannot find the colum label (which is impossible because the first code runs), or the user-defined function or aggregate "Label.ToString", or the name is ambiguous.

When i run

select schema_name(schema_id) from sys.objects where name = 'tblUniqueLabel' the is result is a table with one row and one column with the heading expr 1 and the cell with dbo written on on

Thanks

|||

You've changed the query, so you're getting different errors now. Note that the original error, as I have mentioned in my previous message, was not related to the schema, but to the way the inner clause of the query was written. You don't need to be explicit about the dbo schema, this is one of the schemas searched by default.

What is the type of the Label column? I've looked over the article but it doesn't specify this. In T-SQL, to convert from a type to another, you would need to use the CONVERT function. The ToString method indicates Label is a CLR user defined type. If you've just defined it as a SQL builtin type, then this won't work.

Thanks
Laurentiu

|||

Looking back to the code it does not seem like you need to convert anything. It was just what i thought the toString() procedure did.

I put nchar(20 ) on every column is that the mistake?

How do i chandge it to the CLR type and what is it?

Thanks again.

|||

I don't know how the CLR type is defined and the paper does not seem to describe it. I spoke with one of the authors and they are working to release some additional material that will allow implementing the solution described in the white paper. I don't yet have a date for when this will happen, but I am trying to find one. It will most likely happen next year though. I'll post to this thread when I will have more information.

Thanks
Laurentiu

|||

Thank you anyway.

But i do have another problem with the database menber describe in the white paper, it says in the paper that we have to add as menber of the role the child role. But when you go to add a menber a the role you can not add a role. How is it possible to do so?

|||

Please open a new thread on this issue and provide more details on what commands you are trying and what error messages you receive.

Thanks
Laurentiu

|||

Please feel free to explore the free eval of Data Nomad ( http://www.technicalmedia.com ). This product will automatically generate views for row level security in SQL Server. It works with any version including SQL Server 2005 Express.

You can look at the schemas, views synonyms and stored procedures that are created to learn more about how to build your own (or of course you can use the product too $100 developer - no runtime costs :))

Row-Level Security for Microsoft SQL Server 2005

Data Nomad? is an affordable set of developer tools that extend the Microsoft SQL Server 2005 platform to provide row-level security and remote access features allowing developers to accurately and efficiently create and manage powerful distributed applications that insure access to information is protected.

Developers of .NET 1.1 and .NET 2.0 smart client and web applications can now easily add row-level security to database applications through the Data Nomad? developer tools. Existing databases are easily configured by identifying the tables to be protected and by creating row-level permission grants.

The same (unmodified) SQL statements work against the Nomad database extensions. The extended database appears to only contain the rows to which the user has at least read permissions. Database updates and deletes only succeed against rows to which the user has owner permissions.

This type of seamless integration is achieved by leveraging two powerful new features of Microsoft SQL Server 2005: the schema (a collection of database objects that form a single namespace) and the synonym (an alternative name for another database object providing a layer of abstraction over the original object).

The Nomad extensions support both SQL Server authentication and Integrated NT authentication for database connections, and support local, LAN-connected, and Web-connected backend databases.

“Using Technical Media’s Nomad product has saved us months of development” said Darcy Vaughan, a founder and Director of PetroWEB, Inc.

PetroWEB, Inc has obtained an exclusive Data Nomad? license for the upstream oil and gas industry. For information on utilizing Data Nomad? technology in this industry, please contact Darcy Vaughan at 303.308.9100.