MySQL創立用戶與受權辦法。本站提示廣大學習愛好者：（MySQL創立用戶與受權辦法）文章只能為提供參考，不一定能成為您想要的結果。以下是MySQL創立用戶與受權辦法正文
注:我的運轉情況是widnows xp professional + MySQL5.0
一, 創立用戶:
敕令:CREATE USER 'username'@'host' IDENTIFIED BY 'password';
解釋:username - 你將創立的用戶名, host - 指定該用戶在哪一個主機上可以上岸,假如是當地用戶可用localhost, 假如想讓該用戶可以從隨意率性長途主機上岸,可使用通配符%. password - 該用戶的上岸暗碼,暗碼可認為空,假如為空則該用戶可以不須要暗碼上岸辦事器.
例子: CREATE USER 'dog'@'localhost' IDENTIFIED BY '123456';
CREATE USER 'pig'@'192.168.1.101_' IDENDIFIED BY '123456';
CREATE USER 'pig'@'%' IDENTIFIED BY '123456';
CREATE USER 'pig'@'%' IDENTIFIED BY '';
CREATE USER 'pig'@'%';
二,受權:
敕令:GRANT privileges ON databasename.tablename TO 'username'@'host'
解釋: privileges - 用戶的操作權限,如SELECT , INSERT , UPDATE 等(具體列表見該文最初面).假如要授與所的權限則應用ALL.;databasename - 數據庫名,tablename-表名,假如要授與該用戶對一切數據庫和表的響應操作權限則可用*表現, 如*.*.
例子: GRANT SELECT, INSERT ON test.user TO 'pig'@'%';
GRANT ALL ON *.* TO 'pig'@'%';
留意:用以上敕令受權的用戶不克不及給其它用戶受權,假如想讓該用戶可以受權,用以下敕令:
GRANT privileges ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION;
三.設置與更改用戶暗碼
敕令:SET PASSWORD FOR 'username'@'host' = PASSWORD('newpassword');假如是以後上岸用戶用SET PASSWORD = PASSWORD("newpassword");
例子: SET PASSWORD FOR 'pig'@'%' = PASSWORD("123456");
四.撤消用戶權限
敕令: REVOKE privilege ON databasename.tablename FROM 'username'@'host';
解釋: privilege, databasename, tablename - 同受權部門.
例子: REVOKE SELECT ON *.* FROM 'pig'@'%';
留意: 假設你在給用戶'pig'@'%'受權的時刻是如許的(或相似的):GRANT SELECT ON test.user TO 'pig'@'%', 則在應用REVOKE SELECT ON *.* FROM 'pig'@'%';敕令其實不能撤消該用戶對test數據庫中user表的SELECT 操作.相反,假如受權應用的是GRANT SELECT ON *.* TO 'pig'@'%';則REVOKE SELECT ON test.user FROM 'pig'@'%';敕令也不克不及撤消該用戶對test數據庫中user表的Select 權限.
詳細信息可以用敕令SHOW GRANTS FOR 'pig'@'%'; 檢查.
五.刪除用戶
敕令: DROP USER 'username'@'host';
附表:在MySQL中的操作權限
ALTER
Allows use of
ALTER TABLE.
ALTER ROUTINE
Alters or drops stored routines.
CREATE
Allows use of
CREATE TABLE.
CREATE ROUTINE
Creates stored routines.
CREATE TEMPORARY TABLE
Allows use of
CREATE TEMPORARY TABLE.
CREATE USER
Allows use of
CREATE USER,
DROP USER,
RENAME USER, and
REVOKE ALL PRIVILEGES.
CREATE VIEW
Allows use of
CREATE VIEW.
DELETE
Allows use of
DELETE.
DROP
Allows use of
DROP TABLE.
EXECUTE
Allows the user to run stored routines.
FILE
Allows use of
SELECT...
INTO OUTFILE and
LOAD DATA INFILE.
INDEX
Allows use of
CREATE INDEX and
DROP INDEX.
INSERT
Allows use of
INSERT.
LOCK TABLES
Allows use of
LOCK TABLES on tables for which the user also has
SELECT privileges.
PROCESS
Allows use of
SHOW FULL PROCESSLIST.
RELOAD
Allows use of
FLUSH.
REPLICATION
Allows the user to ask where slave or master
CLIENT
servers are.
REPLICATION SLAVE
Needed for replication slaves.
SELECT
Allows use of
SELECT.
SHOW DATABASES
Allows use of
SHOW DATABASES.
SHOW VIEW
Allows use of
SHOW CREATE VIEW.
SHUTDOWN
Allows use of
mysqladmin shutdown.
SUPER
Allows use of
CHANGE MASTER,
KILL,
PURGE MASTER LOGS, and
SET GLOBAL SQL statements. Allows
mysqladmin debug command. Allows one extra connection to be made if maximum connections are reached.
UPDATE
Allows use of
UPDATE.
USAGE
Allows connection without any specific privileges.
