MySQL加载数据所亟需的file权限实验_mysql

$ mysqlplus.sh  Login MySQL Srevice: lcoalhost_3306 Welcome to the MySQL monitor.  Commands end with ; or /g. Your MySQL connection id is 1 Server version: 5.5.19-log MySQL Community Server (GPL)  Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.  Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.  Type 'help;' or '/h' for help. Type '/c' to clear the current input statement.  mysql>  mysql>  mysql> select user,host from mysql.user; +----------+-----------+ | user     | host      | +----------+-----------+ | fileuser | %         | | mycat    | %         | | zhao     | %         | | root     | 127.0.0.1 | | root     | ::1       | |          | centos01  | | root     | centos01  | |          | localhost | | root     | localhost | +----------+-----------+ 9 rows in set (0.01 sec)  mysql>  mysql> show tables; ERROR 1046 (3D000): No database selected mysql> show tables; ERROR 1046 (3D000): No database selected mysql> show databases; +--------------------+ | Database           | +--------------------+ | information_schema | | DataPlatform       | | auction            | | db1                | | db2                | | db3                | | db_bcty365         | | db_pursey          | | discuzX3           | | hibernate_20140223 | | hivemeta           | | jjbbs              | | jjwiki             | | jjwikidb           | | liferay            | | lportal            | | mysql              | | performance_schema | | test               | +--------------------+ 19 rows in set (0.10 sec)  mysql>  mysql>  mysql>  mysql> use test; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A  Database changed mysql> show tabels; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'tabels' at line 1 mysql>  mysql>  mysql> show tables; +-----------------------------------+ | Tables_in_test                    | +-----------------------------------+ | department                        | | networkspeed_day_isp_r            | | networkspeed_day_province_isp_r   | | networkspeed_day_province_r       | | networkspeed_day_system_ip        | | networkspeed_month_isp_r          | | networkspeed_month_province_isp_r | | networkspeed_month_province_r     | | networkspeed_month_system_ip      | | networkspeed_week_isp_r           | | networkspeed_week_province_isp_r  | | networkspeed_week_province_r      | | networkspeed_week_system_ip       | | privilege                         | | province_r                        | | province_test                     | | role                              | | role_privilege                    | | user                              | | user_role                         | +-----------------------------------+ 20 rows in set (0.00 sec)  mysql>  mysql>  mysql> select user,host from mysql.user; +----------+-----------+ | user     | host      | +----------+-----------+ | fileuser | %         | | mycat    | %         | | zhao     | %         | | root     | 127.0.0.1 | | root     | ::1       | |          | centos01  | | root     | centos01  | |          | localhost | | root     | localhost | +----------+-----------+ 9 rows in set (0.00 sec)  mysql>  mysql>  mysql>  mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE ON test.* TO 'mis_lda'@'192.168.226.121' IDENTIFIED BY 'fPUw2TgI'; Query OK, 0 rows affected (0.00 sec)  mysql>  mysql>  mysql>  mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)  mysql>  mysql>  mysql> select user,host from mysql.user; +----------+-----------------+ | user     | host            | +----------+-----------------+ | fileuser | %               | | mycat    | %               | | zhao     | %               | | root     | 127.0.0.1       | | mis_lda  | 192.168.226.121 | | root     | ::1             | |          | centos01        | | root     | centos01        | |          | localhost       | | root     | localhost       | +----------+-----------------+ 10 rows in set (0.00 sec)  mysql>  mysql>  mysql> show tables; +-----------------------------------+ | Tables_in_test                    | +-----------------------------------+ | department                        | | networkspeed_day_isp_r            | | networkspeed_day_province_isp_r   | | networkspeed_day_province_r       | | networkspeed_day_system_ip        | | networkspeed_month_isp_r          | | networkspeed_month_province_isp_r | | networkspeed_month_province_r     | | networkspeed_month_system_ip      | | networkspeed_week_isp_r           | | networkspeed_week_province_isp_r  | | networkspeed_week_province_r      | | networkspeed_week_system_ip       | | privilege                         | | province_r                        | | province_test                     | | role                              | | role_privilege                    | | user                              | | user_role                         | +-----------------------------------+ 20 rows in set (0.01 sec)  mysql>  mysql>  mysql> create table test01 as select user,host from mysql.user where 1=0; Query OK, 0 rows affected (0.06 sec) Records: 0  Duplicates: 0  Warnings: 0  mysql>  mysql> select user,host from mysql.user into outfile '/tmp/test01.txt'; Query OK, 10 rows affected (0.01 sec)  mysql>  mysql> select * from test02; ERROR 1146 (42S02): Table 'test.test02' doesn't exist mysql>  mysql> select * from test01; Empty set (0.00 sec)  mysql>  mysql> load data infile '/tmp/test01.txt' into table test01; Query OK, 10 rows affected (0.04 sec) Records: 10  Deleted: 0  Skipped: 0  Warnings: 0  mysql>  mysql> select * from test02; ERROR 1146 (42S02): Table 'test.test02' doesn't exist mysql>  mysql> select * from test01; +----------+-----------------+ | user     | host            | +----------+-----------------+ | fileuser | %               | | mycat    | %               | | zhao     | %               | | root     | 127.0.0.1       | | mis_lda  | 192.168.226.121 | | root     | ::1             | |          | centos01        | | root     | centos01        | |          | localhost       | | root     | localhost       | +----------+-----------------+ 10 rows in set (0.00 sec)  mysql> 

