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:
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.
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
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.
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.
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.
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.
Permite el uso de la instrucción SHOW CREATE VIEW. Este privilegio también es necesario para las vistas utilizadas con EXPLAIN.
Permite el uso de la instrucción SHUTDOWN, el comando de apagado mysqladmin y la función de API mysql_shutdown () C.
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”.
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.
Permite que las filas se actualicen en tablas en una base de datos.
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.
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)
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:
La consulta debería ser similar a la siguiente:
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:
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:
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.
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 | Alter_priv | Tables |
ALTER ROUTINE | Alter_routine_priv | Stored routines |
CREATE | Create_priv | Databases, tables, or indexes |
CREATE ROUTINE | Create_routine_priv | Stored routines |
CREATE TABLESPACE | Create_tablespace_priv | Server administration |
CREATE TEMPORARY TABLES | Create_tmp_table_priv | Tables |
CREATE USER | Create_user_priv | Server administration |
CREATE VIEW | Create_view_priv | Views |
DELETE | Delete_priv | Tables |
DROP | Drop_priv | Databases, tables, or views |
EVENT | Event_priv | Databases |
EXECUTE | Execute_priv | Stored routines |
FILE | File_priv | File access on server host |
GRANT OPTION | Grant_priv | Databases, tables, or stored routines |
INDEX | Index_priv | Tables |
INSERT | Insert_priv | Tables or columns |
LOCK TABLES | Lock_tables_priv | Databases |
PROCESS | Process_priv | Server administration |
PROXY | See proxies_priv table | Server administration |
REFERENCES | References_priv | Databases or tables |
RELOAD | Reload_priv | Server administration |
REPLICATION CLIENT | Repl_client_priv | Server administration |
REPLICATION SLAVE | Repl_slave_priv | Server administration |
SELECT | Select_priv | Tables or columns |
SHOW DATABASES | Show_db_priv | Server administration |
SHOW VIEW | Show_view_priv | Views |
SHUTDOWN | Shutdown_priv | Server administration |
SUPER | Super_priv | Server administration |
TRIGGER | Trigger_priv | Tables |
UPDATE | Update_priv | Tables or columns |
USAGE | 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 CLIENTPermite 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 SLAVEPermite 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.
SELECTPermite 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 DATABASESPermite 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 VIEWPermite el uso de la instrucción SHOW CREATE VIEW. Este privilegio también es necesario para las vistas utilizadas con EXPLAIN.
SHUTDOWNPermite el uso de la instrucción SHUTDOWN, el comando de apagado mysqladmin y la función de API mysql_shutdown () C.
SUPERAfecta 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”.
TRIGGERHabilita 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.
UPDATEPermite que las filas se actualicen en tablas en una base de datos.
USAGEPermisos 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.
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';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:
GRANT SELECT, SHOW VIEW ON mydatabase.`Activity` to 'myuser'@`myhost`; GRANT SELECT, SHOW VIEW ON mydatabase.`Marketing` to 'myuser'@`myhost`; GRANT SELECT, SHOW VIEW ON mydatabase.`Operations` to 'myuser'@`myhost`; GRANT SELECT, SHOW VIEW ON mydatabase.`Payments` to 'myuser'@`myhost`; GRANT SELECT, SHOW VIEW ON mydatabase.`Plans` to 'myuser'@`myhost`; GRANT SELECT, SHOW VIEW ON mydatabase.`Services` to 'myuser'@`myhost`; GRANT SELECT, SHOW VIEW ON mydatabase.`Subscriptions` to 'myuser'@`myhost`; GRANT SELECT, SHOW VIEW ON mydatabase.`Users` to 'myuser'@`myhost`; 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`;SET PASSWORD FOR 'chartio_read_only'@`localhost` = PASSWORD('top$secret');SET PASSWORD FOR 'chartio_direct_connect'@`52.6.1.1` = PASSWORD('top$secret');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:
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.
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