Usuarios y privilegios en Oracle

gravatar
Compartir este post:
  • Google Buzz
  • Google Reader
  • Identi.ca
  • Meneame
  • PDF
  • Print
  • Twitter
  • Facebook
  • email
  • LinkedIn

1. Crear Usuarios y asignar privilegios en Oracle

El siguiente es un resumen de algunas consideraciones al momento de crear un usuario o cuenta en Oracle, y los privilegios y roles que le podemos asignar.

  • El nombre de usuario no debe superar 30 caracteres, no debe tener caracteres especiales y debe iniciar con una letra.
  • Un método de autentificación. El mas común es una clave o password, pero Oracle 10g soporta otros métodos (como biometric, certificado y autentificación por medio de token).
  • Un Tablespace default, el cual es donde el usuario va a poder crear sus objetos por defecto, sin embargo, esto no significa que pueda crear objetos, o que tenga una cuota de espacio. Estos permisos se asignan de forma separada, salvo si utiliza el privilegio RESOURCE el que asigna una quota unlimited, incluso en el Tablespace SYSTEM!  Sin embargo si esto ocurre, ud. puede posteriormente mover los objetos creados en  el SYSTEM a otro Tablespace.
  • Un Tablespace temporal, donde el usuario crea sus objetos temporales y hace los sort u ordenamientos.
  • Un perfil o profile de usuario, que son las restricciones que puede tener su cuenta (opcional).

Por ejemplo, conectado como el usuario SYS, creamos un usuario y su clave asi:

SQL> CREATE USER ahernandez IDENTIFIED BY ahz
         DEFAULT TABLESPACE users;

Si no se indica un Tablespace por defecto, el usuario toma el que está definido en la BD (generalmente el SYSTEM). Para modificar el Tablespace default de un usuario se hace de la siguiente manera:

SQL> ALTER USER jperez DEFAULT TABLESPACE datos;

También podemos asignar a los usuarios un Tablespace temporal donde se almacenan operaciones de ordenamiento. Estas incluyen las cláusulas ORDER BY, GROUP BY, SELECT DISTINCT, MERGE JOIN, o CREATE INDEX (también es utilizado cuando se crean Tablas temporales).

SQL> CREATE USER jperez IDENTIFIED BY jpz
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp;

Adicionalmente,  a cada usuario se puede asignar a un profile o perfil, que tiene dos propósitos principalmente:

  • Limita el uso de recursos,  lo que es recomendable, por ejemplo en ambientes de Desarrollo
  • Garantiza y refuerza reglas de Seguridad a nivel de cuentas

Ejemplos, cuando se crea el usuario o asignar un perfil existente:

SQL> CREATE USER jperez IDENTIFIED BY jpz
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
PROFILE resource_profile;

SQL> ALTER USER jperez
PROFILE perfil_desa;

2. Eliminar un Usuario de la Base de Datos

Para eliminar un usuario de la BD se hace uso de la clausula DROP USER y opcionalmente se puede utilizar CASCADE, para decirle que también elimine todos los objetos creados por ese usuario.

SQL> DROP USER jperez CASCADE;

3. Modificar cuentas de Usuarios

Para modificar un usuario creado, por ejemplo cambiar su clave, tenemos la sintáxis:

SQL> 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];

4. Privilegios de Sistema y de Objetos

En Oracle existen dos tipos de privilegios de usuario.

4.1 System: Que permite al usuario hacer ciertas tareas sobre la BD, como por ejemplo crear un Tablespace. Estos permisos son otorgados por el administrador o por alguien que haya recibido el permiso para administrar ese tipo de privilegio. Existen como 100 tipos distintos de privilegios de este tipo.

En general los permisos de sistema, permiten ejecutar comandos del tipo DDL (Data definition Language), como CREATE, ALTER y DROP o del tipo DML (Data Manipulation Language). Oracle 10g tiene mas de 170 privilegios de sistema los cuales pueden ser vistos consultando la vista: SYSTEM_PRIVILEGE_MAP

Entre todos los privilegios de sistema que existen, hay dos que son los importantes: SYSDBA y SYSOPER. Estos son dados a otros usuarios que serán administradores de base de datos.

Para otorgar varios permisos a la vez, se hace de la siguiente manera:

SQL> GRANT CREATE USER, ALTER USER, DROP USER TO ahernandez;

