Richard C.
—Once you’ve created a new database in MySQL, how do you give users permissions to use it? And how do you grant a new user all privileges to a database?
Assume that you just created a new database in MySQL, like so:
CREATE DATABASE Test;
If you have no users you can create one:
CREATE USER 'alice'@'%' IDENTIFIED BY 'thepassword';
In the above command, @'%'
specifies from where the user may connect. The wildcard character '%'
allows a user to connect from any machine. You might also restrict them to a specific IP address, such as @'127.0.0.1'
for the localhost.
To grant permissions to your new user, or your existing users, to do anything with the new database, such as modifying tables and their content, run the following command:
GRANT ALL PRIVILEGES ON Test.* TO 'alice'@'%';
Test.*
means every object in the Test database.
Do not append WITH GRANT OPTION
to the end of the command. You cannot give a user permissions to alter other users only for a specific database. If you want your user to be able to create and edit other users, you instead have to give the user permissions for the entire server:
GRANT ALL PRIVILEGES ON *.* TO 'alice'@'%';
*.*
means every object in every database.
Be careful when using this command, as it creates a superuser.
You do not need to run the command FLUSH PRIVILEGES
after altering user permissions. This command is necessary only when directly editing the grant tables manually with updated SQL queries, or when importing users from a backup file. However, your users may need to log out and in again for their session to have the new permissions.
Your user should now be able to create a table in the new database. You can test this by logging in as Alice and running:
CREATE TABLE MyTable (Id INT);
She should not need to be able to create users. You can test this by checking that the following command fails:
CREATE USER 'bob'@'%' IDENTIFIED BY 'thepassword';
If you wish to limit a user’s permissions to only altering data, and not altering table structure, you can grant specific permissions:
GRANT SELECT, INSERT, UPDATE, DELETE ON Test.* TO 'alice'@'%'
Finally, if you wish to revoke Alice’s permissions to the database, you can run the command below. Remember though that it won’t take effect until she logs out.
REVOKE ALL PRIVILEGES ON Test.* FROM 'alice'@'%';
Tasty Treats for Web Developers brought to you by Sentry. Web development tips and tricks hosted by Wes Bos and Scott Tolinski
Listen to SyntaxLoved by over 4 million developers and more than 90,000 organizations worldwide, Sentry provides code-level observability to many of the world’s best-known companies like Disney, Peloton, Cloudflare, Eventbrite, Slack, Supercell, and Rockstar Games. Each month we process billions of exceptions from the most popular products on the internet.
Here’s a quick look at how Sentry handles your personal information (PII).
×We collect PII about people browsing our website, users of the Sentry service, prospective customers, and people who otherwise interact with us.
What if my PII is included in data sent to Sentry by a Sentry customer (e.g., someone using Sentry to monitor their app)? In this case you have to contact the Sentry customer (e.g., the maker of the app). We do not control the data that is sent to us through the Sentry service for the purposes of application monitoring.
Am I included?We may disclose your PII to the following type of recipients:
You may have the following rights related to your PII:
If you have any questions or concerns about your privacy at Sentry, please email us at compliance@sentry.io.
If you are a California resident, see our Supplemental notice.