Ediciones ENI Editorial | líder en informática
La seguridad de los datos (DCL)
Introducción
El lenguaje SQL permite asignar derechos a los usuarios a través de comandos DCL (Data Control Language o en castellano lenguaje de control de los datos (LCD)).
En general, los comandos del DCL normalmente los utiliza el DBA. La seguridad de los datos es muy importante en una empresa, por tanto estas funciones se tienen que manejar con precaución.
Este capítulo trata de la configuración de la seguridad de las bases de datos. No es un curso de administración de servidores de bases de datos.
Por qué definir derechos
En una empresa existen tipos de usuarios muy diferentes. Algunos simplemente quieren consultar algunas tablas, otros tienen que insertar y modificar datos, las personas del departamento de informática tendrán necesidades más amplias como crear o eliminar tablas.
Se tiene que poder controlar los accesos a la base de datos y permitir a cada uno realizar sus funciones sin interferir en el trabajo de otra persona.
Antes de asignar derechos a una tabla, hay que clasificar a todos los usuarios de la base de datos en función de su trabajo y determinar para cada uno qué tablas utiliza y para cada una de las tablas indicar si puede actualizar o solo tener acceso de lectura. En general, esta información es parte de un documento de arquitectura general o técnica.
Este trabajo lo deben hacer personas del departamento de informática y los responsables de las aplicaciones (a menudo llamados Gestores de proyecto).
Una vez se ha recabado toda esta información, podemos clasificarlas en una tabla para poder hacer evolucionar esta gestión de los derechos y que pueda mantenerla el administrador de la base de datos u otra persona del departamento de informática dedicada a este trabajo.
Es preferible utilizar un controlador de dominio en el que se defina las cuentas de usuario, miembros de grupo. Se da acceso a las bases de datos, tablas o registros a estos grupos. Los usuarios de SQL generalmente se crean para dedicarse a una aplicación y no a una persona física.
El hecho de usar las tablas en este capítulo va a permitir entender la puesta en marcha de la seguridad en una base de datos.
Por ejemplo
Descripción de un modelo de datos que permite gestionar los derechos de los usuarios.
Modelo de datos utilizado para ilustrar el DCL
TABLA: USUARIOS
Consulta de creación de la tabla:
CREATE TABLE USUARIOS (IDENT_PERS INTEGER,CODIGOACCESO CHAR(8),
APELLIDO VARCHAR(50), NOMBRE VARCHAR(50), DEPARTAMENTO VARCHAR(40),
FECHA_CREACION DATE, ACTIVO CHAR(1)); Consultas de inserción de datos:
INSERT INTO USUARIOS VALUES
(1,'ASMITH','SMITH','ALAN','CORREO',TO_DATE('03/04/2012','DD/MM/
YYYY'),'S');
INSERT INTO USUARIOS VALUES
(2,'GPEROC','PEROC','GEORGES','COMERCIAL',TO_DATE('04/05/2016', ...Crear una conexión
Los SGDBR gestionan de manera diferente sus permisos de acceso, incluso si las sintaxis son similares. Con SQL Server, es necesario crear una conexión al servidor SQL (que se puede crear a partir del diario del controlador de dominio). SQL Server es un motor de bases de datos, independiente del sistema operativo.
Sintaxis SQL Server
CREATE LOGIN <login> WITH PASSWORD = '<Contraseña>'; O creación de la conexión a partir de una cuenta Windows:
CREATE LOGIN <domainName>\<login> WITH PASSWORD = '<Contraseña>'; Ejemplo con SQL Server
Creación de la conexión SQL al servidor a partir de la base de datos ‘master’, que es una base de sistema:
USE master;
GO
CREATE LOGIN ASMITH
WITH PASSWORD = '@Smith.72'; Crear un usuario
Los usuarios suelen ser creados por el administrador de la base de datos según las reglas de seguridad propias de cada empresa. El comando de creación de un usuario es muy simple. Con SQL Server, el usuario se crea a partir de la conexión.
Sintaxis Oracle
CREATE <usuario> IDENTIFIED BY <Contraseña>; Sintaxis MySQL
CREATE '<usuario>' IDENTIFIED BY '<Contraseña>'; Sintaxis SQL Server
CREATE '<user>' FROM LOGIN <login> Sintaxis PostgreSQL
CREATE USER '<user>' Ejemplo en Oracle
CREATE USER ASMITH IDENTIFIED BY ASMITH; Ejemplo en MySQL
CREATE USER 'ASMITH' IDENTIFIED BY 'ASMITH'; Otro método para declarar un usuario en MySQL
GRANT ALL PRIVILEGES ON *.* TO ASMITH@localhost
IDENTIFIED BY 'ASMITH' WITH GRANT OPTION; Ejemplo con SQL Server
USE RESAHOTEL;
GO
CREATE USER ASMITH FROM LOGIN ASMITH; Ejemplo con PostgreSQL
CREATE USER ASMITH; El Sr. Smith podrá conectarse a la base de datos utilizando ASMITH/ ASMITH@<Nombre de la base>.
La creación de un usuario no le permite acceder a las tablas y hacer selecciones. Hay que asignarle derechos específicos en función de su perfil.
Cambiar la contraseña de un usuario
Por razones de seguridad, puede ser necesario modificar la contraseña de un usuario. En la mayor parte de sistemas, es el DBA el que puede utilizar este comando.
Sintaxis Oracle
ALTER USER <usuario> IDENTIFIED BY <Nueva contraseña>; Sintaxis MySQL
SET PASSWORD FOR '<usuario>'@'<host>' = PASSWORD('contraseña'); Sintaxis SQL Server
ALTER LOGIN <user> WITH PASSWORD='mypass'; Sintaxis PostgreSQL
ALTER USER <user> PASSWORD 'mypass'; Ejemplo Oracle
ALTER USER ASMITH IDENTIFIED BY ABCD12E; Ejemplo MySQL
SET PASSWORD FOR ASMITH@localhost=PASSWORD('ABCD12E'); Ejemplo SQL Server
USE [master];
GO
ALTER LOGIN ASMITH WITH PASSWORD='@Smith.50'; Ejemplo PostgreSQL
USE [master];
GO
ALTER USER ASMITH PASSWORD '@Smith.50'; Asignar derechos (GRANT)
1. Asignar derechos de manipulación de una tabla
A partir de algunas de estas tablas de ejemplos, vamos a poder asignar derechos a los usuarios con los comandos GRANT y REVOKE.
La asignación de los derechos está reservada a los creadores de la tabla; no obstante el administrador de la base de datos puede dar autorización a otro usuario para administrar los derechos sobre las tablas.
El comando GRANT permite asignar accesos por usuario sobre una o más tablas.
Los derechos más utilizados son:
-
SELECT: autoriza la selección de datos.
-
UPDATE: autoriza la modificación de datos.
-
DELETE: autoriza la eliminación de datos.
-
INSERT: autoriza la inserción de datos.
La sintaxis es la siguiente
GRANT <derecho1>, <derecho2>, ...
ON TABLE <nombre tabla>
TO <usuario1>, <usuario2> ...
[ WITH GRANT OPTION] Para asignar todos los derechos, hay que utilizar la siguiente sintaxis:
Sintaxis Oracle y PostgreSQL
GRANT ALL PRIVILEGES
ON <tabla1>, <tabla2>, ...
[ WITH GRANT OPTION] TO <usuario1>, <usuario2> ... Sintaxis MySQL
GRANT ALL PRIVILEGES
ON TABLE <tabla1>, <tabla2>, ...
[ WITH GRANT OPTION] TO <usuario1>, <usuario2> ... Ejemplo SQL Server
ALTER AUTHORIZATION ON SCHEMA::[db_owner] TO [ASMITH] El ALL PRIVILEGES o db_owner otorga todos los derechos por lo que se tiene que utilizar con precaución y preferentemente asignarlos a personas de perfil administrador.
La cláusula WITH GRANT OPTION da autorización a los usuarios designados para asignar estos derechos sobre estas tablas a otros usuarios.
Con SQL Server, se asigna el rol db_owner para asignar todos los permisos a una base de datos.
El análisis de las tablas de ejemplos nos permite sacar los elementos que nos indicarán cómo asignar o no derechos a los usuarios con el comando GRANT.
Selección de los derechos por usuario:
SELECT CODIGOACCESO, ATFUNC.IDENT_FUNCT, NOMBRE_TABLA, DERECHOS
FROM USUARIOS HERRAMIENTA
INNER JOIN ATRIB_FUNC ATFUNC ON HERRAMIENTA.IDENT_PERS
ATFUNC.IDENT_PERS
INNER JOIN FUNCIONES FUNC ON ATFUNC.IDENT_FUNCT =
FUNC.IDENT_FUNCT
INNER JOIN ATRIB_DERECHOS ATDERECHO ON FUNC.IDENT_FUNCT =
ATPERMISO.IDENT_FUNCT
INNER JOIN TABLAS TAB ON ATDERECHO.IDENT_TABLA = TAB.IDENT_TABLA ...Prohibir el acceso (DENY)
1. Prohibir el acceso a algunos objetos de la base de datos
Con SQL Server, es posible prohibir el acceso a determinados objetos de la base de datos con la cláusula DENY.
Ejemplo SQL Server
Prohibir al Sr. Peroc la modificación de la tabla ATRIB_PERMISOS:
DENY ALTER ON ATRIB_PERMISOS TO GPEROC Prohibir todo al Sr. Peroc sobre la tabla ATRIB_PERMISOS:
DENY VIEW DEFINITION ON ATRIB_PERMISOS TO GPEROC
DENY VIEW CHANGE TRACKING ON ATRIB_PERMISOS TO GPEROC
DENY CONTROL ON ATRIB_PERMISOS TO GPEROC
DENY INSERT ON ATRIB_PERMISOS TO GPEROC
DENY UPDATE ON ATRIB_PERMISOS TO GPEROC
DENY ALTER ON ATRIB_PERMISOS TO GPEROC
DENY TAKE OWNERSHIP ON ATRIB_PERMISOS TO GPEROC
DENY REFERENCES ON ATRIB_PERMISOS TO GPEROC
DENY SELECT ON ATRIB_PERMISOS TO GPEROC
DENY DELETE ON ATRIB_PERMISOS TO GPEROC Eliminar derechos (REVOKE)
1. Eliminar derechos sobre la manipulación de una tabla
Una vez asignados los derechos, hay que poder quitarlos si el usuario abandona la empresa o cambia de departamento. Para ello, utilizaremos el comando REVOKE.
No confunda DENY, que prohíbe, con REVOKE, que elimina un derecho sin prohibir el acceso.
La sintaxis Oracle es la siguiente
REVOKE <derecho1>, <derecho2>, ...
ON TABLE <nombre tabla>
FROM <usuario1>, <usuario2> ...; La sintaxis MySQL es la siguiente
REVOKE <derecho1>, <derecho2>, ...
ON [TABLE <nombre tabla>],[*]
FROM <usuario1>, <usuario2> ...; Por ejemplo, para quitar los derechos de lectura de la tabla USUARIOS a SMITH:
REVOKE SELECT ON USUARIOS FROM ASMITH; Para quitar todos los derechos a Smith:
REVOKE ALL PRIVILEGES ON USUARIOS FROM ASMITH; Del mismo modo que el GRANT, si añadimos la palabra PUBLIC, se eliminan los derechos sobre esta tabla para todos los usuarios de la base de datos.
REVOKE ALL PRIVILEGES ON USUARIOS FROM PUBLIC; 2. Eliminar derechos sobre los objetos de la base
Como para la manipulación de una tabla, se pueden eliminar derechos que se han asignado para la creación de tablas o de índices.
Ejemplo Oracle
REVOKE CREATE TABLE FROM ASMITH; Ejemplo MySQL
REVOKE CREATE ON * ON 'ASMITH'; Para eliminar los derechos de modificación, de eliminación y de creación de tablas en...
Utilización de los roles
Un rol es un grupo al que se le van a asignar los mismos derechos. En lugar de asignar los derechos individualmente a cada usuario, se pueden crear grupos que tendrán los derechos y a continuación asignar los usuarios a un grupo.
Este concepto de rol no existe actualmente en MySQL.
Sintaxis Oracle
CREATE ROLE <nombre rol>; Ejemplo Oracle
CREATE ROLE CONTROL_GESTION; A continuación, hay que asignar derechos al rol que acabamos de crear de la misma manera que para un usuario x.
GRANT ALL PRIVILEGES ON CLIENTES TO CONTROL_GESTION;
GRANT ALL PRIVILEGES ON PROVEEDORES TO CONTROL_GESTION;
GRANT ALL PRIVILEGES ON FACTURAS TO CONTROL_GESTION; Para SQL Server
GRANT UPDATE ON PROVEEDOR TO CONTROL_GESTION A continuación asignamos este rol a los usuarios:
GRANT CONTROL_GESTION TO ASMITH;
GRANT CONTROL_GESTION TO BMARTIN;
etc ...
exec sp_addrolemember [CONTROL_GESTION], ASMITH; El concepto de rol es bastante interesante en sitios con muchos usuarios, ya que permite simplificar la gestión de los diferentes perfiles.
Se deben definir los diferentes roles en la utilización de la base de datos y en las aplicaciones existentes y a continuación clasificar los usuarios por rol. A cada persona nueva que llegue, tan solo hay que crear el usuario y asignarle un rol.
También existen roles definidos en Oracle que son CONNECT, DBA o RESOURCE pero están reservados...
Eliminar un rol
Para SQL Server, es necesario eliminar los miembros del rol antes de eliminar este último.
Sintaxis SQL Server
ALTER ROLE <nombre rol> DROP MEMBER <user>; Ejemplo SQL Server
ALTER ROLE CONTROL_GESTION DROP MEMBER ASMITH; Sintaxis
DROP ROLE <nombre rol>; Ejemplo
DROP ROLE CONTROL_GESTION; Ejercicios
Primer ejercicio
Crear un usuario ALFREDO y asignarle los derechos de crear una sesión y de seleccionar datos de la tabla CASTING.
Segundo ejercicio
Asignar a todos los usuarios el derecho de seleccionar datos de la tabla PELICULA.
Tercer ejercicio
Asignar derechos al usuario ALFREDO para que pueda modificar en la tabla PELICULA solo las columnas TITULO y SINOPSIS.
Cuarto ejercicio
Eliminar para el usuario ALFREDO los derechos sobre la modificación de la columna SINOPSIS.
Soluciones de los ejercicios
Primer ejercicio
Crear el usuario:
CREATE USER ALFREDO IDENTIFIED BY ALFREDO; (Oracle)
CREATE USER ALFREDO WITH PASSWORD (Postgresql);
Syntaxe SQL Server
USE master;
GO
CREATE LOGIN ALFREDO
WITH PASSWORD = 'SqlServer.72';
USE CINEMA
Go
CREATE USER ALFREDO FROM LOGIN ALFREDO; Autorizarlo a conectarse:
GRANT CREATE SESSION TO ALFREDO; Permitirle seleccionar:
GRANT SELECT ON CASTING TO ALFREDO; Segundo ejercicio
Utilización de la palabra PUBLIC:
GRANT SELECT ON PELICULA TO PUBLIC; Tercer ejercicio
Asignación de derechos:
GRANT UPDATE (TITULO,SINOPSIS) ON PELICULA TO ALFREDO; Si ahora el usuario ALFREDO prueba a modificar otra columna, habrá un mensaje de error indicándole que no tiene derechos:
UPDATE PELICULA SET GENERO1='Pepe' WHERE IDENT PELICULA =1
*
ERROR en la fila 1:
ORA-01031: privilegios insuficientes Por el contrario, en la columna TITULO, sí puede:
UPDATE TITULO SET TITULO='SUBWAY' WHERE IDENT =1 ;
1 fila(s) se ha actualizado. Cuarto ejercicio
Eliminación de los derechos UPDATE sobre la columna TITULO.
No se pueden eliminar los derechos solo sobre una columna, el REVOKE se aplica sobre todas las columnas.
REVOKE UPDATE ON PELICULA FROM ALFREDO; A continuación deberá...