Troubleshooting MariaDB Access: User with Global Privileges Missing Database Permissions

• MariaDB user: This is an account that allows someone to connect to the MariaDB server.
• Global privileges: These are special permissions that grant a user broad powers across the entire MariaDB system, including creating databases and managing user accounts.
• No access to database: Even with global privileges, a user might not be able to access a particular database if they lack the specific permissions for that database.

Why might this happen?

There are a couple of reasons:

How to diagnose the problem?

# Create a user with global privileges (can create databases and manage users)
GRANT ALL PRIVILEGES ON *.* TO 'new_user'@'localhost' WITH GRANT OPTION;

# Create a new database
CREATE DATABASE my_database;

# User tries to access the database (will fail due to missing permissions)
USE my_database;

# Check user's granted permissions (shows global privileges but no specific database access)
SHOW GRANTS FOR 'new_user'@'localhost';

Verifying User Permissions:

# Connect as a user with SHOW GRANTS privilege
GRANT SHOW GRANTS ON *.* TO 'check_user'@'localhost';

# Check what permissions 'new_user' has
SHOW GRANTS FOR 'new_user'@'localhost';

Granting Database Access to the User:

# Grant all privileges on the specific database to the user
GRANT ALL PRIVILEGES ON my_database.* TO 'new_user'@'localhost';

# Now the user can access the database
USE my_database;

Remember to replace 'new_user' and 'my_database' with your actual username and database name.

1. Grant Specific Database Privileges:

This remains the recommended approach. As shown in the previous examples, you can use the GRANT statement to assign specific permissions for a user on a particular database. This provides granular control and avoids giving excessive global privileges.

1. Use Roles:

MariaDB supports roles, which are named sets of privileges that can be assigned to users. You can create a role with the necessary database permissions and then assign that role to the user. This can be helpful for managing complex permission structures.

Here's an example using roles:

# Create a role with access to my_database
CREATE ROLE db_user_role;

GRANT ALL PRIVILEGES ON my_database.* TO db_user_role;

# Assign the role to the user
GRANT db_user_role TO 'new_user'@'localhost';

# Now the user has access to the database through the role
USE my_database;

1. Temporary Access with GRANT TEMPORARY (cautiously):

In some limited scenarios, you might consider using GRANT TEMPORARY which grants access only for the current connection. This can be useful for troubleshooting or one-time actions. However, use this with caution as it bypasses user management best practices and could be a security risk.

Remember:

• Always strive for the least privilege principle - grant only the specific permissions a user needs for their tasks.
• Use roles for complex permission structures.
• Avoid GRANT TEMPORARY unless absolutely necessary.