Mysql_load_data and Rights Management

————————————————————————————————————————

Add Permissions:

The only Priv_type values can specify for a table is SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, GRANT OPTION, INDE X, and ALTER.
"FILE" is only assigned as a GLOBAL permission
"The execution, FILE, PROCESS, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, SHOW DATABASES, SHUTDOWN, and SUPER privile GES is administrative privileges that can is only being granted globally (using on *. * syntax). "

The original file this permission belongs to the administrative permission, to give the user, must use *. *. (Not available for libraries and tables)
[Email protected]:mysql.sock[(none)] 15:31:33>grant FILE on *. * to [email protected];
Query OK, 0 rows Affected (0.00 sec)

Problem solving.

————————————————————————————————————————

Today, when inserting a file using MySQL's load DATA LOCAL INFILE syntax, Tip: ERROR 1148 (42000): The used command is not a allowed with this MySQL ver Sion

The reason is that if you do not specify –enable-local-infile when you compile and install MySQL, the above error will be reported when using the above command:

Workaround:

There are two workarounds, one is to recompile the installation plus the above parameters, but instead directly with the command line execution, as follows:

mysql-uroot-proot mydb_name--local-infile=1-e ' Load data local infile "d:/ab.txt" into table Mytbl (name,age) '

————————————————————————————————————————

Mysql:error13 (HY000): Problem with Can ' t get stat of But replace load data INFILE with load data LOCAL INFILE ok! Cause analysis: From the MySQL Chinese manual: For security reasons, when reading a text file located in the server, the www.2cto.com file must be in the database directory, or be all-readable. In addition, to use load DATA INFILE for server files, you must have file permissions. If local is specified, the file is read by clients on the client's host and sent to the server. The file is given a full path name to specify the exact location.

————————————————————————————————————————

Various permissions in MySQL

For the grant and REVOKE statements, Priv_type can be specified as any of the following:

Permissions	Significance
all [privileges]	To set all simple permissions except Grant option
Alter	Allow alter TABLE to be used
ALTER ROUTINE	To change or cancel a stored subroutine
CREATE	Allow use of Create TABLE
CREATE ROUTINE	To create a stored sub-program
CREATE Temporary TABLES	Allow use of Create temporary TABLE
CREATE USER	Allow use of the Create user, DROP user, RENAME user and revoke all privileges.
CREATE VIEW	Allow use of Create VIEW
DELETE	Allow use of delete
DROP	Allow drop TABLE to be used
EXECUTE	Allow users to run stored subroutines
FILE	Allow use of SELECT ... into outfile and load DATA INFILE
INDEX	Allow use of CREATE INDEX and drop index
INSERT	Allow use of Insert
LOCK TABLES	Allows you to use lock TABLES for tables for which you have SELECT permissions
PROCESS	Allow show full processlist to be used
REFERENCES	has not been implemented
RELOAD	Allow flush to be used
REPLICATION CLIENT	Allow users to ask the address of a subordinate server or home server
REPLICATION SLAVE	For replicated slave servers (read binary log events from the primary server)
SELECT	Allow use of Select
SHOW DATABASES	Show databases display all databases
SHOW VIEW	Allow use of show CREATE VIEW
SHUTDOWN	Allow the use of mysqladmin shutdown
SUPER	Allows the use of change master, KILL, PURGE master Logs and set global statements,mysqladmin debug commands, allowing you to connect (once) even if the max_connections has been reached.
UPDATE	Allow use of update
USAGE	Synonyms for "No permissions"
GRANT OPTION	Allow permissions to be granted

When upgrading from an older version of MySQL, to use Execute, create VIEW, SHOW view, create USER, create routine and alter routine permissions

————————————————————————————————————————

The permissions granted can be divided into multiple tiers:

· Global Hierarchy

Global permissions apply to all databases in a given server. These permissions are stored in the Mysql.user table. Grant all on * * and REVOKE all on * * ONLY GRANT and REVOKE global permissions.

· Database Hierarchy

Database permissions apply to all targets in a given database. These permissions are stored in the mysql.db and Mysql.host tables. Grant all ondb_name. * and revoke all on db_name. * Grant and REVOKE database permissions only.

· table Level

Table permissions apply to all columns in a given table. These permissions are stored in the Mysql.talbes_priv table. Grant all on db_name.tbl_name and revoke all on db_name.tbl_name only grant and revoke table permissions.

· Column Hierarchy

Column permissions apply to a single column in a given table. These permissions are stored in the Mysql.columns_priv table. When using revoke, you must specify the same columns as the authorized column.

· Sub-Program level

CREATE ROUTINE, ALTER ROUTINE, execute and grant permissions apply to stored subroutines. These permissions can be granted at the global level and at the database level. Furthermore, in addition to the Create routine, these permissions can be granted as sub-program levels and stored in the Mysql.procs_priv table.

When a subsequent target is a table, a stored function, or a stored procedure, theobject_type clause should be specified as table, function, or procedure. When upgrading from an older version of MySQL, to use the phrase, you must upgrade your authorization form.

————————————————————————————————————————

Mysql_load_data and Rights Management