0 Replies Latest reply: Mar 1, 2013 8:25 AM by 588487 RSS

    Problem creating unit testing repository (and workaround)

    588487
      I tried to create the unit testing repository with a user that had all the required system privileges granted through a different role other than RESOURCE and CONNECT roles, and who wasn't explicitely granted CREATE VIEW, but inherited it through the custom role. However the check in SQL Developer is explicitely for the RESOURCE and CONNECT roles (regardless of what privileges they have granted to them in whichever database version past, present or future) and for CREATE VIEW privilege granted direct to the user:
      SELECT DECODE (roles.required_role_count + privs.required_priv_count,
                     3, 1,
                     0)
                AS basic_privs_granted
        FROM (SELECT COUNT (*) AS required_role_count
                FROM user_role_privs
               WHERE granted_role IN ('CONNECT', 'RESOURCE')) roles,
             (SELECT COUNT (*) AS required_priv_count
                FROM USER_SYS_PRIVS
               WHERE privilege IN ('CREATE VIEW')) privs
      I found this SQL contained within the sqldeveloper/extensions/oracle.sqldeveloper.unit_test.jar in an XML file: oracle/dbtools/unit_test/manage_user/UserSql.xml and replaced the SQL with one that actually checks for the specific system privileges:
      SELECT DECODE (required_sys_priv_count,
                     10, 1,
                     0)
                AS basic_privs_granted
        FROM 
             (SELECT COUNT(*) AS required_sys_priv_count FROM 
               (SELECT privilege
                  FROM role_sys_privs
                 WHERE privilege IN ('CREATE CLUSTER','CREATE INDEXTYPE','CREATE OPERATOR','CREATE PROCEDURE','CREATE SEQUENCE','CREATE SESSION','CREATE TABLE','CREATE TRIGGER','CREATE TYPE','CREATE VIEW')
                UNION
                SELECT privilege
                  FROM user_sys_privs
                 WHERE privilege IN ('CREATE CLUSTER','CREATE INDEXTYPE','CREATE OPERATOR','CREATE PROCEDURE','CREATE SEQUENCE','CREATE SESSION','CREATE TABLE','CREATE TRIGGER','CREATE TYPE','CREATE VIEW')))
      This enabled me to bypass that check when creating the respository. I don't know if all these privileges are actually required by the unit testing repository or not, so maybe the above list can be reduced...

      Edited by: RDB on Mar 1, 2013 2:24 PM