0 Replies Latest reply on Jul 25, 2020 11:40 PM by Mike Kutz

    Creating Unit Test Repo in Oracle Cloud (ATP Free Tier)

    Mike Kutz

      SQL*Developer 20.2

       

      Database is Oracle Cloud Autonomous Transaction Processor (ATP) Free Tier ( currently 19c EE with limits)

       

      Business Requirements

      I like to treat ADMIN (your cloud's DBA) like SYS.  --> don't install anything there.

      I also like to keep my Data Schema from my Parsing Schema.

       

      As such, what I am trying to do:

      • Install the repository under user UT_REPO
      • use the repository as user TESTER

       

      Problem 1

      I still get the "insufficient privileges" warning box

      However, I can click "CANCEL" when it asks for Username/Password and it installs.

       

      In other words, an error is thrown but "ignored" (to some degree)

       

      NOTE Except for this unnecessary pop-up I have no problems creating the repository as ADMIN.

      (I forgot if I need to create the ROLEs first)

       

      This problem might be known.

       

      Problem 2

      The roles aren't being created in the process.

      They MUST exist prior to creating the repository... even though the user has CREATE ROLE privilege. 

       

      After you create the role (as your repository owner), the repository installs.

      The repository is  also created if you create the roles as ADMIN and grant them to the repository owner WITH ADMIN OPTION.

       

      Problem 3

      When I try to connect to the repository as TESTER, the repository does not exists.

      The public synonyms aren't being created.

       

      current workaround:  connect to the repository as UT_REPO.  Run tests as TESTER.

       

      Code:

      create user ut_repo identified by S0methingC0mplicated
          default tablespace data
          temporary tablespace temp
          quota 1 G on data
          account unlock;
      
      -- modified from a prior post of mine
      grant create session  
          ,connect 
          ,resource
          ,SELECT ANY DICTIONARY
          ,select_catalog_role  
          ,create table  
          ,create sequence  
          ,create trigger 
          ,create procedure  
          ,create view  
          ,create materialized view  
          ,create role
          ,create synonym -- is this in RESOURCE?
          ,create public synonym
          ,unlimited tablespace
      to ut_repo;      
      
      -- stop here to simulate problem 2
      create role UT_REPO_USER;
      create role UT_REPO_ADMINISTRATOR;
      grant UT_REPO_USER, UT_REPO_ADMINISTRATOR to ut_repo with admin option;  
      

       

      After running that script as ADMIN, I can connect as UT_REPO and create the repository.

      I haven't created any test in there yet.

       

      If anyone has ideas I can/should test out, let me know.

       

      Thanks

       

      MK