2

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?

1
  • What if you grant the privileges directly to FAS, instead of through a role? – SQB Dec 4 '14 at 8:06
2

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,...

2
  • Thanks for the answer. After providing privilege directly rather than through role am able to create a view. But why it has to be direct and why not through a role? – Vishwanath gowda k Dec 5 '14 at 3:45
  • I don't know. I can only make a guess. At least the structure is more clear. If you tansfer of schemas to another (e.g. with export/import) it is easy to find the grant that you have to transfer,too. The relation of two schemas (schema = something containing objects like tables,views, procedures) is a rather unique thing. So it does not make sense to create a special role nor to use existing ones that (partly) fits by accident. The relation between users and schema objects is not unique in most cases. They have to do similar things on a database so they get the same roles (=groups of grants). – miracle173 Dec 5 '14 at 7:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.