Allowing a users to select from a table

Question

The emp table does exist for cis605 and I want to assign permissions to user. Any ideas to what I am doing wrong?

SQL> grant select on emp to user;

     Grant succeeded.

     SQL> connect user
     Enter password:
     Connected.
     SQL> select * from emp;
     select * from emp
                   *
     ERROR at line 1:
     ORA-00942: table or view does not exist

I have also tried doing it differently

     SQL> connect cis605
     Enter password:
     Connected.
     SQL> grant select on system.emp to chap7;
     grant select on system.emp to chap7
                             *
     ERROR at line 1:
     ORA-00942: table or view does not exist

Heres the statement I should have been using

SQL> SELECT * from cis605.emp;

Answer 1

In the first case it doesn't work because you need to either:

1. Reference the table name including the schema it's in. i.e.

   SELECT * FROM schema.EMP;

OR
2. Create a [public] synonym in order to be able to "see" the table without including the schema in every SQL statement.

---

In the second case you're trying to reference the schema, but getting the wrong one. The EMP table is typically found in the SCOTT schema, not SYSTEM. Though in your case maybe you need to do:

grant select on cis605.emp to chap7;

---

Also, having a user called "USER" is a bad idea - it's an Oracle keyword. (Though I guess this may just be for the purposes of example)