Create a user and Grant privileges in mysql database

mysql database user creation

The user creation process in mysql is one of the most important steps in Database administration.
Below we will list some of the Important terms of Authentication, Authorization with practical demonstration.
The process of gaining access to the database engine with an active login credentials and a login request from a trusted source network ensures Authentication.

The part where in the user is allowed to access certain tables in databases or the whole or part of the Databases determines  Authorization.

In SQL administration, The user creation process involves Authentication and Authorization with a practical implementation of a unique username id, identified by the password, the critical component is the source network identification if logging from remote hosts. The permission to specific databases ensuring the least privileges based on the desired role is one of best the practices

Let’s connect using root access to the MySQL Command-Line Tool

[vamshi@mysql01 linuxcent]$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 8.0.19 MySQL Community Server - GPL


Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

 

How to create a new mysql user and Grant the privileges

Sample Syntax:

CREATE USER 'mysql_user'@'hostname' IDENTIFIED BY 'user_password';

It is important to understand that the ‘username’ @ ‘hostname’ is a unique entry of identification pattern for Authenticating to the mysql engine.

The hostname field accepts the values such as [/code]“ip address” / 10.100.0.0/24 / localhost [/code]

And only the incoming requests will be allowed matching the user name.

The syntax for creating a user on mysql goes as follows:

Enabling access for a source of localhost identified by the authentication information

CREATE USER 'vamshi'@'localhost' IDENTIFIED BY 'user_password';

Enabling access for a source IP range of 10. network identified with /24 CIDR followed by the authentication information

CREATE USER 'vamshi'@'10.100.0.0/24' IDENTIFIED BY 'user_password';

Enabling access for a source IP range of specific hostname identified the authentication information

CREATE USER 'vamshi'@'hostname' IDENTIFIED BY 'user_password';

The First step of user access is done, Now we need to grant the access to the Databases, which grants the Privileges to perform actions on the DB by the new user.

Granting Privileges

This section deals with the Authorization;

On the mysqlcli prompt, you would need to issue the GRANT command with appropriate access permissions.

What are Privileges types in mysql?

The Grant Authorizes the Following actions

Like the ability to CREATE tables and databases, read or write FILES, and then even SHUTDOWN the server.

The most commonly used privileges are:

  • ALL PRIVILEGES: Grants all privileges to a user account.
  • SELECT: The user account is allowed to read a database.
  • INSERT: The user account is allowed to insert rows into a specific table.
  • UPDATE: The user account is allowed to update table rows.
  • CREATE: The user account is allowed to create databases and tables.
  • DROP: The user account is allowed to drop databases and tables.
  • DELETE: The user account is allowed to delete rows from a specific table.
  • PROCESS: The user is allowed to get the information about the threads executing within the server
  • SHUTDOWN: The user is allowed to use of the SHUTDOWN and RESTART statements within the server.

Now it’s time to grant the privileges to the new user on a tables belonging to a Database or all the tables on a given database;

Here’s what the Simple GRANT SQL statement looks like:

GRANT ALL PRIVILEGES ON Database_name.Table_name TO 'user@'hostname' ;

Let’s break this down and understand what we just told MySQL to do.

GRANT ALL PRIVILEGES (ALL types of Privileges) for only the given Database_Name and given Table_Name to the user Identified by ‘user@’hostname’

The Database_name and the Table_name can be replaced by the wildcard * means to every Database and Table in the Database respectively.

*.* to specify all databases on the server

database_name.* to specify all tables in one database

database_name.table_name to specify all columns of one table

The Privileges assigned to user while connecting from the source hostname can be a IP address / IP address range 10.100.0.0/24 or a DNS name or simple ‘%’ to allow access from anywhere.

Now For simplicity sake we can simulate the user vamshi will need access to only operate on the sales section of the reports Database.

GRANT ALL PRIVILEGES ON reports.sales TO 'joe@'mysql2.linuxcent.com';

What the above command does is to provide only login access to joe from mysql2.linuxcent.com and access the reports table from sales Database.

By replacing the database name with wildcard * will provide the privileges equivalent super_user level access.

This can be demonstrated as follows:

GRANT ALL PRIVILEGES ON *.* TO 'vamshi'@'%';

Or

GRANT INSERT, UPDATE, DELETE ON reports.* to 'vamshi'@'%';

