Understanding MySQL Privileges: Creating Users with CREATE DATABASE Rights

Explore how to effectively manage MySQL user privileges, including strategies for restricting access and ensuring security. --- This video is based on the question https://stackoverflow.com/q/76229848/ asked by the user 'michal.kyjovsky' ( https://stackoverflow.com/u/13238303/ ) and on the answer https://stackoverflow.com/a/76233488/ provided by the user 'danblack' ( https://stackoverflow.com/u/10195153/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions. Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: MySQL Privileges to CREATE DATABASE/SCHEMA for the user with default rights Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l... The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license. If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com. --- MySQL User Privileges: How to Create Users with CREATE DATABASE Access If you’re diving into MySQL user management, you might find yourself puzzled about user privileges, especially when it comes to creating users and granting them the necessary rights. A common question that arises is, "How can I create a user that only has read access, while also ensuring they can't create databases or schemas?" In this guide, we'll tackle this issue and clarify the privileges associated with MySQL users. The Challenge When creating a MySQL user, you might expect that the user is endowed with the least privileges possible. However, some users find anomalies where their newly created database user can execute commands such as CREATE DATABASE despite initial expectations of restricted access. This leads to confusion, especially when the official MySQL documentation suggests that users should have limited permissions by default. Understanding User Privileges To clarify, it’s important to understand that the least privileges level for MySQL users is actually referred to as USAGE. What this means in a real-world scenario is that when you create a user with the command: [[See Video to Reveal this Text or Code Snippet]] The default privilege associated with this command is USAGE, which means the user has no privileges whatsoever on any database. This can be misleading if you're under the impression that the default is SELECT access. Privileges in MySQL Here’s a rundown of the relevant MySQL privileges: USAGE: The user can do nothing. This is the default permission level. SELECT: The user can read data from the database but can't create or modify it. CREATE: This privilege allows the user to create databases (schemas) or tables within a database. Allowing Read Access Without Creating Databases If your goal is to create a user that solely has read access without granting them the ability to create databases, follow these steps: Step 1: Create the User First, create the user as described above. [[See Video to Reveal this Text or Code Snippet]] Step 2: Grant Read Access to Specific Schemas To give this user read access to certain schemas, such as information_schema or sys, you'll need to run specific GRANT statements. It's crucial to note that the information_schema is generally accessible for reading by all users, but the access to the sys schema requires specific grants: For sys schema access, use the following command: [[See Video to Reveal this Text or Code Snippet]] Step 3: Handling Performance Schema Since the sys schema is essentially a view on the performance_schema, you’ll also need to grant access to the performance schema to allow the user to interact with any underlying data: [[See Video to Reveal this Text or Code Snippet]] Conclusion By understanding the principles behind MySQL user privileges, you can effectively manage access levels and ensure security across your databases. The key takeaway is that when you create a user using the CREATE USER command, they start with USAGE, not SELECT privileges. If you want to give them read-only access without the ability to create databases, follow the outlined steps above. With this knowledge, you can now create users tailored to your project’s needs, ensuring that they have the right access without unnecessary privileges. Happy database management!