MYSQL/MARIADB User account Management

User account Management:

' Username ' @ ' Hostname ' Username: Any combination of strings that can contain only the characters of the basic meaning, may contain "_", ".", "-"; Hostname: Can be FQDN (fully qualified domain name), domain name, IP address, You can use the MySQL wildcard "_" to represent any single character "%" for any number of arbitrary characters; Create user account: Creating the Users statement: Create user User [identified by [ PASSWORD] ' PASSWORD ' | Identified with Auth_plugin [as ' auth_string '] Example: MariaDB [mysql]> create user ' testuser ' @ '                %‘;            MariaDB [mysql]> Create user ' testuser ' @ '% ' identified by ' qhdlink ';                You can also use DML statements to create user accounts: INSERT into Mysql.user SET user= ' testuser ', host= '% ', Password=password (' Qhdlink '); Example: MariaDB [mysql]> insert INTO user set user= ' user1 ', host= '% ', Password=password (' Qhdlink '        ), ssl_cipher= ', x509_issuer= ', x509_subject= ', authentication_string= ';            Rename user account: RENAME users statement: RENAME user old_user to New_user [, Old_user to New_user] ... Example: MariaDB [mysql]> rename user ' testuser ' @ '% ' to ' test ' @ ' 172.16.%.% '; You can also rename a user account by using DML statements: Example: MariaDB [mysql]> update user set user= ' User01 ', host= ' 172.16.75        .% ' where user= ' user1 ';            To delete a user account: Drop USERS statement: Drop user user [, user] ...            Example: MariaDB [mysql]> drop user ' test ' @ ' 172.16.%.% ';        You can also use DML statements to delete user accounts: example: MariaDB [mysql]> Delete from user where user= ' User01 '; Password Management for user accounts: 1.SET PASSWORD statement: SET PASSWORD [for user] = {PASSWORD (' cleartext PASSWORD ') | Old_password (' cleartext PASSWORD ') | ' Encrypted password '} example: MariaDB [mysql]> set password for ' test ' @ '% ' = password (' q            Hdlink '); 2. You can also use DML statements to modify the user account password: (insert a data into the database) example: MariaDB [mysql]> update user set Password=pas            SWORD (' qhdlink.com ') where user= ' test '; 3.mysqladmin Tools: # mysqladmin-uusername-hhostname-p PASSWOrd ' New_password ' NOTE: The MySQL user performing this operation needs to have Modify permission on the Mysql.user table; the solution to forget the MySQL administrator's password: method one: 1. Stop the current MySQL or mariadb service; 2.                Add the following two server parameters to the/etc/my.cnf file: skip-grant-tables = on skip-networking = On 3. Start the MySQL or mariadb service and use the MySQL or Mysqladmin client tool to log in with the empty secret root user to change the password of the root user; 4. Remove the above two server parameters from/ETC/MY.CNF and restart the service Method Two: 1. Stop the current MySQL or mariadb service; 2. Start the MySQL service with a command: # MYSQLD_SAF                E--skip-grant-tables--skip-networking 3. Start another session connection and use the MySQL or Mysqladmin client tool to modify its password as the root user of the empty password;        4.kill off the previous mysqld-safe and derivative mysqld services; 5. Start the service again normally; User authorization management (automatically created when the user does not exist): Grant statement:        GRANT Priv_type [(column_list)] [, Priv_type [(column_list]] ...        On [object_type] priv_level to User_specification [, user_specification] ...   [REQUIRE {NONE | ssl_option [[and] ssl_option] ...}]     [With With_option ...]  Priv_type:select, INSERT, UPDATE, DELETE, create, DROP, REFERENCES, INDEX, ALTER, CREATE temporary TABLES, LOCK TABLES, EXECUTE, create VIEW, SHOW view, create ROUTINE, ALTER ROUTINE, EVENT, TRIGGER, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, SHUTDOWN, FILE, SHOW DATABASES, PROCESS, SUPER object_type:table | FUNCTION | PROCEDURE Priv_level: * | *.* | db_name.* | Db_name.tbl_name | Tbl_name | Db_name.routine_name *: represents all databases; *. *: represents all Table objects in all databases; db_name.*: represents all Table objects in the specified database ; Db_name.tbl_name: Represents the specified Table object in the specified database; Tbl_name: represents the specified Table object in the database currently in use; db_name.ro  Utine_name: Represents the stored procedure object after the specified storage function in the specified database, usually with the object_type parameter; User_specification:user [identified by [PASSWORD] ' PASSWORD ' | Identified with Auth_plugin [as ' auth_string '] ssl_option:ssl | X509 | CIPHER ' CIPher ' | ISSUER ' ISSUER ' | SUBJECT ' SUBJECT ' with_option:grant option | Max_queries_per_hour Count | Max_updates_per_hour Count | Max_connections_per_hour Count |            Max_user_connections Count Example: MariaDB [mysql]> grant all privileges on hellodb.* to ' test ' @ '% ';            MariaDB [mysql]> Grant Select,update on hellodb.students to ' test ' @ '% ';            MariaDB [mysql]> Grant Select (Name,age,classid) on hellodb.students to ' test ' @ '% '; You can also create views on some base tables, and then grant user permission to view them: MariaDB [hellodb]> CREATE View stu_base as select Name,age,classid from St                udents;    MariaDB [hellodb]> Grant all on hellodb.stu_base to ' test ' ('% ');            REVOKE AUTHORIZATION/REVOKE authorization: REVOKE statement: REVOKE Priv_type [(column_list)] [, Priv_type [(column_list)]] ...        On [object_type] priv_level from user [, user] ...        REVOKE all privileges, GRANT OPTION from user [, user] ... Example: MariaDB [Mysql]> revoke Delete on hellodb.* from ' Test ' @ '% ';            MariaDB [mysql]> revoke all on hellodb.students from ' Test ' @ '% ';        MariaDB [mysql]> Revoke Select (AGE,CLASSID) on hellodb.students from ' Test ' @ '% ';        Note: When canceling an authorization that has already been made, the priv_level part specified by the REVOKE statement should remain in absolute agreement with the priv_level specified by the GRANT statement at the time of authorization, otherwise the operation of the cancellation authorization fails;            Example: If the TestDB database contains TB1 and TB2 two tables, MariaDB [testdb]> grant all on the testdb.* to ' test ' ('% ');            MariaDB [testdb]> revoke all on TESTDB.TB2 from ' Test ' @ '% ';            ERROR 1147 (42000): There is no such grant defined for the user ' test ' on host '% ' on table ' TB2 ' the proper way to retrieve the authorization:            MariaDB [testdb]> revoke all on testdb.* from ' Test ' @ '% ';            MariaDB [testdb]> Grant all on testdb.tb1 to ' test ' ('% '); At this point, the ' test ' @ '% ' user has all the permissions to the TB2 table in the TestDB database; View user's authorization: Show GRANTS statement: Show GRANTS [for user]

MYSQL/MARIADB user account management