CODEWITHSUNDEEP
oracle11g
Anuj
Anuj

Reputation: 197

Not able to SELECT a DB view

In Oracle, i have below scenario but facing authorization issue.

Schema A: Has 3 tables: TA1, TA2, TA3

Schema B: Has DB View 'VB' on joining tables TA1, TA2 and TA3

User C: Has SELECT grant on VB

Problem: SELECT grant is provided to C on view 'VB' but it is not accessible from 'C'. I have also tried granting SELECT access on TA1, TA2 and TA3 to C but it did not work.

Please advice.

Upvotes: 1

Views: 30

Answers (1)

user672739
user672739

Reputation:

You need to grant using the WITH GRANT option

As schema_a, grant access to underlying tables with the option to give that grant to another schema

GRANT select ON ta1 TO schema_b WITH GRANT OPTION;
GRANT select ON ta2 TO schema_b WITH GRANT OPTION;
GRANT select ON ta3 TO schema_b WITH GRANT OPTION;

As schema_b, you can now grant select on the view using those tables to schema_c

GRANT select ON vb TO schema_c;

As schema_c you can select from the vb but not the underlying tables directly.

SELECT * from schema_b.vb;

No need to grant select on the tables directly to schema_c

Upvotes: 1

Related Questions