Curious about how StrongDM works? 🤔 Learn more here!

Product Solutions Docs Resources Company Login Try it free

Blog / Databases

How to Create Users and Grant

Permissions in MySQL [Tutorial]

by Schuyler Brown
Chairman of the Board
StrongDM

6 min read
Limiting access privileges and
Last updated on:
January 16, 2024
managing user credentials in MySQL
Download this tutorial as a
requires a great deal of repetitive and
PDF
manual effort, especially if you have
Found in: dozens (or even hundreds) of MySQL
Security
Databases instances across multiple servers.
Admins need ways to manage MySQL
database access with consistency and
efficiency.
StrongDM manages and
audits access to In this tutorial, we’ll walk through the most common MySQL
infrastructure. commands and management techniques, including:
Role-based, attribute-based, &
just-in-time access to How to create users within a MySQL database
infrastructure How to grant and revoke user permissions within the
Connect any person or service to
database management system and to the underlying data
any infrastructure, anywhere
Logging like you've never seen How to use a centralized interface to provision user access
in MySQL

This tutorial focuses on a self-managed MySQL environment

Get a demo
where you have root level database access. While many of the
commands below may work in a hosted environment (such as
AWS RDS or Google’s Cloud SQL), there may be restrictions on
the commands you can run and how you manage user access in
such an environment.

Create MySQL databases and users

Once you have MySQL installed on the server(s) that will host
your MySQL environment, you need to create a database and
additional user accounts. In order to run the following
commands, log into the MySQL instance with the MySQL root
account.

Create a MySQL database

To create a database called `strongdm`, type the following into
the MySQL command line:

mysql> CREATE DATABASE strongdm;

If the database does not already have a unique name for the
MySQL instance, MySQL will issue an error message with error
code 1007. Add IF NOT EXISTS to the command to prevent this
error with the code below:

mysql> CREATE DATABASE IF NOT EXISTS

strongdm;

Delete a MySQL database

To delete a database called `strongdm`, type the following
command into your MySQL command line:

mysql> DROP DATABASE strongdm;

Note: This command will permanently delete your database and

its associated data. The MySQL command line interface will not
prompt you to confirm the action, so use this command with
care.

Create a new MySQL user account

MySQL defines users with a username and the hostname or IP
address that they're using to access the MySQL instance. To
create a new user in MySQL, specify the username, the
hostname the user can use to access the database management
system, and a secure password:

mysql> CREATE USER 'local_user'@'localhost'

IDENTIFIED BY 'password';

This command will allow the user with username local_user to

access the MySQL instance from the local machine (localhost)
and prevent the user from accessing it directly from any other
machine. Alternatively, you can use a wildcard character (%) in
the host definition to grant access to the MySQL instance for a
user:

mysql> CREATE USER 'subnet_user'@'10.0.%'

IDENTIFIED BY 'password';
In the above example, the `10.0.%` specifies that the user can
access the MySQL instance from any client that has an IP
address beginning with `10.0.`. You may use the wild card at any
level of the IP address in the host definition.

To view all users in your MySQL instance, use the SELECT

command:

mysql> SELECT * FROM mysql.user;

MySQL user account management

Without privileges, a newly created user account can connect to
the MySQL instance but cannot access any data or perform any
actions. Let’s look at MySQL privileges more closely:

Understanding privileges in MySQL

In MySQL, a privilege is a right to perform an action on a
database that must be granted to users. This effectively defines
a user's access level on a database and what they can do within
it. We can organize these privileges by scope into levels:

Global privileges apply to all databases on the server.

Administrative privileges fall into the global group because
they enable a user to manage operations of the MySQL
server and aren't specific to a particular database.
Database privileges apply to specific databases in your
MySQL instance and all of the objects within those
databases (e.g. tables, columns, and views). You can also
grant database privileges globally.
Proxy privileges allow a user to act as if they have the
privileges granted to another user.
Privileges for database objects (tables, columns, stored
routines, views, etc.) can apply to all objects of one type
within a particular database or to specific objects, such as a
certain table or view. You can also grant database object
privileges globally.

Information about MySQL privileges are stored in grant tables in

