Home » SQL & PL/SQL » SQL & PL/SQL » Fine Grain Access control disabled but still prevents to create Materialized View (Oracle Database 19c Enterprise Edition Release 19.0.0.0.0)
Fine Grain Access control disabled but still prevents to create Materialized View [message #679947] Tue, 14 April 2020 10:13 Go to next message
wtolentino
Messages: 303
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 #679948 is a reply to message #679947] Tue, 14 April 2020 10:53 Go to previous messageGo to next message
John Watson
Messages: 8293
Registered: January 2010
Location: Global Village
Senior Member
Could it be simply that your user has not been granted CREATE ANY MATERIALIZED VIEW ? Problems with MVs and RLS usually show up as
ORA-30372: fine grain access policy conflicts with materialized view
Re: Fine Grain Access control disabled but still prevents to create Materialized View [message #679961 is a reply to message #679948] Tue, 14 April 2020 14:01 Go to previous messageGo to next message
wtolentino
Messages: 303
Registered: March 2005
Senior Member
right before disabling the RSL policy it is throwing the error ORA-30372. after the policy was disabled it is throwing the "ORA-01031: insufficient privileges".


i tried to grant CREATE ANY MATERIALIZED VIEW still does not work.


Re: Fine Grain Access control disabled but still prevents to create Materialized View [message #679966 is a reply to message #679961] Tue, 14 April 2020 14:57 Go to previous messageGo to next message
wtolentino
Messages: 303
Registered: March 2005
Senior Member
grant "CREATE TABLE" privilege solved the issue.
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 messageGo to next message
Solomon Yakobson
Messages: 2963
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.
Re: Fine Grain Access control disabled but still prevents to create Materialized View [message #679973 is a reply to message #679967] Wed, 15 April 2020 06:22 Go to previous messageGo to next message
wtolentino
Messages: 303
Registered: March 2005
Senior Member
thanks SY yes just realized that "CREATE TABLE" privileges is also needed.
Re: Fine Grain Access control disabled but still prevents to create Materialized View [message #679974 is a reply to message #679973] Wed, 15 April 2020 07:21 Go to previous message
Michel Cadot
Messages: 67227
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

CREATE MATERIALIZED VIEW


Prerequisites
The privileges required to create a materialized view should be granted directly rather than through a role.

To create a materialized view in your own schema:
  • You must have been granted the CREATE MATERIALIZED VIEW system privilege and either the CREATE TABLE or CREATE ANY TABLE system privilege.
...

[Updated on: Wed, 15 April 2020 07:26]

Report message to a moderator

Previous Topic: Calculating the Difference of Timestamp in Min, Sec
Next Topic: Create Materialized View throws "ORA-01031: insufficient privileges"
Goto Forum:
  


Current Time: Fri Jul 03 22:38:33 CDT 2020