Sunday, January 25, 2015

ORA-01031 While Creating or Accessing a View

ORA-01031: insufficient privileges

If your CREATE VIEW, CREATE MATERIALIZED VIEW or SELECT statement on a view is failing with this error message, it means that you are using a table in your view which is in another schema and there are some privilege issues. Reason for this error could one of the following

Reason: You don’t have SELECT privilege on this table which you are using in your view (while creating or accessing the view).
Solution: Grant SELECT privileges on this table to your schema before you can create a view on this table, or you can access a view created on this table.


Reason: You have been granted SELECT privilege on this table form different schema, but this privilege was not granted directly, but through a role.
Solution: To use a table from a different schema; in your view, you need to have SELECT privilege granted directly to you, not through a role. Privilege should be granted as follows

GRANT SELECT ON <schema_name>.<table_name> TO <your_schema_name>; 


No comments: