How To Create Users and Grant Privileges in MySQL

Managing user access in MySQL is similar to Postgres (see Postgres user creation here). You can either manage the access privileges through roles or give privileges directly to users.

This post is a quick reference for granting privileges to users. Let’s have a look at a simple use case.

We would like to create a user called writer with full access to test, usermanaged, datamart, and staging schemas and read only access to sakila, sys and world schemas.

First of all, let’s create the user with password.

1
CREATE USER 'writer' IDENTIFIED BY 'Password1';

Now, we can add privileges to the user. MySQL does not allow to have a list of databases in the grant statement. You need to do it by schema. For the table, you can use the wild card asterisk.

1
2
3
4
5
6
7
GRANT ALL Privileges ON test.* TO 'writer';
GRANT ALL Privileges ON usermanaged.* TO 'writer';
GRANT ALL Privileges ON datamart.* TO 'writer';
GRANT ALL Privileges ON staging.* TO 'writer';
GRANT SELECT ON sakila.* TO 'writer';
GRANT SELECT ON sys.* TO 'writer';
GRANT SELECT ON world.* TO 'writer';

We also create a read only user who only has read access to all schemas.

1
CREATE USER 'readonly' IDENTIFIED BY 'Password1';

If the permission is given to all schemas and tables, we can use * as below.

1
GRANT SELECT ON *.* TO 'readonly';

REFERENCE

CREATE USER Syntax
GRANT Syntax
Using Roles

Git
How to specify which Node version to use in Github Actions

When you want to specify which Node version to use in your Github Actions, you can use actions/setup-node@v2. The alternative way is to use a node container. When you try to use a publicly available node container like runs-on: node:alpine-xx, the pipeline gets stuck in a queue. runs-on is not …

AWS
Using semantic-release with AWS CodePipeline and CodeBuild

Here is the usual pattern of getting the source from a git repository in AWS CodePipeline. In the pipeline, we use AWS CodeStart to connect to a repo and get the source. Then, we pass it to the other stages, like deploy or publish. For some unknown reasons, CodePipeline downloads …

DBA
mysqldump Error: Unknown table ‘COLUMN_STATISTICS’ in information_schema (1109)

mysqldump 8 enabled a new flag called columm-statistics by default. When you have MySQL client above 8 and try to run mysqldump on older MySQL versions, you will get the error below. mysqldump: Couldn’t execute ‘SELECT COLUMN_NAME, JSON_EXTRACT(HISTOGRAM ‘$”number-of-buckets-specified”‘) FROM information_schema.COLUMN_STATISTICS WHERE SCHEMA_NAME = ‘myschema’ AND TABLE_NAME = ‘craue_config_setting’;’: Unknown …