ORA-00942: table or view does not exist for a procedure

Copied from: http://www.adp-gmbh.ch/ora/err/ora_00942.html

ORA-00942

Sometimes, when a view or a procedure is created that accesses a table, Oracle issues the error ORA-00942: table or view does not exist, although this table (or view) definitely exists. The reminder here tries to explain as to why this is.

The setup

First, two users are created: table_owner and table_user. As their names indicate, the first owns a table on which the second will then try to perform a select statement.

As both users need to be able to connect to the database, create session is granted to them. Also, the table owner obviously needs the right to create tables, so he gets the create table right as well.

create user table_owner
 identified by table_owner
 default tablespace  ts_data
 temporary tablespace ts_temp
 quota unlimited on ts_data
 quota unlimited on ts_temp;

create user table_user
  identified by table_user
  default tablespace ts_data
  temporary tablespace ts_temp;grant create session to table_owner;
grant create table to table_owner;
grant create role to table_owner;

grant create session to table_user;
grant create procedure to table_user;

Now, we log on as the (future) table owner...

connect table_owner/table_owner;

... and create a table:

create table just_a_table (
  field_1  number,
  field_2  varchar2(20)
);

insert into just_a_table values (4,'four');
insert into just_a_table values (6,'six');
insert into just_a_table values (8,'eight');commit;

Selecting as table_user

Now, we log on as table_user and try to do a select

connect table_user/table_user;

select * from table_owner.just_a_table;

We receive a ORA-00942: table or view does not exist, but that was to be expected, as the owner didn't grant the user with the right to select on the table.

The owner will now do that. As the owner of the table expects to have many users that will want to access his table, and he also expects to give them more than just this select right, he creates a role and grants the role to the user.

First the role:

create role table_accessers;

Then granting the select right to that role

grant select on just_a_table to table_accessers;

Finally, granting the role to table_user

grant table_accessers to table_user;

Selecting again

connect table_user/table_user;

select * from table_owner.just_a_table;

This times, it works perfectly, as the user has the right to access the table (through the role)

Creating a procedure

For most people, it comes as a surprise that the user cannot select the table from within a procedure if he has not been granted the select right directly (as opposed to through the role)

create or replace procedure get_count as
  v_cnt number;
begin
  select count(*) into v_cnt from table_owner.just_a_table;
  dbms_output.put_line('The count is: ' || v_cnt);
end;
/

If table_user tries to compile this procedure, he gets a ORA-00942 although this table certainly exists and he was granted the right to select this table. The problem is that procedures don't respect roles; only directly granted rights are respected. So, that means that table_owner has to regrant the right to select:

connect table_owner/table_owner

grant select on just_a_table to table_user;

connect table_user/table_user;

Now, it is also possible to access the table within a procedure.

create or replace procedure get_count as
  v_cnt number;
begin
  select count(*) into v_cnt from table_owner.just_a_table;
  dbms_output.put_line('The count is: ' || v_cnt);
end;
/

However, if the procedure is created with authid current_user, the granted roles will be enabled when the procedure is called.

Cleaning up

drop user table_owner;
drop user table_user;