-----------

Create User in MySQL with Full Privileges

   systemctl status mariadb.service

   systemctl restart mariadb.service

 [root@www ~]# mysql -u root -p

Enter password:

database_user:   user1

database_name:   demodb

# ROOT Access: user to All database

mysql> CREATE USER 'admin'@'%' IDENTIFIED BY 'passwdStrong'; # CREATE USERmysql> GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' WITH GRANT OPTION; # GRANT ALL PRIVILEGES

     FLUSH PRIVILEGES;

#  Database cycloyarn & User:  cycloyarn   

CREATE DATABASE cycloyarn;

CREATE USER 'cycloyarn'@'localhost' IDENTIFIED BY 'Passwd';

GRANT ALL PRIVILEGES ON cycloyarn.* TO 'cycloyarn'@'localhost' IDENTIFIED BY 'Passwd' WITH GRANT OPTION;

FLUSH PRIVILEGES;

EXIT;

# mysql -u root -p

mysql> CREATE USER 'user1'@'localhost' IDENTIFIED BY 'user_password';

mysql> CREATE DATABASE demodb;

mysql> SHOW DATABASES;

mysql> SELECT User,Host FROM mysql.user;

mysql> FLUSH PRIVILEGES;

mysql> exit

To grant access from a machine with IP 172.8.0.2 you would run:

mysql> CREATE USER 'user2'@'172.8.0.2' IDENTIFIED BY 'user_password';

To create a user that can connect from any host, use the '%' wildcard as a host part:

mysql> CREATE USER 'user3'@'%' IDENTIFIED BY 'user_password';

mysql> GRANT ALL PRIVILEGES ON *.* TO 'user1'@'localhost';   # all databases

mysql>  GRANT ALL PRIVILEGES ON demodb.* TO 'user1'@'localhost';          # specific database 

mysql>  GRANT ALL PRIVILEGES ON demodb.table_name TO 'user1'@'localhost'; # specific table from a database

Grant multiple privileges to a user account over a specific database:

mysql> GRANT SELECT, INSERT, DELETE ON demodb.* TO user1@'localhost';

Display MySQL User Account Privileges

mysql> SHOW GRANTS FOR 'user1'@'localhost';

Revoke Privileges from a MySQL User Account

mysql> REVOKE ALL PRIVILEGES ON demodb.* TO 'user1'@'localhost';

mysql> REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'cactiuser'@'localhost';

Remove an Existing MySQL User Account

mysql> DROP USER 'user1'@'localhost'

mysql> SHOW DATABASES;

mysql>  drop database demodb;            [Remove database]

mysql> use mail;

mysql> show tables;

                                +-----------------------+

                                | Tables_in_mail        |

                                +-----------------------+

                                | admin                 |

                                | alias                 |

                                | employe                 |

                                | alias_domain          |

                                | quota                 |

                                | customers               |

                                | vacation              |

mysql>  DROP TABLE Employe, Customers;   [ DROP TABLE ]

Mysql Port :  3306

MySQL reset password for user account -ARIF

Step 1 – Login as root

Run mysql command:

mysql -u root -p mysql

OR

mysql -u root -p -h localhost mysql

OR

mysql --user root --password --host localhost msyql

Sample outputs:

MySQL reset password for user named Arif

The syntax is as follows to reset password (depends upon your version of mysql/mariadb server):

ALTER USER arif IDENTIFIED BY 'passwordHere';

OR

SET PASSWORD FOR 'arif'@'localhost' = 'passwordHere';

OR

SET PASSWORD FOR 'arif'@'localhost' = PASSWORD('newPass');

For example, if you had an entry with User and Host column values of ‘jerry’ and ‘localhost’, you would write the statement like this at the mysql/maridb shell prompt:

SET PASSWORD FOR 'jerry'@'localhost' = PASSWORD('newPassHere');

Sample outputs:

Query OK, 0 rows affected (0.00 sec)

Step 3 – Exit from mysql shell

You must flush privileges and exit to the shell using following two sql commands:

FLUS PRIVILEGES;

exit

Step 4 – Test new password

Now password has been changes. It is time to test it. Again syntax is

mysql -u arif -p

OR

mysql -u jerry -p -h localhost

OR

mysql --user arif --password --host localhost db_name_here

MariaDB: Change a user password

# mysql --version # [version of MySQL or MariaDB database server]

# mysql -u root -p

MariaDB 10.1.20 and earlier

SET PASSWORD FOR 'username'@'localhost' = PASSWORD('NEWPASSWORD'); FLUSH PRIVILEGES; exit;

MySQL 5.7.6 and later

ALTER USER 'username'@'localhost' IDENTIFIED BY 'NEWPASSWORD'; FLUSH PRIVILEGES;

If ALTER USER statement doesn’t work for you, you can modify the user table directly:

UPDATE mysql.user SET authentication_string = PASSWORD('NEW_USER_PASSWORD')

WHERE User = 'user-name' AND Host = 'localhost';

FLUSH PRIVILEGES;

Changing the ROOT Password

Database server to reload the grant tables by issuing the FLUSH PRIVILEGES command.