How to Create Another non-root MYSQL DB Super User

This is just as a security measure whilst disabling the root login to the mysql engine.

GRANT ALL PRIVILEGES ON *.* TO 'vamshi_superuser'@'%';

Display MySQL User Account Privileges

To Display the Privileges granted to specific Mysql user Account, use the command SHOW GRANTS.

mysql> SHOW GRANTS FOR 'root'@'localhost' \G;
*************************** 1. row ***************************
Grants for root@localhost: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `root`@`localhost` WITH GRANT OPTION
*************************** 2. row ***************************

Now to compare the results

Saving Your Changes to the MYSQL database

The changes made so far are to be saved to the special user database called the grant tables, In total there are 5 special tables in the mysql database such as

user
db
host
tables_priv
columns_priv

We commit the changes by issuing the FLUSH PRIVILEGES command at the mysql prompt:

mysql> flush privileges ;
Query OK, 0 rows affected (0.01 sec)

Create a new kubernetes user with custom kubeconfig auth

The important directories in the reckoning are /etc/kubernetes/pki/
The file ca.key and ca.crt are the Certificate Authority key and certificate respectively.

STEP 1: Generating the key and .csr(Certificate Signing Request)

Lets now generate the .key and .csr. certificates for 1 year with openssl:

[root@node01 ssl]# openssl req -new -sha256 -newkey rsa:2048 -nodes -keyout builduser01.key -days 365 -out builduser01.csr -sha256 -subj "/C=IN/ST=TG/L=/O=/OU=/CN=/subjectAltName=DNS.1="

Verification of the CSR:

[root@node01 ssl]# openssl req -in linuxcent.com.csr -noout -text
Certificate Request:
Data:
Version: 0 (0x0)
Subject: C=IN, ST=TG/subjectAltName=DNS.1= -- INFORMATION RETRACTED --
Subject Public Key Info:
Public Key Algorithm: rsaEncryption
Public-Key: (2048 bit)
Modulus:00:e4:b4:24:d7:22:ec:5d:c1:37:8c:d1:a0:62:17:
96:24:77:8d:75:4e:d5:74:15:4d:61:e0:8b:66:d6:
                Exponent: 65537 (0x10001)
        Attributes:
            a0:00
    Signature Algorithm: sha256WithRSAEncryption
         87:ef:83:b2:a6:f5:3a:f3:6f:1c:e4:02:ec:bf:5d:75:64:1d:

STEP 2: Digitally Signing .csr and generating .crt using root CA files.

Now we will using the root ca.key and ca.crt to digitally sign this csr and generate a .crt

[root@node01 ssl]# openssl x509 -req -in builduser01.csr -CA ca.crt -CAkey ca.key -CAcreateserial -out builduser01.crt -days 365 -sha256
Signature ok
subject=/C=IN/ST=TG/subjectAltName=DNS.1=
Getting CA Private Key

We have successfully generated the .crt file from the .csr along with the .key file from STEP1 with the below names. builduser01.crt and builduser01.key

How to create user accounts on kubernetes

We now will create a builduser-config to create a kubeconfig for new user.

Injecting the cluster and the API server information into the kubernetes config file:

[root@node01 ssl]# kubectl config --kubeconfig=builduser-config set-cluster kubernetes --server=https://10.100.0.10:6443 --insecure-skip-tls-verify

We are now injecting the CA certificate information into the config file:

[root@node01 ssl]# kubectl config --kubeconfig=builduser-config set-cluster kubernetes --server=https://10.100.0.10:6443 --certificate-authority=/etc/kubernetes/pki/ca.crt --embed-certs=true

Injecting the credentials key and cert file data into the config file.

[root@node01 ssl]# kubectl config --kubeconfig=builduser-config set-credentials builduser01 --client-certificate=linuxcent.com.crt --client-key=linuxcent.com.key --embed-certs=true

Using –embed-certs=true, we can dump the cert and key file data into the config file instead of writing the path names

[root@node01 ssl]# kubectl config --kubeconfig=builduser-config set-credentials builduser01 --username=builduser01 --password=password123

Using the username and password is not explicitly required while the keys are being used.
Now copy the builduser-config to the $HOME/.kube/config and connect to the kubernetes cluster.