Gestión de privilegios en Oracle

En esta última entrega de comandos Oracle  vamos a aprender la última parte del lenguaje SQL, el DCL (Data Control Language) que nos va a permitir trabajar con usuarios y concederles ciertos permisos. Veremos como podemos crearlos y asignarles ciertos privilegios para que puedan trabajar con el sistema. Además, aprenderemos a crear diferentes roles que nos van a facilitar la asignación de permisos en el caso de tener diferentes usuarios del mismo nivel.

Ésta es la tercera entrega de comandos Oracle, si quieres acceder a las otras entregas puedes hacerlo mediante los siguientes enlaces:

Enlaces de interés:

Creación de usuarios

Oracle nos permite crear usuarios nuevos en el sistema pudiendo configurar diferentes parámetros de estos. Para la correcta creación de los usuarios debemos conocer su sintaxis.

CREATE USER nombre
 IDENTIFIED BY contraseña
 [DEFAULT TABLESPACE nombreTableSpace]
 [TEMPORARY TABLESPACE nombreTemp]
 [QUOTA INT {K|M} | UNLIMITED ON nombreTableSpace]
 [PROFILE perfil]
 [PASSWORD EXPIRE]
 [ACCOUNT {LOCK | UNLOCK}]
;
  • CREATE USER: Nos permite especificar el nombre del usuario
  • IDENTIFIED BY: Nos permite especificar su contraseña
  • DEFAULT TABLESPACE: Definimos el tablespace por defecto. Si no se especifica se asigna USERS. Para crear un nuevo tablespace puedes leer el siguiente artículo.
  • TEMPORARY TABLESPACE: Definimos el tablespace temporal. Si no especificamos ninguno se asigna TEMP.
  • QUOTA: Espacio que el usuario podrá utilizar en el sistema. Si no se especifica el espacio por defecto es 0 con lo cual el usuario no podrá crear nada.
  • PROFILE: Permite especificar el perfil por defecto. Si no se especifica se asigna DEFAULT. Es utilizado para controlar el acceso a los recursos, por ejemplo, el número de sesiones concurrentes, uso de CPU, etc.
  • PASSWORD EXPIRE: Especifica que la contraseña asignada al usuario expirará, de esta forma, el propio usuario o el DBA deberá asignar una nueva antes de acceder al sistema.
  • ACCOUNT LOCK/UNLOCK: Podemos decidir si el usuario tendrá la cuenta bloqueada o no de forma inicial.

Ejemplo:

CREATE USER aitor
IDENTIFIED BY P@ss
DEFAULT TABLESPACE sis2TS
TEMPORARY TALESPACE sis2TMP
QUOTA 50M ON sis2TS
PASSWORD EXPIRE
ACCOUNT UNLOCK;

ilumina2 photo

Modificación de usuarios

Una vez hemos creado un usuario podemos hacer ciertas modificaciones. La sintaxis es muy parecida a la vista anteriormente en la creación de usuarios.

ALTER USER NombreUsuario
 IDENTIFIED BY contraseña
 [DEFAULT TABLESPACE nombreTableSpace]
 [TEMPORARY TABLESPACE nombreTemp]
 [QUOTA INT {K|M} UNLIMITED ON nombreTableSpace]
 [PASSWORD EXPIRE]
 [ACCOUNT {LOCK | UNLOCK}]
 [PROFILE perfil];

Ejemplo:

ALTER USER aitor
QUOTA UNLIMITED ON sis2TS;

Nota: Los usuarios solo pueden modificar su propia clave de acceso a no ser que tengan privilegios de ALTER USER.

Nota: Para modificar la contraseña del usuario SYSTEM puedes visitar el siguiente artículo.

Borrar usuarios

Podemos realizar una eliminación de usuarios con el comando DROP USER. Una vez se borra un usuario, si se vuelve a crear otro usuario con el mismo nombre no se podrían heredar los objetos del anterior usuario. La razón por la que esto ocurriría es que Oracle asigna automáticamente un ID interno a cada usuario con el que lo identifica de forma única y aunque los usuarios tengan nombre idéntico, su ID siempre será distinto.

DROP USER nombreUsuario [CASCADE];

Nota: CASCADE nos indica que vamos a borrar también TODOS sus objetos antes de proceder a la eliminación del usuario evitando posibles errores. Si el usuario posee algún objeto solo podremos eliminarlo con CASCADE.

Privilegios

1. Privilegios sobre objetos

Podemos conceder diferentes permisos a nuestros usuarios y así controlar exactamente que acciones queremos que puedan realizar y que acciones no van a ser permitidas. Para ver la documentación oficial de oracle sobre los privilegios de objetos puedes visitar el siguiente enlace.