4.2 Object: Este tipo de permiso le permite al usuario realizar ciertas acciones en objetos de la BD, como una Tabla, Vista, un Procedure o Función, etc.  Si a un usuario no se le dan estos permisos sólo puede acceder a sus propios objetos (véase USER_OBJECTS). Este tipo de permisos los da el owner o dueño del objeto, el administrador o alguien que haya recibido este permiso explícitamente (con Grant Option).

Por ejemplo, para otorgar permisos a una tabla Ventas para un usuario particular:

SQL> GRANT SELECT,INSERT,UPDATE, ON analista.venta TO jperez;

Adicionalmente, podemos restringir los DML a una columna de la tabla mencionada. Si quisieramos que este usuario pueda dar permisos sobre la tabla Factura a otros usuarios, utilizamos la cláusula WITH GRANT OPTION. Ejemplo:

SQL> GRANT SELECT,INSERT,UPDATE,DELETE ON venta TO mgarcia WITH GRANT OPTION;

5. Asignar cuotas a Usuarios

Por defecto ningun usuario tiene cuota en los Tablespaces y se tienen tres opciones para poder proveer a un usuario de una quota:
5.1Sin limite, que permite al usuario usar todo el espacio disponible de un Tablespace.
5.2 Por medio de un valor, que puede ser en kilobytes o megabytes que el usuario puede usar. Este valor puede ser mayor o nenor que el tamaño del Tablespace asignado a él.
5.3 Por medio del privilegio UNLIMITED TABLESPACE, se tiene prioridad sobre cualquier cuota dada en un Tablespace por lo que tienen disponibilidad de todo el espacio incluyendo en SYSTEM y SYSAUX.

No se recomienda dar cuotas a los usuarios en los Tablespaces SYSTEM y SYSAUX, pues tipicamente sólo los usuarios SYS y SYSTEM pueden crear objetos en éstos. Tampoco dar cuotas en los Tablespaces Temporal o del tipo Undo.

6. Roles

Finalmente los Roles, que son simplemente un conjunto de privilegios que se pueden otorgar a un usuario o a otro Rol. De esa forma se simplifica el trabajo del DBA en esta tarea.

Por default cuando creamos un usuario desde el Enterprise Manager se le asigna el permiso de connect, lo que permite al usuario conectarse a la BD y crear sus propios objetos en su propio esquema. De otra manera, debemos asignarlos en forma manual.

Para crear un Rol y asignarlo a un usuario se hace de la siguiente manera:

SQL> CREATE ROLE appl_dba;

Opcionalmente, se puede asignar una clave al Rol:

SQL> SET ROLE appl_dba IDENTIFIED BY app_pwd;

Para asignar este Rol a un usuario:

SQL> GRANT appl_dba TO jperez;

Otro uso común de los roles es asignarles privilegios a nivel de Objetos, por ejemplo en una Tabla de Facturas en donde sólo queremos que se puedan hacer Querys e Inserts:

SQL> CREATE ROLE consulta;

SQL> GRANT SELECT,INSERT on analista.factura TO consulta;

Y finalmente asignamos ese rol con este “perfil” a distintos usuarios finales:

SQL> GRANT consulta TO ahernandez;

Nota: Existen algunos roles predefinidos, tales como:
CONNECT, CREATE SESSION, CREATE TABLE, CREATE VIEW, CREATE SYNONYM, CREATE SEQUENCE, CREATE DATABASE LINK, CREATE CLUSTER,
ALTER SESSION, RESOURCE, CREATE PROCEDURE, CREATE SEQUENCE, CREATE TRIGGER, CREATE TYPE, CREATE CLUSTER, CREATE INDEXTYPE, CREATE OPERATOR SCHEDULER, CREATE ANY JOB, CREATE JOB, EXECUTE ANY CLASS, EXECUTE ANY PROGRAM,
MANAGE SCHEDULER, etc.
DBA: Tiene la mayoría de los privilegios, no es recomendable asignarlo a usuarios que no son administradores.
SELECT_CATALOG_ROLE: No tiene privilegios de sistema, pero tiene cerca de 1600 privilegios de objeto.

Para consultar los roles definidos y los privilegios otorgados a través de ellos, utilize las vistas:

SQL> select * from DBA_ROLES;
SQL> select * from DBA_ROLE_PRIVS order by GRANTEE;

Etiquetas: , , , , , , , , , ,

Escrito por: Alejandro Hernandez L

Esta entrada se publicó , el Martes, 2 de junio de 2009 a las 11:42 am horas y está guardada bajo Oracle 10g. Puedes seguir cualquier respuesta a esta entrada mediante la fuente RSS 2.0. Puedes dejar un comentario o enviar un trackback desde tu propio sitio.