the `mysql` database within your MySQL instance, as follows:
Some common privileges include:

`ALL PRIVILEGES`: The user is granted all privileges except

GRANT OPTION and PROXY.
`ALTER`: The user can change the structure of a table or
database.
`CREATE`: The user can create new databases and tables.
`DELETE`: The user can delete rows in a table.
`INSERT`: The user can add rows to a table.
`SELECT`: The user can read rows from a table.
`UPDATE`: The user can update rows in a table.

Grant permissions to a MySQL user account

The GRANT statement allows you to set MySQL access
permissions using the following syntax:

mysql> GRANT privilege ON privilege_level TO

account_name;

Type the following to grant `SELECT` and `INSERT` privileges to a

local user on the `strongdm` database:

mysql> GRANT SELECT, INSERT ON strongdm.* TO

'local_user'@'localhost';

To create a user with the same privileges as the root user, use
the following command, which grants global privileges to the
user Janet connecting via localhost:

mysql> GRANT ALL ON *.* TO

'janet'@'localhost' WITH GRANT OPTION;

The WITH GRANT OPTION clause allows users to grant their

privileges to other users.

Revoke permissions from a MySQL user account

To remove privileges, use the REVOKE command, which uses a
syntax similar to the GRANT command. For example, if you
wanted to revoke `SELECT` and `INSERT` privileges from a local
user on the `strongdm` database, type the following:

mysql> REVOKE SELECT, INSERT ON strongdm.*

FROM 'local_user'@'localhost';

If the user does not actually have the privilege in question, this
command will not affect any of their privileges.
Change a MySQL user account password
The syntax to change a user password depends on your version
of MySQL. To find out the MySQL version you are running, use
the command:

mysql> SELECT version();

To change a password for MySQL 5.76 or higher, use this

command:

mysql> ALTER USER 'local_user'@'localhost'

IDENTIFIED BY 'new_password';

For older versions of MySQL, use this command instead:

mysql>SET PASSWORD FOR

'local_user'@'localhost' =
PASSWORD('new_password');

Delete MySQL users

To delete a MySQL user, use the DROP command:

mysql> DROP USER 'local_user'@'localhost';

Display MySQL user account privileges

To view the privileges of a MySQL user, use the `SHOW
GRANTS` command:

mysql> SHOW GRANTS FOR

'local_user'@'localhost';

Determining if privileges are correct

Occasionally, admins need to review user access to different
databases, tables, views, or columns. In addition to checking
which grants a user has, you can also look at the privileges that
are set on a specific table or column.

For example, to check the privileges granted on a table named

`my_table`, use the following command:

mysql> SELECT * FROM

'INFORMATION_SCHEMA'.'TABLE_PRIVILEGES' WHERE
TABLE_NAME='my_table';

Similarly, to look at the column privileges:

mysql> SELECT * FROM

'INFORMATION_SCHEMA'.'COLUMN_PRIVILEGES'
WHERE TABLE_NAME='my_table';

Use the SHOW GRANTS command to display all privileges

granted to a specific user. If you don't specify a user, the
privileges for the current user will be displayed.

mysql> SHOW GRANTS;

For a specific user, use:

mysql> SHOW GRANTS FOR

'local_user'@'localhost';

This command is useful for database auditing. For example, an

admin could use it for auditing if a user has access to more
objects than they should.

To show permissions for the user via any host, not just localhost,
you’ll need to run a few commands:

