Operating System - HP-UX
1748008 Members
4709 Online
108757 Solutions
New Discussion юеВ

Re: 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 3
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