DBA Account

Up until now, we’ve predominantly used the root account on localhost for database management. However, as we expand access to remote users, it’s essential to understand how to establish non-root user accounts for remote access. This setup will allow users to connect via various clients like the standard MySQL Client, the newer MySQL Shell, or graphical interfaces such as MySQL Workbench. Here are the steps and concepts we’ll cover:

• Creating Your First Database Account:
  • We will create a database account that can be used to log in remotely, using different MySQL clients.
• Understanding ‘WITH GRANT OPTION’:
  • We’ll explain the significance of the ‘WITH GRANT OPTION’ and why it’s crucial for DBAs to have this ability, which allows them to grant permissions to other users.
• Distinguishing Between MySQL Roles and Users:
  • A discussion on the differences between roles and users in MySQL will be provided, clarifying their distinct uses and management.
• Granting Permissions to Roles and Users:
  • We’ll demonstrate how to grant permissions effectively to roles, and subsequently to users, to streamline user administration and enhance security.
• Locking and Unlocking MySQL Accounts:
  • Instructions on how to secure MySQL accounts by locking and unlocking them as needed for security management.
• Creating Expired Accounts:
  • We will show how to set up accounts with expiration dates to control access and enforce security policies.

These steps will ensure comprehensive access management and enhanced security for MySQL database operations remotely.

MySQL Permissions

Permissions in MySQL are privileges granted to users that allow them to perform specific actions within the database system. Here is a detailed list of common permissions available in MySQL:

• General Permissions:
  • ALL: Grants all available permissions to a user. This is a comprehensive privilege that covers all specified actions within the scope it is applied to (e.g., a database, table, or procedure).
• Specific Permissions:
  • ALTER: Allows the user to modify the structure of a database or table (e.g., changing the schema).
  • CREATE: Enables the creation of new databases, tables, or other objects.
  • DROP: Provides the ability to delete databases, tables, indexes, etc.
  • EXECUTE: Permits the user to run stored procedures.
  • INSERT, DELETE, UPDATE, RENAME: These are row-level permissions that allow the user to perform respective actions on data within tables.
  • SELECT, SHOW: These permissions enable read-only access, allowing users to view but not modify data.
• Wildcard Permission:
  • .*: This notation is used to apply a permission to all objects within a database. For example, specifying GRANT SELECT ON database_name.* allows a user to perform SELECT operations on all tables within database_name.
• Replication-Related Permissions:
  • Includes privileges like REPLICATION CLIENT and REPLICATION SLAVE, which control the ability to manage and monitor replication processes.
• Using GRANT:
  • The GRANT keyword is used to assign any of the above permissions to a user. The syntax for granting permissions typically follows the pattern: GRANT PERMISSION_TYPE ON object TO 'username'@'host';

WITH GRANT OPTION

The WITH GRANT OPTION clause is pivotal when creating MySQL user accounts, particularly for users who will have administrative roles, such as DBAs. Here’s how it works and why it’s important:

• Purpose of WITH GRANT OPTION:
  • This clause allows a user not only to possess certain privileges but also to grant those privileges to other users.
• When to Use:
  • If you intend for a user to have the ability to propagate their permissions to others, include the WITH GRANT OPTION clause when granting them privileges. This is especially crucial for users who manage permissions, such as DBAs.
• Recommendation for DBAs:
  • Always use the WITH GRANT OPTION when creating DBA accounts to ensure they have the necessary authority to manage user permissions effectively.
• Syntax Example:
  • When granting a DBA user all privileges on all databases and the ability to grant those privileges to others, you would use:
    

    GRANT ALL PRIVILEGES ON *.* TO 'dba_username'@'host' WITH GRANT OPTION;

creating DBA account .🌟

reference link
we will create our first non-root DBA user , always before you start any command see if there is help section available
mysql>help create user




