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));
else
puts(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;
}