I'm fairly new to Oracle database. I have installed
Oracle Database 11g R2 on
Oracle Linux 6. I have successfully created a new database with
dbca and connected to the database using:
$ sqlplus "/ as sysdba"
I successfully created a table and inserted some data and performed some selects:
SQL> CREATE TABLE Instructors ( tid NUMBER(7) PRIMARY KEY, fname VARCHAR2(32), lname VARCHAR2(32), tel NUMBER(16), adrs VARCHAR2(128) ); Table created. SQL> INSERT INTO Instructors (tid, fname, lname, tel, adrs) VALUES (8431001, 'John', 'Smith', 654321, 'London'); 1 row created. SQL> SELECT count(*) FROM Instructors; COUNT(*) ---------- 1
Then I created a new user with the CONNECT privilege:
SQL> CREATE USER teacher1 IDENTIFIED BY pass1; User created. SQL> GRANT CONNECT TO teacher1; Grant succeeded.
Then I created a new role with appropriate object privileges:
SQL> CREATE ROLE instructor; Role created. SQL> GRANT SELECT, UPDATE ON Instructors TO instructor; Grant succeeded.
And granted the role to the user:
SQL> GRANT instructor TO teacher1; Grant succeeded.
Next I exited sqlplus with
exit; and connected as the new user to test it. I logged in successfully to the database with:
$ sqlplus teacher1 SQL*Plus: Release 220.127.116.11.0 Production on Thu Jul 25 03:20:50 2013 Copyright (c) 1982, 2009, Oracle. All rights reserved. Enter password: ***** Connected to: Oracle Database 11g Enterprise Edition Release 18.104.22.168.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL>
But when I try to select from the table it says:
SQL> SELECT * FROM Instructors; SELECT * FROM Instructors * ERROR at line 1: ORA-00942: table or view does not exist
What I'm missing here ?!