GRANT privilegio1 [[,privilegio2, ...] | ALL]
[(columna1[,columna2,...])]
[ON usuario[.objeto] | ANY TABLE]
TO {nombreUsuario | rol | PUBLIC}
[WITH GRANT OPTION];
  • ON: Objeto sobre el que aplico los privilegios
  • TO: Usuario al que concedo los privilegios
  • ALL: Permite asignar todos los permisos
  • PUBLIC: Asigna el privilegio o privilegios a todos los usuarios del sistema (también a los futuros)
  • WITH GRANT OPTION: Permite que el usuario que lo reciba pueda conceder permisos a otros usuarios

Algunos de los privilegios mas usados son:

Permiso Tabla Vista Secuencia Procedimiento
ALTER X X
UPDATE X
DELETE X X
EXECUTE X
INSERT X X
SELECT X X X

Nota: En la tabla se especifican algunos de los privilegios podemos conceder y sobre que se utilizan.

Ejemplos:

GRANT SELECT ON juan.empleados TO aitor;
GRANT UPDATE ANY TABLE TO aitor;
GRANT SELECT, INSERT, UPDATE ON luisa.farmacia TO public;
GRANT INSERT(id,apellidos) ON juan.empleados TO luisa;

2. Privilegios del sistema

Podemos también asignar ciertos permisos sobre el sistema a los usuarios que tengamos creados en nuestra base de datos. Los privilegios del sistema no se asignan sobre objetos concretos sino que especifican que acciones se podrán realizar sobre el sistema gestor de base de datos. Para ver la documentación oficial de Oracle sobre los privilegios del sistema puedes visitar el siguiente enlace.

GRANT permiso1[,permiso2,...]
TO nombreUsuario[,nombreUsuario2,...] | nombreRol;

Dónde permiso puede ser alguno de los siguientes:

  • create
    • session: Permite conectarse a la base de datos
    • table: Permite crear tablas
    • sequence: Permite crear secuencias
    • view: Permite crear vistas
    • trigger: Permite crear disparadores
    • procedure: Permite crear procedimientos
    • profile: Permite crear perfiles
    • synonym: Permite crear sinónimos
  • execute any procedure: Permite ejecutar cualquier procedimiento
  • create
    • user: Permite crear usuarios. WITH ADMIN OPTIONS permite que el nuevo usuario tenga permisos administrativos, por ejemplo, para crear nuevos usuarios.
    • role: Permite crear roles
  • drop
    • table: Permite eliminar tables
    • sequence: Permite eliminar secuencias
    • view: Permite eliminar vistas
    • trigger: Permite eliminar disparadores
    • procedure: Permite eliminar procedimientos
    • profile: Permite eliminar perfiles
    • synonym: Permite eliminar sinónimos
    • user: Permite eliminar usuarios
    • role: Permite eliminar roles
    • session: Permite eliminar sesiones
  • grant
    • privilege: Permite asignar privilegios
    • role: Permite asignar roles

Ejemplo:

GRANT CREATE SESSION, CREATE TABLE TO aitor;
GRANT CREATE PROCEDURE, EXECUTE ANY PROCEDURE TO aitor, luisa;
GRANT CREATE USER TO luisa WITH ADMIN OPTIONS;

Conceder permisos de DBA (DataBase Administrator)

GRANT DBA TO NombreUsuario;

Quitar privilegios

Quitar privilegios sobre objetos

REVOKE permiso1[,permiso2,…] | ALL [PRIVILEGES]
ON [usuario.]objeto
FROM nombreUsuario | rol | PUBLIC [,nombreUsuario | nombreRol,...];

Ejemplo:

REVOKE INSERT on employees FROM luisa;

Nota: En los privilegios sobre objetos, se quita el permiso de select sobre employees a luisa y a todos los usuarios a los que ésta les ha concedido dicho permiso.

Ejemplo aclaratorio sobre la Nota anterior:

GRANT SELECT ON farmacias TO aitor WITH GRANT OPTIONS;
CONNECT aitor/P@ss
GRANT SELECT ON farmacias TO luisa WITH GRANT OPTIONS;
CONNECT luisa/P@ssL
GRANT SELECT ON farmacias TO hugo WITH GRANT OPTIONS;
CONNECT system/SysPaS

REVOKE SELECT ON farmacias FROM luisa;

Finalmente, aitor permanece con los permisos de selección sobre farmacias pero a luisa y hugo le han sido revocados.

Quitar privilegios del sistema

REVOKE permiso1[,permiso2,…] | ALL [PRIVILEGES]
FROM nombreUsuario | rol | PUBLIC [,nombreUsuario | nombreRol,...];

Ejemplo:

REVOKE ALL PRIVILEGES FROM aitor;
REVOKE CREATE VIEW FROM luisa;

Nota: En los privilegios de sistema, se quita el permiso de crear vistas a luisa pero no a los usuarios a los que éste haya concedido el privilegio

ROLES

Un rol es una agrupación de permisos. Gracias a los roles, podemos juntar ciertos permisos en un solo rol y concederlo a un usuario o grupo de usuarios.

Creación de ROLEs

