PDF de programación - Gestión de Usuarios en MySQL

Gestión de Usuarios

en MySQL

Gestión de Bases de Datos

LUIS MENGUAL (c)

Gestión de Usuarios

en MySQL
Objetivos:

• Crear cuentas de usuario en MySQL
• Describir los niveles de privilegios de usuarios

en Mysql (Comandos GRANT/REVOKE)

• Asignar y actualizar contraseñas de usuarios
• Presentar ejemplos prácticos

Gestión de Bases de Datos

LUIS MENGUAL (c)

Índice

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 (Comando
Revoke)
5. Cambiar contraseña de cuentas de usuario

Gestión de Bases de Datos

LUIS MENGUAL (c)

Control de Acceso BD

• 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

Gestión de Bases de Datos

LUIS MENGUAL (c)

Usuarios del sistema (II)

SELECT * from mysql.user;

Gestión de Bases de Datos

LUIS MENGUAL (c)

Usuarios del sistema (I)

Gestión de Bases de Datos

LUIS MENGUAL (c)

Comando GRANT

GRANT ALL PRIVILEGES ON *.* TO user1@'localhost' IDENTIFIED BY ‘user1'
WITH GRANT OPTION {REQUIRE (…)};

Comando que permite 'conceder' privilegios a un usuario
• ALL PRIVILEGES: se conceden todos los privilegios a este usuario. Los posibles

privilegios: SELECT, INSERT, UPDATE, DELETE, CREATE, DROP,REFERENCES,
INDEX, ALTER, CREATE_TMP_TABLE, LOCK_TABLES,
CREATE_VIEW,SHOW_VIEW, CREATE_ROUTINE, ALTER_ROUTINE, EXECUTE
y GRANT.

• ON: los objetos a los que se aplican los privilegios, el formato es

base_de_datos.tabla, *.* Otros ejemplos: ventas.*, contabilidad.polizas,

• TO: el usuario al que se le conceden los privilegios, el formato es

usuario@'equipo‘. Otros ejemplos: user1@'%', sergio@'192.168.10.132‘
IDENTIFIED BY: la contraseña se indica en esta parte y se escribe en texto
plano.

•

•

• WITH GRANT OPTION: esta última parte es opcional, e indica que el usuario en

cuestión puede a la vez otorgar privilegios a otros usuarios
REQUIRE: Opciones de seguridad en el acceso relacionadas con SSL

Gestión de Bases de Datos

LUIS MENGUAL (c)

Crear un usuario (I)

GRANT all privileges on *.* to user1@'localhost' identified by 'user1' with grant option;
SHOW GRANTS FOR user1@localhost;

Gestión de Bases de Datos

LUIS MENGUAL (c)

Crear un usuario (II)

SELECT * from mysql.user;

Gestión de Bases de Datos

LUIS MENGUAL (c)

Crear un usuario (III)

SELECT * from cdcol.cds;

Gestión de Bases de Datos

LUIS MENGUAL (c)

Comando REVOKE

REVOKE priv_type [(column_list)] [, priv_type [(column_list)]]
...
ON [object_type] {tbl_name | * | *.* | db_name.*}
FROM user [, user] ...

• Permite revocar los privilegios del usuario otorgados con el

comando GRANT

• La revocación se hará al mismo nivel de privilegios otorgados

con el comando GRANT

Gestión de Bases de Datos

LUIS MENGUAL (c)

Revocar privilegios (I)

REVOKE all privileges on *.* FROM user1@'localhost' identified by 'user1';
SHOW GRANTS FOR user1@localhost;

Gestión de Bases de Datos

LUIS MENGUAL (c)

Revocar privilegios (II)

SELECT * from mysql.user;

Gestión de Bases de Datos

LUIS MENGUAL (c)

Revocar privilegios (III)

SELECT * from cdcol.cds;

Gestión de Bases de Datos

LUIS MENGUAL (c)

Eliminar un usuario (I)

DROP USER user1@localhost;

Gestión de Bases de Datos

LUIS MENGUAL (c)

Eliminar un usuario (II)

SELECT * from mysql.user

Gestión de Bases de Datos

LUIS MENGUAL (c)

Niveles Privilegios

