MySQL User, Privilege Management, C/C++ Connectivity and Usage

subscribers

user management

The mysql user management is located in the user table in the database mysql

mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| engine_cost               |
| event                     |
| func                      |
| general_log               |
| gtid_executed             |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| innodb_index_stats        |
| innodb_table_stats        |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| server_cost               |
| servers                   |
| slave_master_info         |
| slave_relay_log_info      |
| slave_worker_info         |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
31 rows in set (0.00 sec)

mysql> select * from user\G;
*************************** 1. row ***************************
                  Host: localhost ## Allowed Login Methods,localhostIndicates that only local logins are allowed
                  User: root
           Select_priv: Y
           Insert_priv: Y
           Update_priv: Y
           Delete_priv: Y
           Create_priv: Y
             Drop_priv: Y
           Reload_priv: Y
         Shutdown_priv: Y
          Process_priv: Y
             File_priv: Y
            Grant_priv: Y
       References_priv: Y
            Index_priv: Y
            Alter_priv: Y
          Show_db_priv: Y
            Super_priv: Y
 Create_tmp_table_priv: Y
      Lock_tables_priv: Y
          Execute_priv: Y
       Repl_slave_priv: Y
      Repl_client_priv: Y
      Create_view_priv: Y
        Show_view_priv: Y
   Create_routine_priv: Y
    Alter_routine_priv: Y
      Create_user_priv: Y
            Event_priv: Y
          Trigger_priv: Y
Create_tablespace_priv: Y
              ssl_type:
            ssl_cipher:
           x509_issuer:
          x509_subject:
         max_questions: 0
           max_updates: 0
       max_connections: 0
  max_user_connections: 0
                plugin: mysql_native_password
 authentication_string: *********************************2E128811 ## user password(go throughpasswordfunction encryption)
      password_expired: Y
 password_last_changed: 2024-02-28 22:08:19
     password_lifetime: NULL
        account_locked: N
        
### ....

Therefore, there is a simple and brutal user management method is to insert user data into the USER table, but you need to insert a lot of fields such as permissions, which is too troublesome; generally use the methods provided by MySQL for user management.

Query all users

mysql> select user,host from user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
|  | localhost |
|      | localhost |
| root          | localhost |
+---------------+-----------+
3 rows in set (0.00 sec)

View Current Users

mysql> select user();
+--------+
| user() |
+--------+
| root@  |
+--------+
1 row in set (0.00 sec)

View the current number of connections

mysql> show processlist;
+----+------+----------------------+------+---------+------+----------+------------------+
| Id | User | Host                 | db   | Command | Time | State    | Info             |
+----+------+----------------------+------+---------+------+----------+------------------+
|  3 | root | localhost            | NULL | Query   |    0 | starting | show processlist |
|  4 | chj  | *********            | NULL | Sleep   |    4 |          | NULL             |
+----+------+----------------------+------+---------+------+----------+------------------+
2 rows in set (0.00 sec)

Create User

grammatical

create user 'user ID'@'ip' identified by 'cryptographic';
flush privileges; ## refresh access

MySQL doesn't record operations involving passwords.

Operations involving privileges need to be flushed ## flush privileges; ## flush privileges; ## flush privileges; ## flush privileges; ## flush privileges.

• Local login users only

create user 'user ID'@'localhost' identified by 'cryptographic';

• Only specific ip login (generally intranet, in addition to learning to use the general is not recommended for public network)

create user 'user ID'@'ip' identified by 'cryptographic';

