Simpler Navigation for Servers and Operating Systems - Please Update Your Bookmarks
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
If you have bookmarked forums or discussion boards in Servers and Operating Systems, we suggest you check and update them as needed.
General
cancel
Showing results for 
Search instead for 
Did you mean: 

Oracle Database: Implementing Row level security

SOLVED
Go to solution
Sanjay Verma
Super Advisor

Oracle Database: Implementing Row level security

Hi Friends,

Would like to know if it's possible in Oracle to implement row/field or data level security.

For e.g., My database contains data from both Australia & Singapore and would like my reporting person to see data relevant to only Australia or only Singapore. Is that possible? If so, will appreciate if you can let me know the methodology to do that.

Thanks in advance.

Regards,
Sanjay
Co-operation - The biggest chain reaction
3 REPLIES
Christian Gebhardt
Honored Contributor
Solution

Re: Oracle Database: Implementing Row level security

Hi

In oracle9i you can implement row-level-security (virtual private database). You can use package DBMS_RLS

There's no easy way to implement field-level security
Yogeeraj_1
Honored Contributor

Re: Oracle Database: Implementing Row level security

hi,
The solution is FGAC - Fine Grained Access Control.

You will see FGAC referred to with various names in different publications. The following are synonymous terms for this feature:
- Fine Grained Access Control
- Virtual Private Database (VPD)
- Row Level Security or DBMS_RLS (based on the PL/SQL package DBMS_RLS that implements this feature)

Should be OK as from Oracle 8.1.7 and upwards...

With this feature, data in the database is always protected. No matter what tool accesses the data, we are ensured our security policy is invoked and cannot be bypassed. It also allows for evolutionary changes to security policies with no impact on client applications.

Also note that FGAC will not impact performance any more than performing an operation in any other fashion.

hope this helps!

Regards
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Volker Borowski
Honored Contributor

Re: Oracle Database: Implementing Row level security

Hello,

if your reporting is a selfmade application,
you might try to use a view, which seperates data by the appropriate column.

CREATE VIEW AUSTRALIA_DATA as
select * from datatable
where location='AUSTRALIA';

CREATE VIEW SINGAPORE_DATA as
select * from datatable
where location='SINGAPORE';

Now you grant SELECT only on the views, not on the datatable and run the reports with the related view.

Quick-Hit, no application redesign, just exchange the tablename.
Hope this helps
Volker