OraFAQ Forum: Security » ORA-00942 Table or View does not exists

Home » RDBMS Server » Security » ORA-00942 Table or View does not exists (11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ORA-00942 Table or View does not exists [message #473758]

Tue, 31 August 2010 11:31

drduquette
Messages: 18
Registered: August 2010
Location: Florida

Junior Member

I am receiving an "ORA-00942 Table or View does not exists" error message when I try to run the following SQL statement:

SELECT id_status INTO :ls_id_status
FROM USER_ADMINISTRATION
WHERE RTRIM(user_id) = :as_userid;

where :as_userid is an argument passed to the SQL.

Here are some background facts in order to save time regarding actions already taken:
(1) The userid used to login successfully is the same one passed to the above SQL;
(2) I have confirmed that the table does exist - we are using it in an application;
(3) The case of the table name is upper case as seen above and is correct;
(4) The spelling of the table name is correct;
(5) The schema name was prefixed to the table and I still got the same error (not shown above);
(6) The userid that I used is assigned to a role and the role has SELECT permissions to the above table.

Does anyone have any other suggestions that may be contributing to the error?

Thanks in advance,
Don

Report message to a moderator

Re: ORA-00942 Table or View does not exists [message #473760 is a reply to message #473758]

Tue, 31 August 2010 11:34

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Does anyone have any other suggestions that may be contributing to the error?

Though you obscure what you really are doing, it appears posted SQL is from within PL/SQL procedure.

Privileges acquired via ROLE do NOT apply within named PL/SQL procedures.

GRANT SELECT on OWNER.OBJECT to <user>
must be explicitly done

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

Report message to a moderator

Re: ORA-00942 Table or View does not exists [message #473764 is a reply to message #473760]

Tue, 31 August 2010 11:44

drduquette
Messages: 18
Registered: August 2010
Location: Florida

Junior Member

The SQL mentioned above is from a PowerBuilder event. Obscurity is my intention because I am dealing with application security of sensitive data. I tried to give enough info without giving away too much!

Your point: "Privileges acquired via ROLE do NOT apply within named PL/SQL procedures" might be the problem if it applies to embedded SQL within a PowerBuilder application. Do you think this is the case?

Also, what part of posting guidelines am I not following?

Don

Report message to a moderator

Re: ORA-00942 Table or View does not exists [message #473766 is a reply to message #473764]

Tue, 31 August 2010 11:51

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

> Do you think this is the case?
SQL & PL/SQL does not know of care whether or not PowerBuilder exists of not.
My statement is true.
Error ORA-00942 can/does occur as direct result of stated behavior in previous post.
Owner can ALWAYS access its own objects.
Therefore I can safely conclude that USER issuing SELECT does not own table "USER_ADMINISTRATION"
I suspect a SYNONYM is involved, but not directly part of problem or solution.

Report message to a moderator

Re: ORA-00942 Table or View does not exists [message #473775 is a reply to message #473766]

Tue, 31 August 2010 12:05

drduquette
Messages: 18
Registered: August 2010
Location: Florida

Junior Member

You are correct that the user does not own the table in question. However, I did prefix the table name with the DBO name and I still got the same error. So if a user is assigned to a role and the role has permission to SELECT then I am missing why a userid that is logged in to the Oracle database in question cannot SELECT from a table regardless if there is a SYNONYM or not especially if I prefixed the table. I

Report message to a moderator

Re: ORA-00942 Table or View does not exists [message #473778 is a reply to message #473775]

Tue, 31 August 2010 12:07

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Privileges acquired via ROLE do NOT apply within named PL/SQL procedures.
Privileges acquired via ROLE do NOT apply within named PL/SQL procedures.
Privileges acquired via ROLE do NOT apply within named PL/SQL procedures.

GRANT SELECT on OWNER.USER_ADMINISTRATION to <user>
GRANT must be explicitly done; NOT via ROLE

Report message to a moderator

Re: ORA-00942 Table or View does not exists [message #473780 is a reply to message #473778]

Tue, 31 August 2010 12:22

drduquette
Messages: 18
Registered: August 2010
Location: Florida

Junior Member

It seems to defeat the purpose of ROLES. We have many stored procedures and functions that are being converted to Oracle. Also,there are many application users with their own database login id - it makes managing ids a big pain if we cannot use role!

Report message to a moderator

Re: ORA-00942 Table or View does not exists [message #473784 is a reply to message #473780]

Tue, 31 August 2010 12:27

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>It seems to defeat the purpose of ROLES.
If PL/SQL procedure were owned by same USER who owns USER_ADMINISTRATION table,
then do as follows:

GRANT EXECUTE ON PROCEDURE_1 TO <user>.

In a well designed application the total number of application schemas should be fewer than a handful.

Report message to a moderator

Re: ORA-00942 Table or View does not exists [message #473785 is a reply to message #473784]

Tue, 31 August 2010 12:30

drduquette
Messages: 18
Registered: August 2010
Location: Florida

Junior Member

Okay, Thanks

Report message to a moderator

Previous Topic:	Secure Password
Next Topic:	New procedures not working even after granting EXECUTE

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Aug 03 14:16:20 CDT 2025