One more interesting thing encountered this week, during integration of ADF Task Flows into WebCenter 11g PS3. We were using ADF library JAR files and integrating ADF Task Flows into WebCenter 11g PS3 Menu Model. ADF Task Flows were rendered fine inside WebCenter, however data was not coming. Funniest thing, while checking detail log, we saw that ADF appends multiple (1 = 2) conditions to original SQL statement - naturally its why data was not queried, but why (1=2) was appended? First we thinking, may be WebCenter is playing some evil games and modifying SQL statements on runtime. However, at the end it was proved WebCenter was unrelated to this problem, problem was related to ADF Security and ADF BC EO's permissions. The thing is, some of the EO's from common library were enabled with ADF Security read permissions and packaged into ADF libraries. Once such EO's are imported into secured ADF environment, permissions are evaluated on runtime - if user is not granted with read permission, no data is returned. Okej, but what we really didn't expected, is to see ADF generating (1=2) conditions for those EO's, where we didn't had permission to read.
Download working sample application - EOReadPermission.zip. Below I will explain complete scenario for this example.
Main ADF application imports ADF library JAR file with common EO objects:
This application implements local EO for Employees and is using two imported EO's two create SQL Join with Employees:
As I was explaining above, Departments and Jobs EO's are coming from ADF library.
We login into our main application with user redsam/welcome1:
Page is rendered correctly, but no data is returned:
If we check generated SQL statement, we will notice (1=2) condition applied two times:
Why two times? Its because we join two imported EO's into main VO. These imported EO's are enabled with ADF Security permission. So, ADF instead of checking if user is allowed to use EO for query, just appending (1=2) condition and sending SQL for database processing. I don't want to criticize this, but imagine having complex VO with multiple joins - generated SQL always will be sent for database processing, isn't it bad for performance? It would be smarter to verify EO security read permissions earlier, in the middle-tier - and not to generate SQL when there are clearly no results will be returned.
It was quite hard to believe (1=2) is generated because no authorization to access EO for reading. But indeed, our EO from reusable library was enabled with security for read access:
We copied required EO read security permissions into main application jazn-data.xml:
Permissions are declared now and visible inside main application:
SQL join is performed correctly, data is returned:
Download working sample application - EOReadPermission.zip. Below I will explain complete scenario for this example.
Main ADF application imports ADF library JAR file with common EO objects:
This application implements local EO for Employees and is using two imported EO's two create SQL Join with Employees:
As I was explaining above, Departments and Jobs EO's are coming from ADF library.
We login into our main application with user redsam/welcome1:
Page is rendered correctly, but no data is returned:
If we check generated SQL statement, we will notice (1=2) condition applied two times:
Why two times? Its because we join two imported EO's into main VO. These imported EO's are enabled with ADF Security permission. So, ADF instead of checking if user is allowed to use EO for query, just appending (1=2) condition and sending SQL for database processing. I don't want to criticize this, but imagine having complex VO with multiple joins - generated SQL always will be sent for database processing, isn't it bad for performance? It would be smarter to verify EO security read permissions earlier, in the middle-tier - and not to generate SQL when there are clearly no results will be returned.
It was quite hard to believe (1=2) is generated because no authorization to access EO for reading. But indeed, our EO from reusable library was enabled with security for read access:
We copied required EO read security permissions into main application jazn-data.xml:
Permissions are declared now and visible inside main application:
SQL join is performed correctly, data is returned:
Andrejus, We are dealing with exactly same problem at the moment. But is this standard way to add grants in the jazn-data? Why it isn't working when the read permission is enabled at the EO level itself?
ReplyDeleteYes, its standard way. Permission is declared in two places - EO and then mapping to roles inside jazn-data.xml
ReplyDeleteThats fine. This blog is more 1=2 query, when no permission.
Thanks,
Andrejus
Thanks a lot for this great info. I am facing this issue and now able to relate with permission thing
ReplyDelete