Fine Grain Access control disabled but still prevents to create Materialized View [message #679947] |
Tue, 14 April 2020 10:13 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
wtolentino
Messages: 404 Registered: March 2005
|
Senior Member |
|
|
FGA was disabled but still could not create a materialized view in the same schema.
SQL> BEGIN
2 DBMS_RLS.ENABLE_POLICY (object_schema=> 'SSPOWNER',
3 object_name => 'SSP_DTL',
4 policy_name => 'CODE_SSP_INCOME_POL_00',
5 enable => TRUE);
6 END;
7 /
PL/SQL procedure successfully completed.
when attempting to create the materialized view it is throwing and error "ORA-01031: insufficient privileges".
SQL> CREATE MATERIALIZED VIEW sspowner.SSP_DTL_MV
2 NOLOGGING
3 CACHE
4 BUILD IMMEDIATE
5 REFRESH FAST ON COMMIT AS
6 SELECT sd.*
7 FROM sspowner.SSP_DTL SD
8 WHERE Nvl(sd.Modify_Ts, sd.Create_Ts) > '01-JAN-19';
WHERE Nvl(sd.Modify_Ts, sd.Create_Ts) > '01-JAN-19'
*
ERROR at line 8:
ORA-01031: insufficient privileges
Or maybe there is something that I am missing.
please advise. thank you.
|
|
|
|
|
|
Re: Fine Grain Access control disabled but still prevents to create Materialized View [message #679967 is a reply to message #679947] |
Tue, 14 April 2020 18:14 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Solomon Yakobson
Messages: 3273 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Even though user who is creating MV in other schema has CREATE ANY MATERIALIZED VIEW privilege, that's not enough. Other schema must have CREATE TABLE privilege:
SQL> create materialized view u2.mv1 as select * from dual;
create materialized view u2.mv1 as select * from dual
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL> grant create table to u2;
Grant succeeded.
SQL> create materialized view u2.mv1 as select * from dual;
Materialized view created.
SQL>
SY.
|
|
|
|
|