Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have installed Oracle 11g on fedora 14. I connected with SQL*Plus as 'SYS' and created some tables, then created a user named "account" and a role, "payroll" with the following privileges. I assigned the role to the "account" user but I cannot access the created tables when I log-in as "account".

Here are my queries:


create type pay_t as object(
id int,
name varchar(10))

create table pay of pay_t; 
insert into pay values(pay_t(10,'p01'));

create user account identified by paccount;
grant create session to account;
grant connect to account;

create role pay_roll;
grant select on pay to pay_roll;
grant pay_roll to account;

As account:

select * from pay;

This gives me an error saying there is no such table or view.

share|improve this question

migrated from Mar 16 '13 at 16:22

This question came from our site for professional and enthusiast programmers.

The table you created as sys is created in the "sys" schema.

SELECT by default accesses the schema of the caller, so in your case, as SELECT is run as "account", it tries to access table "pay" in schema "account".

You'll need to either use a schema-prefixed query:


Or first create a synonym in "account" schema (as sys):


You could also use public synonyms, but I would rather not recommend them.

share|improve this answer
Also note that creating objects in the SYS schema isn't really a good practice either. – Mat Mar 16 '13 at 17:15

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.