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.
- 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.
- 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.
- 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.
- Script Execution:
python set_db_privileges.py users.txt
Replace users.txt with the path to your text file containing the list of users.
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")
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.