Gestión de Usuarios en Oracle
Usuarios
Una cuenta de usuario no es una estructura física de la BD, pero está relacionada con los objetos de la BD: los usuarios poseen los objetos de la BD. Existen dos usuarios especiales: SYS y SYSTEM. El usuarios SYS posee las tablas del diccionario de datos; que almacenan información sobre el resto de las estructuras de la BD. El usuario SYSTEM posee las vistas que permiten acceder a las tablas del diccionario, para el uso del resto de los usuarios de la BD.
Todo objeto creado en la BD se crea por un usuario, en un espacio de tablas y en un fichero de datos determinado. Toda cuenta de la BD puede estár unida a una cuenta del S.O., lo que permite a los usuarios acceder a la cuenta de la BD sin dar la clave de acceso.
Cada usuario puede acceder a los objetos que posea o a aquellos sobre los que tenga derecho de acceso.
El conjunto de objetos de un usuario es conocido como esquema.
Create User
Esta sentencia sirve para crear un usuario oracle.
Un usuario es un nombre de acceso a la base de datos oracle. Normalmente va asociado a una clave (password).
Lo que puede hacer un usuario una vez ha accedido a la base de datos depende de los permisos que tenga asignados ya sea directamente (GRANT) como sobre algun rol que tenga asignado (CREATE ROLE).
El perfil que tenga asignado influye en los recursos del sistema de los que dispone un usuario a la hora de ejecutar oracle (CREATE PROFILE).
La sintaxis es:
CREATE USER username
IDENTIFIED {BY password | EXTERNALLY | GLOBALLY AS 'external_name'}
options;
Donde options:
DEFAULT TABLESPACE tablespace
TEMPORARY TABLESPACE tablespace
QUOTA int {K | M} ON tablespace
QUOTA UNLIMITED ON tablespace
PROFILE profile_name
PASSWORD EXPIRE
ACCOUNT {LOCK|UNLOCK}
Alterar un usuario
ALTER USER NOMBRE_USUARIO
IDENTIFIED BY CLAVE _ACCESO
[DEFAULT TABLESPACE ESPACIO_TABLA]
[TEMPORARY TABLESPACE ESPACIO_TABLA]
[QUOTA {ENTERO {K | M } | UNLIMITED } ON ESPACIO_TABLA
[PROFILE PERFIL];
Crea un usuario sin derecho a guardar datos o crear objetos:
CREATE USER usuariolimitado IDENTIFIED BY miclavesecreta;
Crea un usuario con todos los derechos para guardar datos o crear objetos:
DROP USER miusuario CASCADE;
CREATE USER miusuario IDENTIFIED BY miclavesecreta
DEFAULT TABLESPACE data
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON data;
CREATE ROLE programador;
GRANT CREATE session, CREATE table, CREATE view,
CREATE procedure,CREATE synonym,
ALTER table, ALTER view, ALTER procedure,ALTER synonym,
DROP table, DROP view, DROP procedure,DROP synonym,
TO conn;
GRANT programador TO miusuario;
Es neceario crear el usuario antes de asignar permisos con GRANT o un ROLE por defecto.
Gestión de permisos- Grant (Dar permisos)
Grant (dar permisos)
Esta sentencia sirve para dar permisos (o privilegios) a un usuario o a un rol.
Un permiso, en oracle, es un derecho a ejecutar un sentencia (system privileges) o a acceder a un objeto de otro usuario (object privileges).
La sintaxis es:
GRANT <ROL> TO <URUARIO / ROL/public > [WITH GRANT OPTION]
La opcion WITH GRANT OPTION otorga al usuario la posibilidad de otorgar el rol a otro usuario o rol.
El conjunto de permisos es fijo, esto quiere decir que no se pueden crear nuevos tipos de permisos.
Si un permiso se asigna a rol especial PUBLIC significa que puede ser ejecutado por todos los usuarios.
Permisos para acceder a la base de datos (permiso de sistema):
GRANT CREATE SESSION TO miusuario;
Permisos para usuario de modificación de datos (permiso sobre objeto):
GRANT SELECTS, INSERT, UPDATE, DELETE ON T_PEDIDOS TO miusuario;
Permisos de solo lectura para todos:
GRANT SELECT ON T_PEDIDOS TO PUBLIC;
Permisos de sistema (system privileges)
Los permisos del sistema pueden ser:
CREATE SESSION - Permite conectar a la base de datos
UNLIMITED TABLESPACE - Uso de espacio ilimitado del tablespace.
SELECT ANY TABLE - Consultas en tables, views, or mviews en cualquier esquema
UPDATE ANY TABLE - Actualizar filas en tables and views en cualquier esquema
INSERT ANY TABLE - Insertar filas en tables and views en cualquier esquema
Permisos de administrador para CREATE, ALTER o DROP:
Cluster, context, database, link, dimension, directory, index,
materialized view, operator, outline, procedure, profile, role,
rollback segment, sequence, session, synonym, table, tablespace,
trigger, type, user, view.
Los roles predefindos son:
SYSDBA, SYSOPER, OSDBA, OSOPER, EXP_FULL_DATABASE, IMP_FULL_DATABASE, SELECT_CATALOG_ROLE, EXECUTE_CATALOG_ROLE, DELETE_CATALOG_ROLE, AQ_USER_ROLE, AQ_ADMINISTRATOR_ROLE - manejo de la cola
SNMPAGENT - Agente inteligente.
RECOVERY_CATALOG_OWNER - rman
HS_ADMIN_ROLE - servicios heterogeneos
Los roles CONNECT, RESOURCE y DBA ya no deben usarse (aunque estan soportados).
Permisos sobre objetos (object privileges)
Los permisos sobre objetos mas importantes son: SELECT, UPDATE, INSERT, DELETE, ALTER, DEBUG, EXECUTE, INDEX, REFERENCES
GRANT object_priv [(column, column,...)]
ON [schema.]object
TO {user, | role, |PUBLIC} [WITH GRANT OPTION] [WITH HIERARCHY OPTION]
GRANT ALL PRIVILEGES [(column, column,...)]
ON [schema.]object
TO {user, | role, |PUBLIC} [WITH GRANT OPTION] [WITH HIERARCHY OPTION]
GRANT object_priv [(column, column,...)]
ON DIRECTORY directory_name
TO {user, | role, |PUBLIC} [WITH GRANT OPTION] [WITH HIERARCHY OPTION]
GRANT object_priv [(column, column,...)]
ON JAVA [RE]SOURCE [schema.]object
TO {user, | role, |PUBLIC} [WITH GRANT OPTION] [WITH HIERARCHY OPTION]
Con la opcion WITH HIERARCHY OPTION damos permisos sobre todos los subojetos, incluso sobre los que se creen despues de ejecutar el GRANT.
Con la opción WITH GRANT OPTION damos permiso para que el que los recibe los pueda a su vez asignar a otros usuarios y roles.
La opción "GRANT ALL PRIVILEGES..." se puede escribir tambien como "GRANT ALL..."
Podemos obtener la lista de permisos de las tablas asi:
SELECT * FROM ALL_TAB_PRIVS_MADE;
Es posible asignar varios Object_Privs en un solo comando GRANT.
GRANT SELECT (empno), UPDATE (sal) ON pepe.tabla TO miusuario
Permisos del rol SYSDBA:
CREATE DATABASE
CREATE SPFILE
STARTUP and SHUTDOWN
ALTER DATABASE: open, mount, back up, or change character set
ARCHIVELOG and RECOVERY
Includes the RESTRICTED SESSION privilege
Permisos del rol SYSOPER:
CREATE SPFILE
STARTUP and SHUTDOWN
ALTER DATABASE: open, mount, back up
ARCHIVELOG and RECOVERY
Includes the RESTRICTED SESSION privilege
Cada tipo de objeto tiene su propio conjunto de permisos:
Tables: select, insert, update, delete, alter, debug, flashback, on commit refresh, query rewrite, references, all
Views: select, insert, update, delete, under, references, flashback, debug
Sequence: alter, select
Packeges, Procedures, Functions (Java classes, sources...): execute, debug
Materialized Views: delete, flashback, insert, select, update
Directories: read, write
Libraries:execute
User defined types: execute, debug, under
Operators: execute
Indextypes: execute
Gestión de permisos - Revoke (quitar permisos)
Esta sentencia sirve para quitar permisos (o privilegios) a un usuario o a un rol.
No dejamos nada:
REVOKE ALL PRIVILEGES FROM miusuario;
Quitamos todo:
REVOKE ALL ON T_PEDIDOS FROM miusuario;
Sintaxis, quitar un rol asignado:
REVOKE role FROM {user, | role, |PUBLIC}
Quitar un permiso de sistema:
REVOKE system_priv(s) FROM {user, | role, |PUBLIC}
REVOKE ALL FROM {user, | role, |PUBLIC}
Quitar un permiso de objeto:
REVOKE object_priv [(column1, column2..)] ON [schema.]object
FROM {user, | role, |PUBLIC} [CASCADE CONSTRAINTS] [FORCE]
REVOKE object_priv [(column1, column2..)] ON [schema.]object
FROM {user, | role, |PUBLIC} [CASCADE CONSTRAINTS] [FORCE]
REVOKE object_priv [(column1, column2..)] ON DIRECTORY directory_name
FROM {user, | role, |PUBLIC} [CASCADE CONSTRAINTS] [FORCE]
REVOKE object_priv [(column1, column2..)] ON JAVA [RE]SOURCE [schema.]object
FROM {user, | role, |PUBLIC} [CASCADE CONSTRAINTS] [FORCE]
La opción FORCE, quita todos los privilegios y descompila todos sus objetos.
Roles
Create Role
Esta sentencia sirve para crear un rol de usuario.
Un rol es una forma de agrupar permisos (o privilegios) para asignarlos luego a los usuarios.
Cada usuario puede tener varios roles.
Ejemplo de creación de un rol:
CREATE ROLE MI_PROPIO_ROLE
Crea un rol sin password:
CREATE ROLE role NOT IDENTIFIED
Crea un rol con password:
CREATE ROLE role IDENTIFIED BY password
Crea un rol de aplicación:
CREATE ROLE role IDENTIFIED USING [schema.]package
Crea un rol basado en uno del S.O.:
ALTER ROLE role IDENTIFIED EXTERNALLY
Crea un rol basado en el servicio de directorio:
ALTER ROLE role IDENTIFIED GLOBALLY
Ejemplo para crear un script que asigna todos los permisos de actual esquema
SELECT decode(object_type,
'TABLE','GRANT SELECT, INSERT, UPDATE, DELETE , REFERENCES ON'||&OWNER||'.',
'VIEW','GRANT SELECT ON '||&OWNER||'.',
'SEQUENCE','GRANT SELECT ON '||&OWNER||'.',
'PROCEDURE','GRANT EXECUTE ON '||&OWNER||'.',
'PACKAGE','GRANT EXECUTE ON '||&OWNER||'.',
'FUNCTION','GRANT EXECUTE ON'||&OWNER||'.' )||object_name||' TO MI_PROPIO_ROLE ;'
FROM user_objects
WHERE
OBJECT_TYPE IN ( 'TABLE', 'VIEW', 'SEQUENCE', 'PROCEDURE', 'PACKAGE','FUNCTION')
ORDER BY OBJECT_TYPE
Perfiles de usuario
Create profile
Esta sentencia sirve para crear un perfil de usuario.
Un perfil de usuario es una forma de limitar los recursos que puede utilizar un usuario.
Cada usuario puede tener un único perfil.
Antes de asignar un perfil a un usuario es necesario que este perfil exista en la base de datos.
Un perfil se asigna en la creación de un usuario CREATE USER o modificandolo ALTER USER.
Un ejemplo de script sería:
CREATE PROFILE app_user LIMIT
SESSIONS_PER_USER 2 --
CPU_PER_SESSION 10000 -- decimas de segundo
CPU_PER_CALL 1 -- decimas de segundo
CONNECT_TIME UNLIMITED -- minutos
IDLE_TIME 30 -- minutos
LOGICAL_READS_PER_SESSION DEFAULT -- DB BLOCKS
LOGICAL_READS_PER_CALL DEFAULT -- DB BLOCKS
-- COMPOSITE_LIMIT DEFAULT --
PRIVATE_SGA 20M --
FAILED_LOGIN_ATTEMPTS 3 --
PASSWORD_LIFE_TIME 30 -- dias
PASSWORD_REUSE_TIME 12 --
PASSWORD_REUSE_MAX UNLIMITED --
PASSWORD_LOCK_TIME DEFAULT -- dias
PASSWORD_GRACE_TIME 2 -- dias
PASSWORD_VERIFY_FUNCTION NULL;
Los recursos que limitamos son recursos del kernel: uso de la CPU, duración de sesion,...
Y tambien limites de uso de las claves de acceso (passwords): duración, intentos de acceso, reuso, ...
Por ejemplo:
ALTER PROFILE default LIMIT IDLE_TIME 20;
Limita el perfil por defecto a 20 minutos. IDLE_TIME: Es el tiempo que puede estar una sesión sin hacer nada antes de ser cerrada.
Un sinónimo es un nombre alternativo que identifica un tabla en la base de datos. Con un sinónimo se pretende normalmente simplicar el nombre original de la tabla, aunque tambien se suelen utilizar para evitar tener que escribir el nombre del propietario de la tabla.
Sinonimos
Un sinónimo es un nombre alternativo que identifica un objeto en la base de datos. Con un sinónimo se pretende normalmente simplicar el nombre original de la tabla, aunque tambien se suelen utilizar para evitar tener que escribir el nombre del propietario del objeto.
Create Synonym
Crea un sinonimo para algun objeto de la base de datos.
CREATE [OR REPLACE] [PUBLIC] SYNONYM [esquema.]sinonimo
FOR [esquema.]objeto [@dblink]
Con la opción 'PUBLIC' se crea un sinonimo público accesible a todos los usuarios, siempre que tengan los privilegios adecuados para el mismo. (ver GRANT)
Sirve para no tener que usar la notación 'esquema.objeto' para referirse a un objeto que no es propiedad de usuario.
CREATE PUBLIC SYNONYM T_PEDIDOS FOR PROGRAMADOR.T_PEDIDOS;
No es necesario recompilarlos cuando se redefinen las tablas, de hecho puedes existir sin que exista el objeto al que refererencian.
El acceso es un poco mas eficiente cuando se accede por sinonimos públicos.
Cuando en una sentencia no citamos el nombre del esquema, Oracle resuelve los nombres en el siguiente orden:
-
usuario actual
-
private synonym
-
public synonym
Tambien podemos usarlo para cambiar el objeto que usamos sin tener que cambiar la programacion.
Asi cambiamos la tabla:
CREATE PUBLIC SYNONYM T_PEDIDOS FOR PROGRAMADOR.T_PEDIDOS_PRUEBA;