Title: Create database users and get user information from MariaDB in Python

[Getting user information from MariaDB in Python]

When you work with MariaDB, or any database for that matter, it's a 💥REALLY BAD IDEA💥 to perform database operations while logged in as root. The root user can do anything including things like dropping tables, changing user privileges, removing users, and destroying the entire database. Doing those things accidentally can be disasterous.

It's much safer to make your program log in as a normal user with just enough privileges to do what you need it to. For example, if a program only needs look at some data, it doesn't need the ability to delete users.

This post explains a bunch of helper functions that you can use to create and list users, and to see what privileges a user has.

Note that you must be logged in as root or some other user with superpowers to do this because these commands are pretty foundational.

HeidiSQL

This post is mainly about managing users from inside a Python program, but HeidiSQL lets you work with users interactively. Just use the Tools menu's User Manager command to launch the manager shown in the picture at the top of this post. There you can create and delete users, view and set user privileges, and change passwords.

Creating Users

You use the SQL CREATE USER command to create a user. The following create_user function provides a wrapper.

def create_user(cur, username, password): '''Create a user.''' # Compose the command. command = f'CREATE OR REPLACE USER {username} IDENTIFIED BY "{password}"' #print(command) # Execute the command. cur.execute(command)

This code creates an SQL command with the following form:

CREATE OR REPLACE USER rod@localhost IDENTIFIED BY "my_secret_password"

