Permisos de usuarios mysql

Tema:
Gestión de Usuarios
Elaboró: M.I.T.I Ambrosio Cardoso Jiménez
Fecha: 16-Nov-2014
Institución: ITVO

MySql

1. Acceso usuarios del sistema y creación de cuentas de usuario
2. Crear un usuario del sistema
3. Otorgar privilegios al usuario (Comando GRANT)
• Nivel Global
• Nivel de Base de Datos
• Nivel de tabla
• Nivel de rutina
4. Revocación de privilegios de usuario (ComandoRevoke)
5. Cambiar contraseña de cuentas de usuario
Control de Acceso Base de Datos
•

•

Nivel 1: Comprobación de la conexión
◦ Desde dónde se conecta el usuario
◦ Nombre del usuario
◦ Consulta a tabla “user” (host, user, password)
Nivel 2: Comprobación de privilegios
• Por cada petición en la conexión se comprueba si hay privilegios para efectuarla
• Consulta a tablas user, db,tables_priv, colums_priv, procs_priv

[boshy@boshiba ~]$ mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.39-MariaDB MariaDB Server
Copyright (c) 2000, 2014, Oracle, Monty Program Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> SELECT* from mysql.user;
Los campos que se visualizan son:
+------------------------+-----------------------------------+------+-----+---------+-------+
| Field
| Type
| Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+---------+-------+
| Host
| char(60)
| NO | PRI |
|
|
| User
| char(16)
| NO | PRI |
|
|
| Password

|char(41)

| NO |

|

|

|

| Select_priv
| enum('N','Y')
| NO | | N
|
|
| Insert_priv
| enum('N','Y')
| NO | | N
|
|
| Update_priv
| enum('N','Y')
| NO | | N
|
|
| Delete_priv
| enum('N','Y')
| NO | | N
|
|
| Create_priv
| enum('N','Y')
| NO | | N
|
|
| Drop_priv
| enum('N','Y')
| NO | | N
|
|
| Reload_priv
| enum('N','Y')
| NO | | N
|
|
| Shutdown_priv
|enum('N','Y')
| NO | | N
|
|
| Process_priv
| enum('N','Y')
| NO | | N
|
|
| File_priv
| enum('N','Y')
| NO | | N
|
|
| Grant_priv
| enum('N','Y')
| NO | | N
|
|
| References_priv
| enum('N','Y')
| NO | | N
|
|
| Index_priv
| enum('N','Y')
| NO | | N
|
|
| Alter_priv
| enum('N','Y')
| NO | | N
|
|
| Show_db_priv
| enum('N','Y')
| NO | | N
|
|
| Super_priv
|enum('N','Y')
| NO | | N
|
|
| Create_tmp_table_priv | enum('N','Y')
| NO | | N
|
|
| Lock_tables_priv
| enum('N','Y')
| NO | | N
|
|
| Execute_priv
| enum('N','Y')
| NO | | N
|
|
| Repl_slave_priv
| enum('N','Y')
| NO | | N
|
|
| Repl_client_priv
| enum('N','Y')
| NO | | N
|
|
| Create_view_priv
| enum('N','Y')
| NO | | N
|
|
| Show_view_priv
| enum('N','Y')
|NO | | N
|
|
| Create_routine_priv | enum('N','Y')
| NO | | N
|
|
| Alter_routine_priv | enum('N','Y')
| NO | | N
|
|
| Create_user_priv
| enum('N','Y')
| NO | | N
|
|
| Event_priv
| enum('N','Y')
| NO | | N
|
|
| Trigger_priv
| enum('N','Y')
| NO | | N
|
|
| Create_tablespace_priv | enum('N','Y')
| NO | | N
|
|
| ssl_type
| enum('','ANY','X509','SPECIFIED') | NO ||
|
|
| ssl_cipher
| blob
| NO | | NULL |
|
| x509_issuer
| blob
| NO | | NULL |
|
| x509_subject
| blob
| NO | | NULL |
|
| max_questions
| int(11) unsigned
| NO | | 0
|
|
| max_updates
| int(11) unsigned
| NO | | 0
|
|
| max_connections
| int(11) unsigned
| NO | | 0
|
|
| max_user_connections | int(11)
| NO | | 0
|
|
| plugin
| char(64)
| NO | |
|
|
|authentication_string | text
| NO | | NULL |
|
+------------------------+-----------------------------------+------+-----+---------+-------+
En esta tabla (user) y base de datos mysql se guarda la lista de usuarios y los privilegios que tiene y
sobre los objetos a los que tiene acceso
MariaDB [(none)]> select user, host from mysql.user;
+----------+-----------------------+
| user | host
|...