Custom Row Level Security

Hi,
We have developed a 3-tier application and have developed our own method of
handling row level data security. Basically our application passes all
ad-hoc SQL queries through an object that replaces table names with view
names. Each user is assigned a role which has a view defined for it. For
example, issuing the statement:
SELECT FIRSTNAME FROM PEOPLE
would send the follow statement to the database:
SELECT FIRSTNAME FROM OC_LINEMANAGER_PEOPLE_READ
Where OC_LINEMANAGER_PEOPLE_READ is the name of a view in our SQL Server
database.
Obviously we want to apply the same security to users when accessing our
database using Report Services. The only way I can see of doing this is
that we parse SQL statements issued from Reporting Services and modify them
to use our view names.
Can we write a Data Processing Extension to do this? Would this apply to
the results returned in the visual query designer in the embedded report
writer in VS.NET?
Thanks
PeterI should probably hesitate to answer this, because I am not certain, but I
beleive that you can do what you wish with an extension. I suspect that it
would also apply to the generic designer, but I do not know how the DaVinci
toolkit designer would respond... Perhaps someone else will jump in .
Something which others have done is, instead of having a view for each user,
which might not scale well and is probably a management problem... Create a
mapping table with the user name and the keys the user can see, then join
the mapping table back to the orig table... this is essentially putting your
where clauses from all of your views into a table... Then the suser_sname()
or user! values can be used in the query...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Peter Sedman" <psedman@.community.nospam> wrote in message
news:%238aGVTpEFHA.1408@.TK2MSFTNGP10.phx.gbl...
> Hi,
> We have developed a 3-tier application and have developed our own method
> of
> handling row level data security. Basically our application passes all
> ad-hoc SQL queries through an object that replaces table names with view
> names. Each user is assigned a role which has a view defined for it. For
> example, issuing the statement:
> SELECT FIRSTNAME FROM PEOPLE
> would send the follow statement to the database:
> SELECT FIRSTNAME FROM OC_LINEMANAGER_PEOPLE_READ
> Where OC_LINEMANAGER_PEOPLE_READ is the name of a view in our SQL Server
> database.
> Obviously we want to apply the same security to users when accessing our
> database using Report Services. The only way I can see of doing this is
> that we parse SQL statements issued from Reporting Services and modify
> them
> to use our view names.
> Can we write a Data Processing Extension to do this? Would this apply to
> the results returned in the visual query designer in the embedded report
> writer in VS.NET?
> Thanks
> Peter
>|||Hi Wayne,
Thanks for the reply. We are creating views for roles rather than users, so
if we have say 10,000 users there may only be 20 roles.
If you use a mapping table you would still need to use a view in order to
package the table to the user (the view would join the two tables).
I think the best thing we can do is to experiment with the DPE and see what
we come up with.
What is the DaVinci toolkit designer?
Thanks,
Peter|||You could still do the join to a table of 10,000 rows that has user names
and groups, and filter on user name. That's a relatively inexpensive join.
Another possibility is to use a custom assembly, and make all of your SQL
dynamic rather than static. For example:
=code.Parse("SELECT FIRSTNAME FROM PEOPLE")
rather than just the SQL text in the value.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Peter Sedman" <psedman@.community.nospam> wrote in message
news:u%23k3IZqEFHA.392@.TK2MSFTNGP14.phx.gbl...
> Hi Wayne,
> Thanks for the reply. We are creating views for roles rather than users,
> so
> if we have say 10,000 users there may only be 20 roles.
> If you use a mapping table you would still need to use a view in order to
> package the table to the user (the view would join the two tables).
> I think the best thing we can do is to experiment with the DPE and see
> what
> we come up with.
> What is the DaVinci toolkit designer?
> Thanks,
> Peter
>

Custom Code in Web Report Builder