• All users can log in (dangerous, only for learning, general users don't have fixed ip)

create user 'user ID'@'%' identified by 'cryptographic';

If you encounter SSL mismatch problem, you can try to add skip_ssl in, note that the position is not right may not work.

Delete User

Syntax.

drop user 'username'@'hostname'

Change Password Rules

article citation

View Rules/Policies

mysql> SHOW VARIABLES LIKE 'validate_password%';
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password_check_user_name    | OFF    |
| validate_password_dictionary_file    |        |
| validate_password_length             | 8      |
| validate_password_mixed_case_count   | 1      |
| validate_password_number_count       | 1      |
| validate_password_policy             | MEDIUM |
| validate_password_special_char_count | 1      |
+--------------------------------------+--------+
7 rows in set (0.00 sec)

Explanation of the rules

causality	default value	Property Description
validate_password_check_user_name	OFF	When set to ON, the password can be set to the current user name.
validate_password_dictionary_file		Pathname of the dictionary file used to check passwords, default is empty
validate_password_length	8	The minimum length of the password, i.e. the password length must be greater than or equal to 8
validate_password_mixed_case_count	1	If the password policy is medium or stronger, validate_password requires the minimum number of lowercase and uppercase characters that the password has. For the given value the password must have that many lowercase characters and that many uppercase characters.
validate_password_number_count	1	The number of digits the password must contain
validate_password_policy	MEDIUM	The right-aligned password strength check level, which can be specified using the numeric values 0, 1, 2 or the corresponding symbolic values LOW, MEDIUM, STRONG.0/LOW: Only the length is checked. 1/MEDIUM: Checks for length, numbers, case, special characters. 2/STRONG: Checks for length, numbers, case, special characters, dictionary files.
validate_password_special_char_count	1	Number of special characters the password must contain

temporary setup

You can change the password rules before changing the password

## Security strength, defaults to Medium, i.e., 1, and requires that it must contain a number, symbol, or small or medium name with at least 8 digits.
set global validate_password_policy=0; ## Set to weak password strength.

set global validate_password_policy=0; ## Set to weak password strength; ## set global validate_password_length=1; ## Set the minimum password length.

persistence setting

Add the following to the\etc\center

validate_password_policy=LOW
validate_password_length=6
validate_password_number_count=0
validate_password_special_char_count=0
validate_password_mixed_case_count=0

change your password

• Reform yourself.

set password=password('new password');

• root to specified user

set password for 'username'@'hostname'=password('new password');

• Root operates directly on the user table

update user set authentication_string=password('111') where user='chj';
flush privileges;

maybe

ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';

flush privileges; ## Refresh privileges

scope of one's jurisdiction

List of permissions provided by the database

View Permissions

mysql> show grants for 'chj'@'%';
+--------------------------------------------------+
| Grants for chj@%                                 |
+--------------------------------------------------+
| GRANT USAGE ON *.* TO 'chj'@'%'                  |
| GRANT ALL PRIVILEGES ON `test_db`.* TO 'chj'@'%' |
+--------------------------------------------------+
2 rows in set (0.00 sec)

license

Syntax.

grant permission list on library. Object name to 'username'@'login location' [identified by 'password'] ;
## identified by: Optional. If the user exists, change the password while granting permissions, if the user does not exist, create the user.
## Passwords are usually ignored, because they are usually created first and then granted, and the password is already set when the user is created.

Case in point.

grant select on ...

grant select, delete, create on ....

grant all on ... -- means that the user is granted all privileges on the object.

grant [privileges] on library. * to 'username'@'login location'
## Library. * : represents all data objects (tables, views, stored procedures, etc.) in a database.

grant [privileges] on *. * to 'username'@'login location'
## *. * represents all objects (tables, views, stored procedures, etc.) in all databases in this system

### View permissions

``
mysql> show grants for 'chj'@'%'.
+--------------------------------------------------+
| Grants for chj@% |
+--------------------------------------------------+
| GRANT USAGE ON *. * TO 'chj'@'%' | GRANT ALL PRIVILEGE ON *.
| GRANT ALL PRIVILEGES ON `test_db`. * TO 'chj'@'%' | | GRANT ALL PRIVILEGES ON `test_db`.
+--------------------------------------------------+
2 rows in set (0.00 sec)
``

Recovery of user rights

Case in point.

mysql> revoke delete on test_db.* from 'chj'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for 'chj'@'%'\G;
*************************** 1. row ***************************
Grants for chj@%: GRANT USAGE ON *.* TO 'chj'@'%'
*************************** 2. row ***************************
Grants for chj@%: GRANT SELECT, INSERT, UPDATE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `test_db`.* TO 'chj'@'%'
2 rows in set (0.00 sec)

Permission List.

SELECT, INSERT, UPDATE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER, DELETE

Connecting with C

mysql operations are thread-safe (transactions)

Getting to know the mysql API by the API route.

Library Installation

If you install mysql through yum or other installation methods, the installation process mysql development package is generally installed, so this way of installing mysql is recommended.

After installation, the development header file directory is located at/usr/include/mysql, the library is located in the/usr/lib64/mysql

If it is not found, it may not be installed, so you can install it manually by commanding.

yum install -y mysql-community-devel ## development kit

You can also download the mysql connection tool separately.MySQL :: Download MySQL Connector/C++ (Archived Versions)Note, if you can find the corresponding version is the best, but now the old version of the official website may not show up, this does not matter, mysql on the new version is able to do!backwards and forwards compatibleYou can download the recommended version. The disadvantage of this method is that it is more cumbersome and may cause compatibility problems, so it is not recommended.

Download the package, the most important is the include folder and lib folder, there are two directories can be C / C + + connection to the database and development. (Need dynamic and static library series knowledge)

linux generic: generic version

C API

MySQL Official C Interface Statement

MySQL C Structures

mysql_init

MySQL :: MySQL 5.7 C API Developer Guide :: 5.4.54 mysql_real_connect()

MYSQL* mysql_init(MYSQL *mysql);

As the name suggests, it's a good idea toMYSQL* object is initialized.

mysql_real_connect

MySQL :: MySQL 5.7 C API Developer Guide :: 5.4.54 mysql_real_connect()

 MYSQL *mysql_real_connect(
         MYSQL *mysql, // Output parameter: return mysql access handle on success, NULL on error; const char *host, // mysqld's ip address.
         const char *host, // mysqld ip
         const char *user, // login username.
         const char *passwd, // the password for the login.
         const char *db, // the database to connect to
         unsigned int port, // mysqld port number
         const char *unix_socket, // interdomain socket (similar to interprocess pipeline communication)
         unsigned long clientflag); // Bitmap to enable some features; generally use the default, which is 0.

Caution.

You need to initialize first with mysql_initMYSQL*Object.

return value

The MYSQL* connection handler is used if the connection is successful, or NULL if the connection is unsuccessful.For successful connections, the return value is the same as the value of the first parameter.

mysql_close

MySQL :: MySQL 5.7 C API Developer Guide :: 5.4.5 mysql_close()

void mysql_close(MYSQL *mysql);

cloture

mysql_query

MySQL :: MySQL 5.7 C API Developer Guide :: 5.4.53 mysql_query()

int mysql_query(
		 	MYSQL *mysql,
      const char *stmt_str);

Caution.

The official documentation describes that you don't need to add ';' and '\G' to the end of the sql statement.

Return Value.

Success returns 0, Failure returns an error code.

Simulate the implementation of a simple MySQL client.

#include<iostream>
#include<mysql/>
#include<string>
#include<cassert>
#include<chrono>
#include<thread>

/*
MYSQL *mysql_real_connect(
        MYSQL *mysql, // Output Type Parameters:Success returnsmysqlAccess handle,Error returnNULL
        const char *host, // mysqld(used form a nominal expression)ip
        const char *user, // 登录(used form a nominal expression)用户名
        const char *passwd, // login password
        const char *db, // 连接(used form a nominal expression)数据库
        unsigned int port, // mysqldport number
        const char *unix_socket, // interdomain socket (computing)(Pipeline-like communication between processes)
        unsigned long clientflag); // bitmap,Enabling some features;Usually just use the default.,assume (office)0
 */

const std::string host = "127.0.0.1";
const std::string user = "chj";
const std::string passwd = "123456";
const std::string db = "test_db";
const unsigned int port = 3306;
//const std::string socket = ;
const unsigned long clientflag = 0;


int main(){
  std::cout<<"mysql client version :" << mysql_get_client_info()<<std::endl;
  MYSQL* mysql = mysql_init(nullptr);
  if(mysql == nullptr){
    std::cerr<<" mysql init error "<<std::endl;
    return 1;
  }
  if(mysql_real_connect(mysql,host.c_str(),user.c_str(),passwd.c_str(),db.c_str(),port,nullptr,clientflag)){
    std::cout<<"conect success" <<std::endl;
  }
  else{
    std::cerr<<"mysql connect error"<<std::endl;
    return 2;
  }

  std::string sql;
  while(true){
    std::cout<<"mysql >>> ";
    if(!std::getline(std::cin,sql)) break;
    if(sql == "quit" || sql == "exit") break;
    int n = mysql_query(mysql,sql.c_str());
    if(n == 0){
      std::cout<<sql<<" sucess"<<std::endl;
    }
    else{
      std::cerr<<sql<<" failed, "<<n<<std::endl;
    }

  }

  mysql_close(mysql);

  return 0;
}

mysql_set_character_set

MySQL :: MySQL 5.7 C API Developer Guide :: 5.4.69 mysql_set_character_set()

int mysql_set_character_set(
					MYSQL *mysql,
          const char *csname);	//cs == character set

Question: Generally speaking, mysqld is also set to utf8, and C/C++ code is also utf8, so why the Chinese may appear garbled?

Ans: Both ends are fine, then the problem can only come from the link, so you need to set the character set of the link.

After establishing a good link, there is no problem to get English, if you get Chinese is a mess:
set uplink (on a website)The default character set is utf8, and the original default is latin1.
mysql_set_character_set(myfd, "utf8");

mysql_store_result

MySQL :: MySQL 5.7 C API Developer Guide :: 5.4.77 mysql_store_result()

MYSQL_RES *mysql_store_result(MYSQL *mysql);