If you want to ensure in Oracle that a user can just access his own records, for example example his contacts, you can do it in several ways.
- The first would be to realize it in the application, that means you add an where-clause there. So this where-clause has to be added in every select-statement that access the "contacts" table. I dislike this option because it is to much wacork and very soon anyone will forget the where-clause and somebody can see data he is not supposed to see.
- The second would be to use the "fine grained access control" a feature from Oracl. With this feature you can add a where-clause to the table. That is a perfect solution but has one big disadvantage, the tuning of an select-statement. The access-control adds another select-statement that restricts the original query. If you have a huge table that you want to restrict, it is uncomfortable to tune. But nevertheless it can be the perfect solution. In my case it was to untransparent to other developers and we would have to rewrite a huge part of our application. So i choose the following.
- Another way would be to use an application context and use views for access-controls. Following example to demonstrate that.
CREATE CONTEXT TESTCONTEXT USING SCOTT.TESTPACKAGE;
This creates a context and defines that just the package scott.testpackage is allowed to set values to this context, this Package has the procedure setUser just a minimal version without error-handling etc.:
PROCEDURE setUser (pUser IN USER_INFO.USERNAME%TYPE) IS
DBMS_SESSION.SET_CONTEXT( 'UserInfoContext' 'UserName', pUser);
END;CREATE OR REPLACE VIEW V_CONTACTS AS SELECT * FROM CONTACTS WHERE USR = SYS_CONTEXT('UserInfoContext','UserName')
After setting the user (at logon-time) with "exec scott.testpackage.setuser('iamthesuperuser');" you get just the records from this user.You could say that is would also be possible to use a simple pl/sql-function and that is true, but there would be performance-costing switchings between the pl/sql and the sql-engine and also the optimizer would not be able to interpret this pl/sql-function as a constant and so it would be much slower, i tested it with pl/sql and with the context and using the context is much faster.