1

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.

1 Answer 1

2

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.

1
  • 1
    Also note that creating objects in the SYS schema isn't really a good practice either.
    – Mat
    Mar 16, 2013 at 17:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.