db_修改過（用戶 角色 權限）：1、新增用戶時候id 改為最大id值加一，之前用的select查看出來的記錄數加一，刪除后再增加會出錯；2、刪除用戶時候，若該用戶創建過其他用戶（不能改此用戶名、不能改此用戶角色，不能刪除此用戶）；3、（下一步加入）增加時候，若改用戶名存在（給出用戶存在的提示信息）；4、修改了用戶的表字段信息，新增了認證方式的字段；5、用戶有三種認證方式，當選擇口令認證的時候，syn_sn_有輸入信息，當選擇KEY認證的時候，dyn_pass_sn_字段有輸入信息

/********************************************* 編譯命令：gcc db.c -lmysqlclient -o db* 執行命令：./db* ******************************************/ #include <stdio.h> #include <stdlib.h> #include <string.h> #include <time.h> #include "mysql/mysql.h"MYSQL *g_conn;//mysql 鏈接 MYSQL_RES *g_res;//mysql 記錄集 MYSQL_ROW g_row;//字符串數組，mysql 記錄行const char *g_host_name = "localhost"; const char *g_user_name = "root"; const char *g_password = "asdfgh"; const char *g_db_name = "test"; const unsigned int g_db_port = 3306;#define MAX_BUF_SIZE 1024 //緩沖區最大字節數 char sql[MAX_BUF_SIZE]; char Time[MAX_BUF_SIZE];int iNum_rows = 0;//mysql語句執行結果返回行數賦初值 int flag = 0;//管理員權限開關 int i = 1;//系統運行開關 //登錄使用的結構體 struct Login {char name[24];char password[20]; }login; //認證的方式 口令認證、KEY認證、第三方認證 enum authType{Passauth=0,Keyauth=1,Thpaauth=2}; //操作使用的結構體 struct Operation { char tables[24]; char name[24]; char passwd[20]; char role[24]; char remark[20]; char authtype[255]; //可以根據變量的類型 做認證操作char passauth[255];char keyauth[255]; }ope; /***************************************************** time : 20180622* addby : swj* function :print_mysql_error() 打印錯誤信息* ******************************************************/ void print_mysql_error(const char *msg) {if(msg)printf("%s: %s\n",msg,mysql_error(g_conn));elseputs(mysql_error(g_conn)); } /***************************************************** time : 20180622* addby : swj* function :executesql() 執行sql語句，成功返回0，失敗返回-1 ******************************************************/ int executesql(const char * sql) {if(mysql_real_query(g_conn,sql,strlen(sql)))return -1;return 0; } /***************************************************** time : 20180622* addby : swj* function :init_mysql() 初始化鏈接******************************************************/ int init_mysql() {//init the database connection g_conn = mysql_init(NULL);//connection the database if(!mysql_real_connect(g_conn,g_host_name,g_user_name,g_password,g_db_name,g_db_port,NULL,0))return -1;//鏈接失敗if(executesql("set names utf8"))return -1;return 0; //返回成功 }/***************************************************** time : 20180622* addby : swj* function :create_database() 選擇數據庫 沒有的時候 創建數據;有的時候 進去數據庫 * ******************************************************/ void create_database() {sprintf(sql,"use workProject");if(executesql(sql) == -1){puts("create database");executesql("create database workProject;");print_mysql_error(NULL);puts("choice database");executesql("use workProject;");print_mysql_error(NULL);puts("!!!Initialize the success!!!");}else{executesql("use workProject;");print_mysql_error(NULL);}} /***************************************************** time : 20180622* addby : swj* function :create_table() 創建表 * ******************************************************/ void create_table() {sprintf(sql,"show tables;");executesql(sql);g_res = mysql_store_result(g_conn);// printf("g_res = %d\n",g_res);iNum_rows = mysql_num_rows(g_res);// printf("iNum_rows = %d\n",iNum_rows);if(iNum_rows == 0){puts("create users table");executesql("create table users(id_ int(11) unsigned primary key auto_increment,name_ char(255) not null unique,password_ char(32) not null,create_time_ datetime,creator_id_ int(11) unsigned,auth_type_ int(11) not null,dyn_sn_ char(10),dyn_pass_sn_ text,remark_ varchar(200),foreign key(creator_id_) references users(id_));"); puts("create roles table");executesql("create table roles(id_ int(11) unsigned primary key auto_increment,name_ char(255) not null unique,remark_ varchar(200));");puts("create authoritys table");executesql("create table authoritys(id_ int(11) unsigned primary key auto_increment,name_ varchar(24) not null unique,remark_ varchar(200));");puts("create roleAuthority table");executesql("create table roleAuthority(role_id_ int(11) unsigned,authority_id_ int(11) unsigned,primary key(role_id_,authority_id_),foreign key(role_id_ ) references roles(id_ ),foreign key(authority_id_) references authoritys(id_));"); puts("create userRole table");executesql("create table userRole(user_id_ int(11) unsigned,role_id_ int(11) unsigned,primary key(user_id_,role_id_),foreign key(user_id_) references users(id_),foreign key(role_id_ ) references roles(id_));");}mysql_free_result(g_res);//釋放結果集 } /***************************************************** time : 20180622* addby : swj* function :init_administrator() 初始化管理員賬戶* 管理員用戶名：root 密碼：root* ******************************************************/ void init_administrator() {//查詢users表sprintf(sql,"select * from users where id_='1' and name_='root';");executesql(sql);g_res = mysql_store_result(g_conn);iNum_rows = mysql_num_rows(g_res);if(iNum_rows == 0){puts("Init Administrtor User");//插入管理員用戶printf("Passauth = %d\n",Passauth);sprintf(sql,"insert into users values(1,'root','root','2017-08-18 12:21:11',1,0,'','','0:VIP 1:local pwd 2:local cert');"); //0:VIP 1:local pwd 2:local cert 4:2-fa/ cert+pw 5:2-fa / dyn + pw');executesql(sql);}mysql_free_result(g_res); //釋放結果集//查詢roles表sprintf(sql,"select * from roles;");executesql(sql);g_res = mysql_store_result(g_conn);iNum_rows = mysql_num_rows(g_res);if(iNum_rows < 3){puts("Init System Role");//插入系統角色sprintf(sql,"insert into roles values(1,'ADMINISTRTOR','administrtor');");executesql(sql);sprintf(sql,"insert into roles values(2,'USER','user');");executesql(sql);sprintf(sql,"insert into roles values(3,'OTHER','other');");executesql(sql);}mysql_free_result(g_res); //釋放結果集//查詢authoritys表 sprintf(sql,"select * from authoritys;"); executesql(sql); g_res = mysql_store_result(g_conn); iNum_rows = mysql_num_rows(g_res); if(iNum_rows < 3) { puts("Init System Authority"); //插入系統權限 sprintf(sql,"insert into authoritys values(1,'QUERY','query');"); executesql(sql); sprintf(sql,"insert into authoritys values(2,'ADD','addmsg');"); executesql(sql); sprintf(sql,"insert into authoritys values(3,'ADD&QUERY','query and addmsg');"); executesql(sql); sprintf(sql,"insert into authoritys values(4,'DEL&ALT','delete and alter');"); executesql(sql); sprintf(sql,"insert into authoritys values(5,'QUERY&DEL&ALT','query and delete and alter');"); executesql(sql); sprintf(sql,"insert into authoritys values(6,'ADD&DEL&ALT','addmsg and delete and alter');"); executesql(sql); sprintf(sql,"insert into authoritys values(7,'QUERY&ADD&DEL&ALT','query and addmsg and delete and alter');"); executesql(sql); } mysql_free_result(g_res); // 釋放結果集 //查詢roleAuthority表 sprintf(sql,"select * from roleAuthority;"); executesql(sql); g_res = mysql_store_result(g_conn); iNum_rows = mysql_num_rows(g_res); if(iNum_rows < 3) { puts("Init RoleAuthority"); //插入角色權限 sprintf(sql,"insert into roleAuthority values(1,7);"); executesql(sql); sprintf(sql,"insert into roleAuthority values(2,3);"); executesql(sql); sprintf(sql,"insert into roleAuthority values(3,1);"); executesql(sql); } mysql_free_result(g_res); // 釋放結果集 //查詢userRole表sprintf(sql,"select * from userRole where user_id_='1' and role_id_='1';");executesql(sql);g_res = mysql_store_result(g_conn);iNum_rows = mysql_num_rows(g_res);if(iNum_rows == 0){puts("Init User Role");//插入管理員用戶sprintf(sql,"insert into userRole values(1,1);");executesql(sql);}mysql_free_result(g_res); // 釋放結果集 } /***************************************************** time : 20180624* addby : swj* function :user_login() 用戶登錄* 管理員用戶名：root 密碼：root* ******************************************************/ void user_login() {puts("Init success,please put any key to continue!");while(1){while((getchar()) != '\n');system("clear");puts("!!!login system!!!");//輸入登錄的用戶名和密碼printf("Name：");scanf("%s",login.name);printf("Passwd：");scanf("%s",login.password);//在數據庫中查詢，可查詢到信息即表明users表中有賬號信息，登錄成功sprintf(sql,"select * from users where name_='%s' and password_='%s';",login.name,login.password); executesql(sql);g_res = mysql_store_result(g_conn);iNum_rows = mysql_num_rows(g_res);if(iNum_rows != 0){puts("!!! Login Success !!! ");while ((getchar()) != '\n');getchar();break;}else{puts("!!!Login Failed!!! Check name or password!");while ((getchar()) != '\n');}}mysql_free_result(g_res); // 釋放結果集 }/***************************************************** time : 20180625* addby : swj* function :judge() 權限判定函數* ******************************************************/ int judge(int authority) {int auth = 0; //通過當前登錄的用戶名字獲取用戶id sprintf(sql,"select id_ from users where name_='%s';",login.name); executesql(sql); g_res = mysql_store_result(g_conn); iNum_rows = mysql_num_rows(g_res); int iNum_fields = mysql_num_fields(g_res); while((g_row=mysql_fetch_row(g_res))){ //通過當前登錄用戶的id查詢這個用戶的角色id sprintf(sql,"select role_id_ from userRole where user_id_='%s';",g_row[0]); } executesql(sql); g_res = mysql_store_result(g_conn); iNum_rows = mysql_num_rows(g_res); iNum_fields = mysql_num_fields(g_res); while((g_row=mysql_fetch_row(g_res))){ //通過當前用戶的角色id查詢該用戶的權限id sprintf(sql,"select authority_id_ from roleAuthority where role_id_='%s';",g_row[0]); } executesql(sql); g_res = mysql_store_result(g_conn); iNum_rows = mysql_num_rows(g_res); iNum_fields = mysql_num_fields(g_res); while((g_row=mysql_fetch_row(g_res))){ //通過當前用戶的權限id查詢權限級別 sprintf(sql,"select name_ from authoritys where id_='%s';",g_row[0]); } executesql(sql); g_res = mysql_store_result(g_conn); iNum_rows = mysql_num_rows(g_res); iNum_fields = mysql_num_fields(g_res); while((g_row=mysql_fetch_row(g_res))){ if(strcmp(g_row[0],"QUERY") == 0) auth = 1; if(strcmp(g_row[0],"ADD") == 0) auth = 2; if(strcmp(g_row[0],"ADD&QUERY") == 0) auth = 3; if(strcmp(g_row[0],"DEL&ALT") == 0) auth = 4; if(strcmp(g_row[0],"QUERY&DEL&ALT") == 0) auth = 5; if(strcmp(g_row[0],"ADD&DEL&ALT") == 0) auth = 6; if(strcmp(g_row[0],"QUERY&ADD&DEL&ALT") == 0) auth = 7; if(auth >= 4) flag = 1;//管理員權限開關 //判斷該用戶權限級別能否執行將要進行的操作 if(auth < authority) { mysql_free_result(g_res); // 釋放結果集 return 0; } else { mysql_free_result(g_res); // 釋放結果集 return 1; } } } /***************************************************** time : 20180625* addby : swj* function :display() 顯示所有用戶及用戶角色函數* ******************************************************/ void display() {//通過權限判定函數的返回值決定是否可以進行顯示所有用戶操作，other用戶不可使用此功能//當時other用戶登錄的時候 judge中的auth會被賦值為1 調用的時候形參為2 1<2 return0//判斷條件judge(2) == 0 成立 所以判定此時為other 用戶登錄if(judge(2) == 0){puts("!!!Insufficient permissions!!! ");while ((getchar()) != '\n');getchar();//權限不夠，退出函數return ;}//可以執行 //查詢users表 sprintf(sql,"select * from users;"); executesql(sql); g_res = mysql_store_result(g_conn); // 從服務器傳送結果集至本地，mysql_use_result直接使用服務器上的記錄集 iNum_rows = mysql_num_rows(g_res); // 得到記錄的行數 int iNum_fields = mysql_num_fields(g_res); // 得到記錄的列數 system("clear"); puts("!!! users table !!! \n"); puts("id_ | name_ |password_| create_time_ |creator_id_ | auth_type_ |dyn_sn_| dyn_pass_sn_ |remark_ "); while((g_row=mysql_fetch_row(g_res))) // 打印結果集printf("%s\t%s\t%s\t%s\t%s\t%s\t%s\t%s\t%s\n",g_row[0],g_row[1],g_row[2],g_row[3],g_row[4],g_row[5],g_row[6],g_row[7],g_row[8]); //查詢roles表 sprintf(sql,"select * from roles;"); executesql(sql); g_res = mysql_store_result(g_conn); iNum_rows = mysql_num_rows(g_res); // 得到記錄的行數 iNum_fields = mysql_num_fields(g_res); // 得到記錄的列數 puts("\n\n!!! roles table !!! \n"); puts(" id_ | name_ | remark_ "); while((g_row=mysql_fetch_row(g_res))) printf("%s\t%s\t\t%s\n",g_row[0],g_row[1],g_row[2]); //查詢userRole表 sprintf(sql,"select * from userRole;"); executesql(sql); g_res = mysql_store_result(g_conn); iNum_rows = mysql_num_rows(g_res); // 得到記錄的行數 iNum_fields = mysql_num_fields(g_res); // 得到記錄的列數 puts("\n\n!!! userRole table !!! \n"); puts(" user_id_ | role_id_ "); while((g_row=mysql_fetch_row(g_res))) printf("\t%s\t%s\n",g_row[0],g_row[1]); mysql_free_result(g_res); while ((getchar()) != '\n'); getchar(); } /***************************************************** time : 20180625* addby : swj* function :query_msg() 查詢函數 條件查詢 選擇查找的表 輸入要查找的名字* ******************************************************/ void query_msg() {while(1){int choice; system("clear"); puts("!!! Query_msg !!! "); puts("!!! choice table !!! "); puts("!!! 1: users !!! "); puts("!!! 2: roles !!! "); puts("!!! 3: userRole !!! "); puts("!!! 0: Return !!! "); scanf("%d",&choice); //通過權限判定函數的返回值決定是否可以進行顯示所有用戶操作，other用戶不可使用此功能if(judge(2) == 0){puts("!!!Insufficient permissions!!! ");while ((getchar()) != '\n');getchar();//權限不夠，退出函數return ;}switch(choice){case 1: sprintf(ope.tables,"users"); system("clear"); puts("!!! enter name !!! "); printf("Name：");scanf("%s",ope.name); //在指定表中查詢用戶名相關信息 sprintf(sql,"select * from %s where name_='%s';",ope.tables,ope.name); executesql(sql); g_res = mysql_store_result(g_conn); iNum_rows = mysql_num_rows(g_res); // 得到記錄的行數 system("clear"); if(iNum_rows == 0) puts("No such person!"); else { int iNum_fields = mysql_num_fields(g_res); // 得到記錄的列數 puts("id_ | name_ |password_| create_time_ |creator_id_ | auth_type_ |dyn_sn_| dyn_pass_sn_ |remark_ "); while((g_row=mysql_fetch_row(g_res))) // 打印結果集printf("%s\t%s\t%s\t%s\t%s\t%s\t%s\t%s\t%s\n",g_row[0],g_row[1],g_row[2],g_row[3],g_row[4],g_row[5],g_row[6],g_row[7],g_row[8]); } mysql_free_result(g_res); while ((getchar()) != '\n'); getchar(); break; case 2: sprintf(ope.tables,"roles"); system("clear"); //查詢角色表 sprintf(sql,"select * from %s;",ope.tables); executesql(sql); g_res = mysql_store_result(g_conn); iNum_rows = mysql_num_rows(g_res); // 得到記錄的行數 system("clear"); if(iNum_rows == 0) puts("No such person!"); else { int iNum_fields = mysql_num_fields(g_res); // 得到記錄的列數 puts(" id_ | name_ | remark_ "); while((g_row=mysql_fetch_row(g_res))) printf("%s\t%s\t\t%s\n",g_row[0],g_row[1],g_row[2]); } mysql_free_result(g_res); while ((getchar()) != '\n'); getchar(); break; case 3: sprintf(ope.tables,"userRole"); system("clear"); //查詢用戶角色表 sprintf(sql,"select * from %s;",ope.tables); executesql(sql); g_res = mysql_store_result(g_conn); iNum_rows = mysql_num_rows(g_res); // 得到記錄的行數 system("clear"); if(iNum_rows == 0) puts("No such person!"); else { int iNum_fields = mysql_num_fields(g_res); // 得到記錄的列數 puts(" user_id_ | role_id_ "); while((g_row=mysql_fetch_row(g_res))) printf("\t%s\t%s\n",g_row[0],g_row[1]); } mysql_free_result(g_res); while ((getchar()) != '\n'); getchar(); break; case 0: return ; default : puts("!!! Please enter right choice !!! "); while ((getchar()) != '\n'); getchar(); break; } } } /***************************************************** time : 20180625* addby : swj* function :add_msg() 添加函數* ******************************************************/ void add_msg() {int o,op;//根據switch選擇 把枚舉類型的值賦值給該變量 將值插入數據庫 int auty; char NEWID[20];char ID[20]; //通過權限判定函數的返回值決定是否可以進行添加用戶操作 if(judge(2) == 0){ puts("!!!Insufficient permissions!!! "); while ((getchar()) != '\n'); getchar(); //權限不夠，退出函數 return ; } //可以執行 system("clear"); puts("!!! Add_user !!! "); //根據當前已有用戶的行數判斷，新建的用戶id應為Id_中最大值+1 sprintf(sql,"select (@id_:=id_+1) as idnum_,users.* from users where id_ = (select max(id_) from users);"); //sprintf(sql,"select * from users where id_ = (select max(id_) from users);"); executesql(sql); g_res = mysql_store_result(g_conn); iNum_rows = mysql_num_rows(g_res); // 得到記錄的行數 while((g_row = mysql_fetch_row(g_res))){sprintf(NEWID,"%s",g_row[0]);}//printf("NEWID=%s\n",NEWID);//輸入賬戶和密碼 printf(" Name：");scanf("%s",ope.name); printf("Password：");scanf("%s",ope.passwd); //獲取系統時間，作為創建時間 time_t temp; struct tm *t; time(&temp); t = localtime(&temp); sprintf(Time,"%d-%d-%d %d:%d:%d",t->tm_year+1900,t->tm_mon+1,t->tm_mday,t->tm_hour,t->tm_min,t->tm_sec); //通過當前登錄的用戶名字獲取用戶id，作為創建者id使用 sprintf(sql,"select id_ from users where name_='%s';",login.name); executesql(sql); g_res = mysql_store_result(g_conn); iNum_rows = mysql_num_rows(g_res); int iNum_fields = mysql_num_fields(g_res); while((g_row=mysql_fetch_row(g_res))){ sprintf(ID,"%s",g_row[0]); }//輸入用戶認證方式 while(1){ system("clear"); puts("!!! AUTHENTICATION TYPE !!! "); puts("!!! 1: PASSWORD AUTH !!! "); puts("!!! 2: KEY AUTH !!! "); puts("!!! 3: THIRD PARTY AUTH !!! ");printf("Choice the authentication type of user %s：",ope.name);scanf("%d",&op); switch(op){ case 1://printf("Passauth = %d\n",Passauth); auty = Passauth;sprintf(ope.authtype,"Passauth"); sprintf(ope.passauth,"INSERT口令認證需要的口令"); sprintf(ope.keyauth," ");break; case 2: sprintf(ope.authtype,"Keyauth");auty = Keyauth; sprintf(ope.passauth," "); sprintf(ope.keyauth,"KEY認證需要的文件"); break; case 3: auty = Thpaauth; sprintf(ope.authtype,"Thpaauth"); sprintf(ope.passauth," "); sprintf(ope.keyauth," ");break; default : puts("!!! enter right choice !!! "); while ((getchar()) != '\n'); getchar(); } break; } //printf("ope.authtype=%s\n ope.passauth=%s\n ope.passauth=%s\n",ope.authtype,ope.passauth,ope.keyauth);//備注 printf(" Remark：");scanf("%s",ope.remark);//向用戶表中插入一個新的用戶的信息 sprintf(sql,"insert into users values(%s,'%s','%s','%s',%s,%d,'%s','%s','%s');",NEWID,ope.name,ope.passwd,Time,ID,auty,ope.passauth,ope.keyauth,ope.remark); executesql(sql); //管理員用戶設定權限 if(flag) { while(1){ system("clear"); puts("!!! ROLE !!! "); puts("!!! 1: USER !!! "); puts("!!! 2:OTHER !!! "); printf("Choice the Role of user %s：",ope.name);scanf("%d",&o); switch(o){ case 1: sprintf(ope.role,"USER"); break; case 2: sprintf(ope.role,"OTHER"); break; default : puts("!!! enter right choice !!! "); while ((getchar()) != '\n'); getchar(); } break; } //通過角色名找到角色id sprintf(sql,"select id_ from roles where name_='%s';",ope.role); executesql(sql); g_res = mysql_store_result(g_conn); iNum_rows = mysql_num_rows(g_res); iNum_fields = mysql_num_fields(g_res); while((g_row=mysql_fetch_row(g_res))){ //將用戶id和角色id寫入用戶角色表 sprintf(sql,"insert into userRole values(%s,%s);",NEWID,g_row[0]); executesql(sql); } } //如果沒有管理員權限，默認添加的用戶角色為other else{ sprintf(sql,"insert into userRole values(%s,3);",NEWID); executesql(sql); } flag = 0;//管理員權限開關 puts("!!! success !!! "); while ((getchar()) != '\n'); getchar(); } /***************************************************** time : 20180625* addby : swj* function :del_alt_msg() 刪改函數* ******************************************************/ void del_alt_msg() {int o,op; char p; char ID[20]; //通過權限判定函數的返回值決定是否可以進行刪改用戶操作 if(judge(4) == 0){ puts("!!!Insufficient permissions!!! "); while ((getchar()) != '\n'); getchar(); //權限不夠，退出函數 return ; } //可以執行 system("clear"); puts("!!! del_alt_msg !!! "); printf(" Name：");scanf("%s",ope.name); printf("Password：");scanf("%s",ope.passwd); //判斷要進行刪改的用戶是不是管理員用戶，禁止對管理員用戶進行刪改操作 if(strcmp(ope.name,"root") == 0) { puts("Administrator user deletion is prohibited"); while ((getchar()) != '\n'); getchar(); return; } //通過用戶名和密碼查看用戶表中是否有該用戶 sprintf(sql,"select id_ from users where name_='%s' and password_='%s';",ope.name,ope.passwd); executesql(sql); g_res = mysql_store_result(g_conn); iNum_rows = mysql_num_rows(g_res); // 得到記錄的行數 int iNum_fields = mysql_num_fields(g_res); //將該用戶id取出來備用 while((g_row=mysql_fetch_row(g_res))){ sprintf(ID,"%s",g_row[0]); }//沒有查到 if(iNum_rows == 0) { puts("No such person!"); puts("Please check the name or password enterd!"); puts("!!! enter right choice !!! "); while ((getchar()) != '\n'); getchar(); } //進入刪改選擇 else{system("clear"); puts("!!! del_alt_msg !!! "); puts("!!! 1:change name !!! "); puts("!!! 2:change passwd !!! "); puts("!!! 3:change role !!! "); puts("!!! 4:change remark !!! "); puts("!!! 5:delete user !!! "); printf("!!! choice： !");scanf("%d",&o); sprintf(sql,"select id_ from users where creator_id_=%s;",ID);executesql(sql);g_res = mysql_store_result(g_conn);iNum_rows = mysql_num_rows(g_res); // 得到記錄的行數switch(o){case 1://當用戶被引用的時候 提示名字不能被修改（其創建了其他用戶）if(iNum_rows != 0){puts("Be quoted!It created other users!");puts("Name can`tbe chenge !");}else{system("clear"); puts("!!! del_alt_msg !!! "); printf("!!! enter name: ");scanf("%s",ope.name);//更新用戶名sprintf(sql,"update users set name_='%s' where id_=%s",ope.name,ID);executesql(sql);}break;case 2: system("clear"); puts("!!! del_alt_msg !!! "); printf("!!! enter password: ");scanf("%s",ope.passwd); //更新密碼 sprintf(sql,"update users set password_='%s' where id_=%s;",ope.passwd,ID); executesql(sql); break;case 3: //當用戶被引用的時候 提示角色不能被修改（其創建了其他用戶）if(iNum_rows != 0){puts("Be quoted!It created other users!");puts("Role can`tbe chenge !");}else{system("clear"); puts("!!! del_alt_msg !!! "); puts("!!! 1.USER !!! "); puts("!!! 2.OTHER !!! "); printf("!!! choice： !");scanf("%d",&op); switch(op) { case 1: //設置角色為用戶 sprintf(sql,"update userRole set role_id_=2 where user_id_=%s;",ID); executesql(sql); break; case 2: //設置角色為其他人 sprintf(sql,"update userRole set role_id_=3 where user_id_=%s;",ID); executesql(sql); break; default : puts("!!! enter right choice !!! "); while ((getchar()) != '\n'); getchar(); }} break; case 4: system("clear"); puts("!!! del_alt_msg !!! "); printf("!!! enter remark: ");scanf("%s",ope.remark); //更新備注 sprintf(sql,"update users set remark_='%s' where id_=%s;",ope.remark,ID); executesql(sql); break; case 5: system("clear"); puts("!!! del_alt_msg !!! "); printf("!!! sure delete? (Y/N):");scanf("%s",&p); switch(p) { case 'Y': case 'y': //當用戶被引用的時候 提示不能被刪除（其創建了其他用戶） if(iNum_rows != 0){puts("Be quoted!It created other users!");puts("It can`t be delete!");}else{//需要先刪除用戶角色表當中的信息，才可刪除用戶表中的信息 sprintf(sql,"delete from userRole where user_id_=%s;",ID); executesql(sql); sprintf(sql,"delete from users where id_=%s;",ID); executesql(sql); }break; case 'N': case 'n': return; } break;default : puts("!!! enter right choice !!! "); while ((getchar()) != '\n'); getchar(); break;}mysql_free_result(g_res); while ((getchar()) != '\n'); getchar();}puts("!!! success !!! "); } /***************************************************** time : 20180625* addby : swj* function :operate_menu() 操作菜單 對用戶進行增刪改查* ******************************************************/ void operate_menu() {while(i){int choice;system("clear");puts("!!! choice： !!! ");puts("!!! 1:query msg !!! ");puts("!!! 2: add user !!! ");puts("!!! 3:del|alt msg !!! ");puts("!!! 4:display all !!! ");puts("!!! 5:exit login !!! ");puts("!!! 0:exit system !!! ");scanf("%d",&choice);switch(choice){case 1:query_msg();//需判斷權限，部分角色可使用此查詢 break;case 2:add_msg();//添加用戶操作 break;case 3:del_alt_msg();//刪改用戶操作 break;case 4:display();//顯示所有用戶及用戶角色 break;case 5://退出登錄 flag = 0;//管理員權限開關 return;case 0:puts("!!! thank you for using !!! ");//退出系統 i = 0;break;default :puts("!!! enter right choice !!! ");while ((getchar()) != '\n');getchar();break;}} } int main(void) {while(i){puts("!!!The system is initializing!!!");//初始化鏈接 if(init_mysql())print_mysql_error(NULL);//當鏈接數據庫時候 有錯誤 會報錯//選擇數據庫 沒有的時候 創建數據庫 有的時候 進去數據庫create_database();//創建表create_table(); //初始化管理員賬戶init_administrator();//用戶登錄user_login(); //用戶操作operate_menu();}mysql_close(g_conn);return EXIT_SUCCESS; }

總結

以上是生活随笔為你收集整理的Mysql —— C语言链接mysql数据库，用户 角色 权限（用户根据角色的不同拥有增删改查的权限、用户有三种认证方式）的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。