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
>