mysql查询授权信息_MySQL授权以及状态查询

CREATE DATABASE odi; -- 创建ODI数据库

GRANT ALL PRIVILEGES ON *.* TO odi@'%' IDENTIFIED BY 'odipasswd'; -- 远程授权；

FLUSH PRIVILEGES; -- 刷新授权表

DELETE FROM USER WHERE USER="odi"; -- 删除该用户授权

CREATE DATABASE allen;

GRANT ALL PRIVILEGES ON test.* TO odi@'%' IDENTIFIED BY 'odipasswd'; -- 远程授权

GRANT ALL PRIVILEGES ON test TO odi@'%' IDENTIFIED BY 'odipasswd'; -- 远程授权

GRANT ALL PRIVILEGES ON phplampDB.* TO phplamp@localhost IDENTIFIED BY '1234';

-- grant 权限 on 数据库名.表名 用户@登录主机 identified by "用户密码";

-- grant 权限 on 数据库名.表名 用户@登录主机 identified by "用户密码";

-- select host,user,password from user;

-- update user set host = ’%’ where user = ’fang’;

CREATE DATABASE ccc;

GRANT ALL PRIVILEGES ON ccc.* TO ccc@'%' IDENTIFIED BY 'cccpasswd'; -- 远程授权；

FLUSH PRIVILEGES; -- 刷新授权表

-- 新建用户

INSERT INTO mysql.user(HOST,USER,PASSWORD) VALUES("%","testdb",PASSWORD("testpasswd"));

-- 创建新数据库

CREATE DATABASE testdb;

-- 授权test用户拥有testDB数据库的所有权限(某个数据库的所有权限)

GRANT ALL PRIVILEGES ON testdb.* TO test@localhost IDENTIFIED BY 'testpasswd';

-- 授权test用户拥有所有数据库的某些权限

GRANT SELECT,DELETE,UPDATE,CREATE,DROP ON *.* TO test@"%" IDENTIFIED BY "1234";

-- 删除用户

DELETE FROM USER WHERE USER='test' AND HOST='localhost';

FLUSH PRIVILEGES;

DROP DATABASE testDB; //删除用户的数据库

-- 删除账户及权限：

DROP USER 用户名@'%';

DROP USER 用户名@ localhost;

-- 修改某一个用户的密码

UPDATE mysql.user SET PASSWORD=PASSWORD('新密码') WHERE USER="test" AND HOST="localhost";

FLUSH PRIVILEGES;

-- 列出所有数据库

SHOW DATABASES;

-- 切换数据库

USE database_name;

-- 列出所有表

SHOW TABLES;

-- 显示表结构

DESCRIBE tables_name;

-- 删除数据库

DROP DATABASE database_name;

-- 删除数据表

DROP TABLE tables_name;