3. Sentencias de control

3. Sentencias de control Dataprix 24 Septiembre, 2009 - 11:10

Además de definir y manipular una base de datos relacional, es importante que se establezcan mecanismos de control para resolver problemas de concurrencia de usuarios y garantizar la seguridad de los datos. Para la concurrencia de usuarios utilizaremos el concepto de transacción, y para la seguridad veremos cómo se puede autorizar y desautorizar a usuarios a acceder a la base de datos.

3.1. Las transacciones

3.1. Las transacciones Dataprix 24 Septiembre, 2009 - 11:17
Una transacción es una unidad lógica de trabajo. O informalmente, y
trabajando con SQL, un conjunto de sentencias que se ejecutan como si fuesen una sola. En general, las sentencias que forman parte de una
transacción se interrelacionan entre sí, y no tiene sentido que se ejecute una sin que se ejecuten las demás.

La mayoría de las transacciones se inician de forma implícita al utilizar alguna sentencia  que  empieza  con  CREATE, ALTER, DROP, SET, DECLARE,  GRANT o REVOKE, aunque existe la sentencia SQL para iniciar transacciones, que es la siguiente:

SET TRANSACTION {READ ONLY|READ WRITE};

Si queremos actualizar la base de datos utilizaremos la opción READ WRITE, y si no la queremos actualizar, elegiremos la opción READ ONLY.

Sin embargo, en cambio, una transacción siempre debe acabar explícitamente con alguna de las sentencias siguientes:

{COMMIT|ROLLBACK} [WORK];

La diferencia entre COMMIT y ROLLBACK es que mientras la sentencia COMMIT confirma todos los cambios producidos contra la BD durante la ejecución de la transacción, la sentencia ROLLBACK deshace todos los cambios que se hayan producido en la base de datos y la deja como estaba antes del inicio de nuestra transacción.

La palabra reservada WORK sólo sirve para aclarar lo que hace la sentencia, y es totalmente opcional.

Ejemplo de transacción

A continuación proponemos un ejemplo de transacción en el que se quiere disminuir el sueldo de los empleados que han trabajado en el proyecto 3 en 1.000 euros. y aumentar el sueldo de los empleados que han trabajado en el proyecto 1 también en 1.000 euros.

SET TRANSACTION READ WRITE;
UPDATE empleados SET sueldo = sueldo – 1000 WHERE num_proyec = 3;
UPDATE empleados SET sueldo = sueldo + 1000 WHERE num_proyec = 1;
COMMIT;

3.2. Las autorizaciones y desautorizaciones

3.2. Las autorizaciones y desautorizaciones Dataprix 24 Septiembre, 2009 - 11:25

Todos los privilegios sobre la base de datos los tiene su propietario, pero no es el único que accede a ésta. Por este motivo, el SQL nos ofrece sentencias para autorizar y desautorizar a otros usuarios.

1)  Autorizaciones

Para autorizar, el SQL dispone de la siguiente sentencia:

GRANT privilegios ON objeto TO usuarios
[WITH GRANT OPTION];

Donde tenemos que:

a)  privilegios puede ser:

•    ALL PRIVILEGES: todos los privilegios sobre el objeto especificado.

•    USAGE: utilización del objeto especificado; en este caso el dominio.

•    SELECT: consultas.

•    INSERT [(columnas)]: inserciones. Se puede concretar de qué columnas.

•    UPDATE [(columnas)]: modificaciones. Se puede concretar de qué columnas.

•    DELETE: borrados.

•    REFERENCES [(columna)]: referencia del objeto en restricciones de integridad. Se puede concretar de qué columnas.

b)  Objeto debe ser:

•    DOMAIN: dominio

•    TABLE: tabla.

•    Vista.

c)  Usuarios puede ser todo el mundo: PUBLIC, o bien una lista de los identificadores de los usuarios que queremos autorizar.

d)  La opción WITH GRANT OPTION permite que el usuario que autoricemos pueda, a su vez, autorizar a otros usuarios a acceder al objeto con los mismos privilegios con los que ha sido autorizado.

2)  Desautorizaciones

Para desautorizar, el SQL dispone de la siguiente sentencia:

REVOKE [GRANT OPTION FOR] privilegios ON objeto FROM
usuarios [RESTRICT|CASCADE];

Donde tenemos que:

a)  privilegios, objeto y usuarios son los mismos que para la sentencia GRANT.

b)  La opción GRANT OPTION FOR se utilizaría en el caso de que quisiéramos eliminar el derecho a autorizar (WITH GRANT OPTION).

c)  Si un usuario al que hemos autorizado ha autorizado a su vez a otros, que al mismo tiempo pueden haber hecho más autorizaciones, la opción CASCADE hace que queden desautorizados todos a la vez.

d)  La opción RESTRICT no nos permite desautorizar a un usuario si éste ha autorizado a otros.