MySQL创建用户

MySQL创建用户

MySQL如何创建新用户

操作的数据库：mysql

创建指令：

CREATE USER '用户名'@'范围' IDENTIFIED BY '密码';

范围包括：ip、localhost、%

ip即只能从指定地址登录

localhost 即只能从本机登录

% 即可以从任何地址登录

光创建用户，并没有什么作用，还需要授权操作表

GRANT privileges ON databasename.tablename TO '用户名'@'范围'

privileges可以是：

SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER,EXECUTE,INDEX,REFERENCES

ALTER ROUTINE,CREATE ROUTINE,CREATE TEMPORARY,SHOW VIEW,LOCK TABLES

ALL

databasename是库的名称，可以填入*表示所有

tablename是表的名称，可以填入*表示所有

有授权自然有撤销权限

REVOKE 权限 ON databasename.tablename FROM '用户名'@'范围'

实操：

MYSQL* mysql = new MYSQL();

//MYSQL mysql;//占用的是栈上的内存

MYSQL* pDB = mysql_init(mysql);

if (pDB == NULL) {

std::cout << "mysql_init failed!" << std::endl;

return -1;

}

pDB = mysql_real_connect(pDB, "localhost", "root", "FengPan12#$56", "mysql", 3306, NULL, 0);

std::cout << pDB << std::endl;

if (pDB) {

std::string sql = "CREATE USER 'hello'@'localhost' IDENTIFIED BY '123456'";

int ret = mysql_real_query(pDB, sql.c_str(), (unsigned long)sql.size());

if (ret != 0) {

std::cout << "mysql error:" << mysql_error(pDB) << std::endl;

}

MYSQL_RES* result = mysql_use_result(mysql);

if (result != NULL) {

unsigned nFields = mysql_num_fields(result);

my_ulonglong nRows = mysql_num_rows(result);

MYSQL_FIELD* fields = mysql_fetch_fields(result);

for (unsigned i = 0; i < nRows; i++) {

MYSQL_ROW row = mysql_fetch_row(result);

if (row != NULL) {

for (unsigned j = 0; j < nFields; j++) {

std::cout << "type:" << fields[j].type << " " << fields[j].name << ":" << row[j] << std::endl;

}

}

std::cout << "===================================================" << std::endl;

}

mysql_free_result(result);

}

sql = "GRANT ALL ON *.* TO 'hello'@'localhost'";

ret = mysql_real_query(pDB, sql.c_str(), (unsigned long)sql.size());

if (ret != 0) {

std::cout << "mysql error:" << mysql_error(pDB) << std::endl;

return -1;

}

result = mysql_use_result(mysql);

if (result != NULL) {

unsigned nFields = mysql_num_fields(result);

my_ulonglong nRows = mysql_num_rows(result);

MYSQL_FIELD* fields = mysql_fetch_fields(result);

for (unsigned i = 0; i < nRows; i++) {

MYSQL_ROW row = mysql_fetch_row(result);

if (row != NULL) {

for (unsigned j = 0; j < nFields; j++) {

std::cout << "type:" << fields[j].type << " " << fields[j].name << ":" << row[j] << std::endl;

}

}

std::cout << "===================================================" << std::endl;

}

mysql_free_result(result);

}

mysql_close(pDB);

}

delete mysql;

执行后可以看到新的用户hello

可以看到用户的权限