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 res

Solution 1:

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 selecton 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.

createview v_my_table asselect*from my_table
  where score >4;

grantselecton 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 selecton my_table to user_B with grant option;

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

grant selecton 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;
grantselecton 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

grantinserton my_table to my_role;

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