CODEWITHSUNDEEP
sqloracle-database
Aviral Srivastava
Aviral Srivastava

Reputation: 4582

How to write a policy in Oracle SQL that restricts the access to non-owners for a table?

I want to restrict non-owners (excluding sys dba) from accessing the table i.e. users can access only the data of their own but sysdba should be able to access any data without restrictions. Finally, I want to attach the policy to the table.

For eg: This is the table:

id name score
1 SYS 4
2 RHWTT 5
3 LEO 4
4 MOD3_ADMIN 5
5 VPD674 4
6 SCOTT 5
7 HR 4
8 OE 5
9 PM 4
10 IX 5
11 SH 4
12 BI 5
13 IXSNEAKY 4
14 DVF 5

This table is created by A and B is able to run select queries. I want to design and attach a policy that lets only A and sys_dba access to this table.

I read documentation but could not deduce the logic for my case. I am new to Oracle SQL as well.

Upvotes: 0

Views: 216

Answers (1)

Littlefoot
Littlefoot

Reputation: 142705

You don't have to do absolutely anything about it.

Tables (and data stored within) are owned by user A. Nobody can see it unless user A grants certain privileges to other users, such as user B.

That can be done by granting select privilege, i.e.

grant select on my_table to user_B;

and user B would then fetch data as

select * from user_A.my_table;

User B won't be able to modify data (as it wasn't granted insert/update/delete).

Furthermore, you (as user A) can create a view which selects only part of data, e.g.

create view v_my_table as 
  select * 
  from my_table
  where score > 4;

grant select on v_my_table to user_B;

Doing so, user B would see only rows whose score is larger than 4.

If there's user C, it can't see absolutely anything. If you'd want it to see some data, you'd do what you already did with user B - grant certain privileges.

However, there's an option to let user B "forward" privileges to other users - you'd use with grant option, e.g.

grant select on my_table to user_B with grant option;

That would allow user B to grant select to other users, e.g.

grant select on user_A.my_table to user_C;

Finally (talking about this answer), if there are many users you'd want to grant such privileges to, you can create roles. Then you'd grant privileges to a role, and grant role to another user(s). It allows you to modify roles depending on your (and other users') wishes. 

create role my_role;
grant select on my_table to my_role;

For example, for beginning, you could grant select to my_role, and then grant my_role to users B, C and D. 

grant my_role to user_B;
grant my_role to user_C;

Later, you can grant insert to my_role

grant insert on my_table to my_role;

and all users granted my_role would automatically be able to insert rows into user A's my_table.

Upvotes: 2

Related Questions