oracle11goracle-apexoracle-apex-5.1oracle-apex-5

Oracle APEX User Rights and Privileges


I do not have much experience and I would like to know if there is an easy way to create user rights and privileges, so that each user can access only specific records from the database tables, based on the level he belongs to.

More specifically, suppose we have a group of companies where this group has some companies and these companies have some branches and the branches have some users.

I want the user belonging to the "group of companies" level to have access to and view all the entries in the database related to that group and what is below it (its companies and the branches of these companies).

The user who belongs to the "company" level should have access and see only the files of this company and the branches that this company may have in the database.

The user belonging to the "Branch" level should only be able to access and view this barnch records in the database.

And finally the user belonging to the "End User" level to have access and see only the records created by the user in the database.

Of course level "administrator" will have access to all records in the database.

I thought of creating a user table with a field "User_Level" and in each table to enter USER_ID where based on this I can find the level of a user but how can I restrict access based on the Group of Companies or the Company or the Branch where it belongs?


Solution

  • In APEX you can create authorization schemes to determine what components a user has access to within an application - but that is just a part of the answer to this question. Your question is about filtering the data that is showed to a user based on certain criteria. There are a couple of possible solutions to this. Since this is a very broad question I'm just going to give you pointers/concepts to start your research. Up to you to determine what solution/combination is most suitable for your implementation.

    Concept: Multi-Tenancy If the data is used by multiple tenants then add a tenant_id to each table that has tenant specific data. In your case a tenant should be a branch. A simple design could be a groups table (to hold branch - companies - company groups), a group_members table (to define relationship between branch - companies - company groups OR between any group and a user) and a users table.

    Concept: VPD This is a feature in the oracle database that allows a transparent implementation of security rules. In the application you'll define a simple select like

      SELECT * FROM emp
    

    But the VPD implementation will automatically add a where clause to the query to only show the records defined in the VPD policy. This makes developing the application a lot easier since there is less room for errors. Note that this database option could not be included for your licence. There is also something called "Poor Man's VPD" that does not use the VPD option. Google on how to implement this in your apex application.

    Just do it all by hand: This is the least preferred option but it can be done. For every component where a select is done, manually add a where clause to restrict the returned rows. However this is very maintenance intensive and there is a ton of room for errors - obiously the data model will still have to support the striping of the data.

    This blog post by Jeffrey Kemp might give you some pointers as well: https://jeffkemponoracle.com/2017/11/convert-an-apex-application-to-multi-tenant/ - go through the "further reading" section at the bottom.