9 Replies Latest reply on Mar 15, 2019 7:10 PM by Nick Fury

    Unable to CREATE VIEW when the privilege is granted through a role.

    Nick Fury

      We have a schema where the privileges are granted to a <schema_owner> role and not to the schema itself.

       

      Create table works, but create view fails in sqldeveloper, 18.4 but it works from sqlplus 12.2 and sqlcl 18.4...

       

      The error raised is:

      ORA-01031: insufficient privileges

      01031. 00000 -  "insufficient privileges"

       

      I can immediately switch to a sqplus window and the command executes successfully.

        • 1. Re: Unable to CREATE VIEW when the privilege is granted through a role.
          thatJeffSmith-Oracle

          how are you creating the view, using the wizard or writing the sql in a worksheet

           

          and we need to see how your user is defined privilege-wise

          • 2. Re: Unable to CREATE VIEW when the privilege is granted through a role.
            Glen Conway

            If the role with the CREATE VIEW privilege is a default role for the schema in question, then it seems to me CREATE VIEW should work.  If not, then you probably have a login.sql available when running sqlplus and sqlcl.  If you want the same in SQL Developer then you must set this preference:

            Capture.JPG

            Edit:  Of course, the startup script specified there would include a SET ROLE statement to activate the role.

            • 3. Re: Unable to CREATE VIEW when the privilege is granted through a role.
              Nick Fury

              Actually I tried both the wizard and from a worksheet and they both failed.

              • 4. Re: Unable to CREATE VIEW when the privilege is granted through a role.
                Nick Fury

                I checked and the role is defaulted.

                • 5. Re: Unable to CREATE VIEW when the privilege is granted through a role.
                  Nick Fury

                  SQL> select * from dba_sys_privs where grantee='KBDW';

                   

                   

                  no rows selected

                   

                   

                  SQL> select * from dba_role_privs where grantee='KBDW';

                   

                   

                  GRANTEE                        GRANTED_ROLE                   ADM DEF

                  ------------------------------ ------------------------------ --- ---

                  KBDW                           CONNECT                        NO  YES

                  KBDW                           APEX_SCHEMA_ROLE               NO  YES

                   

                   

                  SQL> select * from dba_sys_privs where grantee = 'APEX_SCHEMA_ROLE';

                   

                   

                  GRANTEE                        PRIVILEGE                                ADM

                  ------------------------------ ---------------------------------------- ---

                  APEX_SCHEMA_ROLE               CHANGE NOTIFICATION                      NO

                  APEX_SCHEMA_ROLE               CREATE SEQUENCE                          NO

                  APEX_SCHEMA_ROLE               SELECT ANY DICTIONARY                    NO

                  APEX_SCHEMA_ROLE               CREATE INDEXTYPE                         NO

                  APEX_SCHEMA_ROLE               CREATE MATERIALIZED VIEW                 NO

                  APEX_SCHEMA_ROLE               CREATE PROCEDURE                         NO

                  APEX_SCHEMA_ROLE               CREATE SYNONYM                           NO

                  APEX_SCHEMA_ROLE               ALTER SESSION                            NO

                  APEX_SCHEMA_ROLE               CREATE SESSION                           NO

                  APEX_SCHEMA_ROLE               CREATE TABLE                             NO

                  APEX_SCHEMA_ROLE               CREATE TYPE                              NO

                  APEX_SCHEMA_ROLE               CREATE DIMENSION                         NO

                  APEX_SCHEMA_ROLE               CREATE OPERATOR                          NO

                  APEX_SCHEMA_ROLE               CREATE LIBRARY                           NO

                  APEX_SCHEMA_ROLE               CREATE VIEW                              NO

                  APEX_SCHEMA_ROLE               CREATE CLUSTER                           NO

                  APEX_SCHEMA_ROLE               SELECT ANY TABLE                         NO

                  APEX_SCHEMA_ROLE               CREATE TRIGGER                           NO

                  APEX_SCHEMA_ROLE               CREATE JOB                               NO

                  APEX_SCHEMA_ROLE               CREATE DATABASE LINK                     NO

                  • 6. Re: Unable to CREATE VIEW when the privilege is granted through a role.
                    thatJeffSmith-Oracle

                    the error doesn't come from us, it comes from the database

                     

                    are you connected to the same database? using the exact same credentials?

                    • 7. Re: Unable to CREATE VIEW when the privilege is granted through a role.
                      Glen Conway

                      And you could always check "select * from session_privs" for the SQL Developer connection.

                      • 8. Re: Unable to CREATE VIEW when the privilege is granted through a role.
                        Nick Fury

                        Oh, I wonder if it's a stale connection...  Privileges are checked when you log in (I think)...

                        • 9. Re: Unable to CREATE VIEW when the privilege is granted through a role.
                          Nick Fury

                          Yes, logging out and logging back in resolved the issue.

                           

                          Stupid me!

                           

                          Thanks!