Privileges Provided by MariaDB

MySQL provides privileges that apply in different contexts and at different levels of operation:

• Administrative privileges enable users to manage operation of the MariaDB server. These privileges are global because they are not specific to a particular database.
• Database privileges apply to a database and to all objects within it. These privileges can be granted for specific databases, or globally so that they apply to all databases.
• Privileges for database objects such as tables, indexes, views, and stored routines can be granted for specific objects within a database, for all objects of a given type within a database (for example, all tables in a database), or globally for all objects of a given type in all databases).

Information about account privileges is stored in the user, db, host, tables_priv, columns_priv, and procs_priv tables in the MariaDB database (see , "Privilege System Grant Tables"). The MariaDB server reads the contents of these tables into memory when it starts and reloads them under the circumstances indicated in , "When Privilege Changes Take Effect". Access-control decisions are based on the in-memory copies of the grant tables.

Some releases of MariaDB introduce changes to the structure of the grant tables to add new access privileges or features. Whenever you update to a new version of MySQL, you should update your grant tables to make sure that they have the current structure so that you can take advantage of any new capabilities. See , "mysql_upgrade - Check Tables for MariaDB Upgrade".

The following table shows the privilege names used at the SQL level in the GRANT and REVOKE statements, along with the column name associated with each privilege in the grant tables and the context in which the privilege applies.

Table 5.6. Permissible Privileges for GRANT and REVOKE

Privilege	Column	Context
CREATE	Create_priv	databases, tables, or indexes
DROP	Drop_priv	databases, tables, or views
GRANT OPTION	Grant_priv	databases, tables, or stored routines
LOCK TABLES	Lock_tables_priv	databases
REFERENCES	References_priv	databases or tables
EVENT	Event_priv	databases
ALTER	Alter_priv	tables
DELETE	Delete_priv	tables
INDEX	Index_priv	tables
INSERT	Insert_priv	tables or columns
SELECT	Select_priv	tables or columns
UPDATE	Update_priv	tables or columns
CREATE TEMPORARY TABLES	Create_tmp_table_priv	tables
TRIGGER	Trigger_priv	tables
CREATE VIEW	Create_view_priv	views
SHOW VIEW	Show_view_priv	views
ALTER ROUTINE	Alter_routine_priv	stored routines
CREATE ROUTINE	Create_routine_priv	stored routines
EXECUTE	Execute_priv	stored routines
FILE	File_priv	file access on server host
CREATE TABLESPACE	Create_tablespace_priv	server administration
CREATE USER	Create_user_priv	server administration
PROCESS	Process_priv	server administration
PROXY	see proxies_priv table	server administration
RELOAD	Reload_priv	server administration
REPLICATION CLIENT	Repl_client_priv	server administration
REPLICATION SLAVE	Repl_slave_priv	server administration
SHOW DATABASES	Show_db_priv	server administration
SHUTDOWN	Shutdown_priv	server administration
SUPER	Super_priv	server administration
ALL [PRIVILEGES]		server administration
USAGE		server administration

The following list provides a general description of each privilege available in MySQL. Particular SQL statements might have more specific privilege requirements than indicated here. If so, the description for the statement in question provides the details.

• The ALL or ALL PRIVILEGES privilege specifier is shorthand. It stands for "all privileges available at a given privilege level" (except GRANT OPTION). For example, granting ALL at the global or table level grants all global privileges or all table-level privileges.
• The ALTER privilege enables use of ALTER TABLE to change the structure of tables. ALTER TABLE also requires the CREATE and INSERT privileges. Renaming a table requires ALTER and DROP on the old table, ALTER, CREATE, and INSERT on the new table.
• The ALTER ROUTINE privilege is needed to alter or drop stored routines (procedures and functions).
• The CREATE privilege enables creation of new databases and tables.
• The CREATE ROUTINE privilege is needed to create stored routines (procedures and functions).
• The CREATE TABLESPACE privilege is needed to create, alter, or drop tablespaces and log file groups.
• The CREATE TEMPORARY TABLES privilege enables the creation of temporary tables using the CREATE TEMPORARY TABLE statement.

  As of MariaDB 5.6.3, after a session has created a temporary table, the server performs no further privilege checks on the table. The creating session can perform any operation on the table, such as DROP TABLE, INSERT, UPDATE, or SELECT.

  One implication of this behavior is that a session can manipulate its temporary tables even if the current user has no privilege to create them. Suppose that the current user does not have the CREATE TEMPORARY TABLES privilege but is able to execute a DEFINER-context stored procedure that executes with the privileges of a user who does have CREATE TEMPORARY TABLES and that creates a temporary table. While the procedure executes, the session uses the privileges of the defining user. After the procedure returns, the effective privileges revert to those of the current user, which can still see the temporary table and perform any operation on it.

  Before MariaDB 5.6.3, other operations on a temporary table, such as INSERT, UPDATE, or SELECT, require additional privileges for those operations for the database containing the temporary table, or for the nontemporary table of the same name.

  To keep privileges for temporary and nontemporary tables separate, a common workaround for this situation is to create a database dedicated to the use of temporary tables. Then for that database, a user can be granted the CREATE TEMPORARY TABLES privilege, along with any other privileges required for temporary table operations done by that user.

• The CREATE USER privilege enables use of CREATE USER, DROP USER, RENAME USER, and REVOKE ALL PRIVILEGES.
• The CREATE VIEW privilege enables use of CREATE VIEW.
• The DELETE privilege enables rows to be deleted from tables in a database.
• The DROP privilege enables you to drop (remove) existing databases, tables, and views. The DROP privilege is required in order to use the statement ALTER TABLE ... DROP PARTITION on a partitioned table. The DROP privilege is also required for TRUNCATE TABLE. If you grant the DROP privilege for the MariaDB database to a user, that user can drop the database in which the MariaDB access privileges are stored.
• The EVENT privilege is required to create, alter, drop, or see events for the Event Scheduler.
• The EXECUTE privilege is required to execute stored routines (procedures and functions).
• The FILE privilege gives you permission to read and write files on the server host using the LOAD DATA INFILE and SELECT ... INTO OUTFILE statements and the LOAD_FILE() function. A user who has the FILE privilege can read any file on the server host that is either world-readable or readable by the MariaDB server. (This implies the user can read any file in any database directory, because the server can access any of those files.) The FILE privilege also enables the user to create new files in any directory where the MariaDB server has write access. As a security measure, the server will not overwrite existing files.
• The GRANT OPTION privilege enables you to give to other users or remove from other users those privileges that you yourself possess.
• The INDEX privilege enables you to create or drop (remove) indexes. INDEX applies to existing tables. If you have the CREATE privilege for a table, you can include index definitions in the CREATE TABLE statement.
• The INSERT privilege enables rows to be inserted into tables in a database. INSERT is also required for the ANALYZE TABLE, OPTIMIZE TABLE, and REPAIR TABLE table-maintenance statements.
• The LOCK TABLES privilege enables the use of explicit LOCK TABLES statements to lock tables for which you have the SELECT privilege. This includes the use of write locks, which prevents other sessions from reading the locked table.
• The PROCESS privilege pertains to display of information about the threads executing within the server (that is, information about the statements being executed by sessions). The privilege enables use of SHOW PROCESSLIST or mysqladmin processlist to see threads belonging to other accounts; you can always see your own threads.
• The PROXY privilege enables a user to impersonate or become known as another user. See , "Proxy Users".
• The REFERENCES privilege currently is unused.
• The RELOAD privilege enables use of the FLUSH statement. It also enables mysqladmin commands that are equivalent to FLUSH operations: flush-hosts, flush-logs, flush-privileges, flush-status, flush-tables, flush-threads, refresh, and reload.

  The reload command tells the server to reload the grant tables into memory. flush-privileges is a synonym for reload. The refresh command closes and reopens the log files and flushes all tables. The other flush-xxx commands perform functions similar to refresh, but are more specific and may be preferable in some instances. For example, if you want to flush just the log files, flush-logs is a better choice than refresh.

• The REPLICATION CLIENT privilege enables the use of SHOW MASTER STATUS and SHOW SLAVE STATUS.
• The REPLICATION SLAVE privilege should be granted to accounts that are used by slave servers to connect to the current server as their master. Without this privilege, the slave cannot request updates that have been made to databases on the master server.
• The SELECT privilege enables you to select rows from tables in a database. SELECT statements require the SELECT privilege only if they actually retrieve rows from a table. Some SELECT statements do not access tables and can be executed without permission for any database. For example, you can use SELECT as a simple calculator to evaluate expressions that make no reference to tables:

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

  The SELECT privilege is also needed for other statements that read column values. For example, SELECT is needed for columns referenced on the right hand side of col_name=expr assignment in UPDATE statements or for columns named in the WHERE clause of DELETE or UPDATE statements.

• The SHOW DATABASES privilege enables the account to see database names by issuing the SHOW DATABASE statement. Accounts that do not have this privilege see only databases for which they have some privileges, and cannot use the statement at all if the server was started with the --skip-show-database option. Note that any global privilege is a privilege for the database.
• The SHOW VIEW privilege enables use of SHOW CREATE VIEW.
• The SHUTDOWN privilege enables use of the mysqladmin shutdown command. There is no corresponding SQL statement.
• The SUPER privilege enables an account to use CHANGE MASTER TO, KILL or mysqladmin kill to kill threads belonging to other accounts (you can always kill your own threads), PURGE BINARY LOGS, configuration changes using SET GLOBAL to modify global system variables, the mysqladmin debug command, enabling or disabling logging, performing updates even if the read_only system variable is enabled, starting and stopping replication on slave servers, specification of any account in the DEFINER attribute of stored programs and views, and enables you to connect (once) even if the connection limit controlled by the max_connections system variable is reached.

  To create or alter stored functions if binary logging is enabled, you may also need the SUPER privilege, as described in , "Binary Logging of Stored Programs".

• The TRIGGER privilege enables trigger operations. You must have this privilege for a table to create, drop, or execute triggers for that table.
• The UPDATE privilege enables rows to be updated in tables in a database.
• The USAGE privilege specifier stands for "no privileges." It is used at the global level with GRANT to modify account attributes such as resource limits or SSL characteristics without affecting existing account privileges.

It is a good idea to grant to an account only those privileges that it needs. You should exercise particular caution in granting the FILE and administrative privileges:

• The FILE privilege can be abused to read into a database table any files that the MariaDB server can read on the server host. This includes all world-readable files and files in the server's data directory. The table can then be accessed using SELECT to transfer its contents to the client host.
• The GRANT OPTION privilege enables users to give their privileges to other users. Two users that have different privileges and with the GRANT OPTION privilege are able to combine privileges.
• The ALTER privilege may be used to subvert the privilege system by renaming tables.
• The SHUTDOWN privilege can be abused to deny service to other users entirely by terminating the server.
• The PROCESS privilege can be used to view the plain text of currently executing statements, including statements that set or change passwords.
• The SUPER privilege can be used to terminate other sessions or change how the server operates.
• Privileges granted for the MariaDB database itself can be used to change passwords and other access privilege information. Passwords are stored encrypted, so a malicious user cannot simply read them to know the plain text password. However, a user with write access to the user table Password column can change an account's password, and then connect to the MariaDB server using that account.

Retornar