Handling database column privileges in datawindows

Question

How to handle DB column privileges in datawindows to avoid getting "SELECT permission denied on column"....

I have a datawindow that displays table columns, the users/roles permissions differ for certain columns and are set as per the business rules on the database level to avoid overriding application permissions...

the problem is the users logged in the system who do not have "select privilege" on certain columns will receive an error message "select permission denied on column xyz..." and the datawindow will not retrieve any information istead of retrieving only the rows for columns have "select" privilege to...

does anyone knows how to handle the columns permissions with datawindow for similar cases?

Answer 1

There is no easy way to handle this that that I've seen.

Not sure if you are looking for ideas or not, but it kind of depends on how many possible variations there are and how dynamic you want to make the solution.

I've never worked in a shop that used column level permissions at DBMS level but assume there are groups of columns locked out for various roles. If there were a handful (say 3) variations then I'd probably just make three versions of the dataobject and swap out the appropriate one prior to retrieval.

Otherwise the more difficult option would be to dynamically modify the dataobject prior to retrieval either by role or by somehow querying the system tables.

Another idea that I've never tried and not sure if it would work is to dynamically create the dataobject from SQL, I believe the function is called createfromsql, and just use select * from xyz but that would only be feasible if it's for display only otherwise you'd have a lot of dynamic modifications to make the dataobject updateable and set all the updateable columns.

Good luck.