The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Cannot create new mysql user and grant access using shell access

Discussion in 'Database Discussions' started by itconstruct, Sep 12, 2014.

  1. itconstruct

    itconstruct Registered

    Joined:
    Sep 12, 2014
    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Reseller Owner
    I have a cPanel reseller account and I do alot of web development using mysql database and my host gives me shell access to mysql so that I can sql queries however I have come across a particular issue of late that I haven't been able to get past and would like some assistance on.

    When I create new databases I will create a new mysql user and grant them access to this new database for security reasons rather than using the main user account for my reseller account which is a major security risk as this password is stored in plain text for some systems to connect to the databases.

    I have been able to create the new databases ok using sql queries however when I then try and create the user and grant privileges I am unable to do so. (I have omitted the code for creating a new database as it works).

    Code:
    mysql> CREATE USER 'username_dbname'@'localhost' IDENTIFIED BY '******'; 
    GRANT ALL PRIVILEGES ON username_user TO 'username_dbname'@'localhost' WITH GRANT OPTION;
    However I get an error that I don't have permission to create the user:

    Code:
    Error 
    SQL query:
    
    CREATE USER 'user_username'@'localhost' IDENTIFIED BY '******'; 
    MySQL said: Documentation
    
    #1227 - Access denied; you need (at least one of) the CREATE USER privilege(s) for this operation
    
    ERROR 1142 (42000): GRANT command denied to user 'username_user' for table 'username_dbname' 
    mysql>
    I had raised a ticket with my WebHost however after investigating the issue with them they suggested I should come here to seek further assistance regarding this.

    Even after they tried to run the commands as root using the sql queries I still couldn't use the mysql account correctly and it had to be recreated manually through the cPanel console which is cumbersome and time consuming when creating multiple databases due to issues with the privileges for the user.

    Can someone please help me regarding this issue. I'm not sure if this is currently a bug/feature request or something that cPanel can do already but perhaps my webhost may not be aware of what to action in order for it work properly.

    Any help will be greatly appreciated.
     
    #1 itconstruct, Sep 12, 2014
    Last edited by a moderator: Sep 12, 2014
  2. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,678
    Likes Received:
    653
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    Hello :)

    You will not be able to assign the user to the database through the command line because access to /usr/local/cpanel/bin/dbmaptool is restricted to the "root" user. You would have to utilize our API to grant a database user permissions to a database, as referenced in the following post:

    Database User Permissions

    Thank you.
     
Loading...

Share This Page