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...
... 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:
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;
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; |