Imposing column level security on a view for different user [duplicate]

Question

Possible Duplicate:
Column level security

I have a view which can be viewed by multiple users, and I would like to restrict some columns to specific users.

e.g I have a view with fields A, B... F, and I have users P, Q, R, S. Here's what I am trying to implement:

• when user P browses the view, he will see data in A, B, C and F, and columns D and E as NULL
• when user Q browses the view, he will see data in all fields
• when user R browses the view, he will see data in A, D, E and F, and columns B and C as NULL

Answer 1

There are several options:

• You can use synonyms (and as many views as there are different cases), as Phil showed in his answer.
• This is a feature of Virtual Private Database, included in the Enterprise Edition.
• You can do it by hand with a little code

Here's an example of how you could do it yourself:

CREATE TABLE base_table (a NUMBER, b NUMBER, c NUMBER, d NUMBER, e NUMBER, f NUMBER);

CREATE TABLE column_rights (username VARCHAR2(30) PRIMARY KEY,
                            a NUMBER, b NUMBER, c NUMBER, d NUMBER, e NUMBER, f NUMBER);

INSERT INTO column_rights VALUES ('P', 1, 1, 1, 1, NULL, NULL);
INSERT INTO column_rights VALUES ('Q', 1, 1, 1, 1, 1, 1);
INSERT INTO column_rights VALUES ('R', 1, NULL, NULL, 1, 1, 1);

CREATE OR REPLACE VIEW my_view AS
SELECT b.a * c.a a,
       b.b * c.b b,
       b.c * c.c c,
       b.d * c.d d,
       b.e * c.e e,
       b.f * c.f f
  FROM base_table b
  JOIN column_rights c ON c.username = USER;

 GRANT SELECT ON my_view TO P; -- Q, R

The above assumes that all fields are NUMBER, but this would work with anything if you use CASE. This also assumes that the users are defined at the database level, but you could also use a custom identification scheme (replace USER by your function -- V('USER') in APEX).

In a more general setting, you could use a COLUMN_RIGHTS table with TABLE_NAME and COLUMN_NAME columns (this would unfortunately require N lookups where N is the number of columns).

You could also use a single INTEGER field or RAW field for the concatenated bit representation of the column rights so that all the information would still be in a single row and this could be used for multiple views.