CODEWITHSUNDEEP
oracle-databaseplsql
geek
geek

Reputation: 616

Oracle - checking privileges during compile

When I compile a view using:

ALTER VIEW schema.vwName COMPILE;

I get 'Execute succeeded'. But when I try to select data from a view I get error: 'ORA-01031: insufficient privileges'. I understand error message and what causes that (schema doesn't have select permission to table in another schema). I wonder if there is a way to get information about this kind of error during view compilation or maybe there is some other command to check it without selecting data from a view?

I'm changing privileges in db and I want to make sure that all view and procedures are ok and user wouldn't get error ORA-01031 during select or execute.

Thanks

Upvotes: 1

Views: 927

Answers (1)

evilive
evilive

Reputation: 996

I think you may have created your view using force keyword and though aren`t getting compile errors caused by missing objects or privileges. Example:

# shema1
create table test_table ( column1 varchar2(10));

grant select on test_table to shema2;

#shema2
create or replace view test_view as select column1 from shema1.test_table; --works/no error

# shema1
revoke select on test_table from shema2;

# shema2
alter view test_view compile; --Gives ORA-00941: Table or view does not exist

create or replace force view test_view as select column1 from shema1.test_table; --gives no error, due to force keyword

alter view test_view compile; --gives no error, due to force keyword

Or saying it the other way round: If you want to check you're views via compilation you may not use force in the view-definition:

FORCE Specify FORCE if you want to create the view regardless of whether the base tables of the view or the referenced object types exist or the owner of the schema containing the view has privileges on them. These conditions must be true before any SELECT, INSERT, UPDATE, or DELETE statements can be issued against the view.

docs.oracle

Upvotes: 1

Related Questions