3 Replies Latest reply on May 6, 2020 6:03 PM by Richard_Allen

    PL/SQL Unit Testing with SQLDev and Autonomous

    Richard_Allen

      I’m looking to do some unit testing of pl/sql for the first time!  I'm trying to use the UT functionality in SQL Developer, which I’m running on a Mac connected to my ATP database running on OCI.

       

      I’m following the instructions to set up the UNIT_TEST_REPOS user, which uses the Create a Repository command on SQLDeveloper.  That requires you to log into the database as SYS to grant privileges to UNIT_TEST_REPOS.  Unless I’m missing something, you can’t connect to sys from a client to ATP because of the cloud security rules, and I don’t think I know or am allowed to change the SYS password.  Connecting as ADMIN doesn't allow you to run the scripts. 

       

      This feels like a catch 22, or am I missing something?  Do I need to run utPLsql instead?

        • 1. Re: PL/SQL Unit Testing with SQLDev and Autonomous
          Mike Kutz

          I haven't tried on ATP yet.

           

          You can install the repo as a non-SYS user.  But, that user needs a series of GRANTS first.

           

          I would create a user specifically for UT and install the UT Repo as that user.

          Your ADMIN account has (essentially) the DBA Role enabled.  Use this account to create the dedicated user and grant the necessary roles.

          See this thread for which grants are needed: Unit Test Repository Creation issue

           

          If that doesn't work, try installing the UT Repo as your ADMIN user.

           

          If that doesn't work, politely ask the Product Manager of SQL Developer to (have his team) redo the "check privilege/quota" logic so that it will work.  (via Enhancement Request/Ticket with Oracle Support)

           

          Please post up what work/what doesn't work so that future readers know what to try/not try.

           

          Thanks,

           

          MK

          1 person found this helpful
          • 2. Re: PL/SQL Unit Testing with SQLDev and Autonomous
            thatJeffSmith-Oracle

            That's a bug. I filed it for you. Thanks for the report!

            1 person found this helpful
            • 3. Re: PL/SQL Unit Testing with SQLDev and Autonomous
              Richard_Allen

              Thanks Mike - the info in your earlier post (referenced above) did the trick.

               

              For benefit of those coming behind having the same problem,  connect as the ADMIN user on your ATP database, and make the following grants:  (I've used the USER_TEST_REPOS username for my repository files, as per the tutorial in the SQLDev User Guide section on Unit Testing.

               

              alter session set "_ORACLE_SCRIPT"=true; 

              create user USER_TEST_REPOS identified by **********;

              grant create session to USER_TEST_REPOS;

               

              grant connect, resource, create view, unlimited tablespace to "USER_TEST_REPOS";

              grant select on dba_roles to "USER_TEST_REPOS";

              grant select on dba_role_privs to "USER_TEST_REPOS";

               

              create role UT_REPO_ADMINISTRATOR;

              create role UT_REPO_USER;

              grant create public synonym,drop public synonym to UT_REPO_ADMINISTRATOR;

              grant select on dba_role_privs to UT_REPO_USER;

              grant select on dba_role_privs to UT_REPO_ADMINISTRATOR;

              grant select on dba_roles to UT_REPO_ADMINISTRATOR;

              grant select on dba_roles to UT_REPO_USER;

              grant select on dba_tab_privs to UT_REPO_ADMINISTRATOR;

              grant select on dba_tab_privs to UT_REPO_USER;

              grant execute on dbms_lock to UT_REPO_ADMINISTRATOR;

              grant execute on dbms_lock to UT_REPO_USER;

              grant UT_REPO_USER to UT_REPO_ADMINISTRATOR with admin option;

              grant UT_REPO_ADMINISTRATOR to "USER_TEST_REPOS" with admin option;

               

              And you are very right about it being a problem that SQLDev looks for the CONNECT and RESOURCE roles, not the underlying privileges that it actually needs.  When we added roles and granular privileges in Oracle7 in 1992, we created CONNECT, RESOURCE, and DBA roles to be upwards-compatible with Oracle 6 and below -- never intending that people would be forced still have them as roles in 2020!  (I was the Product Manager responsible back then!)