Restrict access to tables

637711

Hello,
I would like to know, if i can restrict users to access or view certain tables in a schema. I want to give access to other users (read only access) of some tables and restrict viewing of other tables..Other users should not be able to view the data of tables...Is it possible to do so in Oracle..If it is possible then how can i do it..
Any help?

Satish Kandi

If I understand your question correctly, then read more about GRANT statement here.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_9013.htm#i2155015

529937

Once you create a user, he initially has no access to any objects. If you want to revoke existing permissions, then you would: revoke select on <table_name> to from <user_name>;
Now if you want to add priviliges, then you would: grant select on table <table_name> to <user_name>;

525507

Yes in oracle fine grained access control is possible. You can restrict to user to see data only. Even you can allow to see only limited column data only by creating the view on few of the table columns and granting the permission on that view to other.

You can give update permission to other. you can give table alter permission.

This can be done through grant commands in oracle.

eg. user A and user B

user A contains table emp

desc emp
emp_nm varchar2(10),
emp_id number(4),
dept_no number(2),
add varchar2(50)

If you don't give any grant on this table emp to B user he will not be able to do any access this table.

By command "Grant select on emp to B' the user b will be able to see the emp table data only.

You can give update,alter table permission to B user as given bellow

Grant update on emp to B
Grant alter on emp to B


Even you can ristict B to see only few columns of emp table creating view


create view test_view as select emp_nm,emp_id from emp;

Then grant select on test_view to B;

Mario Alcaide

You can also use Database Vault, if you want to create domains. That way, you make sure that not even the DBAs are allowed to access your data.

However, the easiest way is to create another user and grant select to that schema's objects.

Regards