mysql資料完整性測試

轉自：http://www.cnblogs.com/jifeng/archive/2011/03/06/1972183.html

轉自：http://my.oschina.net/u/1179414/blog/202377

建立使用者:

指令:

1	CREATE USER  'username' @ 'host' IDENTIFIED BY  'password' ;

說明:username – 你将建立的使用者名, host – 指定該使用者在哪個主機上可以登陸,如果是本地使用者可用localhost,  如 果想讓該使用者可以從任意遠端主機登陸,可以使用通配符%. password –  該使用者的登陸密碼,密碼可以為空,如果為空則該使用者可以不需要密碼登 陸伺服器.

例子：

?

1 2 3 4 5

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' @ '%' ;

授權:

指令:

?

1	GRANT privileges ON databasename.tablename TO  'username' @ 'host'

說明: privileges – 使用者的操作權限,如SELECT , INSERT , UPDATE  等(詳細清單見該文最後面).如果要授予所 的權限則使用ALL.;databasename –  資料庫名,tablename-表名,如果要授予該使用者對所有資料庫和表的相應操作權限則可用* 表示, 如*.*.

例子:

?

1 2	GRANT SELECT, INSERT ON  test .user TO  'pig' @ '%' ;  GRANT ALL ON *.* TO  'pig' @ '%' ;

注意:用以上指令授權的使用者不能給其它使用者授權,如果想讓該使用者可以授權,用以下指令:

?

1	GRANT privileges ON databasename.tablename TO  'username' @ 'host' WITH GRANT OPTION；

設定與更改使用者密碼

指令:

?

1	SET PASSWORD FOR  'username' @ 'host' = PASSWORD( 'newpassword' );

如果是目前登陸使用者用

?

1	SET PASSWORD = PASSWORD( "newpassword" );

撤銷使用者權限 

指令:

?

1	REVOKE privilege ON databasename.tablename FROM  'username' @ 'host' ;

說明: privilege, databasename, tablename – 同授權部分.

例子

?

1	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’@'%’; 檢視.

删除使用者

指令:

?

1	DROP USER ‘username’@'host’;

一個典型的資料庫建表, 建使用者過程:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

建立用于localhost連接配接的使用者并指定密碼  mysql> create user  'pcom' @ 'localhost' identified by  'aaa7B2249' ;  Query OK, 0 rows affected (0.00 sec)  建立資料庫  mysql> create database pcom default character  set utf8 collate utf8_bin;  Query OK, 1 row affected (0.00 sec)  給本地使用者授權, 這裡不需要指定密碼  mysql> grant all on pcom.* to  'pcom' @ 'localhost' ;  Query OK, 0 rows affected (0.00 sec)  給其他IP位址下的使用者授權, 注意: 這裡必須指定密碼, 否則就可以無密碼通路  mysql> grant all on pcom.* to  'pcom' @ '192.168.0.0/255.255.0.0' identified by  'aaa7B2249' ;  Query OK, 0 rows affected (0.00 sec)  同理  mysql> grant all on pcom.* to  'pcom' @ '172.20.0.0/255.255.0.0' identified by  'aaa7B2249' ;  Query OK, 0 rows affected (0.00 sec)  Done!

附表:在MySQL中的操作權限

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77

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.

最後不要忘記重新整理資料庫

?

1	flush privileges;

在建立使用者過程中總是遇到一個問題就是，他總會将設定放在mysql這個資料庫下，即建立的使用者操作權是針對預設資料庫mysql的。

1.為使用者授權

　　授權格式：grant 權限 on 資料庫.* to 使用者名@登入主機 identified by "密碼";　

mysql 資料庫授權(給某個使用者授權某個資料庫)

先設定該使用者隻有show database權限

grant select,insert,update,delete on redmine1.* to [email protected]"%" identified by "jira";

新增超級權限并允許遠端通路：

 GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'192.168.1.3' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;   

FLUSH   PRIVILEGES; 

授權指令GRANT 語句的文法如下： 

