Friday, 13 May 2016

Using views with check option in Apex to protect your data

The other day I noticed a problem in an time registration system built with Apex. One employee could register hours under the name of another employee. This was possible because the application had access to the table with all the registrations and the filtering to the employee was done on the page.
One way to prevent this is to use views refering to application items. On the EMP table we create a view restricting it to one department:

create view emp_for_dept as select * from emp where deptno = v('APP_DEPTNO');

Outside of Apex this view will return no rows. Within an Apex application with the application item APP_DEPTNO set to 10, 20 or 30 the view will return the rows for that specific department. This way it is possible to limit the visible rows in a report.
It gets even better when we create the view with check option:

create view emp_for_dept as 
select * from emp 
where deptno = ( select v('APP_DEPTNO') from dual ) 
with check option;

The check option prevents rows to be inserted or updated, that do not comply with the where clause of the view. If you have a form based on this view, you cannot change the department. If you try to do so, you will get an error:

So in the case of the time registration system mentioned above the Apex application could be based on views limited by an application item. On logon this application item can be filled with the ID of the employee and she will only see her registrations. Moreover she can only enter new registrations that match the views selection criteria, so only for her own hours.
It is an elegant and easy way of solving a security issue.

Happy apexing,
Dick Dral

PS For better performance the call to v() is wrapped in a subselect. Thanks for the tip, Andre


Anonymous said...

Hi and thank you,
While the idea to protect data in such a way is very good in priciple.
On thing should be changed:
... = (select v('APP_DEPTNO') from dual)
(see context switch parsing behavior of SQL statements when fuinction calls in the WHERE clause)


Dick Dral said...

Hi Andre,

you are right. Thanks for the tip.
I have changed the blogpost accordingly.

Dick Dral

Morten Braten said...

Even better, use a sys_context call to specify the filter value (where deptno = sys_context('my_app_context', 'current_dept_no')) and use the "Initialization PL/SQL Code" attribute of the application in APEX to call a package that sets the value of the context item.

- Morten