ORA-00942: table or view does not exist while creating synonym

oraclepermissionssynonymsview

We are using oracle 11g, the db structure goes like this:

We have users CST and FAS.
CST contains a table ST_CAT2, a public synonym ST_CAT has been created for the table CST.ST_CAT2 as a SYS user.
ST_ROLE is a role which has given SELECT UPDATE DELETE ALTER privileges on ST_CAT2. ST_ROLE is granted to FAS.

Now, while creating a view as FAS,

CREATE OR REPLACE VIEW "FAS"."EXTERNAL_SR" ("PROD_ID", "PRODUCT") AS 
SELECT
prod_id,
    product
FROM
st_cat;

we are experiencing the error

ORA-00942: table or view does not exist

what could be the cause?

Best Answer

In the SQL Language Reference you will find under Prerequesites (emphasized by me):

The owner of the schema containing the view must have the privileges necessary to either select (READ or SELECT privilege), insert, update, or delete rows from all the tables or views on which the view is based. The owner must be granted these privileges directly, rather than through a role.

So you have to grant the privilges directly to the user that creates the view.

The same is true for PL/SQL procedures and is already discussed in this post.

There are two ways to grant a select privilege directly:

  1. grant it by an object privilege: grant select on A_TABLE to A_USER;
  2. grant it by a system privilege: grant select any table to A_USER;

The latter is not recommended, granted such an ANY system privileges to non DBA users is considered as a security risk.

Such ANY privileges exists for a lot of procedures and actions, e.g. INSERT ANY TABLE, EXECUTE ANY PROCEDURE,...

Related Question