Is it possible in Oracle to create a table (or a view) where two different users access the same table (or the same view) and they see different sets of columns?

For instance, two users execute the same query against a table/view named employees:

select * from employees;

One user sees data for columns name and salary (total columns = 2).

The other user sees data only for the name column (total columns = 1).

I know that a common solution is to create two views against a base table, and assign different permissions to each view. However, that doesn't work for us, as our application needs to use the same select (the same FROM) for both users. In other words, we need to treat the table/view as the same object, with the same db.schema.name identifier for all users. Creating two views would create two objects with different names.

Some databases allow granting column permissions to a table (or view's) columns, but Oracle apparently doesn't allow me to GRANT SELECT privs on individual columns.

The other option is VPD. However, the examples of VPDs I've seen don't show how to hide an entire column. They show how to hide rows based on column values (e.g. https://docs.oracle.com/cd/B28359_01/network.111/b28531/vpd.htm#DBSEG98231) or they show how to mask some or all of the values in a column. I haven't seen a VPD example that completely hides a column based on user permissions.

Are there any other options in Oracle to hide a column based on user permissions?

Thanks in advance

CodePudding user response：

You go into right direction. Create specific view for each user

create view employees_a as select name, salary from employees;
create view employees_b as select name from employees;

Then in order to use always the same name, create user specific SYNONYMS:

create synonym user_a.employees for main_schema.employees_a;
create synonym user_b.employees for main_schema.employees_b;