Oracle view usage for multi layer application security

When designing a solution which includes a database you commonly have a couple of expertise's involved in the architecture of the solution. Sometimes combined into one person and sometimes spread accords multiple people. You will commonly have an application architect and a data-model architect. The application architect will be responsible for creation the application side of things and the data-model architect will be responsible for creating the database side of the solution. In small projects you will most likely combine the roles and in more complex projects you like to have them separated. Both architects will have to consider security and will have to provide a solution that is as secure as possible. Having security implemented on several levels, both the application and the database design in good practice and will strengthen your total design.

To be able to create a secure solution both architects will have to work together and will have to follow some guidelines. One of the guidelines that can be considered good practice is the use of views where possible. Where possible is depending on the obvious fact that the application will only have to be able to read from the data presented by the view and there is a performance aspect to it. To find out if there are performance differences that are critical you will have to do some benchmark testing with a view based and a direct query based approach. The results will have to be taken into account when deciding to use a direct query on a table or to use a view. Not taking the performance into account the following approach might be a good approach to harden your application.

for example, lets state you have a table ORDER_ENTRY_B2C and you have a table ORDERS_SHIP_COMPLETE. The application you are developing is used by your salesforce to enter new orders when the make a sale in the business to consumer market segment. Secondly they will have to have the ability to check if a order has been shipped already. Two things are to be noted here. The application used will never update or insert anything in the table ORDERS_SHIP_COMPLETE and this table contains both B2C as well as B2B orders.

The most easy to implement way is to provide access to the entire database for this application as shown below. This will ensure that the application can read and write to all tables possibly limiting the user database account from reading and writing to the table for B2B orders. This example is shown below.

This will work however there are some flaws with it and some things to keep in mind.  You will have to only grant read access for the application user to ORDERS_SHIP_COMPLETE table. The flaw in this design is that the user is only limited by the application layer from reading information about B2B orders, the application is intended only for B2C orders however is on a database level not limited from reading the B2B orders. A better and more secure model is to ensure that the application will only read the shipment information from a view which is limited to only show the orders for B2C orders. Shown below you can see how we have segregated the data model in 3 sections. One section for the B2B, one for B2C and one to hold the information about completed shipments. By defining your view in such a way that it is filtering only the B2B or B2C orders for the specific section you can ensure that not only access to information is secured on a application level however that it is also secured on a database level.


By having checks and limitations on multiple levels you are taking action against the possibility that someone is gaining access to data they are not privilege to. It is however very often seen that such a security model is only applied in the application. As soon as someone is able to retrieve the information used to login to the database they are able to view a lot more information than they are supposed to. Having a multi layer security approach is always the best option to go for even if this includes setting a lot more grants on database objects and building additional views. What you have to keep in mind is possible performance issue that might arise however commonly this is not an issue or it can be resolved by using a different type of view.