CODEWITHSUNDEEP
oracle-databaseviewpermissions
sbrbot
sbrbot

Reputation: 6469

Permission to create VIEW for table with SELECT right

I'm granted SELECT on table NOTMYSCHEMA.XYZ but Oracle does not allow me to create VIEW that includes this table! What's the purpose of this ban? I assume if I can see the table content then I should be allowed to make a view showing this table content by default.

Upvotes: 0

Views: 3737

Answers (2)

R. Du
R. Du

Reputation: 633

Note that it's now possible with Oracle 23c, using what's called Schema-level Privileges and the "GRANT SELECT ANY TABLE on SCHEMA" instruction.

See Oracle blog entry "Schema-level privilege grants with Database 23ai"

Example of this instruction in Oracle 23c (or later) could be :

GRANT SELECT ANY TABLE ON SCHEMA HR TO SCOTT;

Upvotes: 0

Elkhan Ibrahimov
Elkhan Ibrahimov

Reputation: 244

Privileges Required to Create Views

To create a view, you must meet the following requirements:

You must have been granted the CREATE VIEW (to create a view in your schema) or CREATE ANY VIEW (to create a view in another user's schema) system privilege, either explicitly or through a role.

You must have been explicitly granted the SELECT, INSERT, UPDATE, or DELETE object privileges on all base objects underlying the view or the SELECT ANY TABLE, INSERT ANY TABLE, UPDATE ANY TABLE, or DELETE ANY TABLE system privileges. You may not have obtained these privileges through roles.

Additionally, in order to grant other users access to your view, you must have received object privilege(s) to the base objects with the GRANT OPTION option or appropriate system privileges with the ADMIN OPTION option. If you have not, grantees cannot access your view."

Upvotes: 1

Related Questions