row-level security

[tpaddack]

Thanks in advance for any help.

I do apologize for the size of this question, but I could sure use some help and it does not seem to be coming from Crystal.

We are starting an effort to implement row-level security on our reports and I really have no clue how to do it. Due to licensing fee issues, our are using Crystal Enterprise without named users and we would like to find a way to enforce row level security on the dbase which we are reporting.

We are using a single signon (as Administrator) from our web app and would like to pass our application's userid to a hidden parameter for which to run the report. This parameter would then be used to enforce security based on the business logic in the dbase. This way the security layer is invisible to our customer's CE Admin.

Has anyone done this? Does anyone know a good place where I can see an example of this? Reading the forums, it looks like using URL reporting might be a good way to go, but that would mean I'd have to do some serious recoding of our version of CE's ePortfolio.

All ideas are welcomed.

Thanks Mucho,

EZT

 

[mdwyer]

I am currently experimenting with a simple modification to the ePortfolio logon that allows infrequent (unnamed) users to logon with their own name but have ePortfolio log them on to a shared named account. This much works fine, and is in anticipation of eventual row-level security needs.

The next step, which I have not implemented, will store that user name, perhaps, in a cookie or session variable. The scheduling function could pick that up and pass it to a parameter field, though I'm not sure it would be invisible to the user.

 

[synapsevampire]

Row level security is best served when implemented on the database, which you neglected to include in your post, as well as the version of Crystal Enterprise.

Aside from coding the security as in mdwyer's approach, there is a 3rd party product suggested by Crystal:

http://support.businessobjects.com/library/kbase/articles/c2011766.asp

-k

 

[rhinok]

"Due to licensing fee issues, our are using Crystal Enterprise without named users..."

You're attempting to violate the CE EULA. All users that access reports through CE must have a license in CE. If you distribute reports to non-licensed users then you violate the EULA unless you purchase a Report Distribution License (RDL ~ $45K).


~Kurt

 

[tpaddack]

Hey guys,

thanks for the posts. Let me respond to each of your concerns in turn.

