Hi, We are using CR XI on Oralce. We applying RLS (Row Level Security) in DB. RLS will base on information we set in the session (by calling trans_pkg.set_username('abc')) to decide which data rows can appear in dataset. But CR seems to have its own Connection to Oracle. We have no way to run a package like: trans_pkg.set_username('abc') before CR query data. It is now the big security risk to our system. If you have any solution please help me.
A:
I have not used crystal against oracle, only sql server, but the answer should be the same. When you set thwe connection info for the report, you need to specify the user name and password. The database should use this info to identify the user and assign correct access permissions.
Ray
2010-10-26 17:16:24
Thanks for replying, Our system is 3 tiers, user Database is the same for all sessions. The only way is executing some procedure before querying data. Any suggestion?
QuangND
2010-10-26 17:19:52
do you mean execute a 'stored procedure' in the db, or a procedure in you data access tier, or what? in any case, what does the procedure do?
Ray
2010-10-26 17:24:27
execute stored procedure. we have a package trans_pkg. In this package, we have a global variable g_username. trans_pkg.set_username(p_username varchar2) simply set g_username:= p_username; and in RLS context we filter only rows that this username can access.
QuangND
2010-10-26 17:29:08
I would guess that your report is using a different connection to Oracle than your stored proc call, and it therefore doesn't see your g_username variable. It sounds like you will need to use the Crystal API to set the user name in the report before it gets run. Look at Table.LogOnInfo.ConnectionInfo. You use it to set the db name, user name, and password. Again, this works for sql server - my Oracle experience is limited and way in the past, so it may not work exactly the same way.
Ray
2010-10-26 17:37:08
How are you using CR. Are the reports in a Business Object Server or are you using a crystal reports viewer
Conrad Frix
2010-10-26 17:42:23
Just Viewer. Our Report Server is a replacement of BO Server.
QuangND
2010-10-26 17:43:34
A:
Since you're using the crystal report viewer instead of a BO Server you can just use DataSets as in this answer
If you were using a BO Server you'd need to create another procedure that first calls sets the user name and then execute the report source procedure or the query.
Conrad Frix
2010-10-26 17:55:35
In our system, user can create new rpt files and upload to report server and others can view those reports. In a rpt file, there is a various of queries. I think we can use that approach in this case.
QuangND
2010-10-26 18:10:19
Well I hope it helps. BTW you may want to start accepting answers (when there is one) in encourages more/better answers.
Conrad Frix
2010-10-26 18:48:23