Report Model Data Security

Hello
I have designed a report model, but now have a requirement to limit data
access to certain people. I have tried researching about Role based
security, but nothing seems to make sense. I have to design a suite of
reports 16 in total. However,I have 8 users, all looking at data from
difference business departments. It has been requested that each department
cannot look at another department's data. The thought of cloning these 16
reports, 8 times over, does not seem like a viable option.
Is there an alternative?
Kind regards
Ricky
(SS2005)If you are saying that each report needs to be viewed by each dept and that
the data should be different on the report depending on who is viewing it
then:
On your reports you can have a hidden extra parameter that defaults to the
user id.
Then pass this id into your stored procedures that generate the data.
The stored procedures will do the filtering based on your own internal logic.
"RickyP" wrote:
> Hello
> I have designed a report model, but now have a requirement to limit data
> access to certain people. I have tried researching about Role based
> security, but nothing seems to make sense. I have to design a suite of
> reports 16 in total. However,I have 8 users, all looking at data from
> difference business departments. It has been requested that each department
> cannot look at another department's data. The thought of cloning these 16
> reports, 8 times over, does not seem like a viable option.
> Is there an alternative?
> Kind regards
> Ricky
> (SS2005)
>|||Hi Jimbo
Thanks for your post, but the model cannot not based on an SP, it is created
from a View.
Kind regards
Ricky
"Jimbo" <Jimbo@.discussions.microsoft.com> wrote in message
news:B3DF6CA7-175E-42E7-A432-C023612E582D@.microsoft.com...
> If you are saying that each report needs to be viewed by each dept and
> that
> the data should be different on the report depending on who is viewing it
> then:
> On your reports you can have a hidden extra parameter that defaults to the
> user id.
> Then pass this id into your stored procedures that generate the data.
> The stored procedures will do the filtering based on your own internal
> logic.
>
>
>
> "RickyP" wrote:
>> Hello
>> I have designed a report model, but now have a requirement to limit data
>> access to certain people. I have tried researching about Role based
>> security, but nothing seems to make sense. I have to design a suite of
>> reports 16 in total. However,I have 8 users, all looking at data from
>> difference business departments. It has been requested that each
>> department
>> cannot look at another department's data. The thought of cloning these
>> 16
>> reports, 8 times over, does not seem like a viable option.
>> Is there an alternative?
>> Kind regards
>> Ricky
>> (SS2005)|||Your sp can read from the view :)
"RickyP" wrote:
> Hi Jimbo
> Thanks for your post, but the model cannot not based on an SP, it is created
> from a View.
> Kind regards
> Ricky
> "Jimbo" <Jimbo@.discussions.microsoft.com> wrote in message
> news:B3DF6CA7-175E-42E7-A432-C023612E582D@.microsoft.com...
> > If you are saying that each report needs to be viewed by each dept and
> > that
> > the data should be different on the report depending on who is viewing it
> > then:
> >
> > On your reports you can have a hidden extra parameter that defaults to the
> > user id.
> >
> > Then pass this id into your stored procedures that generate the data.
> >
> > The stored procedures will do the filtering based on your own internal
> > logic.
> >
> >
> >
> >
> >
> >
> >
> > "RickyP" wrote:
> >
> >> Hello
> >>
> >> I have designed a report model, but now have a requirement to limit data
> >> access to certain people. I have tried researching about Role based
> >> security, but nothing seems to make sense. I have to design a suite of
> >> reports 16 in total. However,I have 8 users, all looking at data from
> >> difference business departments. It has been requested that each
> >> department
> >> cannot look at another department's data. The thought of cloning these
> >> 16
> >> reports, 8 times over, does not seem like a viable option.
> >>
> >> Is there an alternative?
> >>
> >> Kind regards
> >>
> >> Ricky
> >> (SS2005)
> >>
>|||Hi Jimbo
Thanks, but I think you must be confused, or maybe I have not stated my
scenario clearly. I have built a Report Model, to be used in Report
Builder, there is no facility to use SP's, maybe I have misunderstood what
you mean?
Kind regards
~Ricky
"Jimbo" <Jimbo@.discussions.microsoft.com> wrote in message
news:2CB0DA49-ED42-4E2F-8326-93322C0F429A@.microsoft.com...
> Your sp can read from the view :)
>
>
> "RickyP" wrote:
>> Hi Jimbo
>> Thanks for your post, but the model cannot not based on an SP, it is
>> created
>> from a View.
>> Kind regards
>> Ricky
>> "Jimbo" <Jimbo@.discussions.microsoft.com> wrote in message
>> news:B3DF6CA7-175E-42E7-A432-C023612E582D@.microsoft.com...
>> > If you are saying that each report needs to be viewed by each dept and
>> > that
>> > the data should be different on the report depending on who is viewing
>> > it
>> > then:
>> >
>> > On your reports you can have a hidden extra parameter that defaults to
>> > the
>> > user id.
>> >
>> > Then pass this id into your stored procedures that generate the data.
>> >
>> > The stored procedures will do the filtering based on your own internal
>> > logic.
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> > "RickyP" wrote:
>> >
>> >> Hello
>> >>
>> >> I have designed a report model, but now have a requirement to limit
>> >> data
>> >> access to certain people. I have tried researching about Role based
>> >> security, but nothing seems to make sense. I have to design a suite
>> >> of
>> >> reports 16 in total. However,I have 8 users, all looking at data from
>> >> difference business departments. It has been requested that each
>> >> department
>> >> cannot look at another department's data. The thought of cloning
>> >> these
>> >> 16
>> >> reports, 8 times over, does not seem like a viable option.
>> >>
>> >> Is there an alternative?
>> >>
>> >> Kind regards
>> >>
>> >> Ricky
>> >> (SS2005)
>> >>
>>|||Hello!
Im in the same situation as you. What we have done is that we have used the
feature "Default Security Filter" which allows
you to filter data using an entity which you base upon your view containing
DepartmentID/userID. The entity should also include
an attribute which contains the calculated "GETUSERID()".
You then specify a reliation between your entities to be filtered and this
filter-entity and you also set the "Default Security Attribute"
to be this filter-entity.
Thing is that this causes RS to produce even worse-looking SQL and makes it
hard to debug so Im currently trying to
figure out some other way, because it is almost impossible to debug 300
lines of SQL (most of it rs-schmuck).
Anyway - you can try it out, but try and read up on it on the web somewhere
first...
David|||Hi David
Thanks for your post, it seems like there isn't much in the way of security
on the net at the moment, I can't believe we're the only ones trying to
achieve this? I look in to this further - thanks Daivd.
Kind regards
Ricky
"David" <david_sundstrom_sandegard.nospam@.hotmail.com> wrote in message
news:2C9C703C-BA30-4EAA-ABEA-79F0DE457287@.microsoft.com...
> Hello!
> Im in the same situation as you. What we have done is that we have used
> the feature "Default Security Filter" which allows
> you to filter data using an entity which you base upon your view
> containing DepartmentID/userID. The entity should also include
> an attribute which contains the calculated "GETUSERID()".
> You then specify a reliation between your entities to be filtered and this
> filter-entity and you also set the "Default Security Attribute"
> to be this filter-entity.
> Thing is that this causes RS to produce even worse-looking SQL and makes
> it hard to debug so Im currently trying to
> figure out some other way, because it is almost impossible to debug 300
> lines of SQL (most of it rs-schmuck).
> Anyway - you can try it out, but try and read up on it on the web
> somewhere first...
> David