Set DB privileges cPanel/WHM API

set_db_privileges.md

MySQL User Privilege Management Script

Description

This Python script automates the process of setting MySQL database privileges for users managed under a cPanel/WHM environment. It leverages the cPanel's UAPI and WHM API to modify database privileges based on a predefined list of users. The script is particularly useful for system administrators who need to manage database access rights efficiently across multiple users.

Features

• List Accounts: Retrieves all cPanel accounts using the WHM API.
• List Databases and Users: For each cPanel account, it lists all associated MySQL databases and the users who have access to them.
• Set Privileges: Based on a user list provided through a text file, the script sets:
  • ALL PRIVILEGES for users found in the list.
  • READ-ONLY privileges (SELECT, SHOW VIEW) for all other users who are not listed but have access to the databases.

Requirements

• Python 2.6 or higher (the script includes Python 2.6 compatible syntax).
• Access to the server where cPanel/WHM is installed with privileges to run WHM API and UAPI commands.
• The whmapi1 and uapi command line tools must be available on the system where the script is run.

Usage

1. Prepare User List File: Create a text file (users.txt) containing the usernames of the cPanel accounts for which you want to set ALL PRIVILEGES. Each username should be on a new line.
2. Script Execution:

python set_db_privileges.py users.txt

Replace users.txt with the path to your text file containing the list of users.

Script Example

Here is how the script is structured:

import subprocess
import sys
import json

def run_command(command):
    try:
        process = subprocess.Popen(command, shell=True, stdout=subprocess.PIPE, stderr=subprocess.PIPE)
        stdout, stderr = process.communicate()
        if process.returncode != 0:
            print("Command Error:", stderr)
            return None
        return stdout
    except Exception as e:
        print("Failed to execute command:", str(e))
        return None

def parse_cpanel_accounts(output):
    accounts = []
    current_account = {}
    reading_account = False

    for line in output.split('\n'):
        line = line.strip()
        if line.startswith('-'):
            if current_account:
                accounts.append(current_account)
            current_account = {}
            reading_account = True
        elif reading_account and ':' in line:
            key, value = line.split(':', 1)
            key = key.strip().lower()
            value = value.strip()
            current_account[key] = value
    if current_account:
        accounts.append(current_account)
    return accounts

def list_cpanel_users():
    command = "whmapi1 listaccts"
    output = run_command(command)
    if output is None:
        print("Failed to retrieve cPanel accounts.")
        return []
    return parse_cpanel_accounts(output)

def list_mysql_databases_and_users(cpanel_user):
    command = "whmapi1 --output=jsonpretty list_mysql_databases_and_users user='{0}'".format(cpanel_user)
    output = run_command(command)
    if output is None:
        print("Failed to retrieve MySQL databases for user", cpanel_user)
        return {}
    try:
        data = json.loads(output)
        db_users = data['data']['mysql_databases']
        print("Databases and users for", cpanel_user, ":", db_users)
        return db_users
    except json.JSONDecodeError as e:
        print("JSON decoding error:", str(e))
        return {}

def set_privileges(cpuser, dbuser, dbname, privileges):
    formatted_privileges = privileges.replace(" ", "%20").replace(",", "%2C")
    command = "uapi --output=jsonpretty --user={0} Mysql set_privileges_on_database user='{1}' database='{2}' privileges='{3}'".format(cpuser, dbuser, dbname, formatted_privileges)
    output = run_command(command)
    if output:
        print("Successfully set {0} for {1} on {2}".format(privileges, db_user, dbname))
    else:
        print("Failed to set privileges for {0} on {1}".format(db_user, dbname))

if __name__ == "__main__":
    if len(sys.argv) != 2:
        print("Usage: python script.py <path_to_users_file>")
        sys.exit(1)

    users_file = sys.argv[1]
    target_users = [line.strip() for line in open(users_file)]
    print("Target users to update privileges for:", target_users)

    cpanel_accounts = list_cpanel_users()

    for account in cpanel_accounts:
        if 'user' in account:
            cpanel_user = account['user']
            db_users = list_mysql_databases_and_users(cpanel_user)
            for dbname, users in db_users.items():
                for db_user in users:
                    if db_user in target_users:
                        print("Attempting to set ALL PRIVILEGES for", db_user, "on", dbname)
                        set_privileges(cpanel_user, db_user, dbname, "ALL%20PRIVILEGES")
                    else:
                        print("Applying READ-ONLY privileges for", db_user, "on", dbname)
                        set_privileges(cpanel_user, db_user, dbname, "SELECT,SHOW%20VIEW")

Note

Ensure that the script is executed with appropriate administrative privileges to access and modify cPanel/WHM settings. Incorrect usage or errors in the user list file might lead to unwanted changes in database access settings.