this a very help section we can copy the syntax for creating user as showing in above photo and edit as you which , this context i have setup the below command using the help section
CREATE USER IF NOT EXISTS dba IDENTIFIED BY 'Awersdfzxc.1' PASSWORD EXPIRE NEVER ACCOUNT UNLOCK ;

syntax explanation

• User Creation Basics:
  • CREATE USER [IF NOT EXISTS] [user-name] allows you to create a new user. Using IF NOT EXISTS is optional but recommended if you want to prevent duplicate usernames.
• Specifying the Host:
  • username without an @ and host specifies that the user can connect from any host.
  • user@'localhost' restricts the user to connect only from the local machine.
  • user@'ip' allows the user to connect from a specified IP address.
  • user@'%' permits the user to connect from any IP address or host.
• Setting a Password:
  • IDENTIFIED BY 'auth_string' sets the user’s password by replacing 'auth_string' with the desired password.
• Password Expiry Options:
  • PASSWORD EXPIRE [DEFAULT | NEVER | INTERVAL N DAY] controls how the user’s password expiration is handled:
    • DEFAULT: Applies the default password policy of the server.
    • NEVER: The password never expires.
    • INTERVAL N DAY: The password expires after N days.
• Account Status:
  • ACCOUNT UNLOCK: This option ensures the user account is active and unlocked, allowing immediate login access.

granting permission

we have create the user dba now we need to grant permission
snice this is DBA account i will grant it all permission using GRANT ALL PRIVILEGES
also we will add WITH GRANT OPTION; clause so DBA account can give permission to other users
SYNTAX
mysql>GRANT [ALL PRIVILEGES | delete , create etc ] ON [*.* | table name | database name ] TO [username] WITH GRANT OPTION [is optional];

mysql>GRANT ALL PRIVILEGES ON *.* TO DBA WITH GRANT OPTION;

Syntax Explanation in Points:

• GRANT ALL PRIVILEGES: Assigns all available permissions to the user.
• ON *.*: These permissions apply across all databases and tables.
• TO 'DBA': The privileges are granted to the user ‘DBA’.
• WITH GRANT OPTION;: Allows ‘DBA’ to grant their permissions to other users, a necessary feature for administrative roles.

FLUSH PRIVILEGES

once you have granted privileges you must run FLUSH PRIVILEGES; so that grant tables can reactivate right away
mysql>FLUSH PRIVILEGES;

view the user privileges

we can use the below command that will show permission for certain user
show grants for [username];
show grants for dba;

Connecting to MySQL

reference link

since we have created our first MySQL account now its time to connect to MySQL remotely
we will show three methods to connect

connecting using MySQL client

To connect to a remote MySQL server using a MySQL client installed on another server, you will use the following syntax:

mysql --host=[ip or hostname] --user=[username] --password=[password]

Alternatively, if you prefer to enter the password interactively to ensure security, you can omit the password value, which will then prompt you to enter the password:

mysql --host=[ip or hostname] --user=[username] --password

For example, to connect as the user ‘dba’ to a MySQL server at the IP address 10.10.10.112, you would use:

mysql --host=10.10.10.112 --user=dba --password

to confirm we are connected to remote MySQL server we will use select user();

also if we run \s will show status

connecting using connecting using MySQL client

we will connect remotely using MySQL shell using SQL mode
below is the syntax
mysqlsh --sql --uri=username@ip or host:3306/[database-name ]
mysqlsh --sql [email protected]:3306/mysql

connecting using connecting using MySQL workbench

for that we need to download MySQL workbench , you use the following link to download it

one you have installed MySQL workbench start up and then + button to start filling the connection string

in the connection name give any friendly name for your connection
in host section provide hostname or Ip of the remote MySQL server
in the username section put the MYSQL account that have permission in our case we will fill dba

once done click Test connection to see if the connection is enabled

you will prompt to fill password for your account

the connection seceded now you can click OK button and you will MySQL to mysql workbench

mysql_native_password & caching_sha2_password auth plugins

