Wiki

User Tools

Site Tools

Trace: • add_route • configure_banners
mysql:privileges

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Next revision		Previous revision
mysql:privileges [2016/07/19 15:23] – created petermysql:privileges [2020/07/15 09:30] (current) – external edit 127.0.0.1
Line 7: Line 7:
 ===== Global Privileges ===== ===== Global Privileges =====
  
-Global privileges are administrative or apply to all databases on a given server. To assign global privileges, use ON *.* syntax:+Global privileges are administrative or apply to all databases on a given server. To assign global privileges, use **ON *.*** syntax:
  
 <code mysql> <code mysql>
Line 24: Line 24:
 ===== Database Privileges ===== ===== Database Privileges =====
  
-Database privileges apply to all objects in a given database.  To assign database-level privileges, use ON db_name.* syntax:+Database privileges apply to all objects in a given database.  To assign database-level privileges, use **ON db_name.*** syntax:
  
 <code mysql> <code mysql>
Line 31: Line 31:
 </code> </code>
  
-If you use ON * syntax (rather than ON *.*) and you have selected a default database, privileges are assigned at the database level for the default database. An error occurs if there is no default database.+If you use **ON *** syntax (rather than **ON *.***) and you have selected a default database, privileges are assigned at the database level for the default database. An error occurs if there is no default database.
  
-The CREATE, DROP, EVENT, GRANT OPTION, LOCK TABLES, and REFERENCES privileges can be specified at the database level. Table or routine privileges also can be specified at the database level, in which case they apply to all tables or routines in the database.+The CREATE, DROP, EVENT, GRANT OPTION, LOCK TABLES, and REFERENCES privileges can be specified at the database level.  Table or routine privileges also can be specified at the database level, in which case they apply to all tables or routines in the database.
  
 MySQL stores database privileges in the **mysql.db** table.  MySQL stores database privileges in the **mysql.db** table. 
Line 40: Line 40:
 ===== Table Privileges ===== ===== Table Privileges =====
  
-Table privileges apply to all columns in a given table. To assign table-level privileges, use ON db_name.tbl_name syntax:+Table privileges apply to all columns in a given table. To assign table-level privileges, use **ON db_name.tbl_name** syntax:
  
 <code mysql> <code mysql>
Line 49: Line 49:
 If you specify tbl_name rather than db_name.tbl_name, the statement applies to tbl_name in the default database.  An error occurs if there is no default database.  If you specify tbl_name rather than db_name.tbl_name, the statement applies to tbl_name in the default database.  An error occurs if there is no default database. 
  
- The permissible priv_type values at the table level are ALTER, CREATE VIEW, CREATE, DELETE, DROP, GRANT OPTION, INDEX, INSERT, REFERENCES, SELECT, SHOW VIEW, TRIGGER, and UPDATE.+The permissible priv_type values at the table level are ALTER, CREATE VIEW, CREATE, DELETE, DROP, GRANT OPTION, INDEX, INSERT, REFERENCES, SELECT, SHOW VIEW, TRIGGER, and UPDATE.
  
 MySQL stores table privileges in the **mysql.tables_priv** table.  MySQL stores table privileges in the **mysql.tables_priv** table. 
Line 56: Line 56:
 ===== Column Privileges ===== ===== Column Privileges =====
  
-Column privileges apply to single columns in a given table. Each privilege to be granted at the column level must be followed by the column or columns, enclosed within parentheses.+Column privileges apply to single columns in a given table.  Each privilege to be granted at the column level must be followed by the column or columns, enclosed within parentheses.
  
 <code mysql> <code mysql>
Line 63: Line 63:
  
 The permissible priv_type values for a column (that is, when you use a column_list clause) are INSERT, REFERENCES, SELECT, and UPDATE.  The permissible priv_type values for a column (that is, when you use a column_list clause) are INSERT, REFERENCES, SELECT, and UPDATE. 
- 
- The permissible priv_type values for a column (that is, when you use a column_list clause) are INSERT, REFERENCES, SELECT, and UPDATE. 
  
 MySQL stores column privileges in the **mysql.columns_priv** table.  MySQL stores column privileges in the **mysql.columns_priv** table. 
