Synonyms & materialized view

Question

1. I have two tables. a) student b) restricted_student.
   2. two users a) admin b) user
   3. one synonyms stu for both the users. in admin it refer to student but for user it refer to restricted_student.

I want to create a materialized view MV . The code of materialized view look like

CREATE MATERIALIZED VIEW SELECT TABLE_NAME,COLUMN_NAME FROM USER_TAB_COLUMNS;

such that if i am connected to admin then it should refer to student and for user it should refer to restricted_student by passing the only synonyms name stu. something like

SELECT TABLE_NAME,COLUMN_NAME FROM USER_TAB_COLUMNS E='st';

the result i wanted when i connected to user it should give the restricted_student table columns. whereas whenever i connected to admin it should give the student table columns.

I created all the above objects.but materialized view is not giving the expected result as i want.

Answer 1

You can use a normal view where you can union these 2 tables and filter by the user logged in. Also check Oracle RLS (row level security), https://docs.oracle.com/cd/B28359_01/network.111/b28529/intro.htm

Irrespective of the application and user, you can control how the where clause should be. That way you have a much higher control over the records that is queries.

For example,

Admin user
select * from stu;

Normal user
select * from stu;

Would have completely different results, based on the predicate you set for RLS.