MySQL supports two primary authentication plugins which are essential for configuring user security: mysql_native_password and caching_sha2_password. Understanding the differences and configurations of these plugins is crucial for effective database management.

mysql_native_password

• Functionality: Implements the native pluggable authentication, known as ‘NPM’, which is based on the password hashing methods used before the introduction of pluggable authentication.
• Compatibility: This plugin is non-pluggable, meaning it cannot be loaded or unloaded on the fly while MySQL server is running. There is no separate library file because this plugin is built into the MySQL server itself.
• Usage: In MySQL Server versions 5.x and older, mysql_native_password was the default authentication method.

To start MySQL server using mysql_native_password, use the following syntax:

mysql --default-auth=mysql_native_password

caching_sha2_password

• Default Setting: From MySQL server 8.0 onwards, caching_sha2_password is the default password authentication plugin.
• Recommendation: MySQL recommends using this plugin as it provides a more secure password encryption using SHA-256.
• Functionality: The server assigns this plugin to an account and uses it to encrypt the password. These encrypted values are then stored in the authentication_string in the user table of the MySQL system database.
• Integration: This plugin is built into the server and needs to be explicitly loaded; it cannot be disabled by unloading.

which one to use ? mysql_native_password or caching_sha2_password

in some cases you might face issue where user will complain that he is unable to connect to MySQL server
the user also add that he is getting the following error ‘error authentication plugin caching_sha2_password cannot be loaded ‘

the error means that the application or the connector is still using the legacy old authentication method meaning they are still using mysql_native_password
quit possible that the connector our the application has not been upgraded to use caching_sha2_password

workaround
you as a dba will have to go back to the user and alter the user to use mysql_native_password

syntax :
ALTER USER [user-name] IDENTIFIED WITH 'mysql_native_password' by 'password for the user';

create user that uses either mysql_native_password or caching_sha2_password 🌟

another option you have is that you can specify which authentication method the user will use while creating the user

syntax:
CREATE USER [USERNAME] WITH 'mysql_native_password or caching_sha2_password' by password ['password for the user '];

MySQL Roles

• ROLES is named collection of privileges
• to create role your user must have GLOBAL CREATE ROLE or CREATE USER privilege
• whenever you create a role this will be recorded as an event in database and this will be written in binary log
• when role is created is by default locked , and used the default auth plugin
• when creating a role there will not be any password given to the role . so the authentication string is empty
• Roles can be considered as Users in the MySQL database user table
  syntax

CREATE ROLE IF NOT EXISTS 'USERNAME' , 'USERNAME2' , 'USERNAME3';

CREATING MySQL Roles

REFREANCE LINK

as usual we will check help section for creating role
HELP CREATE ROLE

now we will create the following roles ‘reader’ , writer , admin
CREATE ROLE IF NOT EXISTS READER, WRITER, ADMIN;

then we will granted it permission as per the user name suggest
GRANT SELECT ON employees.employees TO READER;

GRANT INSERT, UPDATE, DELETE ON employees.employees TO WRITER;
GRANT ALL ON employees.* TO ADMIN;

next we we will confirm the status of the role and there privilege using the below query

SHOW GRANTS FOR READER;
SHOW GRANTS FOR WRITER;
SHOW GRANTS FOR ADMIN;

next we will create 3 user and assignee them the roles as privilege

CREATE USER IF NOT EXISTS db_reader IDENTIFIED BY 'P@ssw0rd';
CREATE USER IF NOT EXISTS db_writer IDENTIFIED BY 'P@ssw0rd';
CREATE USER IF NOT EXISTS db_admin IDENTIFIED BY 'P@ssw0rd';

now we will start GRANT ROLES TO USERS we have created before

GRANT READER TO db_reader;
GRANT WRITER to db_writer;
GRANT ADMIN to db_admin;

finally we have to run FLUSH PRIVILEGES; to make grants table recreated with the new privileges we have assinged

now let’s confirm the privileges of our user using SHOW GRANTS FOR [username];