This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 30th, when you will be able to use this site as normal.

    Forum Stats

  • 3,889,858 Users
  • 2,269,775 Discussions
  • 7,916,823 Comments

Discussions

Restrict access to tables

637711
637711 Member Posts: 73
edited Sep 5, 2008 8:01AM in General Database Discussions
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?

Answers

  • Satish Kandi
    Satish Kandi Member Posts: 9,627 Gold Trophy
    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
    529937 Member Posts: 958
    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
    525507 Member Posts: 341
    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
    Mario Alcaide Member Posts: 406 Bronze Badge
    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
This discussion has been closed.