CODEWITHSUNDEEP
sqloracle-databaseexceptionprivilegessql-grant
user3454315
user3454315

Reputation: 11

unable to grant privileges to scott

I'm trying to grant all to scott on emp from SYS, but oracle throwing ORA-00942: table or view does not exist error message. I'm not even getting emp table details in sys as dba.

How can I grant all permission to scott so that I can create views or user in scott. When I tried to create a view or user in scott, system is throwing insufficient privileges error message.

Thanks for your help.

Upvotes: 0

Views: 2918

Answers (2)

APC
APC

Reputation: 146179

The EMP table is a venerable object, used in many old tutorials and blog posts. However, it is not built by default any more.

If you want EMP in a schema (and traditionally SCOTT is its owner) you need to run the build script. Where you'll find it depends on the precise version of the database, but it might be under the sqlplus/demo sub-directory. On my Linux 11gR2 build it's at

%ORACLE_HOME/rdbms/admin/utlsampl.sql

OraFAQ has lots more about the SCOTT schema. Check it out.

On the other hand, if you want to use the modern demo schema (HR, SALES, etc) which are referenced in the current documentation, you will find all the scripts under:

%ORACLE_HOME/demo/schema

The documentation has installation instructions. Find out more.

Upvotes: 1

Frank Schmitt
Frank Schmitt

Reputation: 30765

You're having (at least) three different problems:

  • missing system privileges to create views etc.
  • (assuming you want to use the example tables provided by the database installation) the table is called EMPLOYEES, not EMP
  • the EMPLOYEES table is in the HR schema, not in the SYS schema
  • missing privileges on HR.EMPLOYEES

To fix these:

-- connect as SYS
GRANT CREATE VIEW TO scott;

-- connect as HR
CREATE SELECT ON employees TO scott;

-- connect as SCOTT
CREATE VIEW v_scott AS SELECT * FROM hr.employees;

UPDATE

If you're unsure about the correct table name, you can use this query to get a list of all tables in the database whose names start with EMP (to run this, you'll have to use a privileged user account, e.g. SYS):

select owner, table_name from dba_tables where table_name like 'EMP%'

Upvotes: 1

Related Questions