Note: If the ALTER USER command doesn’t work, it’s usually indicative of a bigger problem. However, you can try UPDATE ... SET to reset the root password instead.

mysql> UPDATE mysql.user SET authentication_string = PASSWORD('new_password') WHERE User = 'root' AND Host = 'localhost';

Reset the MariaDB Root Password

If you forget your root MariaDB password, don’t worry and be sad because it can be reset easily with this tutorial.

• 1. Stop the current MariaDB server instance, then restart it with an option to not ask for a password:

     1. • 1. $  systemctl stop mariadb  $  mysqld_safe --skip-grant-tables &

  2. Reconnect to the MariaDB server with the MariaDB root account:

• • • • 1. $ mysql -u root

• 3. Use the following commands to reset root’s password. Replace password with a strong password:

• • • • 1. use mysql;  update user SET PASSWORD=PASSWORD("ROOTpassword") WHERE USER='root';  flush privileges;  exit

• 4. Then restart MariaDB:

• • • • 1. $ systemctl start mariadb

                                                 ---------------------- -----X---------------------------------

create a new user, run the following command in the mariadb shell:

# mysql -u root -p

mysql >  CREATE DATABASE demodb;

mysql >  SHOW DATABASES;

+--------------------+ | Database           | +--------------------+ | information_schema | | demodb             | | mysql              | +--------------------+ 3 rows in set (0.00 sec)

Create User: user1

CREATE USER 'user1'@'localhost' IDENTIFIED BY 'demopassword';

user was created by running a SELECT query again:  mysql >   SELECT User, Host, Password FROM mysql.user;

mysql >  SELECT User, Host, Password FROM mysql.user; +----------+-----------+------------------------------------------+ | User     | Host      | Password                                 | +----------+-----------+------------------------------------------+ | root     | localhost | 2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 | | root     | demohost  | 2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 | | root     | 127.0.0.1 | 2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 | | user1   | localhost | 0756A562377EDF6ED3AC45A00B356AAE6D3C6BB6 | +----------+-----------+------------------------------------------+

Grant database user privileges

Right after you create a new user, it has no privileges. The user can be used to log in to MariaDB, but it can’t be used to make any database changes.

• 1. Give the user full privileges for your new database [ demodb  ] by running the following command:

     1. 1. mysql >   GRANT ALL PRIVILEGES ON demodb.* to demouser@localhost;

  2. Flush the privileges to make the change take effect.

• • • 1. mysql >   FLUSH PRIVILEGES;

• 3. To verify that the privileges were set, run the following command:

• • • 1. mysql >  SHOW GRANTS FOR 'demouser'@'localhost';

• • 1. MariaDB returns the commands needed to reproduce that user’s privileges if you were to rebuild the server. USAGE on \*.\* means that the user gets no privileges on anything by default. That command is overridden by the second command, which is the grant that you ran for the new database.

       1. +-----------------------------------------------------------------------------------------------------------------+  | Grants for demouser@localhost                                                                                   |  +-----------------------------------------------------------------------------------------------------------------+  | GRANT USAGE ON *.* TO 'demouser'@'localhost' IDENTIFIED BY PASSWORD '*0756A562377EDF6ED3AC45A00B356AAE6D3C6BB6' |  | GRANT ALL PRIVILEGES ON `demodb`.* TO 'demouser'@'localhost'                                                    |  +-----------------------------------------------------------------------------------------------------------------+  2 rows in set (0.00 sec)

Revoke privileges

Sometimes you might need to revoke (remove) privileges from a user. For example, suppose that you were granting ALL privileges to ‘demouser’@’localhost’, but you accidentally granted privileges to all other databases, too, as shown in the following commands:

+-----------------------------------------------------------------------------------------------------------------+ | Grants for demouser@localhost                                                                                   | +-----------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'demouser'@'localhost' IDENTIFIED BY PASSWORD '*0756A562377EDF6ED3AC45A00B356AAE6D3C6BB6' | | GRANT ALL PRIVILEGES ON *.* TO 'demouser'@'localhost'                                                           | +-----------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)

To correct the mistake, you can use a REVOKE statement, followed by GRANT statement to apply the correct privileges.

REVOKE ALL PRIVILEGES ON database_name.* TO 'database_user'@'localhost';

 REVOKE ALL ON *.* FROM demouser@localhost;mysql > GRANT ALL PRIVILEGES ON demodb.* to demouser@localhost; mysql > SHOW GRANTS FOR 'demouser'@'localhost';  +-----------------------------------------------------------------------------------------------------------------+ | Grants for demouser@localhost                                                                                   | +-----------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'demouser'@'localhost' IDENTIFIED BY PASSWORD '*0756A562377EDF6ED3AC45A00B356AAE6D3C6BB6' | | GRANT ALL PRIVILEGES ON 'demodb'TO 'demouser'@'localhost'                                                           | +-----------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) 

Now your user has the correct privileges, and your database server is slightly more secure (granting privileges like ALL on *.* is deemed as a very bad practice). You should also read official MariaDB documentation regarding possible privilege choices, to grant only those privileges truly needed, rather than using ALL.

------

--