Does anyone know how to parse SQL statements, and again build in back using Java? This is required because I would need to add extra columns to WHERE clause based on the some conditions. FOr example, based on the Logon user, I would need to decide whether the user is restricted to see the records like it is restricted outside USA.
Rather than allowing the users query the tables directly and then trying to filter-out what they they are not permitted to access, try creating a collection of views for each of the user roles, and let the views constrain what the role can access.
Arulprakasam Narasimhan wrote:These SQLs are dynamically typed by business users in UI and before it is executed I need to capture, check for security restrictions based on the logon user and edit or add where clause if needed.
This is a recipe for disaster. It would be a full-time job just chasing down and closing loopholes.
SQL is fairly easy to parse and you can use compiler-compiler tools like antlr to do that, but, as I said, the real issue isn't so much parsing as it is in anticipating and handling all the "gotcha's". Users can do horrible things with even the most basic SQL statements whether they intended to or not.
More likely what you really need is some sort of Business Intelligence tool that can build and run the SQL for them and return results in a format that's friendlier to their needs. There are plenty of those around.
When it comes to destroying a civilization, gas chambers cannot hold a candle to echo chambers.