MYSQL
MYSQL
¿Que es el
cotejamiento en MySql?
Una clasificación es un conjunto de reglas que define cómo comparar y
ordenar cadenas de caracteres. Cada intercalación en MySQL pertenece a un único
conjunto de caracteres. Cada conjunto de caracteres tiene al menos una
intercalación, y la mayoría tiene dos o más intercalaciones. Una colación
ordena caracteres basados en pesos.
Como crear una base
de datos en MYSQL por pasos:
Para poder agregar y manipular datos, primero debe crear una base de
datos. No hay mucho de esto. Está creando solo un contenedor en el que agregará
tablas. Crear una tabla es más complicado y ofrece muchas opciones. Hay varios
tipos de tablas para elegir, algunas con características únicas. Al crear
tablas, también debe decidir la estructura de cada tabla: el número de
columnas, el tipo de datos que puede contener cada columna, cómo se indexarán
las tablas y varios otros factores. Sin embargo, mientras aún está aprendiendo,
puede aceptar la configuración predeterminada para la mayoría de las opciones
al crear tablas.
Hay algunas cosas básicas para decidir al crear una estructura para sus
datos:
El número de tablas a incluir en su base de datos, así como los nombres
de las tablas.
Para cada tabla, el número de columnas que debe contener, así como los
nombres de las columnas.
Para cada columna, qué tipo de datos se almacenarán
Para la última parte, al principio, usaremos solo cuatro tipos de
columnas: columnas que contienen solo números; columnas que contienen
caracteres alfanuméricos, pero no demasiados (es decir, un máximo de 255
caracteres); columnas que contienen mucho texto y quizás archivos binarios; y
columnas para registrar información de fecha y hora. Este es un buen punto de
partida para crear una base de datos y tablas. A medida que avanzamos, podemos
ampliar esa lista de tipos de datos de columna para mejorar el rendimiento de
sus bases de datos.
Crear una base de datos
Crear una base de datos es simple, principalmente porque no tiene mucho
que ver. Use la instrucción SQL CREATE DATABASE. Deberá proporcionar un nombre
para la base de datos con esta declaración SQL. Podrías llamarlo algo soso como
db1. Sin embargo, hagamos algo más realista e interesante. Soy un fanático de
los pájaros, así que he usado una base de datos de un sitio web ficticio de
observación de aves para los ejemplos de este libro. Algunas aves viven en
grupos, o en una colonia llamada colonia. Para comenzar, creemos una base de
datos que contendrá información sobre las aves y la llamaremos rookery. Para
hacer esto, ingrese lo siguiente desde el cliente mysql:
CREE LA BASE DE DATOS
Como se mencionó anteriormente, esta primera declaración SQL mínima
creará un subdirectorio llamado rookery en el sistema de archivos en el
directorio de datos para MySQL. No creará ningún dato. Simplemente configurará
un lugar para agregar tablas, que a su vez contendrá datos. Por cierto, si no
le gusta la palabra clave BASE DE DATOS, puede usar SCHEMA en su lugar: CREATE
SCHEMA database_name. Los resultados son los mismos.
Sin embargo, puede hacer un poco más que la instrucción SQL que se
muestra aquí para crear una base de datos. Puede agregar un par de opciones en
las que puede establecer los tipos de caracteres predeterminados que se
utilizarán en la base de datos y cómo se ordenarán o cotejarán los datos.
Entonces, dejemos caer la base de datos de rookery y volvamos a crearla así:
La primera línea en esta declaración SQL es la misma que la anterior;
recuerde, todo esto es una declaración SQL distribuida en dos líneas, que
termina con punto y coma. La segunda línea, que es nueva, le dice a MySQL que
los caracteres predeterminados que se usarán en las tablas de la base de datos
son letras latinas y otros caracteres. La tercera línea le dice a MySQL que el
método predeterminado de ordenar datos en tablas se basa en caracteres latinos
binarios. Discutiremos los caracteres binarios y la clasificación binaria en un
capítulo posterior, pero no es necesario entender eso en este momento. De
hecho, para la mayoría de los propósitos, el método mínimo de crear una base de
datos sin opciones, como se mostró anteriormente, está bien. Siempre puede cambiar
estas dos opciones más adelante si es necesario. Solo menciono las opciones
aquí para que sepa que existen si necesita configurarlas algún día.
Ahora que hemos creado una base de datos, confirmemos que está allí, en
el servidor MySQL. Para obtener una lista de bases de datos, ingrese la
siguiente instrucción SQL:
Los resultados aquí muestran la base de datos de rookery y otras tres
bases de datos que se crearon cuando MySQL se instaló en el servidor. Vimos los
otros tres en Comenzar a explorar bases de datos, y los cubriremos en capítulos
posteriores de este libro según sea necesario.
Antes de comenzar a agregar tablas a la base de datos de rookery,
ingrese el siguiente comando en el cliente mysql:
Este pequeño comando establecerá la nueva base de datos que se acaba de
crear como la base de datos predeterminada para el cliente mysql. Seguirá
siendo la base de datos predeterminada hasta que la cambie a otra diferente o
hasta que salga del cliente. Esto facilita la entrada de instrucciones SQL para
crear tablas u otras instrucciones SQL relacionadas con tablas. De lo
contrario, cuando ingrese cada instrucción SQL relacionada con la tabla, deberá
especificar cada vez que la base de datos donde se encuentra la tabla.
Crear tablas
El siguiente paso para estructurar una base de datos es crear tablas.
Aunque esto puede ser complicado, haremos que comenzar sea sencillo.
Inicialmente crearemos una tabla principal y dos tablas más pequeñas para
información de referencia. La tabla principal tendrá un montón de columnas,
pero las tablas de referencia tendrán solo unas pocas columnas.
Para nuestro sitio ficticio de observadores de aves, el interés clave
son las aves. Por lo tanto, queremos crear una tabla que contenga datos básicos
sobre aves. Con fines de aprendizaje, no haremos de esta una mesa elaborada.
Ingrese la siguiente instrucción SQL en mysql en su computadora:
Esta instrucción SQL crea los pájaros de la tabla con cinco campos, o
columnas, con comas que separan la información sobre cada columna. Tenga en
cuenta que todas las columnas juntas están contenidas en un par de paréntesis.
Para cada columna, especificamos el nombre, el tipo y la configuración
opcional. Por ejemplo, la información que damos sobre la primera columna es:
El nombre, bird_id
El tipo, INT (lo que significa que debe contener enteros)
Los ajustes, AUTO_INCREMENT y PRIMARY KEY
Los nombres de las columnas pueden ser cualquier cosa que no sean
palabras reservadas para sentencias, cláusulas y funciones de SQL. En realidad,
puede usar una palabra de reserva, pero siempre debe aparecer entre comillas
para distinguirla. Puede encontrar una lista de tipos de datos para elegir en
los sitios web de MySQL y MariaDB, o en mi libro, MySQL in a Nutshell.
Creamos esta tabla con solo cinco columnas. Puede tener muchas columnas
(hasta 255), pero no debería tener demasiadas. Si una tabla tiene demasiadas
columnas, puede ser engorrosa de usar y la tabla será lenta cuando se acceda a
ella. Es mejor dividir los datos en varias tablas.
La primera columna en la tabla de pájaros es un número de identificación
simple, bird_id. Será la columna de clave principal en la que se indexarán los
datos; de ahí las palabras clave, PRIMARY KEY. Discutiremos la importancia de
la clave primaria más adelante.
La opción AUTO_INCREMENT le dice a MySQL que incremente automáticamente
el valor de este campo. Comenzará con el número 1, a menos que especifiquemos
un número diferente.
La siguiente columna contendrá el nombre científico de cada ave (por
ejemplo, Charadrius vociferus, en lugar de Killdeer). Puede pensar que la
columna de nombre_científico sería el identificador ideal para usar como clave
principal para indexar la tabla de pájaros, y que no necesitaríamos la columna
de nombre de pájaro. Pero el nombre científico puede ser muy largo y
generalmente en latín o griego (o, a veces, una combinación de ambos idiomas),
y no todos se sienten cómodos usando palabras de estos idiomas. Además, sería
incómodo ingresar el nombre científico de un pájaro cuando se hace referencia a
una fila en la tabla. Configuramos la columna scientific_name para que tenga un
tipo de datos de caracteres de ancho variable (VARCHAR). El 255 que
especificamos entre paréntesis después de establecer el tamaño máximo (255
debería ser suficiente para los nombres largos que tendremos que acomodar).
Si el nombre científico de un pájaro tiene menos de 255 caracteres, el
motor de almacenamiento reducirá el tamaño de la columna para la fila. Esto es
diferente del tipo de datos de la columna CHAR. Si los datos en una columna
CHAR son menores que su máximo, el espacio aún se asigna para todo el ancho que
establezca. Hay compensaciones con estos dos tipos básicos de datos de
caracteres. Si el motor de almacenamiento sabe exactamente qué esperar de una columna,
las tablas se ejecutan más rápido y se pueden indexar más fácilmente con una
columna CHAR. Sin embargo, una columna VARCHAR puede usar menos espacio en el
disco duro del servidor y es menos propensa a la fragmentación. Eso puede
mejorar el rendimiento. Cuando esté seguro de que una columna tendrá un número
determinado de caracteres, use CHAR. Cuando el ancho puede variar, use VARCHAR.
A continuación, establecemos el tipo de datos de columna para el
nombre_común de cada ave en una columna de caracteres de ancho variable de solo
50 caracteres como máximo.
La cuarta columna (family_id) se usará como números de identificación
para la familia de aves a la que pertenece cada ave. Son tipos de datos enteros
(es decir, INT). Crearemos otra tabla para obtener más información sobre las
familias. Luego, al manipular datos, podemos unir las dos tablas, usar un
número para identificar a cada familia y vincular cada ave con su familia.
La última columna es para la descripción de cada ave. Es un tipo de
datos TEXTO, lo que significa que es una columna de ancho variable y puede
contener 65.535 bytes de datos para cada fila. Esto nos permitirá ingresar
mucho texto sobre cada ave. Podríamos escribir varias páginas que describan un
pájaro y ponerlo en esta columna.
Hay factores adicionales a tener en cuenta al buscar un pájaro en una
base de datos, por lo que hay muchas columnas que podríamos agregar a esta
tabla: información sobre patrones migratorios, características notables para
detectarlos en la naturaleza, etc. Además, hay muchos otros tipos de datos que
pueden usarse para columnas. Podemos tener columnas que permitan números
mayores y menores, o que se incluyan archivos binarios en cada fila. Por
ejemplo, es posible que desee una columna con un tipo de datos binarios para
almacenar una fotografía de cada ave. Sin embargo, esta tabla básica le ofrece
una buena muestra de las posibilidades al crear tablas.
Para ver cómo se ve la tabla, use la instrucción DESCRIBE. Muestra
información sobre las columnas de una tabla o el esquema de la tabla, no los
datos en sí. Para usar esta declaración SQL para obtener información sobre la
tabla que acabamos de crear, debe ingresar la siguiente declaración SQL:
Observe que estos resultados se muestran en un formato de tabla hecho
con caracteres ASCII. No tiene un aspecto muy elegante, pero es limpio, rápido
y proporciona la información solicitada. Estudiemos este diseño, no el contenido,
per se.
La primera fila de este conjunto de resultados contiene encabezados de
columna que describen las filas de información que le siguen. En la primera
columna de este conjunto de resultados, Campo contiene los campos o columnas de
la tabla creada.
La segunda columna, Tipo, enumera el tipo de datos para cada campo.
Observe que para las columnas de la tabla en las que especificamos el tipo de
datos VARCHAR con los anchos específicos entre paréntesis, esa configuración se
muestra aquí (por ejemplo, varchar (255)). Cuando no especificamos el tamaño de
las columnas INT, se asumieron los valores predeterminados y se muestran aquí.
Más adelante veremos qué significa INT (11) y discutiremos las otras
posibilidades para los tipos de datos enteros.
La tercera columna en los resultados anteriores, Null, indica si cada
campo puede contener valores NULL. NULL no es nada; son datos inexistentes.
Esto es diferente del contenido en blanco o vacío en un campo. Eso puede
parecer extraño: solo acepta que hay una diferencia en este momento. Lo verás
en acción más adelante en este libro.
La cuarta columna, Clave, indica si un campo es un campo clave: una
columna indexada. No es una columna indexada si el resultado está en blanco,
como lo está con common_name. Si se indexa una columna, la pantalla indicará
qué tipo de índice. Debido al espacio limitado permitido en la pantalla, trunca
las palabras. En el ejemplo que se muestra, la columna bird_id es una clave
principal, abreviada a PRI en esta pantalla. Configuramos scientific_name a
otro tipo de clave o índice, uno llamado UNIQUE, que aquí se abrevia UNI.
La penúltima columna de la pantalla, Predeterminada, contendría
cualquier valor predeterminado establecido para cada campo. No establecimos
ninguno al crear la tabla de pájaros, pero podríamos haberlo hecho. Podemos
hacer eso más tarde.
La última columna, Extra, proporciona cualquier información adicional
que la tabla mantiene en cada columna. En el ejemplo que se muestra, podemos
ver que los valores de bird_id se incrementarán automáticamente. Por lo
general, no hay nada más en esta columna.
Si no nos gusta algo dentro de la estructura de la tabla que creamos,
podemos usar la instrucción ALTER TABLE para cambiarla (esta instrucción SQL se
trata en el Capítulo 5). Si cometió algunos errores y solo desea comenzar de
nuevo, puede eliminar la tabla e intentar nuevamente crearla. Para eliminar una
tabla por completo (incluidos sus datos), puede usar la instrucción DROP TABLE,
seguida del nombre de la tabla. Tenga cuidado con esta declaración SQL, ya que
no es reversible y elimina cualquier dato de la tabla.
Relaciones
Estamos progresando con el diseño de nuestra base de datos. Hemos
decidido las entidades que queremos y hemos formado una imagen de las tablas que
necesitamos, las columnas en esas tablas e incluso ejemplos de los datos que
esas columnas mantendrán. Sin embargo, falta algo, y esas son las relaciones
entre nuestras entidades.
Si miramos el diagrama de nuestras cinco tablas, las tablas están todas
aisladas y no es obvio cómo estas tablas deben relacionarse entre sí.
Simplifiquemos un poco nuestras tablas y definamos explícitamente algunas
relaciones entre ellas.
Este diagrama muestra una representación abstracta de nuestras diversas
entidades y también las relaciones entre ellas (nota: en realidad podríamos
imaginar que más de un usuario podría compartir la misma dirección; esta
estructura está destinada a fines ilustrativos). Podemos pensar en este
diagrama como un simple Diagrama de relación de entidad, o ERD. Un ERD es una
representación gráfica de entidades y sus relaciones entre sí, y es una
herramienta de uso común en el diseño de bases de datos.
Existen diferentes tipos de ERD que varían de conceptuales a detallados
y, a menudo, usan convenciones específicas, como la notación de patas de gallo,
para modelar las relaciones. No entraremos en los detalles de estos diferentes
tipos, o las convenciones que usan, en este libro. Por ahora es útil pensar
simplemente en un ERD como cualquier diagrama que modela las relaciones entre
entidades.
Llaves
Bien, ahora sabemos las tablas que necesitamos y también hemos definido
las relaciones que deberían existir entre esas tablas en nuestro ERD, pero
¿cómo implementamos esas relaciones en términos de nuestro esquema de tabla? La
respuesta a eso es usar las teclas.
En una sección anterior de este libro, analizamos un aspecto del esquema
llamado restricciones, y exploramos cómo actúan las restricciones y cómo
funcionan con los datos en las tablas de nuestra base de datos. Las claves son
un tipo especial de restricción utilizada para establecer relaciones y
unicidad. Se pueden usar para identificar una fila específica en la tabla
actual o para referirse a una fila específica en otra tabla. En este capítulo
veremos dos tipos de claves que cumplen estos roles particulares: Claves
primarias y Claves externas.
Claves primarias
Una parte necesaria para establecer relaciones entre dos entidades o dos
datos es poder identificar los datos correctamente. En SQL, la identificación
única de datos es crítica. Una clave primaria es un identificador único para
una fila de datos.
Para actuar como un identificador único, una columna debe contener
algunos datos, y esos datos deben ser únicos para cada fila. Si está pensando
que esos requisitos se parecen mucho a nuestras restricciones NOT NULL y
UNIQUE, tiene razón; de hecho, hacer que una columna sea una CLAVE PRIMARIA es
esencialmente equivalente a agregar restricciones NOT NULL y UNIQUE a esa
columna.
La columna de identificación en nuestra tabla de usuarios tiene ambas
restricciones, y hemos usado esa columna en muchas de nuestras consultas SELECT
para identificar filas de manera única; efectivamente hemos tenido id como
clave principal todo el tiempo, aunque no lo hemos configurado explícitamente
como clave principal. Hagamos eso ahora:
Aunque cualquier columna de una tabla puede tener restricciones ÚNICAS y
NO NULAS aplicadas, cada tabla solo puede tener una Clave primaria. Es una
práctica común que esa Clave primaria sea una columna llamada id. Si observa
las otras tablas que hemos definido para nuestra base de datos, la mayoría de
ellas tienen una columna de identificación. Si bien una columna de cualquier
nombre puede servir como la clave principal, usar una columna llamada id es
útil por razones mnemotécnicas y, por lo tanto, es una convención popular.
Ser capaz de identificar de manera única una fila de datos en una tabla
a través de la columna Clave primaria de esa tabla es solo la mitad de la
historia cuando se trata de crear relaciones entre tablas. La otra mitad de
esta historia es el socio de la Clave primaria, la Clave extranjera.
Llaves extranjeras
Una clave externa nos permite asociar una fila en una tabla a una fila
en otra tabla. Esto se hace estableciendo una columna en una tabla como Clave
externa y haciendo que esa columna haga referencia a la columna Clave primaria
de otra tabla. La creación de esta relación se realiza utilizando la palabra
clave REFERENCES en este formulario:
Exploraremos algunos ejemplos específicos de cómo se usa esto cuando
veamos cómo establecer varios tipos de relaciones más adelante en este
capítulo, pero en términos generales puede pensar en esta referencia como una
conexión entre filas en diferentes tablas.
Imagine, por ejemplo, que tenemos dos tablas, una llamada colores y otra
llamada formas. La columna color_id de la tabla de formas es una Clave externa
que hace referencia a la columna de identificación de la tabla de colores.
En el diagrama anterior, la fila 'Roja' de nuestra tabla de colores está
asociada con las filas 'Cuadrada' y 'Estrella' de nuestra tabla de formas. Del
mismo modo, 'Azul' está asociado con 'Triángulo' y 'Verde' con 'Círculo'.
'Naranja' actualmente no está asociado con ninguna fila en la tabla de formas,
pero existe la posibilidad de crear una asociación de este tipo si insertamos
una fila en las formas con un color_id de 3.
Al configurar esta referencia, estamos asegurando la integridad
referencial de una relación. La integridad referencial es la garantía de que un
valor de columna dentro de un registro debe hacer referencia a un valor
existente; si no es así, se genera un error. En otras palabras, PostgreSQL no
le permitirá agregar un valor a la columna Clave externa de una tabla si la
columna Clave primaria de la tabla a la que hace referencia no contiene ese
valor. Discutiremos este concepto con un poco más de detalle más adelante.
La forma específica en que se utiliza una clave externa como parte del
esquema de una tabla depende del tipo de relación que queremos definir entre
nuestras tablas. Para implementar ese esquema correctamente, es útil describir
formalmente las relaciones que necesitamos modelar entre nuestras entidades:
Un usuario puede tener UNA dirección. Una dirección tiene solo UN
usuario.
Una reseña solo puede ser sobre UN libro. Un libro puede tener MUCHAS
reseñas.
Un usuario puede tener MUCHOS libros que él / ella puede haber sacado o
devuelto. Un libro puede ser / ha sido extraído por MUCHOS usuarios.
Las relaciones entre entidades descritas anteriormente se pueden
clasificar en tres tipos de relaciones:
Uno -a- Uno
Existe una relación uno a uno entre dos entidades cuando existe una
instancia de entidad particular en una tabla, y solo puede tener una instancia
de entidad asociada en otra tabla.
En el mundo de las bases de datos, este tipo de relación se implementa
de la siguiente manera: el ID que es la CLAVE PRIMARIA de la tabla de usuarios
se utiliza como CLAVE EXTERNA y CLAVE PRIMARIA de la tabla de direcciones.
La ejecución de la instrucción SQL anterior creará una tabla de
direcciones y creará una relación entre esta y la tabla de usuarios. Observe
las cláusulas PRIMARY KEY y FOREIGN KEY al final de la instrucción CREATE.
Estas dos cláusulas crean las restricciones que hacen que user_id sea la Clave
primaria de la tabla de direcciones y también la Clave externa para la tabla de
usuarios.
Avancemos y agreguemos algunos datos a nuestra tabla.
La columna user_id usa valores que existen en la columna id de la tabla
de usuarios para conectar las tablas a través de la restricción de clave
externa que acabamos de crear.
Uno -a- muchos
Bien, es hora de volver a nuestros diferentes tipos de relación de tabla
con un vistazo a uno a muchos. Existe una relación de uno a muchos entre dos
entidades si una instancia de entidad en una de las tablas puede asociarse con
múltiples registros (instancias de entidad) en la otra tabla. La relación
opuesta no existe; es decir, cada instancia de entidad en la segunda tabla solo
puede asociarse con una instancia de entidad en la primera tabla.
Vamos a configurar los datos necesarios. Primero creemos nuestras tablas
Estas declaraciones de creación de tablas para nuestros libros y tablas
de reseñas son bastante similares a nuestro ejemplo anterior. Sin embargo, hay
una diferencia clave que vale la pena señalar en la declaración de nuestra
tabla de revisiones:
A diferencia de nuestra tabla de direcciones, PRIMARY KEY y FOREIGN KEY
hacen referencia a diferentes columnas, id y book_id respectivamente. Esto
significa que la columna FOREIGN KEY, book_id no está vinculada por la
restricción ÚNICA de nuestra PRIMARY KEY y, por lo tanto, el mismo valor de la
columna id de la tabla de libros puede aparecer en esta columna más de una vez.
En otras palabras, un libro puede tener muchas reseñas.
Ahora que hemos creado nuestras tablas de libros y reseñas, agreguemos
algunos datos.
El orden en el que agregamos los datos es importante aquí. Dado que una
columna en las revisiones hace referencia a los datos en los libros, primero
debemos asegurarnos de que los datos existan en la tabla de libros para que
podamos consultarlos.
Muchos a muchos
Existe una relación de muchos a muchos entre dos entidades si para una
instancia de entidad puede haber múltiples registros en la otra tabla, y
viceversa.
Para implementar este tipo de relación, necesitamos introducir una
tercera tabla de referencias cruzadas. Esta tabla mantiene la relación entre
las dos entidades, al tener dos CLAVES EXTRANJERAS, cada una de las cuales hace
referencia a la CLAVE PRIMARIA de una de las tablas para las que queremos crear
esta relación. Ya tenemos nuestros libros y tablas de usuarios, por lo que solo
necesitamos crear la tabla de referencias cruzadas: pagos.
Aquí, la columna user_id en las cajas hace referencia a la columna id en
los usuarios, y la columna book_id en las cajas hace referencia a la columna id
en los libros. Cada fila de la tabla de pago utiliza estas dos claves externas
para crear una asociación entre filas de usuarios y libros.
Podemos ver en la primera fila de pagos, el usuario con una
identificación de 1 está asociado con el libro con una identificación de 1. En
la segunda fila, el mismo usuario también está asociado con el libro con una identificación
de 2. En el tercera fila, un usuario diferente, con un id de 2, está asociado
con el mismo libro de la fila anterior. En la cuarta fila, el usuario con una
identificación de 5 está asociado con el libro con una identificación de 3.
No se preocupe si no comprende completamente esto de inmediato, veremos
en breve cómo son estas asociaciones en términos de datos en usuarios y libros.
Primero, creemos nuestra tabla de pagos y agreguemos algunos datos.
Es posible que haya notado que nuestra tabla contiene un par de otras
columnas checkout_date y return_date. Si bien estos no son necesarios para
crear la relación entre los usuarios y la tabla de libros, pueden proporcionar
un contexto adicional a esa relación. Los atributos como una fecha de pago o
una fecha de devolución no pertenecen específicamente a los usuarios o
específicamente a los libros, sino a la asociación entre un usuario y un libro.
Este tipo de contexto adicional puede ser útil dentro de la lógica
empresarial de la aplicación utilizando nuestra base de datos. Por ejemplo,
para evitar que más de un usuario intente retirar el mismo libro al mismo
tiempo, la aplicación podría determinar qué libros están actualmente retirados
al consultar aquellos que tienen un valor en la columna checkout_date de la
tabla de pagos pero dónde return_date se establece en NULL.
Ahora que hemos creado nuestros pagos, podemos agregar los datos que
crearán las asociaciones entre las filas en los usuarios y los libros.
Echemos un vistazo a cómo se ven estos datos en términos de las
relaciones entre las tablas.
Aquí podemos ver que el valor de id de 1 de la tabla de usuarios para
'John Smith' aparece dos veces en la columna user_id de los pagos, pero junto
con diferentes valores para book_id (1 y 2); Esto satisface la parte de la
relación "un usuario puede consultar muchos libros". De manera
similar, podemos ver que el valor de id de 2 de la tabla de libros para 'Mi
segundo libro SQL' aparece dos veces en la columna books_id de las cajas, junto
con diferentes valores para user_id (1 y 2); Esto satisface la parte de la
relación "un libro puede ser extraído por muchos usuarios".
Quizás podamos pensar en una relación de Muchos a Muchos como la
combinación de dos relaciones de Uno a Muchos; en este caso entre cajas y
usuarios, y entre cajas y libros.
Como crear un
usuario y darle permisos en MYSQL
Paso 1: crear un usuario de MySQL y otorgar todos los privilegios
Justo cuando empiece a usar MySQL, recibirá un nombre de usuario y una contraseña. Estas credenciales iniciales le otorgarán acceso "raíz" o control total de todas sus bases de datos y tablas.
Sin embargo, hay ocasiones en las que deberá otorgar acceso a la base de datos a otra persona sin otorgarle el control total.
Por ejemplo, contrata desarrolladores para mantener sus bases de datos, pero no desea proporcionarles la capacidad de eliminar o modificar cualquier información confidencial.
Debido a eso, debe darles las credenciales de un usuario no root. De esta manera, puede realizar un seguimiento de lo que los desarrolladores pueden y no pueden hacer con sus datos.
En esta parte, explicaremos cómo crear usuarios en MySQL y otorgarles todos los privilegios de su base de datos. En un sentido práctico, no es aconsejable dar el reinado completo a un usuario no root. Sin embargo, sigue siendo un buen punto de entrada para conocer los privilegios del usuario.
Para crear un nuevo usuario de MySQL, siga estos pasos:
Acceda a la línea de comando e ingrese al servidor MySQL:
Justo cuando empiece a usar MySQL, recibirá un nombre de usuario y una contraseña. Estas credenciales iniciales le otorgarán acceso "raíz" o control total de todas sus bases de datos y tablas.
Sin embargo, hay ocasiones en las que deberá otorgar acceso a la base de datos a otra persona sin otorgarle el control total.
Por ejemplo, contrata desarrolladores para mantener sus bases de datos, pero no desea proporcionarles la capacidad de eliminar o modificar cualquier información confidencial.
Debido a eso, debe darles las credenciales de un usuario no root. De esta manera, puede realizar un seguimiento de lo que los desarrolladores pueden y no pueden hacer con sus datos.
En esta parte, explicaremos cómo crear usuarios en MySQL y otorgarles todos los privilegios de su base de datos. En un sentido práctico, no es aconsejable dar el reinado completo a un usuario no root. Sin embargo, sigue siendo un buen punto de entrada para conocer los privilegios del usuario.
Para crear un nuevo usuario de MySQL, siga estos pasos:
Acceda a la línea de comando e ingrese al servidor MySQL:
El script regresará con este resultado, que verifica que está accediendo al servidor MySQL.
Luego, ejecute el siguiente comando:
Para otorgar al usuario recién creado todos los privilegios de la base de datos, ejecute el comando:
Para que los cambios surtan efecto inmediatamente, elimine los privilegios escribiendo el comando:
¡Y eso es todo! Su nuevo usuario tiene el mismo acceso a la base de datos que el usuario raíz.
Paso 2: otorgar
privilegios específicos para un usuario de MySQL
Paso 2: otorgar privilegios específicos para un usuario de MySQL
Recuerde especificar la base de datos y los nombres de las tablas para dar al usuario raíz un control de grano fino sobre ciertos datos. Además, reemplace el valor del "tipo de permiso" con el tipo de acceso que desea otorgar al nuevo usuario.
Aquí están los más utilizados en MySQL:
CREAR: permite a los usuarios crear bases de datos / tablas
SELECCIONAR: permite a los usuarios recuperar datos
INSERTAR - permite a los usuarios agregar nuevas entradas en las tablas
ACTUALIZACIÓN: permite a los usuarios modificar entradas existentes en tablas
BORRAR: permite a los usuarios borrar entradas de la tabla
DROP: permite a los usuarios eliminar bases de datos / tablas enteras
Para usar cualquiera de las opciones anteriores, simplemente reemplace el tipo de permiso con la palabra clave apropiada. Para aplicar múltiples privilegios, sepárelos con una coma. Por ejemplo, podemos asignar CREATE y SELECT a nuestro usuario MySQL no root con este comando:
A veces, puede enfrentar una situación en la que necesita revocar los privilegios otorgados por un usuario. Puede hacerlo ingresando:
Por ejemplo, para retirar todos los privilegios para nuestro usuario no root, debemos usar:
Finalmente, puede eliminar completamente un usuario existente utilizando la siguiente línea
Definicion de lso
siguientes conceptos y subconceptos
GRANT
La declaración GRANT permite a los administradores del sistema otorgar
privilegios y roles, que pueden otorgarse a cuentas y roles de usuario. Se
aplican estas restricciones de sintaxis:
GRANT no puede mezclar otorgando privilegios y roles en la misma declaración.
Una declaración GRANT dada debe otorgar privilegios o roles.
La cláusula ON distingue si la declaración otorga privilegios o roles:
Con ON, la declaración otorga privilegios.
Sin ON, la declaración otorga roles.
Está permitido asignar privilegios y roles a una cuenta, pero debe usar
declaraciones GRANT separadas, cada una con una sintaxis adecuada a lo que se
otorgará.
Permiso para crear
usuario
Para poder crear usuarios de mysql, debe poder insertar en la tabla de
usuarios en la base de datos de mysql. Así que crea tu usuario maestro y dale
acceso de escritura a la base de datos mysql. Para usar CREATE USER, debe tener
el privilegio global CREATE USER o el privilegio INSERT para la base de datos
mysql.
Permisos a nivel
global
Los privilegios de MySQL difieren en los contextos en los que se aplican
y en diferentes niveles de operación: los privilegios administrativos permiten
a los usuarios administrar la operación del servidor MySQL. Estos privilegios
son globales porque no son específicos de una base de datos en particular.
Permisos a nivel de
bases de datos
Los privilegios otorgados a una cuenta MySQL determinan qué operaciones
puede realizar la cuenta. Los privilegios de MySQL difieren en los contextos en
los que se aplican y en diferentes niveles de operación:
Los privilegios administrativos permiten a los usuarios gestionar el
funcionamiento del servidor MySQL. Estos privilegios son globales porque no son
específicos de una base de datos en particular.
Los privilegios de la base de datos se aplican a una base de datos y a
todos los objetos dentro de ella. Estos privilegios se pueden otorgar a bases
de datos específicas o de manera global para que se apliquen a todas las bases
de datos.
Se pueden otorgar privilegios para objetos de base de datos como tablas,
índices, vistas y rutinas almacenadas para objetos específicos dentro de una
base de datos, para todos los objetos de un tipo dado dentro de una base de
datos (por ejemplo, todas las tablas en una base de datos) o globalmente para
todos objetos de un tipo dado en todas las bases de datos.
Privilegios permitidos para GRANT y REVOKE
|
Privilegio
|
Grant
|
Contexto
|
|
Todos los privilegios
|
Sinonimos de los privilegios
|
Administracion de server
|
|
Alter_priv
|
Tables
|
|
|
Alter_routine_priv
|
Stored routines
|
|
|
Create_priv
|
Databases, tables, or indexes
|
|
|
Create_routine_priv
|
Stored routines
|
|
|
Create_tablespace_priv
|
Server administration
|
|
|
Create_tmp_table_priv
|
Tables
|
|
|
Create_user_priv
|
Server administration
|
|
|
Create_view_priv
|
Views
|
|
|
Delete_priv
|
Tables
|
|
|
Drop_priv
|
Databases, tables, or views
|
|
|
Event_priv
|
Databases
|
|
|
Execute_priv
|
Stored routines
|
|
|
File_priv
|
File access on server host
|
|
|
Grant_priv
|
Databases, tables, or stored routines
|
|
|
Index_priv
|
Tables
|
|
|
Insert_priv
|
Tables or columns
|
|
|
Lock_tables_priv
|
Databases
|
|
|
Process_priv
|
Server administration
|
|
|
See proxies_priv table
|
Server administration
|
|
|
References_priv
|
Databases or tables
|
|
|
Reload_priv
|
Server administration
|
|
|
Repl_client_priv
|
Server administration
|
|
|
Repl_slave_priv
|
Server administration
|
|
|
Select_priv
|
Tables or columns
|
|
|
Show_db_priv
|
Server administration
|
|
|
Show_view_priv
|
Views
|
|
|
Shutdown_priv
|
Server administration
|
|
|
Super_priv
|
Server administration
|
|
|
Trigger_priv
|
Tables
|
|
|
Update_priv
|
Tables or columns
|
|
|
Synonym for “no privileges”
|
Server administration
|
Descripciones de
privilegios
La siguiente lista proporciona descripciones generales de cada
privilegio disponible en MySQL. Las declaraciones SQL particulares pueden tener
requisitos de privilegios más específicos que los indicados aquí. Si es así, la
descripción de la declaración en cuestión proporciona los detalles.
TODOS LOS PRIVILEGIOS
Estos especificadores de privilegios son la abreviatura de "todos
los privilegios disponibles en un nivel de privilegio dado" (excepto
OPCIÓN DE OTORGAMIENTO). Por ejemplo, otorgar ALL a nivel global o de tabla
otorga todos los privilegios globales o todos los privilegios a nivel de tabla,
respectivamente.
Permite el uso de la instrucción ALTER TABLE para cambiar la estructura
de las tablas. ALTER TABLE también requiere los privilegios CREATE e INSERT. Cambiar
el nombre de una tabla requiere ALTER y DROP en la tabla anterior, CREATE e
INSERT en la tabla nueva.
Permite el uso de declaraciones que alteran o descartan rutinas
almacenadas (procedimientos y funciones almacenados).
Permite el uso de declaraciones que crean nuevas bases de datos y
tablas.
Permite el uso de sentencias que crean rutinas almacenadas
(procedimientos y funciones almacenados).
Permite el uso de declaraciones que crean, alteran o descartan espacios
de tablas y grupos de archivos de registro.
Permite la creación de tablas temporales utilizando la instrucción
CREATE TEMPORARY TABLE.
Después de que una sesión ha creado una tabla temporal, el servidor no
realiza más verificaciones de privilegios en la tabla. La sesión de creación
puede realizar cualquier operación en la tabla, como DROP TABLE, INSERT, UPDATE
o SELECT. Para obtener más información, consulte la Sección 13.1.18.3,
“Declaración de CREAR TABLA TEMPORAL”.
Permite el uso de las declaraciones ALTER USER, CREATE USER, DROP USER,
RENAME USER y REVOKE ALL PRIVILEGES.
Permite el uso de la instrucción CREATE VIEW.
Permite que las filas se eliminen de las tablas en una base de datos.
Permite el uso de declaraciones que eliminan (eliminan) bases de datos,
tablas y vistas existentes. Se requiere el privilegio DROP para usar la
instrucción ALTER TABLE ... DROP PARTITION en una tabla particionada. El
privilegio DROP también se requiere para TRUNCATE TABLE.
Permite el uso de declaraciones que crean, alteran, eliminan o muestran
eventos para el Programador de eventos.
Permite el uso de declaraciones que ejecutan rutinas almacenadas
(procedimientos y funciones almacenados).
Afecta las siguientes operaciones y comportamientos del servidor:
Permite leer y escribir archivos en el host del servidor utilizando las
declaraciones LOAD DATA y SELECT ... INTO OUTFILE y la función LOAD_FILE (). Un
usuario que tiene el privilegio de ARCHIVO puede leer cualquier archivo en el
servidor host que sea legible o legible por el servidor MySQL. (Esto implica
que el usuario puede leer cualquier archivo en cualquier directorio de base de
datos, porque el servidor puede acceder a cualquiera de esos archivos).
Permite crear nuevos archivos en cualquier directorio donde el servidor
MySQL tenga acceso de escritura. Esto incluye el directorio de datos del
servidor que contiene los archivos que implementan las tablas de privilegios.
A partir de MySQL 5.7.17, permite el uso de la opción de tabla
DIRECTORIO DE DATOS o DIRECTORIO DE ÍNDICE para la instrucción CREATE TABLE.
Como medida de seguridad, el servidor no sobrescribe los archivos
existentes.
Para limitar la ubicación en la que los archivos se pueden leer y
escribir, establezca la variable de sistema secure_file_priv en un directorio
específico. Consulte la Sección 5.1.7, “Variables del sistema del servidor”.
Le permite otorgar o revocar a otros usuarios los privilegios que usted
posee.
Permite el uso de declaraciones que crean o eliminan (eliminan) índices.
ÍNDICE se aplica a las tablas existentes. Si tiene el privilegio CREATE para
una tabla, puede incluir definiciones de índice en la instrucción CREATE TABLE.
Permite que las filas se inserten en tablas en una base de datos. INSERT
también es necesario para las declaraciones de mantenimiento de tabla ANALYZE
TABLE, OPTIMIZE TABLE y REPAIR TABLE.
Permite el uso de instrucciones explícitas LOCK TABLES para bloquear
tablas para las que tiene el privilegio SELECT. Esto incluye el uso de bloqueos
de escritura, lo que evita que otras sesiones lean la tabla bloqueada.
Permite la visualización de información sobre los subprocesos que se
ejecutan dentro del servidor (es decir, información sobre las declaraciones que
ejecutan las sesiones). El privilegio permite el uso de SHOW PROCESSLIST o
mysqladmin processlist para ver hilos que pertenecen a otras cuentas; siempre
puedes ver tus propios hilos. El privilegio PROCESS también permite el uso de
SHOW ENGINE.
Permite a un usuario hacerse pasar o hacerse conocido como otro usuario.
Consulte la Sección 6.2.14, "Usuarios proxy".
La creación de una restricción de clave externa requiere el privilegio
REFERENCES para la tabla primaria.
Permite el uso de la instrucción FLUSH. También habilita los comandos
mysqladmin que son equivalentes a las operaciones FLUSH: flush-hosts,
flush-logs, flush-privileges, flush-status, flush-tables, flush-threads,
refrescar y recargar.
REPLICATION CLIENT
Permite el uso de las declaraciones SHOW MASTER STATUS, SHOW SLAVE STATUS y SHOW BINARY LOGS. Otorgue este privilegio a las cuentas que utilizan los servidores esclavos para conectarse al servidor actual como su maestro.
REPLICATION SLAVE
Permite que la cuenta solicite actualizaciones que se hayan realizado en las bases de datos en el servidor maestro, utilizando las declaraciones SHOW SLAVE HOSTS, SHOW RELAYLOG EVENTS y SHOW BINLOG EVENTS. Este privilegio también es necesario para usar las opciones de mysqlbinlog --read-from-remote-server (-R) y --read-from-remote-master. Otorgue este privilegio a las cuentas que utilizan los servidores esclavos para conectarse al servidor actual como su maestro.
SELECT
Permite seleccionar filas de tablas en una base de datos. Las instrucciones SELECT requieren el privilegio SELECT solo si realmente acceden a las tablas. Algunas instrucciones SELECT no acceden a las tablas y pueden ejecutarse sin permiso para ninguna base de datos.
SHOW DATABASES
Permite que la cuenta vea los nombres de las bases de datos emitiendo la instrucción SHOW DATABASE. Las cuentas que no tienen este privilegio solo ven las bases de datos para las cuales tienen algunos privilegios, y no pueden usar la declaración si el servidor se inició con la opción --skip-show-database.
SHOW VIEW
Permite el uso de la instrucción SHOW CREATE VIEW. Este privilegio también es necesario para las vistas utilizadas con EXPLAIN.
SHUTDOWN
Permite el uso de la instrucción SHUTDOWN, el comando de apagado mysqladmin y la función de API mysql_shutdown () C.
SUPER
Afecta las siguientes operaciones y comportamientos del servidor:
Permite cambios en la configuración del servidor modificando las variables globales del sistema. Para algunas variables del sistema, establecer el valor de la sesión también requiere el privilegio SUPER. Si una variable del sistema está restringida y requiere un privilegio especial para establecer el valor de la sesión, la descripción de la variable indica esa restricción. Los ejemplos incluyen binlog_format, sql_log_bin y sql_log_off. Consulte también la Sección 5.1.8.1, “Privilegios variables del sistema”.
Permite cambios en las características de las transacciones globales (consulte la Sección 13.3.6, “ESTABLECER LA TRANSACCIÓN Declaración”)
Permite que la cuenta inicie y detenga la replicación, incluida la replicación grupal.
Permite el uso de las instrucciones CHANGE MASTER TO y CHANGE REPLICATION FILTER.
Habilita el control de registro binario por medio de las sentencias PURGE BINARY LOGS y BINLOG.
Permite configurar la identificación de autorización efectiva al ejecutar una vista o un programa almacenado. Un usuario con este privilegio puede especificar cualquier cuenta en el atributo DEFINER de una vista o programa almacenado.
Permite el uso de las instrucciones CREATE SERVER, ALTER SERVER y DROP SERVER.
Permite el uso del comando de depuración mysqladmin.
Habilita la rotación de la clave de cifrado InnoDB.
Permite leer el archivo de clave DES mediante la función DES_ENCRYPT ().
Permite la ejecución de funciones definidas por el usuario de tokens de versión.
Permite el control sobre conexiones de clientes no permitidas a cuentas que no son SUPER:
Permite el uso de la instrucción KILL o del comando mysqladmin kill para matar hilos que pertenecen a otras cuentas. (Una cuenta siempre puede matar sus propios hilos).
El servidor no ejecuta el contenido variable del sistema init_connect cuando se conectan los clientes SUPER.
El servidor acepta una conexión de un cliente SUPER incluso si se alcanza el límite de conexión configurado por la variable del sistema max_connections.
Un servidor en modo fuera de línea (modo_desconectado habilitado) no finaliza las conexiones del cliente SUPER en la siguiente solicitud del cliente y acepta nuevas conexiones de clientes SUPER.
Las actualizaciones se pueden realizar incluso cuando la variable del sistema read_only está habilitada. Esto se aplica a las actualizaciones explícitas de tablas y al uso de declaraciones de administración de cuentas como GRANT y REVOKE que actualizan las tablas implícitamente.
También es posible que necesite el privilegio SUPER para crear o alterar funciones almacenadas si el registro binario está habilitado, como se describe en la Sección 23.7, “Registro binario del programa almacenado”.
TRIGGER
Habilita las operaciones de disparo. Debe tener este privilegio para que una tabla cree, descarte, ejecute o muestre activadores para esa tabla.
Cuando se activa un activador (por un usuario que tiene privilegios para ejecutar instrucciones INSERT, UPDATE o DELETE para la tabla asociada con el activador), la ejecución del activador requiere que el usuario que definió el activador todavía tenga el privilegio TRIGGER para la tabla.
UPDATE
Permite que las filas se actualicen en tablas en una base de datos.
USAGE
Permisos a nivel de
tabla
Este especificador de privilegio significa "sin privilegios".
Se usa a nivel global con GRANT para modificar atributos de cuenta como límites
de recursos o características SSL sin nombrar privilegios específicos de cuenta
en la lista de privilegios. SHOW GRANTS muestra USAGE para indicar que una
cuenta no tiene privilegios en un nivel de privilegio.
Conceder permisos de nivel de tabla
Puede crear un usuario con permisos de nivel de tabla en MySQL realizando lo siguiente:
Conéctese a MySQL como usuario con Create_user_priv y Grant_priv. Determine qué usuarios tienen estos privilegios ejecutando la siguiente consulta. Su usuario ya necesitará el privilegio SELECT en MySQL.user para ejecutar la consulta.
Conceder permisos de nivel de tabla
Puede crear un usuario con permisos de nivel de tabla en MySQL realizando lo siguiente:
Conéctese a MySQL como usuario con Create_user_priv y Grant_priv. Determine qué usuarios tienen estos privilegios ejecutando la siguiente consulta. Su usuario ya necesitará el privilegio SELECT en MySQL.user para ejecutar la consulta.
1. SELECT User, Host,
Super_priv, Create_user_priv, Grant_priv from mysql.user WHERE Create_user_priv
= 'Y' AND Grant_Priv = 'Y';
Ejecute la siguiente consulta para generar las declaraciones GRANT para su usuario restringido. Reemplace "mydatabase", "myuser" y "myhost" con información específica para su base de datos.
Tenga en cuenta que las citas que rodean myuser y mypassword son dos comillas simples, no dobles. Los caracteres que rodean myhost y, TABLE_NAME, son puntos de retroceso (la tecla se encuentra debajo de la tecla de escape en su teclado)
SELECT
CONCAT('GRANT SELECT, SHOW VIEW ON mydatabase.`', TABLE_NAME, '` to
''myuser''@`myhost`;')
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_SCHEMA = 'mydatabase';
Por ejemplo, si desea conectar el usuario "chartio_read_only"
a su base de datos "Informes" utilizando el cliente chartio_connect,
debe ejecutar lo siguiente:
SELECT
CONCAT('GRANT SELECT, SHOW VIEW ON Reports.`', TABLE_NAME, '` to
''chartio_read_only''@`localhost`;')
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_SCHEMA = 'Reports';
Si desea conectar el usuario "chartio_direct_connect" a su base de datos "Analytics" utilizando una conexión directa desde los servidores de Chartio, debe ejecutar lo siguiente:
SELECT
CONCAT('GRANT SELECT, SHOW VIEW ON Analytics.`', TABLE_NAME, '` to
''chartio_direct_connect''@`52.6.1.1`;')
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_SCHEMA = 'Analytics';
La consulta debería ser similar a la siguiente:
1. GRANT SELECT, SHOW
VIEW ON mydatabase.`Activity` to 'myuser'@`myhost`;
2. GRANT SELECT, SHOW
VIEW ON mydatabase.`Marketing` to 'myuser'@`myhost`;
3. GRANT SELECT, SHOW
VIEW ON mydatabase.`Operations` to 'myuser'@`myhost`;
4. GRANT SELECT, SHOW
VIEW ON mydatabase.`Payments` to 'myuser'@`myhost`;
5. GRANT SELECT, SHOW
VIEW ON mydatabase.`Plans` to 'myuser'@`myhost`;
6. GRANT SELECT, SHOW
VIEW ON mydatabase.`Services` to 'myuser'@`myhost`;
7. GRANT SELECT, SHOW
VIEW ON mydatabase.`Subscriptions` to 'myuser'@`myhost`;
8. GRANT SELECT, SHOW
VIEW ON mydatabase.`Users` to 'myuser'@`myhost`;
9. GRANT SELECT, SHOW
VIEW ON mydatabase.`Visitors` to 'myuser'@`myhost`;
Seleccione las declaraciones solo para las tablas a las que le gustaría otorgar acceso y ejecute esas consultas. Por ejemplo, si solo quisiéramos otorgar acceso a la tabla Usuarios y Visitantes, ejecutaríamos:
GRANT
SELECT, SHOW VIEW ON mydatabase.`Users` to 'myuser'@`myhost`;
GRANT
SELECT, SHOW VIEW ON mydatabase.`Visitors` to 'myuser'@`myhost`;
Dar a la usuario una contraseña segura.
SET
PASSWORD FOR 'chartio_read_only'@`localhost` = PASSWORD('top$secret');
o
SET
PASSWORD FOR 'chartio_direct_connect'@`52.6.1.1` = PASSWORD('top$secret');
Ahora puede acceder de forma segura a su base de datos con este usuario
y tener la seguridad de que solo tiene permisos para las tablas especificadas.
Permisos a nivel de
columnas
Estas tablas de base de datos mysql contienen información de concesión:
user: cuentas de usuario, privilegios globales y otras columnas sin privilegios.
db: privilegios a nivel de base de datos.
tables_priv: privilegios a nivel de tabla.
columns_priv: privilegios a nivel de columna.
procs_priv: procedimiento almacenado y privilegios de función.
proxies_priv: Privilegios de usuario proxy.
Cada tabla de concesión contiene columnas de alcance y columnas de privilegio:
Las columnas de alcance determinan el alcance de cada fila en las tablas; es decir, el contexto en el que se aplica la fila. Por ejemplo, una fila de la tabla de usuario con los valores Host y User de 'h1.example.net' y 'bob' se aplica a la autenticación de conexiones realizadas al servidor desde el host h1.example.net por un cliente que especifica un nombre de usuario de bob . De manera similar, se aplica una fila de tabla db con los valores de columna Host, Usuario y Db de 'h1.example.net', 'bob' e 'informes' cuando bob se conecta desde el host h1.example.net para acceder a la base de datos de informes. Las tablas tables_priv y columnas_priv contienen columnas de alcance que indican tablas o combinaciones de tabla / columna a las que se aplica cada fila. Las columnas de alcance procs_priv indican la rutina almacenada a la que se aplica cada fila.
Las columnas de privilegio indican qué privilegios otorga una fila de tabla; es decir, qué operaciones permite realizar. El servidor combina la información en las diversas tablas de concesión para formar una descripción completa de los privilegios de un usuario. La Sección 4.6, “Control de acceso, Etapa 2: Verificación de solicitud”, describe las reglas para esto.
Además, una tabla de subvención puede contener columnas utilizadas para fines distintos de la evaluación del alcance o privilegio.
El servidor utiliza las tablas de concesión de la siguiente manera:
Las columnas de alcance de la tabla de usuario determinan si rechazar o permitir conexiones entrantes. Para las conexiones permitidas, cualquier privilegio otorgado en la tabla de usuario indica los privilegios globales del usuario. Todos los privilegios otorgados en esta tabla se aplican a todas las bases de datos en el servidor.
Las columnas del alcance de la tabla db determinan qué usuarios pueden acceder a qué bases de datos desde qué hosts. Las columnas de privilegios determinan las operaciones permitidas. Un privilegio otorgado a nivel de base de datos se aplica a la base de datos y a todos los objetos en la base de datos, como tablas y programas almacenados.
Las tablas tables_priv y columnas_priv son similares a la tabla db, pero son más detalladas: se aplican a los niveles de tabla y columna en lugar de a nivel de base de datos. Un privilegio otorgado a nivel de tabla se aplica a la tabla y a todas sus columnas. Un privilegio otorgado a nivel de columna se aplica solo a una columna específica.
La tabla procs_priv se aplica a las rutinas almacenadas (procedimientos y funciones almacenados). Un privilegio otorgado en el nivel de rutina se aplica solo a un solo procedimiento o función.
La tabla proxies_priv indica qué usuarios pueden actuar como servidores proxy para otros usuarios y si un usuario puede otorgar el privilegio PROXY a otros usuarios.
El servidor lee el contenido de las tablas de concesión en la memoria cuando se inicia. Puede indicarle que vuelva a cargar las tablas emitiendo una instrucción FLUSH PRIVILEGES o ejecutando un comando mysqladmin flush-privileges o mysqladmin reload. Los cambios en las tablas de concesión entran en vigencia como se indica en la Sección 4.9, “Cuándo entran en vigencia los cambios de privilegios”.
Cuando modifica una cuenta, es una buena idea verificar que sus cambios tengan el efecto deseado. Para verificar los privilegios de una cuenta determinada, use la instrucción SHOW GRANTS. Por ejemplo, para determinar los privilegios que se otorgan a una cuenta con los valores de nombre de usuario y nombre de host de bob y pc84.example.com, use esta declaración:
user: cuentas de usuario, privilegios globales y otras columnas sin privilegios.
db: privilegios a nivel de base de datos.
tables_priv: privilegios a nivel de tabla.
columns_priv: privilegios a nivel de columna.
procs_priv: procedimiento almacenado y privilegios de función.
proxies_priv: Privilegios de usuario proxy.
Cada tabla de concesión contiene columnas de alcance y columnas de privilegio:
Las columnas de alcance determinan el alcance de cada fila en las tablas; es decir, el contexto en el que se aplica la fila. Por ejemplo, una fila de la tabla de usuario con los valores Host y User de 'h1.example.net' y 'bob' se aplica a la autenticación de conexiones realizadas al servidor desde el host h1.example.net por un cliente que especifica un nombre de usuario de bob . De manera similar, se aplica una fila de tabla db con los valores de columna Host, Usuario y Db de 'h1.example.net', 'bob' e 'informes' cuando bob se conecta desde el host h1.example.net para acceder a la base de datos de informes. Las tablas tables_priv y columnas_priv contienen columnas de alcance que indican tablas o combinaciones de tabla / columna a las que se aplica cada fila. Las columnas de alcance procs_priv indican la rutina almacenada a la que se aplica cada fila.
Las columnas de privilegio indican qué privilegios otorga una fila de tabla; es decir, qué operaciones permite realizar. El servidor combina la información en las diversas tablas de concesión para formar una descripción completa de los privilegios de un usuario. La Sección 4.6, “Control de acceso, Etapa 2: Verificación de solicitud”, describe las reglas para esto.
Además, una tabla de subvención puede contener columnas utilizadas para fines distintos de la evaluación del alcance o privilegio.
El servidor utiliza las tablas de concesión de la siguiente manera:
Las columnas de alcance de la tabla de usuario determinan si rechazar o permitir conexiones entrantes. Para las conexiones permitidas, cualquier privilegio otorgado en la tabla de usuario indica los privilegios globales del usuario. Todos los privilegios otorgados en esta tabla se aplican a todas las bases de datos en el servidor.
Las columnas del alcance de la tabla db determinan qué usuarios pueden acceder a qué bases de datos desde qué hosts. Las columnas de privilegios determinan las operaciones permitidas. Un privilegio otorgado a nivel de base de datos se aplica a la base de datos y a todos los objetos en la base de datos, como tablas y programas almacenados.
Las tablas tables_priv y columnas_priv son similares a la tabla db, pero son más detalladas: se aplican a los niveles de tabla y columna en lugar de a nivel de base de datos. Un privilegio otorgado a nivel de tabla se aplica a la tabla y a todas sus columnas. Un privilegio otorgado a nivel de columna se aplica solo a una columna específica.
La tabla procs_priv se aplica a las rutinas almacenadas (procedimientos y funciones almacenados). Un privilegio otorgado en el nivel de rutina se aplica solo a un solo procedimiento o función.
La tabla proxies_priv indica qué usuarios pueden actuar como servidores proxy para otros usuarios y si un usuario puede otorgar el privilegio PROXY a otros usuarios.
El servidor lee el contenido de las tablas de concesión en la memoria cuando se inicia. Puede indicarle que vuelva a cargar las tablas emitiendo una instrucción FLUSH PRIVILEGES o ejecutando un comando mysqladmin flush-privileges o mysqladmin reload. Los cambios en las tablas de concesión entran en vigencia como se indica en la Sección 4.9, “Cuándo entran en vigencia los cambios de privilegios”.
Cuando modifica una cuenta, es una buena idea verificar que sus cambios tengan el efecto deseado. Para verificar los privilegios de una cuenta determinada, use la instrucción SHOW GRANTS. Por ejemplo, para determinar los privilegios que se otorgan a una cuenta con los valores de nombre de usuario y nombre de host de bob y pc84.example.com, use esta declaración:
Para mostrar las propiedades sin privilegios de una cuenta, use SHOW CREATE
USER:
Permisos sobre
rutinas
Específico para los procedimientos, el esquema de permisos de MySQL
tiene los privilegios CREATE ROUTINE, ALTER ROUTINE y EXECUTE.
Los permisos necesarios para trabajar con procedimientos almacenados son los siguientes:
Ver permisos
Para ver los procedimientos almacenados con SHOW PROCEDURE STATUS, debe tener acceso SELECT a la tabla mysql.proc.
O debe tener el privilegio ALTERAR RUTINA para ese procedimiento en particular.
Permisos de llamadas
Para llamar a un procedimiento almacenado
Necesita la capacidad de conectarse al servidor y tener el permiso EXECUTE para el procedimiento.
Los permisos EXECUTE se pueden otorgar globalmente en la tabla mysql.user.
Los permisos EXECUTE se pueden otorgar a nivel de base de datos en la tabla mysql.db
Se pueden otorgar permisos EXECUTE para una rutina específica en la tabla mysql.procs_priv.
Crear y modificar permisos
Para gobernar la creación y la modificación de un procedimiento almacenado, MySQL utiliza el privilegio CREATE ROUTINE y ALTER ROUTINE.
Los permisos para crear o cambiar procedimientos se pueden otorgar globalmente en la tabla mysql.user.
Los permisos para crear o cambiar procedimientos se pueden otorgar a nivel de base de datos en la tabla mysql.db.
Se pueden otorgar permisos para crear o cambiar procedimientos para una rutina específica en la tabla mysql.procs_priv.
Eliminación de permisos
Para descartar un procedimiento, debe tener el privilegio ALTERAR RUTINA.
Los permisos para descartar procedimientos se pueden otorgar globalmente en la tabla mysql.user.
Los permisos para los procedimientos de descarte se pueden otorgar a nivel de base de datos en la tabla mysql.db.
Se pueden otorgar permisos para descartar procedimientos para una rutina específica en la tabla mysql.procs_priv.
Los permisos necesarios para trabajar con procedimientos almacenados son los siguientes:
Ver permisos
Para ver los procedimientos almacenados con SHOW PROCEDURE STATUS, debe tener acceso SELECT a la tabla mysql.proc.
O debe tener el privilegio ALTERAR RUTINA para ese procedimiento en particular.
Permisos de llamadas
Para llamar a un procedimiento almacenado
Necesita la capacidad de conectarse al servidor y tener el permiso EXECUTE para el procedimiento.
Los permisos EXECUTE se pueden otorgar globalmente en la tabla mysql.user.
Los permisos EXECUTE se pueden otorgar a nivel de base de datos en la tabla mysql.db
Se pueden otorgar permisos EXECUTE para una rutina específica en la tabla mysql.procs_priv.
Crear y modificar permisos
Para gobernar la creación y la modificación de un procedimiento almacenado, MySQL utiliza el privilegio CREATE ROUTINE y ALTER ROUTINE.
Los permisos para crear o cambiar procedimientos se pueden otorgar globalmente en la tabla mysql.user.
Los permisos para crear o cambiar procedimientos se pueden otorgar a nivel de base de datos en la tabla mysql.db.
Se pueden otorgar permisos para crear o cambiar procedimientos para una rutina específica en la tabla mysql.procs_priv.
Eliminación de permisos
Para descartar un procedimiento, debe tener el privilegio ALTERAR RUTINA.
Los permisos para descartar procedimientos se pueden otorgar globalmente en la tabla mysql.user.
Los permisos para los procedimientos de descarte se pueden otorgar a nivel de base de datos en la tabla mysql.db.
Se pueden otorgar permisos para descartar procedimientos para una rutina específica en la tabla mysql.procs_priv.
Tipos numéricos:
Existen tipos de datos numéricos, que se pueden dividir en dos grandes
grupos, los que están en coma flotante (con decimales) y los que no.
TinyInt:
Es un número entero con o sin signo. Con signo el rango de valores
válidos va desde -128 a 127. Sin signo, el rango de valores es de 0 a 255
Bit ó Bool:
Un número entero que puede ser 0 ó 1
SmallInt:
Número entero con o sin signo. Con signo el rango de valores va desde
-32768 a 32767. Sin signo, el rango de valores es de 0 a 65535.
MediumInt:
Número entero con o sin signo. Con signo el rango de valores va desde
-8.388.608 a 8.388.607. Sin signo el rango va desde 0 a16777215.
Integer, Int:
Número entero con o sin signo. Con signo el rango de valores va desde
-2147483648 a 2147483647. Sin signo el rango va desde 0 a 429.4967.295
BigInt:
Número entero con o sin signo. Con signo el rango de valores va desde
-9.223.372.036.854.775.808 a 9.223.372.036.854.775.807. Sin signo el rango va
desde 0 a 18.446.744.073.709.551.615.
Float:
Número pequeño en coma flotante de precisión simple. Los valores válidos
van desde -3.402823466E+38 a -1.175494351E-38, 0 y desde 1.175494351E-38 a
3.402823466E+38.
xReal, Double:
Número en coma flotante de precisión doble. Los valores permitidos van
desde -1.7976931348623157E+308 a -2.2250738585072014E-308, 0 y desde
2.2250738585072014E-308 a 1.7976931348623157E+308
Decimal, Dec, Numeric:
Número en coma flotante desempaquetado. El número se almacena como una
cadena
|
Tipo de Campo
|
Tamaño de Almacenamiento
|
|
TINYINT
|
1 byte
|
|
SMALLINT
|
2 bytes
|
|
MEDIUMINT
|
3 bytes
|
|
INT
|
4 bytes
|
|
INTEGER
|
4 bytes
|
|
BIGINT
|
8 bytes
|
|
FLOAT(X)
|
4 ú 8 bytes
|
|
FLOAT
|
4 bytes
|
|
DOUBLE
|
8 bytes
|
|
DOUBLE PRECISION
|
8 bytes
|
|
REAL
|
8 bytes
|
|
DECIMAL(M,D
|
M+2 bytes sí D > 0, M+1 bytes sí D = 0
|
|
NUMERIC(M,D)
|
M+2 bytes if D > 0, M+1 bytes if D = 0
|
Tipos fecha:
A la hora de almacenar fechas, hay que tener en cuenta que Mysql no
comprueba de una manera estricta si una fecha es válida o no. Simplemente
comprueba que el mes esta comprendido entre 0 y 12 y que el día esta
comprendido entre 0 y 31.
Date:
Tipo fecha, almacena una fecha. El rango de valores va desde el 1 de
enero del 1001 al 31 de diciembre de 9999. El formato de almacenamiento es de
año-mes-dia
DateTime:
Combinación de fecha y hora. El rango de valores va desde el 1 de enero
del 1001 a las 0 horas, 0 minutos y 0 segundos al 31 de diciembre del 9999 a
las 23 horas, 59 minutos y 59 segundos. El formato de almacenamiento es de
año-mes-dia horas:minutos:segundos
TimeStamp:
Combinación de fecha y hora. El rango va desde el 1 de enero de 1970 al
año 2037. El formato de almacenamiento depende del tamaño del campo:
|
Tamaño
|
Formato
|
|
14
|
AñoMesDiaHoraMinutoSegundo aaaammddhhmmss
|
|
12
|
AñoMesDiaHoraMinutoSegundo aammddhhmmss
|
|
8
|
ñoMesDia aaaammdd
|
|
6
|
AñoMesDia aammdd
|
|
4
|
AñoMes aamm
|
|
2
|
Año aa
|
Time:
Almacena una hora. El rango de horas va desde -838 horas, 59 minutos y
59 segundos a 838, 59 minutos y 59 segundos. El formato de almacenamiento es de
'HH:MM:SS'
Year:
Almacena un año. El rango de valores permitidos va desde el año 1901 al
año 2155. El campo puede tener tamaño dos o tamaño 4 dependiendo de si queremos
almacenar el año con dos o cuatro dígitos.
|
Tipo de Campo
|
Tamaño de Almacenamiento
|
|
DATE
|
3 bytes
|
|
DATETIME
|
8 bytes
|
|
TIMESTAMP
|
4 bytes
|
|
TIME
|
3 bytes
|
|
YEAR
|
1 byte
|
Tipos de cadena:
Char(n):
Almacena una cadena de longitud fija. La cadena podrá contener desde 0 a
255 caracteres.
VarChar(n):
Almacena una cadena de longitud variable. La cadena podrá contener desde
0 a 255 caracteres.
Dentro de los tipos de cadena se pueden distinguir otros dos subtipos,
los tipo Test y los tipo BLOB (Binary large Object)
La diferencia entre un tipo y otro es el tratamiento que reciben a la
hora de realizar ordenamientos y comparaciones. Mientras que el tipo test se
ordena sin tener en cuenta las Mayúsculas y las minúsculas, el tipo BLOB se
ordena teniéndolas en cuenta.
Los tipos BLOB se utilizan para almacenar datos binarios como pueden ser
ficheros.
TinyText y TinyBlob:
Columna con una longitud máxima de 255 caracteres.
Blob y Text:
Un texto con un máximo de 65535 caracteres.
MediumBlob y MediumText:
Un texto con un máximo de 16.777.215 caracteres.
LongBlob y LongText:
Un texto con un máximo de caracteres 4.294.967.295. Hay que tener en
cuenta que debido a los protocolos de comunicación los paquetes pueden tener un
máximo de 16 Mb.
Enum:
Campo que puede tener un único valor de una lista que se especifica. El
tipo Enum acepta hasta 65535 valores distintos
Set:
Un campo que puede contener ninguno, uno ó varios valores de una lista.
La lista puede tener un máximo de 64 valores.
|
Tipo de campo
|
Tamaño de Almacenamiento
|
|
CHAR(n)
|
n bytes
|
|
VARCHAR(n)
|
n +1 bytes
|
|
TINYBLOB, TINYTEXT
|
Longitud+1 bytes
|
|
BLOB, TEXT
|
Longitud +2 bytes
|
|
MEDIUMBLOB, MEDIUMTEXT
|
Longitud +3 bytes
|
|
LONGBLOB, LONGTEXT
|
Longitud +4 bytes
|
|
ENUM('value1','value2',...)
|
1 ó dos bytes dependiendo del número de valores
|
|
SET('value1','value2',...)
|
1, 2, 3, 4 ó 8 bytes, dependiendo del número de
valores
|
Diferencia de almacenamiento entre los tipos Char y VarChar
|
Valor
|
CHAR(4)
|
Almace
namiento |
VARCHAR(4)
|
Almace
namiento |
|
''
|
''
|
4 bytes
|
"
|
1 byte
|
|
'ab'
|
'ab '
|
4 bytes
|
'ab'
|
3 bytes
|
|
'abcd'
|
'abcd'
|
4 bytes
|
'abcd'
|
|
|
'abcdefgh'
|
'abcd'
|
4 bytes
|
'abcd'
|
5 bytes
|
Comentarios
Publicar un comentario