————————————————————————————————————————
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