• Global
• De Base de Datos
• De Tabla
• De Columna
• De Rutina

Gestión de Bases de Datos

LUIS MENGUAL (c)

Privilegios Nivel Global

• Los permisos globales se aplican a todas las

bases de datos de un servidor dado.

• Estos privilegios son almacenados en la

tabla “mysql.user”

• GRANT ALL ON *.* y REVOKE ALL ON *.*

otorgan y quitan sólo permisos globales.

GRANT all privileges on *.* to user1@'localhost' identified by 'user1' with grant option;

REVOKE all privileges on *.* FROM user1@'localhost' identified by 'user1';

Gestión de Bases de Datos

LUIS MENGUAL (c)

Privilegios Nivel de Base de Datos (I)

• Los permisos de base de datos se aplican a todos

los objetos en una base de datos dada

• Estos permisos se almacenan en las tablas

“mysql.db”

• GRANT ALL ON db_name.* y REVOKE ALL ON

db_name.* otorgan y quitan sólo permisos de
bases de datos.

GRANT all privileges on cdcol.* to user1@'localhost' identified by 'user1' with grant option;

REVOKE all privileges on cdcol.* FROM user1@'localhost' identified by 'user1';

Gestión de Bases de Datos

LUIS MENGUAL (c)

Privilegios Nivel de Base de Datos (II)

GRANT all privileges on cdcol.* to user1@'localhost' identified by 'user1' with grant option;
SHOW GRANTS FOR user1@localhost;

Gestión de Bases de Datos

LUIS MENGUAL (c)

Privilegios Nivel de Base de Datos (III)

SELECT * from cdcol.cds;

Gestión de Bases de Datos

LUIS MENGUAL (c)

Privilegios Nivel de Base de Datos (IV)

Gestión de Bases de Datos

LUIS MENGUAL (c)

Privilegios Nivel de Base de Datos (V)

REVOKE all privileges on cdcol.* FROM user1@'localhost' identified by 'user1';
SHOW GRANTS FOR user1@localhost;

Gestión de Bases de Datos

LUIS MENGUAL (c)

Privilegios Nivel de Base de Datos (VI)

SELECT * from cdcol.cds;

Gestión de Bases de Datos

LUIS MENGUAL (c)

Privilegios Nivel de Tabla (I)

• Los permisos de tabla se aplican a todas las

columnas en una tabla dada

• Estos permisos se almacenan en la tabla

mysql.tables_priv

• GRANT ALL ON db_name.tbl_name y REVOKE

ALL ON db_name.tbl_name otorgan y quitan
permisos sólo de tabla.

GRANT all privileges on cdcol.cds to user1@'localhost' identified by 'user1' with grant option;

REVOKE all privileges on cdcol.cds FROM user1@'localhost' identified by 'user1';

Gestión de Bases de Datos

LUIS MENGUAL (c)

Privilegios Nivel de Tabla (II)

GRANT all privileges on cdcol.cds to user1@'localhost' identified by 'user1' with grant option;

SHOW GRANTS FOR user1@localhost;

Gestión de Bases de Datos

LUIS MENGUAL (c)

Privilegios Nivel de Tabla (III)

SELECT * from cdcol.cds;

Gestión de Bases de Datos

LUIS MENGUAL (c)

Privilegios Nivel de Tabla (IV)

Gestión de Bases de Datos

LUIS MENGUAL (c)

Privilegios Nivel de Tabla (V)

REVOKE all privileges on cdcol.cds FROM user1@'localhost' identified by 'user1';
SHOW GRANTS FOR user1@localhost;

Gestión de Bases de Datos

LUIS MENGUAL (c)

Privilegios Nivel de Tabla (VI)

SELECT * from cdcol.cds;

Gestión de Bases de Datos

LUIS MENGUAL (c)

Privilegios Nivel de Columna (I)

• Los permisos de columna se aplican a columnas en

una tabla dada

• Estos permisos se almacenanen la tabla

“mysql.columns_priv”

• Usando REVOKE, debe especificar las mismas

columnas que se otorgaron los permisos

GRANT SELECT(titel,interpret) on cdcol.cds to user1@'localhost' identified by 'user1' with grant option;