Hi, I created and deployed a simple custom 'toy' method to test custom code in a Report Server implementation I'm doing. It works just fine in the VS.NET 2005 Report Designer, but it does not seem possible to call it from the Web Report Builder. This would make ad-hoc reporting so much easier in non-trivial cases for our users - is it possible to use custom code in the Web Report Builder? If so, what's the syntax? I tried MyNamespace.MyClass.MyStaticMethod(), which works in the VS.NET designer, but was rejected in the Report Builder. Thanks much, Richard

Right now it is not possible to call custom assemblies from reports built in Report Builder... unless you save report and then edit in in Report Designer.

Custom Authorization in Reporting services

For custom authorization, I am overwriting the CheckAccess method.

But how do I know what I am trying to access in CheckAccess method? I need to know what report the check access method is trying to execute for our requirements.

How do I get that?

I have the same issue! Can anyone help?

Thanks!

Custom Authorization in Reporting services

For custom authorization, I am overwriting the CheckAccess method.

But how do I know what I am trying to access in CheckAccess method? I need to know what report the check access method is trying to execute for our requirements.

How do I get that?

I have the same issue! Can anyone help?

Thanks!

Custom assembly call in footer looses property values.

Custom assembly call in footer looses property values.
I have a custom assembly that uses a method in the body to pass in a data
field. When I fetch the data in the body I get my data back. When I do the
same in the footer I don't get any data. I have an active call in with
Microsoft so will also post there. I thought
we had working code, I was declaring the module scoped variable as static,
but that ends up with other peoples data on occassion when several people
are running reports. MS Support told me don't use static variables, but if
I don't then I can't access my properties in the footer.
I built a sample report and custom assembly to try to figure this out.
In the report I have something like this:
Body:
=Code.DataInFooter_NonStatic.ExpSet_MyData(First(Fields!LoginID.Value))
=Code.DataInFooter_NonStatic.MyData()
Footer:
=Code.DataInFooter_NonStatic.MyData()
What is displayed:
Body:
expected value
Footer:
empty and this is the problem.
Is it an execution order issue?
Here's my sample code:
using System;
using System.Collections.Generic;
using System.Text;
namespace DataInFooter_NonStatic
{
[System.Security.Permissions.PermissionSet(System.Security.Permissions.SecurityAction.Assert,
Unrestricted = true)]
public class DataInFooter_NonStatic
{
private string _MyData;
public string MyData
{
get
{
return _MyData;
}
set
{
_MyData = MyData;
}
}
public void WriteToFile(string Input)
{
//throw new System.NotImplementedException();
}
public string ExpSet_MyData(string Input)
{
_MyData = Input;
return _MyData;
}
}
}
Steve MunLeeuwDo this instead in your footer:
for the field that is displaying originally the
"=code.DataInFooter_NonStatic.MyData()" have it point to the
ReportItems!<name of control in body>.Value i.e. ReportItems!TextBox17.Value
(where textbox 17 in the body contains your call to
"=Code.DataInFooter_NonStatic.MyData()"
=-Chris
"Steve MunLeeuw" <smunson@.clearwire.net> wrote in message
news:Opk5KuF9GHA.5092@.TK2MSFTNGP04.phx.gbl...
> Custom assembly call in footer looses property values.
> I have a custom assembly that uses a method in the body to pass in a data
> field. When I fetch the data in the body I get my data back. When I do
> the
> same in the footer I don't get any data. I have an active call in with
> Microsoft so will also post there. I thought
> we had working code, I was declaring the module scoped variable as static,
> but that ends up with other peoples data on occassion when several people
> are running reports. MS Support told me don't use static variables, but
> if I don't then I can't access my properties in the footer.
> I built a sample report and custom assembly to try to figure this out.
> In the report I have something like this:
> Body:
> =Code.DataInFooter_NonStatic.ExpSet_MyData(First(Fields!LoginID.Value))
> =Code.DataInFooter_NonStatic.MyData()
> Footer:
> =Code.DataInFooter_NonStatic.MyData()
> What is displayed:
> Body:
> expected value
> Footer:
> empty and this is the problem.
> Is it an execution order issue?
> Here's my sample code:
> using System;
> using System.Collections.Generic;
> using System.Text;
> namespace DataInFooter_NonStatic
> {
> [System.Security.Permissions.PermissionSet(System.Security.Permissions.SecurityAction.Assert,
> Unrestricted = true)]
> public class DataInFooter_NonStatic
> {
> private string _MyData;
> public string MyData
> {
> get
> {
> return _MyData;
> }
> set
> {
> _MyData = MyData;
> }
> }
> public void WriteToFile(string Input)
> {
> //throw new System.NotImplementedException();
> }
> public string ExpSet_MyData(string Input)
> {
> _MyData = Input;
> return _MyData;
> }
> }
> }
> Steve MunLeeuw
>|||Ok, thanks I will try that.
I think this causes and exception when you try to access the ReportItems
collection in a footer when the page doesn't have any data fields. For
example if you have a page in the middle of the report that has static text
in a textbox that spans a full page then there are no data rows in the page
and accessing the ReportItems causes the report to fail. Either that or the
ReportItem!MyHiddenFooterData.Value would not show up for that page in
PDF...or something like that. I'll try and experiment around.
Steve MunLeeuw
"Chris Conner" <Chris.Conner@.NOSPAMPolarisLibrary.com> wrote in message
news:u7HPquG9GHA.1492@.TK2MSFTNGP02.phx.gbl...
> Do this instead in your footer:
> for the field that is displaying originally the
> "=code.DataInFooter_NonStatic.MyData()" have it point to the
> ReportItems!<name of control in body>.Value i.e.
> ReportItems!TextBox17.Value (where textbox 17 in the body contains your
> call to "=Code.DataInFooter_NonStatic.MyData()"
> =-Chris
> "Steve MunLeeuw" <smunson@.clearwire.net> wrote in message
> news:Opk5KuF9GHA.5092@.TK2MSFTNGP04.phx.gbl...
>> Custom assembly call in footer looses property values.
>> I have a custom assembly that uses a method in the body to pass in a data
>> field. When I fetch the data in the body I get my data back. When I do
>> the
>> same in the footer I don't get any data. I have an active call in with
>> Microsoft so will also post there. I thought
>> we had working code, I was declaring the module scoped variable as
>> static,
>> but that ends up with other peoples data on occassion when several people
>> are running reports. MS Support told me don't use static variables, but
>> if I don't then I can't access my properties in the footer.
>> I built a sample report and custom assembly to try to figure this out.
>> In the report I have something like this:
>> Body:
>> =Code.DataInFooter_NonStatic.ExpSet_MyData(First(Fields!LoginID.Value))
>> =Code.DataInFooter_NonStatic.MyData()
>> Footer:
>> =Code.DataInFooter_NonStatic.MyData()
>> What is displayed:
>> Body:
>> expected value
>> Footer:
>> empty and this is the problem.
>> Is it an execution order issue?
>> Here's my sample code:
>> using System;
>> using System.Collections.Generic;
>> using System.Text;
>> namespace DataInFooter_NonStatic
>> {
>> [System.Security.Permissions.PermissionSet(System.Security.Permissions.SecurityAction.Assert,
>> Unrestricted = true)]
>> public class DataInFooter_NonStatic
>> {
>> private string _MyData;
>> public string MyData
>> {
>> get
>> {
>> return _MyData;
>> }
>> set
>> {
>> _MyData = MyData;
>> }
>> }
>> public void WriteToFile(string Input)
>> {
>> //throw new System.NotImplementedException();
>> }
>> public string ExpSet_MyData(string Input)
>> {
>> _MyData = Input;
>> return _MyData;
>> }
>> }
>> }
>> Steve MunLeeuw
>|||Turns out the custom assembly gets loaded once in the body, then again in
the footer, that's why they don't share data so well.
Steve MunLeeuw
"Steve MunLeeuw" <smunson@.clearwire.net> wrote in message
news:Opk5KuF9GHA.5092@.TK2MSFTNGP04.phx.gbl...
> Custom assembly call in footer looses property values.
> I have a custom assembly that uses a method in the body to pass in a data
> field. When I fetch the data in the body I get my data back. When I do
> the
> same in the footer I don't get any data. I have an active call in with
> Microsoft so will also post there. I thought
> we had working code, I was declaring the module scoped variable as static,
> but that ends up with other peoples data on occassion when several people
> are running reports. MS Support told me don't use static variables, but
> if I don't then I can't access my properties in the footer.
> I built a sample report and custom assembly to try to figure this out.
> In the report I have something like this:
> Body:
> =Code.DataInFooter_NonStatic.ExpSet_MyData(First(Fields!LoginID.Value))
> =Code.DataInFooter_NonStatic.MyData()
> Footer:
> =Code.DataInFooter_NonStatic.MyData()
> What is displayed:
> Body:
> expected value
> Footer:
> empty and this is the problem.
> Is it an execution order issue?
> Here's my sample code:
> using System;
> using System.Collections.Generic;
> using System.Text;
> namespace DataInFooter_NonStatic
> {
> [System.Security.Permissions.PermissionSet(System.Security.Permissions.SecurityAction.Assert,
> Unrestricted = true)]
> public class DataInFooter_NonStatic
> {
> private string _MyData;
> public string MyData
> {
> get
> {
> return _MyData;
> }
> set
> {
> _MyData = MyData;
> }
> }
> public void WriteToFile(string Input)
> {
> //throw new System.NotImplementedException();
> }
> public string ExpSet_MyData(string Input)
> {
> _MyData = Input;
> return _MyData;
> }
> }
> }
> Steve MunLeeuw
>|||Here's some sample code of a custom assembly that shows the instance is not
shared between the body of the report and the footer.
http://smunson.blogspot.com/2006/10/rs-custom-assembly-instance-gets-re.html
Steve MunLeeuw
"Steve MunLeeuw" <smunson@.clearwire.net> wrote in message
news:Opk5KuF9GHA.5092@.TK2MSFTNGP04.phx.gbl...
> Custom assembly call in footer looses property values.
> I have a custom assembly that uses a method in the body to pass in a data
> field. When I fetch the data in the body I get my data back. When I do
> the
> same in the footer I don't get any data. I have an active call in with
> Microsoft so will also post there. I thought
> we had working code, I was declaring the module scoped variable as static,
> but that ends up with other peoples data on occassion when several people
> are running reports. MS Support told me don't use static variables, but
> if I don't then I can't access my properties in the footer.
> I built a sample report and custom assembly to try to figure this out.
> In the report I have something like this:
> Body:
> =Code.DataInFooter_NonStatic.ExpSet_MyData(First(Fields!LoginID.Value))
> =Code.DataInFooter_NonStatic.MyData()
> Footer:
> =Code.DataInFooter_NonStatic.MyData()
> What is displayed:
> Body:
> expected value
> Footer:
> empty and this is the problem.
> Is it an execution order issue?
> Here's my sample code:
> using System;
> using System.Collections.Generic;
> using System.Text;
> namespace DataInFooter_NonStatic
> {
> [System.Security.Permissions.PermissionSet(System.Security.Permissions.SecurityAction.Assert,
> Unrestricted = true)]
> public class DataInFooter_NonStatic
> {
> private string _MyData;
> public string MyData
> {
> get
> {
> return _MyData;
> }
> set
> {
> _MyData = MyData;
> }
> }
> public void WriteToFile(string Input)
> {
> //throw new System.NotImplementedException();
> }
> public string ExpSet_MyData(string Input)
> {
> _MyData = Input;
> return _MyData;
> }
> }
> }
> Steve MunLeeuw
>