Hi,
I just wanted to post a solution that I have regarding setting access to tables when a report is run. This would alos work for those requiring to fire a procedure or command before the main report.
I created a report with a procedure, in the procedure I put the SQL command {SET ROLE HD_TRILLROLE}. This role grants SELECT access to the required tables for the report.
For this to work, I had to specify this command in our Oracle Database.
ALTER USER <Username> DEFAULT ROLE ALL EXCEPT HD_TRILLROLE
I then created a Subreport for the Main Report. The first command fires, grants select access for that session, then fires the subreport(s).
Of course, since the SET ROLE command is for the current session, this solution works if all the tables are in the same database.
I just wanted to post a solution that I have regarding setting access to tables when a report is run. This would alos work for those requiring to fire a procedure or command before the main report.
I created a report with a procedure, in the procedure I put the SQL command {SET ROLE HD_TRILLROLE}. This role grants SELECT access to the required tables for the report.
For this to work, I had to specify this command in our Oracle Database.
ALTER USER <Username> DEFAULT ROLE ALL EXCEPT HD_TRILLROLE
I then created a Subreport for the Main Report. The first command fires, grants select access for that session, then fires the subreport(s).
Of course, since the SET ROLE command is for the current session, this solution works if all the tables are in the same database.