mdwyr:
I have been poking around in our Web Portal (it's a version of the the ePortfolio that some contractors wrote for my current company before they hired me) and I was thinking of enforcing a parameter using a session variable. While this is handy for users viewing and scheduling new reports, it does not limit the data that is visible in reports that have already been run. So if a user pulls up a pre-run report, he'll be able to see all the data contained therein. Any ideas?

synapsevampire:
I've looked at the APOS solution and it seems to work well. Yet I do not have the luxury of the added expense that it would cost our customers. Sales and Marketing put the kibosh on that one. Looks like I have to come up with something myself... for free...

As for the databases we will be reporting against, we allow the user a choice of Oracle 8.1.7, SQL Server 2000 and DB2 v7.1 (everything later than these versions is acceptable). I'd love to use Oracle's data-security feature, but sadly the rest of the vendors do not provide it and so this solution is not portable.

We are currently using CE 9 but will move to CE 10. I have looked at the Business Views feature of CE 10, but without named users via LDAP, I do not see how this feature will smoothly allow us to implement row-level security.

rhinok:
Thanks for the concern about the CE EULA, but we are distributing reports within the guidelines of our reseller EULA. We are having the users logon as a 'Guest' user and as long as the customer has purchased Crystal with enough CAL (Concurrent Access Licenses), usually 5, we are in the legal clear.
I do not see why adding a layer of data security on top of this is in violation of an EULA. If you know why, please enlighten me. I have been chatting with the guys at Crystal and they have cordially wished me luck.

In essence, I am forced to come up with a duct tape kind of home grown approach to solving this issue. Crystal says that their Processing Extensions are the ticket yet I have not written much C++ code. I was thinking I'm going to have to, and to get the Processing Extension to find the session variable and put it into a hidden parameter. Sounds like a good idea? Got any better ones?

Thanks so much for all your feedback. I really appreciate it!

EZT

 

[rhinok]

rhinok:
Thanks for the concern about the CE EULA, but we are distributing reports within the guidelines of our reseller EULA. We are having the users logon as a 'Guest' user and as long as the customer has purchased Crystal with enough CAL (Concurrent Access Licenses), usually 5, we are in the legal clear.

This is the antithesis of what you wrote above. The very clear implication in your previous post was that you were trying to bypass the purchase of additional licenses by running all reports blindly as 'administrator'.

As long as your customer purchases enough Crystal Enterprise licenses, you're probably within the bounds of the EULA. It may be up to you, as the reseller, to ensure that the client has enough licenses.

Regarding processing extensions, they probably are the right way to go. If you don't feel comfortable writing them and don't want to purchase a complete ancillary package, then you might want to hire somebody to develop one for you.

During last year's CDUGNA conference, Virtual Consulting (

http://www.crystal-partner.com/)

gave a presentation that included their processing extension solution for row-level security as part of a HIPAA-compliant solution. Contact Kevin McCann for more information kmccann@virtualcons.com.


~Kurt

 

[tpaddack]

Hey rhinok,

we are strictly staying within the bounds of the EULA. With Crystal Enterprise Standard they allow you to operate wihtout named users and give you two options, either signing on as the 'Adminsitrator' or as 'Guest'. Are you familair with Standard? I hope for your sake "not", because it is very limited in its abilities.

BTW, why do you care so much about us obeying Crystal's Licensing?

Anyway, thanks fot the tip on the developers.

EZT

 

[mdwyer]

Are you asking the user to enter a userid, or will this be passed into the CE environment by your application?

If entered by the user, who is considered Guest by CE, the userid could be entered as a parameter on the schedule screen.

If passed by the application to a session variable or cookie, the ePortfolio csp pages can retrieve the value and put it in a parameter in the schedule object. The pages can also be customized to hide a specific parameter from display on the schedule and history pages.

In either case the parameter would be used in the report to limit the rows retrieved from the database.

 

[rhinok]

I care because you posted a question in a technical professional users forum on how to circumvent licensing (at least that was what the initial post indicated), which is unethical and outside of the bounds of the technical advice normally offered here. You have since stated otherwise.

My obligation as a professional in this arena is to inform you of your licensing obligations. If you're confident that you are acting within the bounds of the EULA, great. No worries...

Don't forget, I also gave you a resource for processing extensions;-)


~Kurt

 

[tpaddack]

Well, that is a very admirable position to take omn such a topic.

Let me reiterate my thanks for the lead on the Processing Extensions. Thanks!

EZT

 

[tpaddack]

Hey mdwyer,

I was planning on passing the userid from our application and putting it in a hidden parameter like you suggested. But I still do not think it will be adequate for our needs.

Let me illustrate the use case. Say there are 20 users of our app and there are 5 departments. We want to enforce row-level security at the department level (like in our app) so that a person in department X can only see data for department X. Now we run a quarterly report. So when the people belonging to department X look at the report, how do we restrict what they see? They will be able to see department Y and Z's infomration, no? We cannot give anonymous users permissions to see only specific reports.

What do you think?

 

[mdwyer]

I haven't gotten to that requirement yet, unfortunately. It's been a promise from Crystal since we were on Seagate Info. The tack we took was to match Groups and Folders to our Departments and run a set of reports - as Administrator - for each Department. But it has never become a high enough priority to get to a single set of reports from which the users would only be able see their own department pages.

I think that's where we were introduced to the processing extension idea, but we haven't gone anywhere with it. Somebody recently mentioned in another thread that the CE10 Business Views would address this need, but the documentation I've looked at wasn't able to confirm that.

I wish I could give you results instead of suggestions.

 

[moonknightt]

has anybody found a solution to the row level security issue yet

 

[cmmrfrds]

If you have a database that supports parameterized views, then it can be done at the database level.

See thread782-1036693 for the general idea.

 

[tpaddack]

Yeah, I ended up using the web portal to do it. When the user logs on to our version of ePortfolio, via a link in our application, the user's role and id (for our application) are stored as session variables and then whenever the user runs, schedules or looks at the past history of a report, we update those parameters in the report. It works very well and made it through much QAing.

Oh, I looked into such views, but since we were supporting DB2, ORACLE and SQL Server, we could not do anything database specific.

 

[Turkbear]

Hi, Rhinok ( ~Kurt)..
Your statement
All users that access reports through CE must have a license in CE.
is not strictly accurate.
With a Processor based license the Enterprise has the license and can allow unlimited users.

 

[rhinok]

Turkbear,

potato/potato

My statement is accurate in the sense that the user is licensed to use reports through CE by virtue of a processor-based license.

That being said, this original post is over a year old. The statement doesn't fully apply, for example, to reports distributed by CE, but not accessed by CE (emailed reports, for example) if Business Objects Enteprise XI is used, because BOBJ has removed all distribution restrictions on reporting.

~Kurt

 

[cmmrfrds]

Tpaddack, thank you for sharing how you solved the problem.

 

[tpaddack]

Glad to help. It ended up being a pretty simple change but it happened in many places.

1) Updated the database views the reports depend on so that they can use security user_id and user_role parameters.
2) Updated the report template so that it offers the user_id and user_role parameters.
3) Updated ePortofolio to (a) hide the security parameters from the user and (b) apply these hidden parameters when reports are run/scheduled/viewed. You can do most of the work in viewReport.csp, rptSchedule.csp and rptHistory.csp.

I started off using their PROPMTEX web reporting but found that you had to make all your report viewing calls via the Web Component Server and it offers little control over the viewer. In subsequent versions, I switched to updating the parameters programatically via the .csp files, which offered better security (because nothing is passed in the URL or in a form) and more control over the viewer.

It works very nicely and is WAY easier and cleaner then wasting your time writing processing extensions in C++ or spending the extra money and time coding BO's business views to enforce user security.

 

[rb2005]

I have implemented row level security on my reports using business view.