CODEWITHSUNDEEP
sqloracle-databasesecuritypolicy
bloodstorm17
bloodstorm17

Reputation: 521

SQL Database Restrict View of Data

Okay so I am new to SQL and was just wondering if there where a way to limit who would have access to what type of data in terms of when retriving information from the tables. Like for example I had a table with information about a client which would have his name, phone, address, SSN, salary, and etc. I want to figure out if there is a way to limit what is being shown based on who is viewing that data. As in a Person A can see everything while Person B can see everything except for SSN and Salary

EDIT:

Could the use of a trigger to limit the view of a certain group work?

Upvotes: 0

Views: 1397

Answers (2)

DazzaL
DazzaL

Reputation: 21973

To make columns not appear for certain users, you'd either need distinct views for each user group (ie view A that didnt have SSN, salary for users in the restricted group etc). or use fine grained access control FGAC: http://docs.oracle.com/cd/B19306_01/network.102/b14266/apdvcntx.htm

fine grained access control applies straight to the table, so you wouldn't need views to implement that.

e.g. a very simple test to show you. lets say anyone with the Oracle role "SSN_AUTH" can view SSN/Salary for all rows. those without it,cannot.

SQL> create table person(id number, name varchar2(200), ssn varchar2(20), salary number(*,2));

Table created.

now we create a function (by all means, put this in a package for real code). the function will apply a silent predicate exists (select null from session_roles where role = 'SSN_AUTH') for every query fired on the person table. i.e. that predicate will mean that if you don't have an enabled role called SSN_AUTH, you won't see the data. 

SQL> create or replace function person_rls (p_owner in varchar2, p_name in varchar2)
  2  return varchar2 as
  3    v_sql varchar2(2000);
  4  begin
  5     v_sql := 'exists (select null from session_roles where role = ''SSN_AUTH'')';
  6     return v_sql;
  7  end person_rls;
  8  /

Function created.

now, i dont want to supress rows (though we could do). we just want to supress column data. so we add this function as a policy to the table and tell it the columns to secure:

SQL> BEGIN
  2       DBMS_RLS.ADD_POLICY(object_schema=>user, object_name=>'PERSON',
  3                           policy_name=>'SEC_PERSON', function_schema=>user,
  4                           policy_function=>'PERSON_RLS',--our function
  5                           sec_relevant_cols=>'ssn,salary', -- secure these cols.
  6                           sec_relevant_cols_opt=>dbms_rls.ALL_ROWS);
  7  END;
  8  /

PL/SQL procedure successfully completed.

SQL> insert into person values (1, 'DazzaL', 'asdklakjd', 10000.12);

1 row created.

SQL> commit;

Commit complete.

now if we don't have the role set:

SQL> set role none;

Role set.

SQL> select * from person;

        ID NAME                 SSN                      SALARY
---------- -------------------- -------------------- ----------
         1 DazzaL

the salary + SSN is blank..but if we enable the role.

SQL> set role all;

Role set.

SQL> select * From session_roles;

ROLE
------------------------------
CONNECT
RESOURCE
SELECT_CATALOG_ROLE
HS_ADMIN_SELECT_ROLE
PLUSTRACE
SSN_AUTH  <--- we have it now.

SQL> select * from person;

        ID NAME                 SSN                      SALARY
---------- -------------------- -------------------- ----------
         1 DazzaL               asdklakjd              10000.12

the data magically appears.

Upvotes: 1

Equo
Equo

Reputation: 200

You can use views. After that it depends on usage, how the persons access to data (some access rights or user groups etc.)

Upvotes: 2

Related Questions