Home » RDBMS Server » Security » ORA-00942 Table or View does not exists (11g)
ORA-00942 Table or View does not exists [message #473758] Tue, 31 August 2010 11:31 Go to next message
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



Re: ORA-00942 Table or View does not exists [message #473760 is a reply to message #473758] Tue, 31 August 2010 11:34 Go to previous messageGo to next message
BlackSwan
Messages: 26604
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/
Re: ORA-00942 Table or View does not exists [message #473764 is a reply to message #473760] Tue, 31 August 2010 11:44 Go to previous messageGo to next message
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

Re: ORA-00942 Table or View does not exists [message #473766 is a reply to message #473764] Tue, 31 August 2010 11:51 Go to previous messageGo to next message
BlackSwan
Messages: 26604
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.
Re: ORA-00942 Table or View does not exists [message #473775 is a reply to message #473766] Tue, 31 August 2010 12:05 Go to previous messageGo to next message
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
Re: ORA-00942 Table or View does not exists [message #473778 is a reply to message #473775] Tue, 31 August 2010 12:07 Go to previous messageGo to next message
BlackSwan
Messages: 26604
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
Re: ORA-00942 Table or View does not exists [message #473780 is a reply to message #473778] Tue, 31 August 2010 12:22 Go to previous messageGo to next message
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!
Re: ORA-00942 Table or View does not exists [message #473784 is a reply to message #473780] Tue, 31 August 2010 12:27 Go to previous messageGo to next message
BlackSwan
Messages: 26604
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.
Re: ORA-00942 Table or View does not exists [message #473785 is a reply to message #473784] Tue, 31 August 2010 12:30 Go to previous message
drduquette
Messages: 18
Registered: August 2010
Location: Florida
Junior Member
Okay, Thanks
Previous Topic: Secure Password
Next Topic: New procedures not working even after granting EXECUTE
Goto Forum:
  


Current Time: Mon Oct 14 03:29:57 CDT 2019