Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
1  
    
I saw your answer which is more DBA dependent , my case is not like that . I want to implement the same by function or by something else in view source code itself. –  dibakar paul Nov 20 '12 at 23:50
    
@dibakarpaul you're asking how to do DBA level functionality, so why should it not be "dba dependent" as you word it? This is exactly what you need. This question is a duplicate of that, regardless the level of your ability. –  jcolebrand Nov 26 '12 at 17:37
add comment

marked as duplicate by Mark Storey-Smith, RolandoMySQLDBA, Paul White, Derek Downey, jcolebrand Nov 26 '12 at 17:37

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

1 Answer

There are several options:

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.

share|improve this answer
    
Hi Vincent , Thanks for your answer . I have around 50 tables and each have more than 20 columns , so i am thinking will it be a good approach or not. –  dibakar paul Nov 26 '12 at 19:14
add comment

Not the answer you're looking for? Browse other questions tagged or ask your own question.