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, 2014 at 8:06

1 Answer 1

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

3
  • 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? Dec 5, 2014 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, 2014 at 7:02
  • @Vishwanathgowdak Roles can be enabled and disabled by the user for the current session. this concept does not fit for the access privileges of a procedure. Should a procedure become invalid when the user disables a privilege used by the procedure but granted by a role?
    – miracle173
    Aug 24, 2021 at 18:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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