38 comentarios para “Usuarios y privilegios en Oracle”

  1. RonnyMan!!! dice:

    Alejandro

    Muy buen artículo, dicen por ahí que todo se puede decir pero lo importante es dar con la forma (en este caso escribir). En el caso de tu artículo, se encuentra muy bien explicado para dar a entender algo un poco nebuloso para quienes nos metemos a estas areas por necesidad
    (del cliente o los proyectos).

    Bueno, aproveechando tus conocimientos, quiero saber si es posible realizar lo siguiente:

    Tengo una aplicacion VB 2005 y quiero conectarme por Autentificacion via usuario Windows a una BD ORACLE, en la maquina que tiene la BD posee dos esquemas, al conectarse uso un string de conexion que tiene esta forma: SCadenaConexion = “Data Source=MyBD; User Id=/”, el problema es que al ingresar y resolver los permisos de ROLES no sabe con cual Esquema trabajar.

    Leyendo un artículo de MSDN (http://msdn.microsoft.com/es-es/library/system.data.oracleclient.oracleconnection(VS.80).aspx), me indica que al string de conexion no puedo indicarle un esquema puntual a la hora de conectarme ya que no admite una propiedad “Database” ni un método “ChangeDatabase”.

    Mi duda es;
    - Como poder resolver esto, siendo que mis Roles y Usuarios estan bien creados porque al ya estar conectado le pongo el prefijo del esquema al procedimiento almacenado que uso en este proceso y retorna los datos esperados.
    - Al momento de conectarse en la aplicación yo debo resolver a partir de un archivo .INI a que esquema me quiero conectar.

    Espero poder contar con tu ayuda.

    Atte.

    Ronny Morales M.

  2. Jose Reyes dice:

    Prueba creando un conexiòn ODBC y utiliza ese ODBC en tu cadena de conexiòn

  3. fran dice:

    una pregunta como es que al crear un usuario en oracle 10g y no otorgarle privilegios este puede conectase al sqlplus o al plsql y poder gestionar usuarios, mirar las tablas de los otros usuarios, e inclusive sus paquetes de base de datos? la verdad me gustaria salir de esta duda este problema me revienta la cabeza

  4. Cris dice:

    Buenas tardes,

    Tengo una duda, si yo quiero separar en dos tablespaces, por un lado tener uno de datos y por otro uno de índices, como puedo indicarselo?? porque por lo que he estado viendo en las creaciones de los usarios, solo viene para indicar el de datos y el temporal, pero no he visto como puedo indicarle que los índices vayan a otro.

    Muchas gracias por tu ayuda.

  5. luzardo paredes dice:

    Es para consultar sobre como le quito al usuario system que vea las tablas de un usuario, que no vea la data, ni la estructura de la tabla. que yo cree un usuario “VENTAS” cree 5 tablas y el usuario system no pueda ver nada de ese usuario no tenga acceso.

  6. Carlos dice:

    Estimado

    tenemos:

    usuarios
    roles
    profiles

    y groups?

    como creamos grupos para los usuarios?

    Asignar privilegios por cada usuario eso es de un DBA PENCA….

    Los roles estan bien…pero y por grupo???

    como creamos un grupo si no deseo que sea public.

  7. herlinda dice:

    Como puedo separa usuarios en ambiente productivo y no productivo sin quitarle el rol DBA?

  8. icano dice:

    Muchas Gracias… Muy buena y util la información…

  9. Fernando S dice:

    Que debo hacer cuando en una consulta que hago cada mes a la base de datos, el dia de hoy me sale este error:
    snapshot too old: rollback segment number 24 with name “RBS4″ too small
    ¿Acaso tengo que depurar la base de datos o hacer un Tunning?

    SALUDOS

  10. Marcela Diaz dice:

    Hola Fernando S.
    De seguro tu BD se encuentra en Automatic Undo Management, por lo tanto te recomiendo seguir los siguientes pasos:
    1) Conectate como System o Sys y ejecuta:
    select max(maxquerylen) from v$undostat;
    2) Verifica el valor del parámetro de UNDO_RETENTION
    show parameter undo_retention
    3) Si el valor anterior es menor al resultado de la consulta, entonces modifica el parámetro con el valor que recibiste de la consulta.
    4) Debes aumentar el tamaño del tablespace UNDO y colocar su datafile en autoextend = ON
    5) Optimiza la Query ya que el error es por el uso del segmento Rollback que corresponde a la lectura entre transacciones.

    Espero resuelvas tu problema
    Saludos… Marcela Diaz R.

  11. Fernando S. dice:

    Muchas gracias Marcela,

    Tu aportación me sirvió de mucho.

    Saludos Cordiales.

  12. Felipe Manriquez dice:

    Eso no se puede hacer en versiones de Oracle normales. Un usuario SYSTEM es dba y tiene muchos privilegios de sistemas tales como SELECT ANY TABLE. Si le quitas ese privilegio a lo mejor le quitas la capacidad, pero no seria aconsejable. Para proteger los datos del DBA, mejor seria usar ORACLE DATABASE VAULT. Puedes ver aqui

    Saludos

    Felipe

  13. Steeven Araujo dice:

    muy buen articulo, claro y conciso muchas gracias por el conocimiento otorgado, es de mucha ayuda, estoy haciendo un minor de base de datos en oracle 10g y esto me ayudo mucho de verdad le agradesco.

  14. Nacho dice:

    ¿Como puedo hacer para que un usuario solo pueda ver al conectarse por odbs las vistas que yo quiera pero no las tablas de la bd?

  15. Nacho dice:

    Pedon quise decir odbc

  16. Felipe Manriquez dice:

    Se crea otro esquema con sinonimos apuntando a las vistas que ud desea esten accesibles. Por ejemplo

    Ud tiene el esquema llamado A, dueño de las tablas

    a1, a2, a3 y las vistas v1,v2, v3

    Ud crea un esquema (usuario) B
    con los sinonimos v1, v2, v3 apuntando a las vistas A.v1, A.v2 y A.v3

    Como usuario B se hace lo siguiente

    SQL> CREATE SYNONYM v1 FOR A.V1;

    Finalmente, para que funcione, el esquema A debe darle privilegio de SELECT a las vistas v1, v2 y v3 al esquema B, una cosa como

    Como usuario A

    SQL> GRANT SELECT ON v1 to B;

    De esa manera, a través de B se tiene acceso a las vistas de A, pero no a sus tablas.

    Suerte

    Felipe

  17. FRedd dice:

    Intreresante y practica tu explicacion de permisos en oracle gracias y saludos

  18. richard dice:

    se me olvido la clave y la contraseña de mi usuario como puedo entrar al administrador ? agradesco la ayuda prestada

  19. latinmau dice:

    Hola, me gustaria saber lo siguiente.
    Creo un usuario A y creo tablas, procedimientos, paquetes, etc.. en el esquema del usuario A
    luego creo un segundo usuario B, quiero que B trabaje y haga cualquier cosa en el esquema de A.. es decir darle todos los privilegios.. pues B solamente lo voy a usar solo para loguearme y reconocer el trabajo de A y el trabajo de B

    Saludos

  20. mary dice:

    muy interesante resumen esta bien explicado…
    saludos DTB

  21. Paola dice:

    Hola… Alguien sabe cómo hago, para que un usuario pueda consultar los roles de la base de datos y los privilegios que estos roles tienen?? mil gracias…

  22. hugo alfaro dice:

    por q quiero tener juegos

  23. hugo alfaro dice:

    es bueno tener un usuario confiable

  24. hugo alfaro dice:

    Hola, me gustaria saber lo siguiente.
    Creo un usuario A y creo tablas, procedimientos, paquetes, etc.. en el esquema del usuario A
    luego creo un segundo usuario B, quiero que B trabaje y haga cualquier cosa en el esquema de A.. es decir darle todos los privilegios.. pues B solamente lo voy a usar solo para loguearme y reconocer el trabajo de A y el trabajo de B

  25. hugo alfaro dice:

    Hola… Alguien sabe cómo hago, para que un usuario pueda consultar los roles de la base de datos y los privilegios que estos roles tienen??
    Creo un usuario A y creo tablas, procedimientos, paquetes, etc.. en el esquema del usuario A
    luego creo un segundo usuario B, quiero que B trabaje y haga cualquier cosa en el esquema de A.. es decir darle todos los privilegios.. pues B solamente lo voy a usar solo para loguearme y reconocer el trabajo de A y el trabajo de B

  26. nok dice:

    hola, soy nuevo dba y estoy aprendiendo con oracle.

    mi pregunta es:
    ¿ cómo puedo quitarle permisos de conexión a un usuario, que no sean revoke all provileges ni tampoco lock ?

    ya intente con estas sentencias y no me funciona:
    REVOKE ALL ON tabla_usuarios FROM nombre_usuario

    y

    revoke connect to nombre_usuario

    estoy conectado como “SYS”.

  27. Jaime dice:

    Hola:

    quisiera preguntar si yo hago un update de una tabla como usuario “SYS” sabiendo que la tabla es de pepito y el usuario es pepito, me dice que lo ha modificado.

    Pero he realizado la prueba en otra BBDD y me dice que no puede porque la vista no existe.

    me podrian decir si en realidad el usuario SYS puede realizar las modificaciones??

  28. Felipe Manriquez dice:

    Si puedes. Si la tabla se llama “prueba” y el esquema dueño es PEPITO, debes hacer el update desde SYS (otro esquema) referenciando el nombre global de la tabla. En este caso:

    UPDATE PEPITO.PRUEBA
    SET campo1=valor
    WHERE condicion booleana

    Si no antepones PEPITO a la tabla, entonces oracle buscara la tabla PRUEBA en el esquema de SYS, y como no lo encuentra te da un error.

    Suerte

    FM

  29. Felipe Manriquez dice:

    Tienes que entender los privilegios de objeto y privilegios de sistema.
    El privilegio de conexion se puede dar de dos maneras

    GRANT CREATE SESSION TO PEPITO; — le concede el privilegio de sistema llamado “CREATE SESSION” a pepito
    REVOKE CREATE SESSION FROM PEPITO; — le quita el privilegio de sistema llamado “CREATE SESSION” a pepito

    EL rol CONNECT tiene el privilegio CREATE SESSION incorporado. Por lo tanto puedo entregar el privilegio tambien via el siguiente comando

    GRANT CREATE SESSION TO PEPITO; — le concede el rol connect a pepito
    REVOKE CREATE SESSION FROM PEPITO; — le quita el rol connect a pepito

  30. Felipe Manriquez dice:

    COnsulta las siguientes vistas

    USER_SYS_PRIVS
    USER_ROLE_PRIVS (roles que tiene el usuario)
    ROLE_SYS_PRIVS (privilegios de sistema que tiene un rol, se requiere privilegio DBA)
    USER_TAB_PRIVS

  31. Luis GP dice:

    Hola!
    Cuales consideras que serían los privilegios adecuados para desarrolladores?

  32. grace dice:

    Holaaa..

    Tengo un inconveniente para instalar oracle 10g.

    El asunto es que lo tenia instalado y al no poder accesar quise eliminarlo con el mismo sofware, pero no me elimino todas las carpetas. Elimine los temp, también entre a los registro de windows y los elimine, tumbe los servicios y de ninguna forma se desintala.

    Al tratar de instalar me dice que ya tengo otra carpeta abierta.. Me dice que el SID ya existe en mi maquina y me da otro error donde me dice que no elegí una unidad empty.

    No se que pasaría si cambio el nombre del esquema, tengo tablas de practica y quiero saber si puedo accesar aun cambiando el SID.

  33. Alex dice:

    Hola,

    requiero crear un usuario con solo permiso para desbloquear usuarios bloqueados y que no pueda asignar o retirar roles, es eso posible?? le puedo asignar un privilegio diferente al de alter user para realizar esa funcion.

    gracias.

  34. Felipe Manriquez dice:

    CREATE SESSION
    CREATE TABLE
    CREATE VIEW
    CREATE SEQUENCE
    CREATE PROCEDURE

  35. Luis dice:

    Hola, muy útil y completa la información. Quisiera saber como se podría ver la información relacionada a las cuotas de cada usuario por tablespace y su consumo.

    Saludos,

  36. silvia dice:

    Hola he leido publicación y me parece super interesante. Te quiero consultar lo siguiente donde puedo encontrar técnicas para administrar los roles , privilegos y usuarios se requiere optimizar la plataforma de usuarios.
    atte y gracias.

  37. Alex dice:

    hola he leido la publicacion y si me sirvio porque no me dejaba crear porcedimientos almacenados en pl/sql developer usando el oracle 11g, me generaba el error ora-00900 ahora boy a probar a crear mis store procedure a ver como me va gracias, cualquier duda la estare publicando para quienes tengan las mismas consultas.

  38. virna naranjo dice:

    Hola, quisiera crear un usuario igualito a otro ya existente, es decir, mismos objetos, misma data, etc.. Como lo hago?? cuales son los pasos?

    Gracias.

Deja un comentario