How to manage security with free hand sql reports?

building reports desktopintelligence
1

I have: a DB User dbX and dbZ
The user dbZ has a DB controlled restriced view on the data.

I want to build ONE (!) free-hand-report that is valid for both BO Users. My BO users boA and boB should use their appropriate DB connection, that is:
boA uses the FH Report with dbX and
boB uses the very same report with the dbZ connection…

How can I do this? AFAIK I can only assign one static DB Connection to a specific FH report, that is the same for every BO User that accesses the Report. If that is true, how else can I solve this issue?

prinzartus :austria: (BOB member since 2006-09-07)

2

All you can do is restrict access at the database level. In other words, restrict access to the database itself, and possibly consider row-level security (talk to your DBA about this).

However, in my opinion, freehand SQL reports are evil :reallymad: Imagine if you needed to change or drop a column at database level. You have no visibility on the use of this column in freehand sqls. Steer clear of them is my advice. You will only be building a maintenance nightmare for yourself.

jac :australia: (BOB member since 2005-05-10)

3

Thanks for the quick answer.

The access restricition already works on DB level (view limitation depends on the DB User)! That’s why I said “I have”.

Anyway, I found the option “use database credentials” when choosing the appropriate DB connection in my FH STMT. When cheked, the DB connection is chosen dynamically according to a specific BO user. Now I only need to know how to associated a specific BO User (boA) to a specific DB connection (dbX), which is dynamically chosen if boA runs the report, whereas dbZ should be used if boB runs the report.

This idea is similar to the connection paradigm in older BO Versions AFAIK.

prinzartus :austria: (BOB member since 2006-09-07)

4

I know managed to pass through the db (source) credentials via the BO User to my DeskI Report.

As long as my bo users boA and boB refresh (simuntaneously) the report using DeskI everthing is fine.

BUT…

If we upload the report to the repository, the security is not granted!! Sometimes boA sees boB’s results and vice versa. Furthermore, which IS a major security bug from BO, the report refreshed by boA shows “boB” as the return of “CurrentUser()”

Any idea why the WebInterface does not use the pass through source credentials correctly?

Can I force a clear report when opening it (even before a refresh) with VBA?

prinzartus :austria: (BOB member since 2006-09-07)

5

I agree with the above, IMHO freehand SQL has no place in a BO production environment, if the universe and the underlying schema are designed correctly it shouldn’t be required.

There may be something that you can do in VBA?

That having being said, and I know this seems really obvious, but why can’t you have two reports?

Mak 1 :uk: (BOB member since 2005-01-06)

6

The problem is solved. It was some “cache server setting” unchecked. My BO Admin provided following screenshot. It may help, though it is in german…

EDIT: unfortunatley the behaviour still is the same. It has not been solved permanently by the “server cahce” option. :cry:
Server_cache_settings.JPG

prinzartus :austria: (BOB member since 2006-09-07)