specifying owner of table in table reference of procedure

[Guinea]

Hi Everyone,

I need to create a stored procedure that summarizes info in a table. I want to specify the schema that the table resides in. I tried this code

create or replace procedure analyze_dept
AS
v_dept_record dept%ROWTYPE;
BEGIN
SELECT *
INTO v_dept_record
from Scott.dept;
END;
/

and received the error:
PLS-00201: identifier 'SCOTT.DEPT' must be declared

Where do I specify which schema I want the dept table to come from?

Thanks for your help!!
Marc Guinea pigs make the world a nicer place!

Piggydad30@hotmail.com

 

[carp]

You've done it correctly. However, if you don't have SELECT privileges on the scott.dept table (or the table doesn't exist), you will get this error.

 

[Guinea]

Hi CARP,

Thanks for your response. I am still a little confused why I am getting this error. The schema scott is the default demo scott/tiger and I am loged on as system/manager. I can run the query in an SQL statement but not as a stored procedure? I should have select priveleges if I can query the table straight from sql*plus right?

Thanks again,

Marc Guinea pigs make the world a nicer place!

Piggydad30@hotmail.com

 

[MFGlobalDBA]

The owner of the stored procedure (in this case, the owner is SYSTEM) must have whatever access that is being used (in this case it is SELECT) against the oject within the stored procedure (in this case, it is the SCOTT.DEPT table) via direct access and not through a ROLE.

SYSTEM has by default DBA and SELECT ANY TABLE. That is why it can select against the SCOTT.DEPT table in SQL and SQL*Plus. However, in order for this user to access this table within a stored procedure that it owns, then this user must be granted EXPLICITLY select on that table.

This is a feature in Oracle. It is not a bug. The reason it is a feature is that it restricts privileges on objects that privilege maintenance does not become a nightmare like it does in other RDBMS.

Example. User-A grants delete on User-A.Table to User_B. User-B then creates a stored procedure which deletes rows from this table. User-B then grants execute on this procedure to User-C. Now, User-C has delete on User-A.Table. Is that what User-A intended? Well, if he grants the access to User-A through a role, this can't happen. If he grants it explicitly, then it can.

 

[carp]

Exactly so. Log in as Scott, explicitly grant privileges on the dept table to System, and then try recompiling your procedure. It should work now that System has rights granted explicitly to his/her account rather than inheriting them via a role.