Oracle Database: How to restrict a user to see only their own data?
For example, I have a table like this:
+-----------------------------+
| sample_table |
+-----------------------------+
| col_1 | col_2 | col_3 |
+-----------------------------+
User_1 Entered : |'val_1_1'|'val_1_2'|'val_1_3'|
+-----------------------------+
User_2 Entered : |'val_2_1'|'val_2_2'|'val_2_3'|
+-----------------------------+
now I want each of the above users to issue below request:
Select * from sample_table
result for be: User_1
+-----------------------------+
| col_1 | col_2 | col_3 |
+-----------------------------+
|'val_1_1'|'val_1_2'|'val_1_3'|
+-----------------------------+
and for : User_2
+-----------------------------+
| col_1 | col_2 | col_3 |
+-----------------------------+
|'val_2_1'|'val_2_2'|'val_2_3'|
+-----------------------------+
What's a good way to restrict each user's access (select-update-delete) to only his / her data? (all users can insert data, but I have to view it or change their data). Thanks for your response and guidance.
Note: I mean , but it would be helpful if someone gives a solution for . Database users
application users
Add the Data_Owner field to the table filled with the USER function. Do not allow direct access to the table. All access is through the view, which provides only those rows that match the USER value of the person requesting the view. Trigger "instead of" trigger "can use the USER function to manipulate the DML.
create view Sample as
select col_1, col_2, col3
from Sample_Table
where Data_Owner = USER;
For app users, instead of USER, just use the UDF that supplies the app username or USER if not there. The details of this will, of course, depend on how you maintain the app usernames.
source to share