I'm assuming the user and host names don't include spaces or other special characters. If they do include spaces (which I don't recommend), you must enclose them each in quotes.

The host name tells what hosts the user is allowed to connect from. In this example, user Rod can only connect from localhost.

The CREATE USER statement has a couple of useful alternative forms.

CREATE OR REPLACE USER <user> IDENTIFIED BY <password> - This version overwrites the user if it already exists. The net result is that the user gets the new password. This is equivalent to using DROP USER IF EXISTS followed by CREATE USER.
CREATE USER IF NOT EXISTS <user> IDENTIFIED BY <password> - This version only creates the user if no such user already exists.

As always with these kinds of SQL commands, if you try to do something that would break existing values, you get an error. For example, if you try to create a user that already exists and you omit IF NOT EXISTS, you get an error.

Listing Users

You can get some user information by querying mysql.user, but the results aren't very reliable. We'll talk about that shortly.

However, mysql.user can at least give you the users' names. The following list_users function returns a list of the users' names and hosts.

def list_users(cur): '''Return a list of user names.''' query = 'SELECT user, host FROM mysql.user' cur.execute(query) return [f'{user}@{host}' for user, host in cur]

This code builds a query to select the users' names and hosts from mysql.user. It uses a list comprehension to compose them into the form user@host. For example, here's the output I get from this example.

root@127.0.0.1 root@::1 root@farnsworth Rod@localhost mariadb.sys@localhost root@localhost

Granting Privileges

To set a user's privileges, you use an SQL statement similar to the following.

GRANT SELECT, INSERT, UPDATE, DELETE ON Library_Db TO Rod@localhost

The following grant_privileges function provides a wrapper for this command.

def grant_privileges(cur, username, priv_level, privileges): '''Give this user some privileges.''' # You can grant privileges at these levels: global, database, table, # column, function, procedure. command = f'GRANT {privileges} ON {priv_level} TO {username}' cur.execute(command)

This code uses the username (something like Rod@localhost), privilege level (more on that in a second), and privileges (a comma-separated list like SELECT, INSERT, UPDATE) to compose the command and then executes it.

You can give a user privileges at many levels. For example, you can give a user the ability to view records for a particular database, a particular table in a database, or a particular column in a table within a database. The following list shows examples of the formats you would use for the priv_level parameter to achieve those levels.

*.* - Grants privileges to all tables in all databases.
my_database.* - Grants privileges to all tables in my_database.
my_database.my_table - Grants privileges to the table my_table in the my_database database.
(column1, column2) ON my_database.my_table - Grants privileges to the indicated columns in the table my_table in the my_database database.

For example, the following code grants the INSERT and SELECT privileges to user Rod@localhost for the punchlines table in the jokes_db database.

grant_privileges(cur, 'Rod@localhost', 'jokes_db.punchlines', 'INSERT, SELECT')

Listing Available Privileges

To see a list of all of the supported privileges (like SELECT or UPDATE), you can execute the command SHOW PRIVILEGES. You can write a Python program to do that, or you can use HeidiSQL to do it interactively. To use HeidiSQL, follow these steps.

Click the Query tab just below the toolbar.
Enter the query SHOW PRIVILEGES.
Right-click on the query and select Run.

(You can create new Query tabs by clicking the little "Open a blank query tab" icon to the right of the Query tab.)

The picture on the right shows the Query tab with this command in it and a small part of the results below the Query tab.

In addition to those privileges, you can also use GRANT ALL PRIVILEGES ON <priv_level> TO <username> to give the user every privilege. You might do that to create a superuser for a particular database or table while not granting privileges to other databases or tables.

Revoking Privileges

You use the REVOKE statement to remove privileges from a user. The format is:

REVOKE ALTER ON my_database FROM Rod@my_database

Here's a nice little revoke_privileges wrapper for this command.

def revoke_privileges(cur, username, priv_level, privileges): '''Remove some privileges from this user.''' command = f'REVOKE {privileges} ON {priv_level} FROM {username}' cur.execute(command)

This code just composes the command and executes it.

Sometimes it may be easier to grant all privileges to a user and then revoke the few that are unnecessary, although I think it's more common to explicitly grant only the desired privileges instead.

Showing Grants

As I mentioned earlier, MariaDB stores some use privilege information in mysql.user, but that information doesn't capture details about privileges granted at different levels (like database- or table-level). To see what privileges a user has, use a SHOW GRANTS command.

Unfortunately, the results are somewhat confusing. When you use SHOW GRANTS, you get a list of GRANT statements that give the user various privileges. If you use multiple GRANT statements to give the user privileges, then SHOW GRANTS will probably return multiple results. For example, here's one set of results that I got while testing.

GRANT USAGE ON *.* TO `Rod`@`localhost` IDENTIFIED BY PASSWORD '*CFB436A1D43B0D2C347D1505C93QO9F1KJ0183EB' GRANT SELECT, INSERT, UPDATE, DELETE ON `library_db`.* TO `Rod`@`localhost`

The USAGE "privilege" doesn't mean much. It basically grants the user the permission to interact with an object like a database or table. The user still needs other privileges like INSERT and UPDATE to do anything with that object.

The following show_grants_results function prints a user's GRANTS statements.

def show_grants_results(cur, username, priv_level): '''Display unedited results from SHOW GRANTS.''' query = f'SHOW GRANTS FOR {username};' cur.execute(query) for row in cur: print(f' > {row}')

If you want your program to do something with the results, you may want to us the following function instead.

def list_grants(cur, username, priv_level): '''Return a list of the user's privileges.''' query = f'SHOW GRANTS FOR {username};' #print(query) cur.execute(query) # Pull out results between GRANT and TO. privs = [] for row in cur: grants = row[0].replace('GRANT ', '').split(' ON ')[0] privs += [priv.strip() for priv in grants.split(',')] return privs

This function executes the SHOW GRANTS query and then loops through the results. For each result, it removes the GRANT part, splits the statement at the ON, and returns whatever comes before ON. That will be a comma-separated list of the privileges. It then uses a list interpolation to split the privileges, remove blank space, and append the privileges to the privs list.

After it finishes processing the GRANT results, the function returns the list of privileges.

This isn't perfect because the list doesn't indicate the privilege level (e.g. database or table). For example, a user might have different privileges for different tables. You can modify the function if you want more detail.

Using the Helpers

The example uses the following main program to test the helper functions.

# Main program. root_password = 'my_super_secret_password' db_name = 'Library_Db' # Connect to MariaDB. try: conn = mariadb.connect( user='root', password=root_password, host='localhost', port=3306 # Default MariaDB port ) except mariadb.Error as e: print(f'Error connecting to MariaDB: {e}') else: try: # Create a cursor. cur = conn.cursor() # Create the user. create_user(cur, 'Rod@localhost', 'Rods_Password') # Display the users. print('Users:') for user in list_users(cur): print(f' {user}') # Give Rod some privileges. privileges = 'SELECT, INSERT, UPDATE, DELETE, ALTER' grant_privileges(cur, 'Rod@localhost', 'library_db.*', privileges) # Revoke the ALTER privilege. revoke_privileges(cur, 'Rod@localhost', 'library_db.*', 'ALTER') conn.commit() # Show Rod's privileges. print('Rod\'s grants:') show_grants_results(cur, 'Rod@localhost', 'library_db.*') # Show raw GRANTS results. print('Rod\'s privileges:') for priv in list_grants(cur, 'Rod@localhost', 'library_db.*'): print(f' > {priv}') except mariadb.Error as e: print(f'MariaDB error: {e}') finally: # Close the cursor and connection. cur.close() conn.close()

The code connects to MariaDB and creates a cursor. It then performs the following steps.

Calls create_user to create user Rod@localhost.
Calls list_users to list the users.
Calls grant_privileges to grant Rod the SELECT, INSERT, UPDATE, DELETE, and ALTER privileges on the library_db database.
Calls revoke_privileges to revoke Rod's ALTER privilege on the library_db database.
Calls show_grants_results to show the raw GRANTS for Rod@localhost.
Calls list_grants to show the Rod's privileges.

The program finishes by closing the database cursor and connection.

Here are the results.

Users: root@127.0.0.1 root@::1 root@farnsworth Rod@localhost mariadb.sys@localhost root@localhost Rod's grants: > ("GRANT USAGE ON *.* TO `Rod`@`localhost` IDENTIFIED BY PASSWORD '*CFB436A1D43B0D2C347D1505C93QO9F1KJ0183EB'",) > ('GRANT SELECT, INSERT, UPDATE, DELETE ON `library_db`.* TO `Rod`@`localhost`',) Rod's privileges: > USAGE > SELECT > INSERT > UPDATE > DELETE

Conclusion

Hopefully you won't need to create new users and modify their privileges very often. In that case, you may be better off using HeidiSQL to do those things interactively.

In contrast, you may want your Python program to be able to list users and show their privileges to verify that those are correct.

In my next MariaDB post, I'll explain how you can make a Python program create, populate, and query tables. In the meantime, download the example to experiment with it.

For a lot more information about database design and implementation, see my book Beginning Database Design Solutions, Second Edition.