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.

I am in need of a solution to hide specific columns in a table. We have people who need to build reports against this database and specifically some of these tables with confidential information but aren't allowed to see items like salary or ssn. Is it possible to filter specific columns for users?

share|improve this question
1  
What version number of oracle (10.1/10.2/11.1/11.2) and are you using Enterprise Edition/Standard Edition, plus do you have any extra licensed options (Oracle Advanced Security, for example)? –  Phil Sep 13 '12 at 19:03
    
10g R2 Enterprise Edition. No additional licensed features that I'm aware of. –  Robert Sep 13 '12 at 19:05

1 Answer 1

You can do this with a normal view, as long as the users involved haven't already got access to the base table.

EG:

SQL> create user reportuser identified by reportuser;

User created.

SQL> grant create session to reportuser;

Grant succeeded.

SQL> grant create synonym to reportuser;

Grant succeeded.

SQL> select user from dual;

USER
------------------------------
PHIL

SQL> create table basetable
(
  id number primary key,
  viewable varchar2(30),
  secret varchar2(30)
);

Table created.

SQL> insert into basetable values ( 1, 'hello world','this is secret' );

1 row created.

SQL> commit;

Commit complete.

SQL> create view reportview
as
select id, viewable
from basetable;

View created.

SQL> grant select on reportview to reportuser;

Grant succeeded.

SQL> conn reportuser/reportuser
Connected.
SQL> select * from phil.basetable;
select * from phil.basetable
                   *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> select secret from phil.basetable;
select secret from phil.basetable
                        *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> select * from phil.reportview;

        ID VIEWABLE
---------- ------------------------------
         1 hello world

SQL>

If you revoke permissions on the tables in question & create the views, along with a synonym for each users view that has the same name as the original table, it should be transparent.

EG:

SQL> select user from dual;

USER
------------------------------
REPORTUSER

SQL> create synonym basetable for phil.reportview;

Synonym created.

SQL> select * from basetable;

        ID VIEWABLE
---------- ------------------------------
         1 hello world

SQL>

You can also do this with Virtual Private Database, but I think it's an expensive extra licensed option. You use DBMS_RLS to configure the relevant security policies that you require.

share|improve this answer
    
Thanks for the detailed answer. No way you're aware of to filter on the actual table itself. This would be a much cleaner solution for us as we're going to have a number of tables to do this for. Your solution could work though. –  Robert Sep 13 '12 at 19:15
2  
If you revoke permissions on the tables in question & create the views, along with a synonym for each user that has the same name as the original table, it should be transparent. –  Phil Sep 13 '12 at 19:20
    
Ok...that makes sense. I wasn't thinking about creating synonyms for the user. So I need to create a reporting user, grant them access to all tables, then remove the specific tables i'm worried about. After that start creating the views to hide the columns. –  Robert Sep 13 '12 at 19:26
    
Yup! Any more questions, fell free to pop back and ask. Don't forget to test :) –  Phil Sep 13 '12 at 19:27
    
Will do...thanks again. –  Robert Sep 13 '12 at 19:29

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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