topic Re: Oracle Database: Implementing Row level security in Operating System - HP-UX

Oracle Database: Implementing Row level security

Sanjay Verma — Mon, 25 Nov 2002 05:49:44 GMT

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

Re: Oracle Database: Implementing Row level security

Christian Gebhardt — Mon, 25 Nov 2002 07:02:55 GMT

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

Re: Oracle Database: Implementing Row level security

Yogeeraj_1 — Mon, 25 Nov 2002 07:53:01 GMT

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

Re: Oracle Database: Implementing Row level security

Volker Borowski — Mon, 25 Nov 2002 17:42:10 GMT

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