Problem solve Get help with specific problems with your technologies, process and projects.

Users can only access data in their own company section

Our company is dividing into five separate sections and we want the users in each only to be able to access the data for their own section. How can we do that?

We are running Oracle 9i on Windows 2003 Server in our company and we have lots of forms and reports already.

Now, the company is dividing into five separate sections and we want the users in each section only to access the data about their own section. Also the complete data can be accessed in one place for monitoring.

In our tables, there is no field that determines the section. How can we do that with little cost and time and without having to change the source code of all applications (forms and reports)? The workflow in each section is the same. Can we use partitioning or replication or database cloning? Which one is better?

Please help. Thank you so much.

The Oracle component I would use for this task is called the Virtual Private Database (VPD) or Fine Grained Access Control (FGAC). It is known by both names. With FGAC, you can store the data in one table and the database will only let the user see the rows of data they are allowed to see. To the user, the table contains only rows for their section. Yet to another user, they are allowed to see multiple sections of data. Read this document for more information on VPD/FGAC. Pay close attention to Chapters 14 and 15.

Dig Deeper on Oracle database design and architecture

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.