$ mysql -umis_lda -pfPUw2TgI  -h 192.168.226.121 -P 3306  Welcome to the MySQL monitor.  Commands end with ; or /g. Your MySQL connection id is 3 Server version: 5.5.19-log MySQL Community Server (GPL)  Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.  Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.  Type 'help;' or '/h' for help. Type '/c' to clear the current input statement.  mysql>  mysql>  mysql>  mysql> show databases; +--------------------+ | Database           | +--------------------+ | information_schema | | test               | +--------------------+ 2 rows in set (0.00 sec)  mysql>  mysql>  mysql> test; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'test' at line 1 mysql> use test; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A  Database changed mysql>  mysql> show tables; +-----------------------------------+ | Tables_in_test                    | +-----------------------------------+ | department                        | | networkspeed_day_isp_r            | | networkspeed_day_province_isp_r   | | networkspeed_day_province_r       | | networkspeed_day_system_ip        | | networkspeed_month_isp_r          | | networkspeed_month_province_isp_r | | networkspeed_month_province_r     | | networkspeed_month_system_ip      | | networkspeed_week_isp_r           | | networkspeed_week_province_isp_r  | | networkspeed_week_province_r      | | networkspeed_week_system_ip       | | privilege                         | | province_r                        | | province_test                     | | role                              | | role_privilege                    | | user                              | | user_role                         | +-----------------------------------+ 20 rows in set (0.00 sec)  mysql>  mysql>  mysql>  mysql>  mysql> show tables; +-----------------------------------+ | Tables_in_test                    | +-----------------------------------+ | department                        | | networkspeed_day_isp_r            | | networkspeed_day_province_isp_r   | | networkspeed_day_province_r       | | networkspeed_day_system_ip        | | networkspeed_month_isp_r          | | networkspeed_month_province_isp_r | | networkspeed_month_province_r     | | networkspeed_month_system_ip      | | networkspeed_week_isp_r           | | networkspeed_week_province_isp_r  | | networkspeed_week_province_r      | | networkspeed_week_system_ip       | | privilege                         | | province_r                        | | province_test                     | | role                              | | role_privilege                    | | test01                            | | user                              | | user_role                         | +-----------------------------------+ 21 rows in set (0.00 sec)  mysql>  mysql>  mysql>  mysql> create test02 as select * from test01 where 1=0; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'test02 as select * from test01 where 1=0' at line 1 mysql> create table test02 select * from test01 where 1=0; Query OK, 0 rows affected (0.04 sec) Records: 0  Duplicates: 0  Warnings: 0  mysql>  mysql>  mysql> select * from test02; Empty set (0.00 sec)  mysql>  mysql>  mysql> load data infile '/tmp/test01.txt' into table test02; ERROR 1045 (28000): Access denied for user 'mis_lda'@'192.168.226.121' (using password: YES) mysql>  mysql> select * from test02; Empty set (0.00 sec)  mysql>  mysql>  mysql>  mysql> load data infile '/tmp/test01.txt' into table test02; ERROR 1045 (28000): Access denied for user 'mis_lda'@'192.168.226.121' (using password: YES) mysql>  mysql> exit Bye

mysql>  mysql> show grants for mis_lda@'192.168.226.121';    +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Grants for [email protected]                                                                                                                                          | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'mis_lda'@'192.168.226.121' IDENTIFIED BY PASSWORD '*24B612AA4FC098916383320D28EF01789ADEE21F'                                                        | | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE ON `test`.* TO 'mis_lda'@'192.168.226.121' | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.01 sec)  mysql>  mysql> grant file ON `test`.* TO 'mis_lda'@'192.168.226.121'; ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES mysql>  mysql>  mysql> grant file on *.* to 'mis_lda'@'192.168.226.121'; Query OK, 0 rows affected (0.00 sec)  mysql>  mysql>  mysql>  mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)  mysql>  mysql>  mysql> show grants for mis_lda@'192.168.226.121';             +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Grants for [email protected]                                                                                                                                          | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | GRANT FILE ON *.* TO 'mis_lda'@'192.168.226.121' IDENTIFIED BY PASSWORD '*24B612AA4FC098916383320D28EF01789ADEE21F'                                                         | | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE ON `test`.* TO 'mis_lda'@'192.168.226.121' | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) 