GRANT privileges (columns) 

ON what 

TO user IDENTIFIEDBY "password" 

WITH GRANT OPTION 

對使用者授權 

mysql>grant rights on database.* to [email protected] identified by "pass"; 

例1:  

增加一個使用者test1密碼為abc，讓他可以在任何主機上登入，并對所有資料庫有查詢、插入、修改、删除的權限。 

grant select,insert,update,delete on *.* to [email protected]"%" Identified by "abc"; 

ON 子句中*.* 說明符的意思是“所有資料庫，所有的表” 

例2: 

增加一個使用者test2密碼為abc, 讓他隻可以在localhost上登入，并可以對資料庫mydb進行查詢、插入、修改、删除的操作。 

grant select,insert,update,delete on mydb.* to [email protected] identified by "abc"; 

例子3  

增加一個使用者custom，他能從主機localhost、server.domain和whitehouse.gov連接配接。他隻想要從 localhost存取bankaccount資料庫，從whitehouse.gov存取expenses資料庫和從所有3台主機存取customer 資料庫。他想要從所有3台主機上使用密碼stupid。

為了使用GRANT語句設定個使用者的權限，運作這些指令： 

shell> mysql --user=root mysql  

mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP 

ON bankaccount.* TO [email protected] IDENTIFIED BY 'stupid'; 

mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP 

ON expenses.* TO [email protected] IDENTIFIED BY 'stupid'; 

mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP 

ON customer.* TO [email protected]'%' IDENTIFIED BY 'stupid';  

==============================================  

權限資訊用user、db、host、tables_priv和columns_priv表被存儲在mysql資料庫中(即在名為mysql的資料庫中)。  

權限 列 Context 

select Select_priv 表 

insert Insert_priv 表 

update Update_priv 表 

delete Delete_priv 表 

index Index_priv 表 

alter Alter_priv 表 

create Create_priv 資料庫、表或索引 

drop Drop_priv 資料庫或表 

grant Grant_priv 資料庫或表 

references References_priv 資料庫或表 

reload Reload_priv 伺服器管理 

shutdown Shutdown_priv 伺服器管理 

process Process_priv 伺服器管理 

file File_priv 在伺服器上的檔案存取 

1.select、insert、update和delete權限 允許你在一個資料庫現有的表上實施操作，是基本權限  

2.alter權限允許你使用ALTER TABLE 

3.create和drop權限允許你建立新的資料庫和表，或抛棄(删除)現存的資料庫和表 如果你将mysql資料庫的drop權限授予一個使用者，該使用者能抛棄存儲了MySQL存取權限的資料庫！  

4.grant權限允許你把你自己擁有的那些權限授給其他的使用者。  

你不能明顯地指定一個給定使用者應該被拒絕存取。即，你不能明顯地比對一個使用者并且然後拒絕連接配接。你不能指定一個使用者有權建立立或抛棄一個資料庫中的表，也不能建立或抛棄資料庫本身。 可以同時列出許多被授予的單個權限。 

例如，如果想讓使用者能讀取和修改已有表的内容，但又不允許建立新表或删除表，可按如下授權： 

GRANT SELECT,INSERT,DELETE,UPDATE ON samp_db.* TO 'user'@'%' IDENTIFIEDBY "pass"

以上是我從别的地方拷貝過來後稍作修改的文字，下面自己寫一些需要注意的東西。

為什麼使用了Grant all on db.* to user identified by "pass"後，在主機上通路資料庫還會出現ERROR 1045 (28000): Access denied for user 'user'@'localhost'(using password: YES) 的錯誤提示？

解答方法如下：運作指令 Grant all on db.* to 'user'@'localhost' identified by "pass"

原因是：當不加@選項時，效果與加@'%'是一樣的，'%'從名義上包括任何主機，（%必須加上引号，不然與@放在一起可能不會被辨認出。）不過有些時候（有些版本）'%'不包括localhost，要單獨對@'localhost'進行指派