Securing Reports

[mrees]

We are using ce8.5 to deliver reports to our clients. Each client has a separate folder (named after the client) into which the reports are dropped.

As all the reports are essentially the same, all the data for all the clients are stored in the same SQL table(s). Here is where a problem becomes apparent, it is up to us as the report writers to make sure that we put the correct selection criteria on for each report, so that the client only views their own data.

Is there anyway to put in some kind of failsafe to stop the wrong data going to the wrong client? It is not practical for us to store each clients data in separate table, or to write a view/stored proc for each clients' report.

Is it possible to match the client name in the report, to the name of the CE Folder, and if the 2 don't match fail to produce output?

I look forward to hearing any ideas that may potentially save my job!

mrees

 

[Turkbear]

If the reports are in separate folders they are unique objects so the selection criteria, once set, will only apply to that report - if you do not allow your users to modify the parameters, then just the 'correct' data should be shown..
The security applied to the folder should prevent the wrong folks from running someone else's report.

You did not mention the database , but look into building views for each client and only allowing accerss thru that view.

 

[mrees]

Thanks for that.

The data comes from SQL 2000. So far the only solution we have are as you mentioned, using views to limit the data (this in my opinion is ideal). The trouble with that is we have 20+ clients, who will eventually each receive a suite of 60+ reports. Our DBA's aren't keen on the "one view per report per client" situation, and we (the report writers) are not keen on relying on ourselves putting on the selection criteria.

Really we would like to have something in place that would stop the data going out if we negleted to put the selection criteria on.

 

[Turkbear]

Hi,
Without views, all data that the userid allows access to
can be retreived - so if the report designers fails to limit the data - [nosmile]

You choices seem to be either refine the access rights
for the userid ( and, in Sql200,that probably means custom tables or views) or rely on the report designers to do it right.

( In Oracle there is something called fine-grained access control ( or Virtual private database ) that 'appends' an additional where clause limiting record access based on the userid to any database query..I am not sure the same thing can be constructed in SqlServer)

 

[SurfingGecko]

This is one of those things that would be nice to have built into CE. Maybe one day ;-)

As you mentioned, Views would be nice, but are not an ideal solution in all situations.

I ran across a company that develops tools that add-on to CE and expand its functionality. APOS solutions has a tool where you can isolate report parameters based on user and this will also provide some additional auditing.

http://www.apos.on.ca

Cheers,

SurfingGecko

http://www.surfinggeckosoftware.com

Home of Crystal Ease

 

[SteveRemington]

Have you considered using the Processing Extensions capability that comes standard with with CE8.5. One thing that this functionality allows you to develop "View-Time-Security" for your reports. This means that you only have one set of reports because when the client logs in the CE system uses the client's user id to dynamically filter the report. The only maintenance required once it is installed is the updating of a simple entitlements table when a new client is given access to your CE system.

Processing extensions are custom DLLs that intereact with the CE Page Server or Report Server at view time or schedule time respectively of the report. You can do almost anything (within the scope of the currently available PE SDK) you like.

Documentation for the PE SDK is included on the CE CD-ROM. You can either have something developed yourself or approach Crystal Decisions Professional Services direct to develop something for you.



Regards,
Steve Remington
Wave Business Solutions
steve.remington@wavebusiness.com.au

 

[AnnetteH]

Multiple views, one for each individual client should not be necessary. Presumably you have some field stored in your tables which allows you to differentiate which rows belong to which user. Hopefully that is the actual user id. If so, then (I'm not familiar with SQL Server syntax, but) for Oracle you would create the view as shown here.
SELECT ...
FROM ...
WHERE <your field name>=USER
AND ...;
Where <your field name> is the table column storing the user id who owns the record and USER is the Oracle psuedo column that always equals the logged in user. Viola, you just need one view and it works for all users.

If you're not using the actual user id to indicate which user owns which rows, it is more complicated. You may have to create a translation table which translates whatever you are using to the appropriate user_id and join that table into your query view.

 

[BeginnerUser]

mrees,
I'm not sure how long ago this thread is from. I'm having the same issue now. May I know if you've figured out how to handle this?

Thanks!