Take the 2-minute tour ×
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. It's 100% free, no registration required.

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:

As SYS

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 stackoverflow.com Mar 16 '13 at 16:22

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

1 Answer 1

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:

SELECT * FROM sys.pay

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

CREATE SYNONYM account.pay FOR sys.pay;

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

share|improve this answer
1  
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

 
discard

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