March 02, 2007, 08:30 AM
<Nate Conn>Thanks for the suggestions.
I'm wondering if they'll work though...we're using LDAP and instead of creating users in two places (MR and Reporting Server) we just create them in MR and pass them as a single user to the reporting server.
How long will that MR user variable persist and will the Reporting Server know this to work in a standard report in the dashboard?
Thanks,
Nate
March 02, 2007, 09:24 AM
<lfrerker>Nate,
I also work for a university where we needed to limit what depts and funds the dashboard users could run reports against. Our first thought was the DBA Security, but you need to create a profile for each user, and you need to turn SQLPassthrough off for the user because SQLPassthrough doesn't use the mfd's.
We've decided to go with Reporting Objects. In the reporting objects we create a hold file which contains all the funds that the current user can view (we determine current user by &IBIMR_user). We then create reporting objects for the mfd's that are built by joining the hold file to the original mfd, thus limiting the data for each mfd. We will create a new role in MRE that will only be able to see the Reporting Objects and not all the mfd's within the domain, forcing them to use the reporting objects. If this sounds like what you're trying to achieve you can email me at lfrerker@slu.edu.
March 05, 2007, 09:19 AM
<lfrerker>We only set our users up in MRE via the MRE Administration screen. We use the &IBIMR_user in reports also, which are ran within Dashboard, and it works great. Maybe something is different in your configuration with LDAP. We're on Linux.
March 05, 2007, 11:36 AM
GCohenThe DBA Security system is meant to solve this issue. You can control it from the MRE Admin console, so that the database password is passed along every time the user makes a request to that database.
A sample MFD is shown in the WF Doc, note the VALUE area. That is where the FILTER is placed on any password...
Example: Implementing Data Source Security in a Master File
The following is a Master File that uses security features:
FILENAME = PERS, SUFFIX = FOC,$SEGMENT = IDSEG, SEGTYPE = S1,$ FIELD = SSN ,ALIAS = SSN ,FORMAT = A9 ,$ FIELD = FULLNAME ,ALIAS = FNAME ,FORMAT = A40 ,$ FIELD = DIVISION ,ALIAS = DIV ,FORMAT = A8 ,$SEGMENT=COMPSEG, PARENT=IDSEG, SEGTYPE=S1,$ FIELD = SALARY ,ALIAS = SAL ,FORMAT = D8 ,$ FIELD = DATE ,ALIAS = DATE ,FORMAT = YMD ,$ FIELD = INCREASE ,ALIAS = INC ,FORMAT = D6 ,$ENDDBA=JONES76,$USER=TOM ,ACCESS=RW, $USER=BILL ,ACCESS=R ,RESTRICT=SEGMENT ,NAME=COMPSEG ,$USER=JOHN ,ACCESS=R ,RESTRICT=FIELD ,NAME=SALARY ,$ NAME=INCREASE ,$USER=LARRY ,ACCESS=U ,RESTRICT=FIELD ,NAME=SALARY ,$USER=TONY ,ACCESS=R ,RESTRICT=VALUE ,NAME=IDSEG, VALUE=DIVISION EQ 'WEST' ,$USER=MARY ,ACCESS=W ,RESTRICT=VALUE ,NAME=SALTEST, VALUE=INCREASE+SALARY GE SALARY,$ NAME=HISTTEST, VALUE=DIV NE ' ' AND DATE GT 0,$
March 05, 2007, 05:55 PM
<JJI>Nate,
You could create your WHERE statement dynamicly. If you have a table that contains the relation between the user and his department you can do this very easy. Create a hold file that only holds the record or records valid for the user that logged in, by comparing the userid in the table with &IBIMR_user. Then use a -READ to store the department into a &var and
in your actual report you can simply code : WHERE department EQ '&var'.
Now if you have mulitple departments per user you could create a loop to contruct a OR phrase.
I don't know if you need this for every report or even a lot of reports? If so you could build a fex that does the above and run it every time the user runs a report. To do that just go to the Administration console under general there is a variable called _site_profile. Type: -INCLUDE FEXNAME\n . In this case I like to use a &&var to store the department(s).
We have done this before and works like a charm and most importantly there is almost non or zero effect on the performance of the report. That's the way we have done it If you're interested I probably can provide you some sample code and documentation on how we did it.
Hope this helps,