INSTITUTO TECNOLÓGICO DE ACAPULCO
Nombre del Alumno:
Núm. Control
Arizmendi Fernández Fernández Luis Luis Arturo
GRUPO: IS2
AULA: 7LA
11320505
HORARIO: 08:00 A.M.-09:00 A.M.
MATERIA: Taller de Base de Datos PROFESOR: Sebastián Núñez Cesar Augusto
CREACION DE USUARIOS, PRIVILEGIOS Y ROLES
1
CREAR USUARIO MYSQL CON CREATE USER El comando “ CREATE USER” de MySQL nos va a permitir crear usuarios y asignarles una contraseña con el parámetro “ IDENTIFIED BY“. Más adelante veremos que este paso lo podemos evitar utilizando directamente el comando “ GRANT” para la creación del usuario y asignación de privilegios en un único paso. La sintaxis es la siguiente:
CREATE USER user [IDENTIFIED BY [PASSWORD] 'password']
Así pues, si quisieramos crear un usuario llamado “foo” y con clave “mipassword” ejecutaríamos el siguiente comando:
mysql> CREATE USER foo IDENTIFIED BY 'mypassword';
Query OK, 0 rows affected (0.01 sec)
Tenemos la opción de especificar la contraseña con su valor en hash. Este valor debe ser generado anteriormente con la función PASSWORD() de MySQL:
mysql> SELECT PASSWORD('mipassword');
+-------------------------------------------+
| PASSWORD('mipassword')
|
+-------------------------------------------+
| *CEE870801502ACAD44FA46CA2CA4F58C2B721A67 |
+-------------------------------------------+
1 row in set (0.00 sec)
2
mysql> CREATE USER foo IDENTIFIED BY PASSWORD '*CEE870801502ACAD44FA46CA2CA4F58C2B721A67';
Query OK, 0 rows affected (0.01 sec)
ELIMINAR USUARIO MYSQL Eliminar un usuario MySQL es sencillo, el comando a utilizar es “ DROP USER” seguido del usuario a eliminar:
mysql> DROP USER foo;
Query OK, 0 rows affected (0.00 sec)
ASIGNAR PRIVILEGIOS AL USUARIO CON GRANT Cuando creamos un usuario utilizando CREATE USER, efectivamente la creación es satisfactoria, pero veréis que el usuario únicamente puede conectarse y visualizar ciertos parámetros del servidor. La verdad es que podemos crear directamente los usuarios con el comando GRANT y a la vez asignar los privilegios que queramos respecto a las bases de datos del sistema. Básicamente GRANT asigna privilegios al usuarios indicado, y si no
existe lo crea. La sintaxis y modo de uso del comando sería la siguiente:
GRANT ON privilegios TO 'usuario'@'host_de_conexion'
-> IDENTIFIED BY 'password' WITH GRANT OPTION;
En el siguiente ejemplo, asignamos todos los privilegios posibles al usuario “foo” (si no existe se crea) sobre todas las bases de datos y tablas, para conexiones desde localhost únicamente y además le concedemos el permiso GRANT para que tenga la opción de asignar permisos a otros usuarios:
mysql> GRANT ALL PRIVILEGES ON *.* TO 'foo'@'localhost'
3
-> IDENTIFIED BY 'mipassword' WITH GRANT OPTION;
Los privilegios disponibles son los siguientes: ALL PRIVILEGES, ALTER, ALTER ROUTINE, CREATE TABLE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE USER (Permite utilizar CREATE USER, DROP USER, RENAME USER, y REVOKE ALL PRIVILEGES), CREATE VIEW, DELETE, DROP, EXECUTE, FILE, INDEX, INSERT, LOCK TABLES, PROCESS, RELOAD, REPLICATION CLIENT,
REPLICATION
SLAVE,
SELECT,
SHOW
DATABASES,
SHOW
VIEW,
SHUTDOWN, SUPER, UPDATE, USAGE, GRANT OPTION. En la documentación oficial de MySQL encontraréis información y detalle de cada privilegio. Recordad que el comando HELP de la shell de MySQL os mostrará la ayuda relativa a cada comando. Para encontrar ayuda de GRANT usamos HELP GRANT y vemos que además de la información relativa al comando aparecen varios ejemplos de uso:
mysql> HELP GRANT
[...]
CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';
GRANT ALL ON db1.* TO 'jeffrey'@'localhost';
GRANT SELECT ON db2.invoice TO 'jeffrey'@'localhost';
GRANT USAGE ON *.* TO 'jeffrey'@'localhost' WITH MAX_QUERIES_PER_HOUR 90;
[...]
Como podéis observar, cuando los privilegios hacen referencia a bases de datos, tablas o hosts distintos se ejecuta un comando por cada uno de ellos, no se puede englobar todo en una única ejecución ya que se almacena en registros distintos dentro de la tabla de privilegios.
4
Si quisieramos asignar privilegios a un usuario que va a conectar desde distintos hosts, lo haríamos de modo individual, es decir:
mysql> GRANT ALL ON db1.* TO 'foo'@'host1';
mysql> GRANT ALL ON db1.* TO 'foo'@'host2';
Lo mismo para distintas bases de datos o tablas:
mysql> GRANT ALL ON db1.* TO 'foo'@'localhost';
mysql> GRANT ALL ON db2.* TO 'foo'@'localhost';
mysql> GRANT ALL ON db3.tabla1 TO 'foo'@'localhost';
mysql> GRANT ALL ON db4.tabla1 TO 'foo'@'localhost';
Para asignar un “wildcard” de hosts utilizamos el car ácter %. Es decir, de este modo el usuario puede conectar a MySQL desde cualquier host:
mysql> GRANT ALL ON db1.* TO 'foo'@'%';
VISUALIZAR PRIVILEGIOS DE UN USUARIO CON SHOW GRANTS El comando SHOW GRANTS sirve para visualizar en la salida estándar de la shell de MySQL los privilegios asignados a un usuario:
mysql> SHOW GRANTS for 'foo'@'localhost';
+-------------------------------------------------------------------------------+
| Grants for foo@localhost
|
+-------------------------------------------------------------------------------+
5
| GRANT ALL PRIVILEGES ON *.* TO 'foo'@'localhost' IDENTIFIED BY PASSWORD '...' |
+-------------------------------------------------------------------------------+
1 row in set (0.00 sec)
REVOCAR PRIVILEGIOS AL USUARIO CON REVOKE Para eliminar privilegios que previamente hemos asignado a un usuario MySQL con el comando GRANT, utilizaremos el comando REVOKE. La sintaxis sería la siguiente:
REVOKE privilegios, GRANT OPTION FROM 'usuario'@'host';
La opción “GRANT OPTION” sólo es necesaria si el usuario ha sido configurado con el privilegio “WITH GRANT OPTION”. Al igual que a la hora de asignar los privilegios se realiza de forma individual por base de datos, host, tabla, etc. A la hora de revocar es lo mismo:
mysql> SHOW GRANTS for 'foo'@'localhost';
+--------------------------------------------------------------------------------+
| Grants for foo@localhost
|
+--------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'foo'@'localhost' IDENTIFIED BY PASSWORD '*CEE' |
| GRANT SELECT ON `test`.* TO 'foo'@'localhost'
|
+--------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> SHOW GRANTS for 'foo'@'host1';
6
+--------------------------------------------------------------------------------+
| Grants for foo@host1
|
+--------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'foo'@'host1' IDENTIFIED BY PASSWORD '*CEE8707' |
+--------------------------------------------------------------------------------+
1 row in set (0.01 sec)
Una vez visualizados los privilegios con el comando SHOW GRANTS, eliminamos los que estimemos oportuno:
mysql> REVOKE SELECT ON test.* FROM 'foo'@'localhost';
Query OK, 0 rows affected (0.01 sec)
En el caso de „foo‟@‟localhost‟ vemos que tiene todos los privilegios para todas las bases de datos, en lugar de eliminar completamente esa entrada de privilegios podemos hacerla más restrictiva, por ejemplo manteniendo todos los permisos excepto INSERT:
mysql> REVOKE INSERT ON *.* FROM 'foo'@'localhost';
Query OK, 0 rows affected (0.00 sec)
Y vemos como MySQL automáticamente elimina el privilegio INSERT y mantiene el resto:
mysql> SHOW GRANTS for 'foo'@'%';
ERROR 1141 (42000): There is no such grant defined for user 'foo' on host '%'
mysql> SHOW GRANTS for 'foo'@'localhost';
+---------------------------------------------------------------------------+
7
| Grants for foo@localhost
|
+---------------------------------------------------------------------------+
| GRANT SELECT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS,
FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER,
CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE,
REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE,
CREATE USER, EVENT, TRIGGER ON *.* TO 'foo'@'localhost' IDENTIFIED BY PASSWORD
'*CEE870801502ACAD44FA46CA2CA4F58C2B721A67' |
+---------------------------------------------------------------------------+
1 row in set (0.01 sec)
FLUSH PRIVILEGES El comando “ FLUSH PRIVILEGES ” sirve para recargar la tabla de privilegios pero sólo es necesario
cuando
se
manipulan
directamente
las
tablas
de
privilegios
ejecutando INSERT, DELETE, etc en lugar de usar los comandos GRANT y REVOKE:
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)
Es decir, en los casos que he especificado en este artículo no es necesario utilizar este modo de recargar los privilegios, sólo sería necesario si crearamos o eliminaramos los usuarios “a mano” directamente sin los comandos específicos para ello de MySQL:
mysql> INSERT INTO user
-> VALUES('localhost','foo',mipassword'),
8
-> 'Y','Y','Y','Y','Y','Y','N','N','N','Y','Y','Y','Y','Y');
Un rol es una colección de privilegios del sistema y de objetos que se otorgan a usuarios y a otras tareas. Oracle dispone de muchos roles predeterminados mientras que MySQL no los soporta. El rol CONNECECT permite al usuario conectarse a la base de datos, crear tablas, vistas, secuencias, sinónimos y otros objetos en el esquema asociado. El rol RESOURCE permite permite al usuario utilizar los recursos típicos para la programación de aplicaciones (clusters, disparadores, paquetes, funciones, etc.) El rol DBA, típico de los administradores, permite al usuario realizar cualquier función de base de datos y disponer de cualquier privilegio La sentencia que permite crear roles es CREATE ROL. Su sintaxis es la siguiente ? 1 2 3 4
CREATE ROLE rol [ NOT IDENTIFIED | IDENTIFIED {BY password | USING [usuario.] paquete | EXTERNALLY | GLOBALLY} ];
NOT IDENTIFIED indica que no se requiere contraseña para utilizar el
rol, INDENTIFIED
BY password
indica
que
se
requiere
la
contraseñ;a
especificada. EXTERNALLY crea un rol de usuario externo y GLOBALLY crea un rol de usuario global. Ejemplo: Crear un rol y asignarle privilegios ? 1 2 3
CREATE ROLE miPrimerRole; GRANT SELECT, INSERT, UPDATE, DELETE ON tabla TO miPrimerRole;
Ejemplo: Crear un rol y asignarle todos los privilegios ? 1 2
9
CREATE ROLE miPrimerRole; GRANT ALL ON tabla TO miPrimerROLE;
Ejemplo: Asignar un rol a un usuario ? 1
GRANT miPrimerRole TO miUsuario;
Ejemplo: Eliminar un rol ? 1
DROP ROLE miPrimerRole;
USUARIOS, PERMISOS Y SQL SERVER LOGINS Y USERS En SQL Server hay que distinguir entre Logins y Users. Los Logins se crean a nivel de servidor de base de datos. Pueden crearse como Logins de SQL Server, o pueden crearse a partir de una cuenta Windows, lo que en general es más seguro por utilizarse la autenticación integrada. En mi caso era precisamente eso lo que pretendía hacer, crearlos a partir de una determinada cuenta Windows. Esto es muy sencillo de hacer, basta un comando como el que se puede ver a continuación: USE [yourdb] GO CREATE LOGIN [YourDomainYourUser] FROM WINDOWS GO Sin embargo, esto tiene una pega: no se puede hacer en los proyectos de base de datos de Visual Studio 2008, o al menos yo no he encontrado el modo. Tiene su sentido, de todas formas, puesto que estos proyectos no dejan de ser la creación de una base de datos concreta, y no debería afectar a la configuración del servidor en sí. Una vez creado el Login, ya podemos crear un usuario y asociarlo a él. Esta parte sí se puede añadir al proyecto de BD de VS2008, en concreto en la carpeta Users dentro de Security. El TSQL necesario sería algo así: CREATE USER [YourDomainYourDbUser] FROM LOGIN [YourDomainYourUser] GO Bien, con esto ya tendríamos nuestro usuario concreto de la base de datos creado, asociado a un Login a nivel del servidor.
ROLES Y PERMISOS El siguiente paso es crear el rol al que vamos a asociar el usuario que recién hemos creado. Este rol va a pertenecerle a dbo. No podemos darle sus permisos directamente en el script que se crea en la carpeta Database Roles de Security, esa parte tendremos que añadirla en 10
un script de Post-deployment. Así que en el script de creación del rol simplemente tendríamos la siguiente sentencia TSQL: CREATE ROLE [YourNewRole] AUTHORIZATION [dbo] GO Quedarían dos pasos por ejecutar: darle los permisos al rol que necesite y asociarle el usuario que creamos al principio. La primera parte se completa con una sentencia parecida a ésta. Sería necesario indicar qué esquema de los existentes en la base de datos, va a poder el rol ejecutar sus SPs. GRANT EXECUTE ON SCHEMA ::[YourSchema] TO [YourNewRole] GO En cuanto a la segunda, bastaría algo como lo que sigue, apoyándonos en uno de los procedimientos almacenados del sistema: EXEC sp_addrolemember N'YourNewRole', N'YourDomainYourUser' GO
CREAR USUARIOS, PRIVILEGIOS Y ROLES POSTGRESQL
Abrir un cliente de PostgreSQL Para poder desarrollar este nanotutorial necesitamos tener disponible una sesión cliente en un servidor PostgreSQL. Para esto debemos iniciar el cliente con el siguiente comando: psql -U postgres -h localhost -W
11
Si no posees una instalación de PostgreSQL como servidor, te invitamos a que la hagas a través de este nanotutorial: Instalación de PostgreSQL Server.
Crear usuario Para crear un usuario lo haremos con el siguiente comando: CREATE USER nanotutoriales PASSWORD 'password';
12
Eliminar usuario Si deseamos eliminar un usuario debemos ejecutar el siguiente comando: DROP USER nanotutoriales;
13
ALTER ROLE El manejo de roles en PostgreSQL permite diferentes configuraciones, entre ellas estan:
SUPERUSER/NOSUPERUSER. Super usuario, privilegios para crear bases de datos y usuarios. CREATEDB/NOCREATEDB. Permite crear bases de datos. CREATEROLE/NOCREATEROLE. Permite crear roles. CREATEUSER/NOCREATEUSER. Permite crear usuarios. LOGIN/NOLOGIN. Este atributo hace la diferencia entre un rol y usuario. Ya que el usuario tiene permisos para acceder a la base de datos a traves de un cliente. PASSWORD. Permite alterar la contraseña. VALID UNTIL. Expiración de usuarios.
Para cambiar la configuracion de un usuario o rol debemos ejecutar el siguiente comando. ALTE ROLE WITH
14
Asignar permisos de super usuario a un usuario El permiso de super usuario es el mas alto. Con este usuario se podrán administrar todos los objetos del motor de base de datos. Para asignar este privilegio a un rol lo hacemos con el siguiente comando: ALTER ROLE nanotutoriales WITH SUPERUSER;
Cambiar la contraseña de un usuario Para cambiar la contraseña de un usuario es necesario ejecutar el siguiente comando: ALTER ROLE nanotutoriales WITH PASSWORD 'nuevopassword';
15
Crear una base de datos con un usuario específico como propietario Todas las bases de datos que creamos con un usuario que tenga los privilegios CREATEDB automáticamente asignan como propietario al usuario mismo. Si lo que queremos crear es un usuario limitado, la forma de crearlo con una base de datos específica será: CREATE DATABASE nanotutoriales_website WITH OWNER nanotutoriales;
Asignar todos los permisos a un usuario a una base de datos existente Cuando recien hemos creado un usuario y queremos darle permisos a una base de datos existente, podemos utilizar el siguiente comando: GRANT ALL PRIVILEGES ON DATABASE nanotutoriales_website TO nanotutoriales;
16
17
