Layer7 API Management

 View Only
  • 1.  Gateway Database upgrader fails with "Not allowed to create a user with GRANT

    Posted Apr 28, 2022 11:43 AM

    Hello

    I am doing an upgrade test on a sandbox gateway from 10.0 CR04 to Gateway 10.1. I have sucessfully upgraded the SSG application but when I try to run the setup.sh script to perform 1) Upgrade the Layer7 API Gateway database it fails with MySQL error about not allowed to create user with GRANT

    I have searched the CA/Broadcom support for hints and have found none.

    On MySQL forums I see there were some changes in the way grants are setup. 

    Any idea how to get the DB updater to run?  



  • 2.  RE: Gateway Database upgrader fails with "Not allowed to create a user with GRANT

    Broadcom Employee
    Posted May 03, 2022 01:42 PM
    Edited by Barry Stern May 03, 2022 01:42 PM
    The root user you are using has to have the option to GRANT privileges to other users


    GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
    https://techdocs.broadcom.com/us/en/ca-enterprise-software/layer7-api-management/api-gateway/10-1/install-configure-upgrade/configure-a-gateway-cluster/configuring-the-first-processing-node.html




  • 3.  RE: Gateway Database upgrader fails with "Not allowed to create a user with GRANT

    Posted May 06, 2022 06:03 PM

    Thanks however I knew that and tried that first thing prior to posting.

    Apologies, I should have mentioned in original post that giving GRANT ALL to root didn't help. 

    The root user does have that privilege and can add user with grant option w/o problem. 

    This following does work for root however the setup.sh script still has issues with creating user with GRANT. (as seen in original post screen shot) 

    CREATE USER 'foo'@'%' IDENTIFIED WITH caching_sha2_password BY 'bar'  REQUIRE NONE;
    GRANT ALL PRIVILEGES ON *.* TO 'foo'@'%' WITH GRANT OPTION;

    flush privileges;

    I am pretty sure this is related to changes in MySQL 8.0.11  and greater (we are on MySQL 8.0.25)

    as root mysql user can create another user WITH GRANT OPTIONS successfully. 

    I have created a few test users and even recreated a 'root'@'%' user with grant options as well. All work fine on MySQL CLI but setup.sh fails. 

     

    I read from MySQL and other sources that this is a new behavior in MySQL 8.0.11 and >  

    ERROR 1410 (42000): You are not allowed to create a user with GRANT

    If you are trying to run a GRANT on a user that doesn't exist! (in MySQL 8.0.11 >)

    If the setup.sh script is creating a user and setting WITH GRANT OPTION at the same time it will fail. 

    In the newer versions of MySQL 8 we have to do WITH GRANT OPTION in steps not on one line. (like shown above in code clip.)

    Thoughts appreciated? 




  • 4.  RE: Gateway Database upgrader fails with "Not allowed to create a user with GRANT

    Posted May 11, 2022 12:08 PM
    Are you running the GW platform in multi cluster? If yes then I have also experienced it during our upgrade.

    The root cause was that we tried upgrading secondary node DB. The DB upgrade step really need to be executed on Primary node, that's all.

    Check the ssg_0_0.log and observe any exceptions.


  • 5.  RE: Gateway Database upgrader fails with "Not allowed to create a user with GRANT

    Broadcom Employee
    Posted May 12, 2022 01:45 PM
    Have you confirmed that the host part of the grant is resolving properly? MySQL grants are set to 'user'@'host'. On the appliance gateways that is always root@localhost, so it "just works". When setting up a grant for a remote host, the host part must resolve to pass the grant. You can also use 'user'@'ip' in the grant.

    ------------------------------
    Jay MacDonald - Adoption Architect - Broadcom API Management (Layer 7)
    ------------------------------



  • 6.  RE: Gateway Database upgrader fails with "Not allowed to create a user with GRANT

    Posted 9 days ago

    Thanks for your feedback.

    When connecting to External Database , you need to make sure the dmzuser@'localhost' user is created with full permission and you might need this during upgrade process only.

    This will help to solve the subjected MySQL issue.