玩轉mysql授權

標籤：玩轉mysql授權

0. 刪除系統多餘帳號
 use mysql;
 select user,host from mysql.user;
 delete from user where user=‘‘;
 flush privileges;

 mysql建立帳號:
 mysqladmin -u root password ‘123456‘

以demo庫test庫分別demo_1和test_1表為例:
  create database demo;
 

  create table demo_1(id int);
  insert into demo_1(id) values(1),(2),(3);
 

  create database test;
  create table test_1(id int);
  insert into test_1(id) values(1),(2),(3);
 

   create table test_2(id int);
   insert into test_2(id) values(1),(2),(3);

1. 授權所有庫
   建立新使用者並授權,且密碼為空白: grant all on *.* to [email protected]‘localhost‘

   建立新使用者並授權,且設密碼: grant all on *.* to [email protected]‘localhost‘ identified by ‘123456‘;
                   或  grant select, insert, update, delete on *.* to [email protected]‘localhost‘

   重新整理許可權: flush privileges;

   查看許可權: show grants for [email protected]‘localhost‘;
               GRANT ALL PRIVILEGES ON *.* TO ‘test‘@‘localhost‘ IDENTIFIED BY PASSWORD ‘*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9‘ 

   測試許可權(可以): mysql -utest -p123456 -e ‘select * from demo.demo_1‘;  

 1.1 收回許可權
     收回許可權: revoke all on *.* from [email protected]‘localhost‘;

     重新整理許可權: flush privileges;

     查看許可權: show grants for [email protected]‘localhost‘;
                GRANT USAGE ON *.* TO ‘test‘@‘localhost‘ IDENTIFIED BY PASSWORD ‘*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9‘

     測試許可權(不可以): mysql -utest -p123456 -e ‘select * from demo.demo_1‘;
            ERROR 1142 (42000) at line 1: SELECT command denied to user ‘test‘@‘localhost‘ for table ‘demo_1‘
     
2. 授權指定庫(1個或多個庫)
     grant all on test.* to [email protected]‘localhost‘;
     grant all on demo.* to [email protected]‘localhost‘;    
    注意：不能一次對指定的多個庫進行授權，只能一個一個授權：grant all on test.*,demo.* to [email protected]‘localhost‘;

    查看許可權: show grants for [email protected]‘localhost‘;
            GRANT USAGE ON *.* TO ‘test‘@‘localhost‘ IDENTIFIED BY PASSWORD ‘*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9‘

            GRANT ALL PRIVILEGES ON `test`.* TO ‘test‘@‘localhost‘
            GRANT ALL PRIVILEGES ON `demo`.* TO ‘test‘@‘localhost‘

     測試許可權(可以): mysql -utest -p123456 -e ‘select * from demo.demo_1‘; 

 2.1 收回許可權
        revoke all on test.* from [email protected]‘localhost‘;
        revoke all on demo.* from [email protected]‘localhost‘;
        flush privileges;

      測試許可權：mysql -utest -p123456 -e ‘select * from demo.demo_1‘;
                ERROR 1142 (42000) at line 1: SELECT command denied to user ‘test‘@‘localhost‘ for table ‘demo_1‘
3. 授權指定庫(1張或多張表)
      授權: grant all on test.test_2 to [email protected]‘localhost‘;
      重新整理許可權: flush privileges;
      查看許可權: show grants for [email protected]‘localhost‘;
               GRANT USAGE ON *.* TO ‘test‘@‘localhost‘ IDENTIFIED BY PASSWORD ‘*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9‘
               GRANT ALL PRIVILEGES ON `test`.`test_2` TO ‘test‘@‘localhost‘
      測試許可權: mysql -utest -p123456 -e ‘select * from test.test_1‘;
                   ERROR 1142 (42000) at line 1: SELECT command denied to user ‘test‘@‘localhost‘ for table ‘test_1‘
              mysql -utest -p123456 -e ‘select * from test.test_2‘; 查詢有東西

      注意：授權一個不存在的表居然也可以成功，還可以查看出許可權。
 3.1 收回許可權
         revoke all on test.test_2 from [email protected]‘localhost‘;
         flush privileges;
         
4. 隱藏庫不讓授權
   information_schema  該庫儲存了mysql一些中繼資料，如資料庫名或表名，列的資料類型，或存取權限等
   mysql

5. 將ip整成網域名稱訪問資料庫
   192.168.11.17 www.db.com
   navcat中就可以直接填寫網域名稱，指定連接埠，需要注意host是控制訪問的許可權 %/192.168.11.%/192.168.11.18/等等

本文出自 “開發與營運” 部落格，謝絕轉載！

玩轉mysql授權