I have a page which displays account_data on basis of dept.
First there are list of responsibilities(similar to roles on basis of which authorizations would be set up..
Responsibilities
Resp_id Name
1 RESP_ACCOUNT_DEPT_1
2 RESP_ACCOUNT_DEPT_2
3 RESP_ACCOUNT_DEPT_3
Then we have the table where responsibility for each account type set up, basically which account would have what responsibility.
select dept_name,account_name,resps from dept_account_resps;
DEPT_ACCOUNT_RESPS
DEPT_NAME ACCOUNT_NAME RESPS
1 ACC_DEP_1 RESP_ACCOUNT_DEPT_1
2 ACC_DEP_2 RESP_ACCOUNT_DEPT_2
3 ACC_DEP_3 RESP_ACCOUNT_DEPT_1
4 ACC_DEP_4 RESP_ACCOUNT_DEPT_2
Now we have another page that should display accounts on basis of responsibilities.
If APP_USER has responsibility as defined it should view only those accounts..
select account_name from account_info;
IF responsibility = RESP_ACCOUNT_1
then it should display only the assigned accounts as below:
ACCOUNTS
ACC_DEP_1
ACC_DEP_3
Any guidance as to how this can be achieved?
This is not an apex question as such, it is about restricting data access based on who is executing the query (aka Row Level Security). There is nothing you can configure in apex about this, it is at database level or query level that this needs to be tackled. There are 2 options:
-
The “manual option” Adjust each individual query. Inner join to the users and the responsibilities table to so only accounts that a user has access to through a responsibility is shown. This is a lot of work and prone to errors.
-
Implement VPD (Virtual Private Database). here is an AskTom video about how to implement this. This video explains how to do this in APEX. Note that there might be licence restrictions here – that is up to you to check.