I'm just starting to browse through the Oracle APEX tutorials as I'm thinking of moving into this technology. It's very comprehensive and I'm slowly finding my way around the developers pages.
One question that's jumped out straight away is how to keep user data segregated. To get started I'm building a simple CRUD app and I would like each user to only have access to their own data.
Is this achievable using the APEX GUI interface or do I have to immediately dive into PL/SQL?
Thanks
That would be some kind of a VPD (Virtual Private Database).
What Is Oracle Virtual Private Database?
Oracle Virtual Private Database (VPD) enables you to create security policies to control database access at the row and column level. Essentially, Oracle Virtual Private Database adds a dynamic WHERE clause to a SQL statement that is issued against the table, view, or synonym to which an Oracle Virtual Private Database security policy was applied.
A (relatively) simple way to do that is to alter your tables so that you'd know which data belongs to which user - Apex offer the :APP_USER substitution string which is equal to currently logged user:
alter table emp add app_user varchar2(30);
Then, you'd manually add a new piece of a condition to all your WHERE clauses (in interactive reports, classic reports, ...), e.g.
select empno, ename, job, sal
from emp
where deptno = :P1_DEPTNO
-- add this to all queries
and app_user = :APP_USER
-- end of add this ...
order by ename;
Should you do it for all tables? Not necessarily; if you can JOIN those tables to the one that contains the "owner" information (for example, EMP I mentioned above), you could use information stored within.