REVOKE SELECT(titel,interpret) on cdcol.cds FROM user1@'localhost' identified by 'user1';

Gestión de Bases de Datos

LUIS MENGUAL (c)

Privilegios Nivel de Columna (II)

GRANT SELECT(titel,interpret) on cdcol.cds to user1@'localhost' identified by 'user1' with grant option;
SHOW GRANTS FOR user1@localhost;

Gestión de Bases de Datos

LUIS MENGUAL (c)

Privilegios Nivel de Columna (III)

SELECT * from cdcol.cds;

Gestión de Bases de Datos

LUIS MENGUAL (c)

Privilegios Nivel de Columna (IV)

SELECT titel,interpret from cdcol.cds;

Gestión de Bases de Datos

LUIS MENGUAL (c)

Privilegios Nivel de Columna (V)

Gestión de Bases de Datos

LUIS MENGUAL (c)

Privilegios Nivel de Columna (VI)

REVOKE SELECT(titel,interpret) on cdcol.cds FROM user1@'localhost' identified by 'user1';

SHOW GRANTS FOR user1@localhost;

Gestión de Bases de Datos

LUIS MENGUAL (c)

Privilegios Nivel de Columna (VII)

SELECT titel,interpret from cdcol.cds;

Gestión de Bases de Datos

LUIS MENGUAL (c)

Privilegios Nivel de Rutina (I)

DROP SCHEMA IF EXISTS TablasFecha3;
CREATE SCHEMA TablasFecha3;
USE TablasFecha3;

create table Cliente(

Dni VARCHAR(10),
Nombre VARCHAR(10),
Apellido VARCHAR(10),
PRIMARY KEY (Dni)
)ENGINE=InnoDB;

create table Pedidos(

npedido INTEGER,
fecha DATE,
Cantidad DOUBLE,
cliente_Dni VARCHAR(10),
PRIMARY KEY (npedido),
FOREIGN KEY (cliente_Dni) REFERENCES Cliente(Dni)
)ENGINE=InnoDB;

Gestión de Bases de Datos

LUIS MENGUAL (c)

Privilegios Nivel de Rutina (II)

delimiter //
CREATE PROCEDURE `tabla_fecha`( )
BEGIN

DECLARE Fecha_Actual VARCHAR(50);
SET Fecha_Actual=DATE_FORMAT(CURRENT_TIMESTAMP, '%d_%m_%Y_%H%i');

SET @t=CONCAT('Tabla_', Fecha_Actual);
SET @q=CONCAT('CREATE TABLE ',@t,' (i INT)');
PREPARE S1 FROM @q;
EXECUTE S1;
END;
//

delimiter ;

INSERT INTO Cliente VALUES ('7211545v','Carlos','Martinez Lopez');

INSERT INTO Pedidos VALUES ('122','2010/01/05',7,'7211545v');

Gestión de Bases de Datos

LUIS MENGUAL (c)

Privilegios Nivel de Rutina (III)

SHOW PROCEDURE STATUS like 'tabla_fecha';

Gestión de Bases de Datos

LUIS MENGUAL (c)

Privilegios Nivel de Rutina (V)

CALL TablasFecha3.tabla_fecha();

Gestión de Bases de Datos

LUIS MENGUAL (c)

Privilegios Nivel de Rutina (VI)

CREATE USER user1@'localhost' identified by 'user1';
SHOW GRANTS FOR user1@localhost;

Gestión de Bases de Datos

LUIS MENGUAL (c)

Privilegios Nivel de Rutina (VII)

CALL TablasFecha3.tabla_fecha();

Gestión de Bases de Datos

LUIS MENGUAL (c)

Privilegios Nivel de Rutina (VIII)

GRANT EXECUTE ON PROCEDURE TablasFecha3.tabla_fecha TO user1@'localhost';
SHOW GRANTS FOR user1@localhost;

Gestión de Bases de Datos

LUIS MENGUAL (c)

Privilegios Nivel de Rutina (IX)

CALL TablasFecha3.tabla_fecha();

Gestión de Bases de Datos

LUIS MENGUAL (c)

Privilegios Nivel de Rutina (X)

Gestión de Bases d