CREATE ROLE nombreRol
[IDENTIFIED BY Contraseña];

Ejemplo:

CREATE ROLE miRole;

Borrar ROLEs

DROP ROLE nombreRol;

Dar permisos a ROLEs

GRANT permiso1[,permiso2,...]
ON [usuario.]objeto
TO nombreRole;

Ejemplo:

GRANT SELECT, UPDATE ON scott.emp TO miRole;
GRANT SELECT, INSERT, DELETE, UPDATE ON scott.dept TO miRole;
GRANT SELECT ON scott.salgrade TO miRole;
GRANT SELECT ON scott.bonus TO miRole;

Conceder rol a usuario:

Una vez hemos creado los roles, éstos pueden ser asignados a todos los usuarios que deseemos.

GRANT nombreRol TO nomreUsuario;

Ejemplo:

GRANT miRole TO aitor;

Tablas a tener en cuenta

  • Roles:
    • dba_roles: Contiene todos los roles disponibles
    • dba_role_privs: Contiene el mapeado de roles y usuarios
    • dba_sys_privs: Privilegios asignados a cada role (incluso a los ya predefinidos Oracle)
  • Recursos:
    • dba_ts_quotas: Límites de uso de espacio en disco
    • user_resource_limits: Limites de recursos en Oracle para el usuario actual
  • Usuarios:
    • dba_users: Vista que apunta realmente a la tabla sys.user$. Almacena información sobre todos los usuarios de la base de datos
    • user_users: Vista que muestra información sobre el usuario actualmente conectado
    • user_resource_limits: Limites de recursos en Oracle para el usuario actual
    • all_tables: Contiene todas las tablas accesibles por el usuario (las propias más las que tiene permisos sobre ellas)

Perfiles

Los perfiles de usuario nos permiten poder gestionar las limitaciones sobre los recursos que estos deben poseer. Un usuario solo puede tener un perfil al mismo tiempo. Para ver la documentación oficial de Oracle sobre perfiles de usuario puedes visitar el siguiente enlace.

Creación de perfiles

CREATE PROFILE nombrePerfil LIMIT
 nombreLimitacion1 valor1 
 [nombreLimitacion2 valor2...];

Ejemplo:

CREATE PROFILE usuariosASIX LIMIT
   SESSIONS_PER_USER          2
   CPU_PER_SESSION            UNLIMITED 
   CONNECT_TIME               60
   PASSWORD_REUSE_TIME        365
   PASSWORD_REUSE_MAX         2;
  • SESSIONS_PER_USER: Número máximo de sesiones concurrentes.
  • CPU_PER_SESSION: Uso máximo de CPU en una sesión.
  • CONNECT_TIME: Duración máxima de una sesión expresada en minutos.
  • PASSWORD_REUSE_TIME: Especifica el tiempo que debe pasar antes de poder utilizarse nuevamente una contraseña.
  • PASSWORD_REUSE_MAX: Especifica el número máximo de veces que se puede reutilizar una contraseña (número de cambios).

Nota: Puedes ver una lista completa de los parámetros configurables en un perfil en el enlace mostrado anteriormente.

Eliminación de perfiles

DROP PROFILE nombrePerfil [CASCADE];

Al eliminar un perfil con usuarios añadidos, a éstos se les asigna automáticamente el perfil por defecto DEFAULT. El perfil DEFAUL no puede ser eliminado del sistema. La eliminación de perfil no afecta a las sesiones activas en el momento de la eliminación. CASCADE es necesario siempre que un perfil tenga usuarios utilizándolo. De no especificar CASCADE, el perfil no será eliminado.

Ejemplo:

DROP PROFILE usuariosASIX CASCADE;

Hasta aquí la tercera parte de comandos Oracle. Puedes acceder a las entradas anteriores desde los siguientes enlaces:

Anuncios

7 Responses to Gestión de privilegios en Oracle

  1. Alexis says:

    Muchas gracias…
    En verdad me sirvio de mucho esta informacion.

    No esta mal… pero deberias mostrar como se hace a traves de la interfaz grafica de oracle…

    Con todo gracias…

  2. Carlos says:

    Gracias por esos excelentes aportes que me sirve bastante en lo que realizo me sirve mucho…

  3. Miguel Calzada says:

    Hola podrías pasarme la fuente de donde obtuviste esta información
    Gracias.
    (Comandos de Oracle)

  4. arigada says:

    Hola Miguel,

    La fuente son mis apuntes tomados en clase hace algunos años así que… Prácticamente todo el temario se podía encontrar en un par de libros de introducción a sql 8i. Es todo lo que puedo decirte :). Espero que te hayan sido de utilidad!

  5. Pingback: Instalación Oracle 11g en Linux « Aitor Rigada

  6. Pingback: Instalación Oracle 10g en Linux « Aitor Rigada

  7. Pingback: Procedimientos y funciones en Oracle | Aitor Rigada

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

A %d blogueros les gusta esto: