Learned that an ad-hoc Jaspersoft user can, through "Topics" select data sources and supply SQL Queries.
Has anyone run into performance issues from users supplying ill-formed SQL code? Is there any way to turn off this query capability, limiting it to only administrative/support staff?
Typically, if SQL is needed, we have developers do this in Test or Dev environment, not production. Then, when DBA approves code, we push to production.
If we grant users ad-hoc capabilities in production, then they also get this query capability through "Topics." Haven't yet found a way to turn this off. The saving part of this is that it's not that easy to get to this feature, and once there, it's not intuitive, especially for anyone not familiar with SQL. And, there don't appear to be any visual aids - no listing of tables and attributes available from the data source. Appears that one would need/want to do their SQL development in something like SQLDeveloper, then copy/past into this Topics\Query feature.
So, guessing that even though its available and could be abused, its probably rarely found, infrequently used when found, and those that are successful in running a query from here are probably skilled in SQL - that real risk is low-to-zero.
From anyone with experience in this area, is the risk real or only perceived? And, anyway to turn this feature off? Is yes, can we turn it off for most users, while keeping it on for a select few developer types?
If you topics are already prefiltered or restricted, there is little damage the user can do performance wise. However, they may waster their time testing and trying to create their own query.
If you supply documentation/instructions to your users on ad hoc, perhaps add a note in there stating if they need help or are curious about SQL to contact your team. You can then redirect at that touch point.
We have limited the number of users who can access ad hoc. 90% of the users just need the company reports and not all the hassle of getting 'the data'. This seems to be an acceptable comprise for our user-base.