sqloracleoracle11gr2

Administration of oracle database


I create tables with user system than I create another user user1 , I give to him all privileges but when I connect with user1 I can't see or select the tables created by system


Solution

  • First of all, you should never ever create any objects in SYS or SYSTEM schema. Leave them alone, they own the database and - if you do something wrong, you might destroy the database.

    As of your question: "all privileges" is quite broad term. What exactly did you grant to user1? You should've - as system

    grant select on table_created_by_system;
    

    and then, connected as user1,

    select * from system.table_created_by_system;
    

    In other words, you have to precede table name with its owner's name (system in this case). Other option is to create a synonym (public, perhaps?) and then you don't have to use owner's name.


    I suggest you abandon it altogether. Drop tables from system schema and start over in user1's schema.