mysql> SELECT CONCAT('SHOW GRANTS

FOR''',user,'''@''',host,''';') FROM
mysql.user WHERE user='local_user';

This will display all of the SHOW GRANTS commands the user
can run, one for each host the user has permission to access the
database from. Copy each command and run it to see all the
privileges the user has in the MySQL instance.

Effectively managing your MySQL

databases and user accounts
Once you set up databases, users, and permissions, consider
what the daily management of your MySQL databases, user
accounts, and privileges look like. Developers, business users,
contractors, vendors, and more all need access. How will you
manage database credentials as the infrastructure grows? How
will you ensure each user has granular access and only performs
certain tasks and nothing more?

A control plane simplifies provisioning access to MySQL

databases and reduces strain on admins in several ways:

Centralized authentication for everyone in the

organization. Regardless of the user type or destination
database, StrongDM eliminates the need to manually
manage authentication credentials for individual users.
StrongDM integrates directly with your identity provider
using OIDC and SAML protocols to handle authentication.
View and modify access privileges from one place. The
StrongDM admin interface provides a role-based system for
managing permissions. In the Admin UI, simply drag and
drop users to the appropriate roles, and their permissions
automatically update. Infrastructure administrators can
onboard users, view and modify permissions, and revoke
access with ease.
Auditing of all actions against all hosts. The StrongDM
proxy logs all actions — queries, authentication requests,
changes to permissions, and more — so you can easily
understand who did what, when, and in what order. You can
also configure log storage to your specifications with
options for encryption, streaming to any log aggregator,
and retaining logs locally.

Setting up a control plane for your MySQL

databases
 The manual work associated with MySQL account management
frustrates many admins. StrongDM automates and consolidates
access control so that MySQL administrators can efficiently
manage user privileges through a control plane. StrongDM
integrates with any Single Sign-On (SSO) provider and connects
to your infrastructure wherever it resides, whether on-prem or in
the cloud.

Try StrongDM to see how a centralized control plane can help

you efficiently and securely manage your MySQL databases.

Check out our permissions management solution to learn more

about how StrongDM helps companies manage permissions.

About the Author

Schuyler Brown, Chairman of the Board, began working
with startups as one of the first employees at Cross
Commerce Media. Since then, he has worked at the venture
capital firms DFJ Gotham and High Peaks Venture Partners.
He is also the host of Founders@Fail and author of Inc.com's
"Failing Forward" column, where he interviews veteran
entrepreneurs about the bumps, bruises, and reality of life
in the startup trenches. His leadership philosophy: be
humble enough to realize you don’t know everything and
curious enough to want to learn more. He holds a B.A. and
M.B.A. from Columbia University. To contact Schuyler, visit
him on LinkedIn.

💙 this post?
Then get all that StrongDM
goodness, right in your
inbox.

Subscribe

You May Also Like

PostgreSQL Drop Database (15+ Methods)
The DROP DATABASE command in PostgreSQL is a powerful command that is used to
delete a database along with all its associated objects, such as tables, views, indexes, and
other database-specific elements. It is often a good practice to clean up your workspace by
removing unused databases. However, keep in mind that deleting an existing PostgreSQL
database deletes all objects and data within that database. This command should be used
with caution as it irreversibly removes the specified database and its contents.

How to Create a Postgres User (Step-by-Step Tutorial)

Creating Postgres users isn't just a routine step in the complicated world of database
management; it's a critical strategy that has a significant impact on how PostgreSQL
databases operate and remain secure. An increasing number of organizations depend on
sophisticated data systems, so it's critical to recognize the value of Postgres users. This
blog post walks you through the steps of creating a Postgres user, as well as, explores the
significance of these users in database administration, emphasizing their function in
maintaining security, limiting access, and ensuring efficient data management.

Are Your Databases a Pain in the Access?

The number and complexity of databases that every organization must manage has
skyrocketed. If you need access - or need to provide it - it can sure be a pain in the access
to manage.

What is Just-in-Time Access (JIT)? Benefits, Types & More

Today, we’ll take a look at what just-in-time access (JIT) means and what types there are.
You’ll also learn about what a JIT access solution can do for your organization. By the end
of this article, you’ll understand how just-in-time access works, the best practices to
ensure secured implementation, and how strongDM comes to the rescue.

Automating Database Credentialing Guide for 2024

Database sprawl is a lot like expanding into the suburbs: your house may be empty at first,
but before you know it, you’re having to stuff things into your attic.
Product Docs Resources Company Get Started

Zero Trust Privileged Docs Home Blog About Us Try It Free

Access Management
User Guide Articles Help Center Contact Us
Solutions
Admin Guide Videos Support Schedule a Demo
How It Works
API Webinars Careers
We h Your Stack
Podcasts Security
Pricing
Comply Legal
Customers
Customer Stories Press
Compare

© 2024 StrongDM Privacy Policy Terms of Use