$ mysql -umis_lda -pfPUw2TgI  -h 192.168.226.121 -P 3306  Welcome to the MySQL monitor.  Commands end with ; or /g. Your MySQL connection id is 4 Server version: 5.5.19-log MySQL Community Server (GPL)  Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.  Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.  Type 'help;' or '/h' for help. Type '/c' to clear the current input statement.  mysql>  mysql> use test; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A  Database changed mysql>  mysql> select * from test02; Empty set (0.00 sec)  mysql> load data infile '/tmp/test01.txt' into table test02; Query OK, 10 rows affected (0.01 sec) Records: 10  Deleted: 0  Skipped: 0  Warnings: 0  mysql>  mysql>  mysql> select * from test02; +----------+-----------------+ | user     | host            | +----------+-----------------+ | fileuser | %               | | mycat    | %               | | zhao     | %               | | root     | 127.0.0.1       | | mis_lda  | 192.168.226.121 | | root     | ::1             | |          | centos01        | | root     | centos01        | |          | localhost       | | root     | localhost       | +----------+-----------------+ 10 rows in set (0.00 sec)  mysql> 

        权限信息用user、db、host、tables_priv和columns_priv表被存储在mysql数据库中(即在名为mysql的数据库中)。在MySQL启动时和在6.9 权限修改何时生效所说的情况时，服务器读入这些数据库表内容。

        本手册所用的涉及由MySQL提供的权限名称显示在下表，还有在授权表中每个权限的表列名称和每个权限有关的上下文：

        select、insert、update和delete权限允许你在一个数据库现有的表上实施操作。

SELECT语句只有在他们真正从一个表中检索行是才需要select权限，你可以执行某个SELECT语句，甚至没有任何到服务器上的数据库里的存取任何东西的许可。例如，你可使用mysql客户作为一个简单的计算器：

mysql> SELECT 1+1; mysql> SELECT PI()*2; 

        index权限允许你创建或抛弃(删除)索引。

        alter权限允许你使用ALTER TABLE。

        create和drop权限允许你创建新的数据库和表，或抛弃(删除)现存的数据库和表。

        注意：如果你将mysql数据库的drop权限授予一个用户，该用户能抛弃存储了MySQL存取权限的数据库！

        grant权限允许你把你自己拥有的那些权限授给其他的用户。

        file权限给予你用LOAD DATA INFILE和SELECT ... INTO OUTFILE语句读和写服务器上的文件，任何被授予这个权限的用户都能读或写MySQL服务器能读或写的任何文件。

        其余的权限用于管理性操作，它使用mysqladmin程序实施。下表显示mysqladmin支配每个管理性权限允许你执行的命令：

        reload命令告诉服务器再读入授权表，refresh命令清洗所有表并打开和关闭记录文件，flush-privileges是reload的一个同义词，其它flush-*命令执行类似refresh的功能，但是范围更有限，并且在某些情况下可能更好用。例如，如果你只是想清洗记录文件，flush-logs比refresh是更好的选择。

        shutdown命令关掉服务器。

        processlist命令显示在服务器内执行的线程的信息。kill命令杀死服务器线程。你总是能显示或杀死你自己的线程，但是你需要process权限来显示或杀死其他用户启动的线程。

        总的说来，只授予权限给需要他们的那些用户是一个好主意，但是你应该在授予某个权限时试验特定的警告：

• grant权限允许用户放弃他们的权限给其他用户。2个有不同的权限并有grant权限的用户可以合并权限。
• alter权限可以用于通过重新命名表来推翻权限系统。
• file权限可以被滥用在服务器上读取任何世界可读(world-readable，即任何人可读)的文件到一张数据库表，然后其内容能用SELECT被存取。
• shutdown权限通过终止服务器可以被滥用完全拒绝为其他用户服务, 。
• precess权限能被用来察看当前执行的查询的普通文本，包括设定或改变口令查询。
• 在mysql数据库上的权限能被用来改变口令和其他存取权限信息。（口令被加密存储，所以一个恶意的用户不能简单地读取他们。然而，有足够的权限，同一个用户能用不同的一个代替一个口令。）

有一些事情你不能用MySQL权限系统做到：

• 你不能明显地指定一个给定用户应该被拒绝存取。即，你不能明显地匹配一个用户并且然后拒绝连接。
• 你不能指定一个用户有权创建立或抛弃一个数据库中的表，也不能创建或抛弃数据库本身。 

版权声明：本文为博主原创文章，未经博主允许不得转载。