Row and Cell Segurity

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.

Could you please post a script that reproes this problem? I don't see anything wrong with the view definition unless you have a typo somewhere or wrong case (matters in case-sensitive collation).|||

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?

|||It is hard to tell anything conclusive without seeing a repro of the problem. So please post back with sample schema/script.|||

But what do you mean by script scheam? Is it the code that generates the database? Here 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.

|||Not the entire database script but just the SQL statements that can repro the problem. It needn't be the actual schema also.|||

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

|||Is the label column a CLR UDT? If so, does it have a ToString() method? If it is a SQL data type then use CAST to convert the value to string.|||

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.

|||

Why don't you use CAST or CONVERT?

Stjepan

|||dude, you probably have tblUniqueLabel sitting in a different schema, or your default schema isn't dbo.

reference tblUniqueLabel using the form SchemaName.tblUniqueLabel

Also, you don't have to do .ToString() - there's no such function in sql server. just reference label as is.

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.

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.

Row and cell level security

A doctoral student of mine would like to know how he can get in contact
with the authors of the interesting white paper "Implementing Row and
Cell Level Security in Classified Databases Using SQL Server 2005"
Published: April 1, 2005 By Art Rask, Don Rubin, and Bill Neumann,
Microsoft Consulting Services.
Dr. Bo Sanden, Ph.D.
Prof. of Computer Science
Colorado Technical University
Colorado Springs, CO 80907Hi,
hop its correct :
art.rask@.tpi.net
source of information (google search)
http://www.uga.edu/~spc/Undergrad/s...n3700fall05.pdf
http://blogs.msdn.com/federaldev/ar.../19/471595.aspx
http://www.tpi.net/about/selectedAdvisor.aspx?ID=153
HTH
Regards
Andy Davis
Activecrypt Team
---SQL Server Encryption Software
http://www.activecrypt.com
"Bo Sanden" wrote:

> A doctoral student of mine would like to know how he can get in contact
> with the authors of the interesting white paper "Implementing Row and
> Cell Level Security in Classified Databases Using SQL Server 2005"
> Published: April 1, 2005 By Art Rask, Don Rubin, and Bill Neumann,
> Microsoft Consulting Services.
>
> Dr. Bo Sanden, Ph.D.
> Prof. of Computer Science
> Colorado Technical University
> Colorado Springs, CO 80907
>|||Thanks, Andy.
Bo
Andy Davis wrote:
> Hi,
> hop its correct :
> art.rask@.tpi.net
> source of information (google search)
> http://www.uga.edu/~spc/Undergrad/s...n3700fall05.pdf
> http://blogs.msdn.com/federaldev/ar.../19/471595.aspx
> http://www.tpi.net/about/selectedAdvisor.aspx?ID=153
>
> HTH
> Regards
>|||For a well documented, alternative approach for row level security
implementations please see http://www.technicalmedia.com (Data Nomad
product). Please feel free to contact me through the feedback page on the
site.
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 i
s
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.
"Bo Sanden" wrote:

> A doctoral student of mine would like to know how he can get in contact
> with the authors of the interesting white paper "Implementing Row and
> Cell Level Security in Classified Databases Using SQL Server 2005"
> Published: April 1, 2005 By Art Rask, Don Rubin, and Bill Neumann,
> Microsoft Consulting Services.
>
> Dr. Bo Sanden, Ph.D.
> Prof. of Computer Science
> Colorado Technical University
> Colorado Springs, CO 80907
>

Rounding Problem in total

In a cell I have the following:

=iif(Parameters!VOLREV.Value="Volume",sum(math.Round(Fields!ADMINSRV_SLS.Value)),sum(math.Round(Fields!ADMINSRV_FEES.Value)))

Depending on parameter I am either rounding the sum of sales or the sum of fees. This works beautifully. Problem is when I copy this to the total row it is not the total of the column. They are slightly off. I even tried naming the cell and then creating a calculated field in the total line to make sure that I am adding the values of that cell. =sum(ReportItems!MyNamedCell.value) But this crashes VS everytime and it has to restart. Does anyone have a suggestion please.

In the total cell, is the expression rounding and then summing the values, like in the data cell? If so, this should produce identical results. How off are the values?

Also, the other issue, regarding the use of the expression, =sum(ReportItems!MyNamedCell.Value), should just cause a publishing error. ReportItems can not be used inside aggregates unless the aggregate is used in the page header or footer. I'm not sure why it is crashing VS.|||I am wondering if it is summing and then rounding because the totals are slightly off of what the total of the columns should be.|||

Found the answer. For whatever reason in my grouping lines that show my detail this works:

=iif(Parameters!VOLREV.Value="Volume",math.Round(sum(Fields!ADMINSRV_SLS.Value)),math.Round(sum(Fields!ADMINSRV_FEES.Value)))

But in my total line I had to reverse the logic to this.

=iif(Parameters!VOLREV.Value="Volume",sum(math.Round(Fields!ADMINSRV_SLS.Value)),sum(math.Round(Fields!ADMINSRV_FEES.Value)))