Line 71: Line 69:
 ===== Stored Routine Privileges ===== ===== Stored Routine Privileges =====
  
-The ALTER ROUTINE, CREATE ROUTINE, EXECUTE, and GRANT OPTION privileges apply to stored routines (procedures and functions). They can be granted at the global and database levels. Except for CREATE ROUTINE, these privileges can be granted at the routine level for individual routines.+The ALTER ROUTINE, CREATE ROUTINE, EXECUTE, and GRANT OPTION privileges apply to stored routines (procedures and functions).  They can be granted at the global and database levels.  Except for CREATE ROUTINE, these privileges can be granted at the routine level for individual routines.
  
 <code mysql> <code mysql>
Line 78: Line 76:
 </code> </code>
  
-The permissible priv_type values at the routine level are ALTER ROUTINE, EXECUTE, and GRANT OPTION. CREATE ROUTINE is not a routine-level privilege because you must have this privilege to create a routine in the first place.+The permissible priv_type values at the routine level are ALTER ROUTINE, EXECUTE, and GRANT OPTION.  CREATE ROUTINE is not a routine-level privilege because you must have this privilege to create a routine in the first place.
  
 MySQL stores routine-level privileges in the **mysql.procs_priv** table.  MySQL stores routine-level privileges in the **mysql.procs_priv** table. 
Line 93: Line 91:
 When PROXY is granted, it must be the only privilege named in the GRANT statement, the REQUIRE clause cannot be given, and the only permitted WITH option is WITH GRANT OPTION. When PROXY is granted, it must be the only privilege named in the GRANT statement, the REQUIRE clause cannot be given, and the only permitted WITH option is WITH GRANT OPTION.
  
-Proxying requires that the proxy user authenticate through a plugin that returns the name of the proxied user to the server when the proxy user connects, and that the proxy user have the PROXY privilege for the proxied user. For details and examples, see Section 6.3.8, “Proxy Users”.+Proxying requires that the proxy user authenticate through a plugin that returns the name of the proxied user to the server when the proxy user connects, and that the proxy user have the PROXY privilege for the proxied user.  For details and examples, see [[http://dev.mysql.com/doc/refman/5.6/en/proxy-users.html|Section 6.3.8, “Proxy Users”]].
  
 MySQL stores proxy privileges in the **mysql.proxies_priv** table.  MySQL stores proxy privileges in the **mysql.proxies_priv** table. 
  
 +
 +===== Display all user's grants =====
 +
 +<code mysql>
 +mysql -Bse "SELECT CONCAT('SHOW GRANTS FOR \'', user ,'\'@\'', host, '\';') FROM mysql.user" | mysql -Bs | sed 's/$/;/g'
 +</code>
 +
 +
 +===== Possible permissions for a backup user =====
 +
 +<code mysql>
 +mysql> GRANT FILE, SELECT, SHOW VIEW, LOCK TABLES, RELOAD, REPLICATION CLIENT ON
 +*.* TO 'backup_user'@'localhost' IDENTIFIED BY 'some_password';
 +Query OK, 0 rows affected (0.01 sec)
 +
 +mysql> flush privileges;
 +Query OK, 0 rows affected (0.00 sec)
 +</code>
  
 ===== Other examples ===== ===== Other examples =====
Line 102: Line 118:
  
 <code mysql> <code mysql>
-REATE USER 'peter'@'localhost' IDENTIFIED BY 'mypass';+CREATE USER 'peter'@'localhost' IDENTIFIED BY 'mypass';
 GRANT ALL ON db1.* TO 'peter'@'localhost'; GRANT ALL ON db1.* TO 'peter'@'localhost';
 GRANT SELECT ON db2.invoice TO 'peter'@'localhost'; GRANT SELECT ON db2.invoice TO 'peter'@'localhost';
mysql/privileges.1468941810.txt.gz · Last modified: 2020/07/15 09:30 (external edit)
Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki