CREAR PRIVILEGIOS:
use mysql;
CREATE USER 'usuario'@'localhost' IDENTIFIED BY '123456789';
GRANT ALL PRIVILEGES ON * . * TO 'usuario'@'localhost' IDENTIFIED BY '123456789' WITH GRANT OPTION;
CREATE USER 'usuario'@'%' IDENTIFIED BY '123456789';
GRANT ALL PRIVILEGES ON * . * TO 'usuario'@'%' IDENTIFIED BY '123456789' WITH GRANT OPTION;
all-> todos los privilegios
with grant option -> incluye los privilegios grant
%-> correr en cualquier host(servidor)
localhost->correr en forma de red local lan
IDENTIFIED BY -> indica que lo manejara solo con la clave especifica
all es equivalenta a poner :
SELECT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER,
CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE,REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE,CREATE USER, EVENT
Para los tigger poner al lado de los otros privilegios separandolo con coma(,) porque execute solo da privilegio a procedure y function:
TRIGGER ON *.* TO 'usuario'@'localhost' IDENTIFIED BY '123456789'
LISTAR PRIVILEGIOS DE USUARIOS
SELECT password, host, user,
CONCAT(Select_priv, Lock_tables_priv) AS selock,
CONCAT(Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv) AS modif,
CONCAT(Grant_priv, References_priv, Index_priv, Alter_priv) AS meta,
CONCAT(Create_tmp_table_priv, Create_view_priv, Show_view_priv) AS views,
CONCAT(Create_routine_priv, Alter_routine_priv, Execute_priv) AS funcs,
CONCAT(Repl_slave_priv, Repl_client_priv) AS replic,
CONCAT(Super_priv, Shutdown_priv, Process_priv, File_priv, Show_db_priv, Reload_priv) AS admin
FROM USER ORDER BY user, host;
APARECE ALGO ASI:
+----------------------------------+-------------+---------+--------+----------+--------+--------+--------+--------+------------+
| password | host | user | selock | modif | meta | views | funcs | replic | admin |
+----------------------------------+-------------+---------+--------+----------+--------+--------+--------+--------+------------+
| *......... | localhost | backup| YY | NNNNN | NNNN | NNN | NNN | NN | NNNNNN |
| *......... | localhost | nagios | XX | NNNNN | NNNN | NNN | NNN | NN | NNNNNN |
| *......... | 127.0.0.1 | root | YY | YYYYY | YYYY | YYY | YYY | YY | YYYYYY |
| *......... | localhost | root | YY | YYYYY | YYYY | YYY | YYY | YY | YYYYYY |
| | localhost | wheel | NY | NNNNN | NNNN | NNN | NNN | NN | NNNNNY |
+----------------------------------+-------------+---------+--------+----------+--------+--------+-------+--------+-----------+
