Creando Vistas Materializadas en otros esquemas: ORA-01031

Oracle tiene muchas particularidades y algunas son un poco difíciles de pillar de forma intuitiva. Una de ellas es la creación de vistas materializadas en otros esquemas y los privilegios necesarios para ello.

Supongamos que queremos crear un esquema que realmente no va a tener un usuario ‘real’, porque la labor para que lo vamos a destinar no lo necesita:

SQL*Plus: Release 10.2.0.1.0 - Production on Mié Feb 20 09:52:03 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Introduzca el nombre de usuario: carlos@db01.w2k3
Introduzca la contraseña:

Conectado a:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

carlos@db01.w2k3> select * from V$VERSION;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

carlos@db01.w2k3> create user dummy_user identified by dummy;

Usuario creado.

Aquí vemos que el usuario creado no tiene ningún privilegio (ni siquiera CREATE SESSION). Si queremos crear una tabla en dicho esquema:

carlos@db01.w2k3> create table dummy_user.prueba01(
  2  id_n number not null, c_txt varchar2(10));

create table dummy_user.prueba01(id_n number not null, c_txt varchar2(10))
*
ERROR en línea 1:
ORA-01950: no existen privilegios en tablespace 'USERS'

¡Ops! Se necesitan privilegios al menos en el ‘tablespace’ asignado por defecto…

carlos@db01.w2k3> alter user dummy_user quota unlimited on users;

Usuario modificado.

carlos@db01.w2k3> create table dummy_user.prueba01(id_n number not null,
  2  c_txt varchar2(10));

Tabla creada.

carlos@db01.w2k3> alter table dummy_user.prueba01
  2  add constraint prueba01_pk primary key (id_n);

Tabla modificada.

carlos@db01.w2k3> insert into dummy_user.prueba01 values(1,'UNO');

1 fila creada.

carlos@db01.w2k3> commit;

Confirmación terminada.

Hasta aquí todo correcto: hemos visto que los privilegios de cuota son más privilegios de ‘esquema’ que de ‘usuario’. También hemos visto que podemos crear tablas en el esquema (si el usuario que utilizamos para ello tiene los privilegios adecuados) sin ningún problema. Pero ahora viene lo más curioso: vamos a crear una vista materializada en ese esquema basada en la tabla que ya hemos creado también en ese esquema:

carlos@db01.w2k3>  create materialized view
  2  dummy_user.prueba01_mw as
  3  select * from dummy_user.prueba01;

 create materialized view dummy_user.prueba01_mw as select * from dummy_user.prueba01
                                                                             *
ERROR en línea 1:
ORA-01031: privilegios insuficientes

¿Privilegios insuficientes? Si yo (carlos) puedo crear tablas allí, y además tengo el privilegio ‘CREATE ANY MATERIALIZED VIEW’. ¿Cómo es esto?

La respuesta es un tanto enrevesada: los ‘privilegios insuficientes’ van referidos al usuario propietario del esquema (dummy_user), no al que crea la vista materializada. Dicho usuario propietario del esquema ahora sí necesita privilegio ‘CREATE TABLE’, privilegio que antes no requirió cuando creábamos las tablas.

carlos@db01.w2k3> grant create table to dummy_user;

Concesión terminada correctamente.

carlos@db01.w2k3>  create materialized view
  2  dummy_user.prueba01_mw as
  3  select * from dummy_user.prueba01;

Vista materializada creada.

carlos@db01.w2k3> select * from dummy_user.prueba01_mw;

      ID_N C_TXT
---------- ----------
         1 UNO

carlos@db01.w2k3>

Esto es así porque tras (o bajo) una vista materializada hay una tabla (o un ‘segmento’) que ‘debe ser creada’ por el propietario del esquema.

Llegados a este punto, vemos que la separación entre usuario y esquema se vuelve así un poco difusa.

Como curiosidad, en SQL Server 2005 sí existe esa separación: un usuario puede ser propietario de más de un esquema y un esquema puede cambiar de usuario propietario.

Saludos.

Carlos.

Anuncios

9 respuestas a Creando Vistas Materializadas en otros esquemas: ORA-01031

  1. Fernando Garcia dice:

    Hola Carlos,

    hoy inaugure mi blog dedicado a Oracle RAC. Si no te molesta voy a poner tu blog en mi blogroll. Te invito a que hagas lo mismo con el mio.

    Atentamente,
    Fernando.

  2. Óscar de la Torre dice:

    Al hilo de ORA-01031 y las vistas materializadas, ocurre algo parecido cuando desde otro usuario quieres refrescar una vista materializada con dbms_mview.refresh. Necesitas nada menos que conceder ALTER ANY SNAPSHOT/MATERIALIZED VIEW para llevarlo a cabo.

  3. carlosal dice:

    ¡Hey Óscar!

    ¡Qué bueno verte por aquí otra vez!

    Otra particularidad es que si creas la VM en otro usuario siendo ‘sys’ no se produce el error de los privilegios insuficientes aunque dicho usuario no tenga el ‘CREATE TABLE’ (debe de ser por algún privilegio de ‘sys’ del tipo ‘become user’ o así…)

    Saludos.

    Carlos.

  4. Rafo2001 dice:

    Generalmte obtengo ese error cuando estoy creando vistas materializadas con referencia a tablas de otro esquema, ej:
    create materialized view esquema1.vista1
    as
    select campo1, campo2
    from esquema2.tabla1;

    para que funcione necesito darle un

    grant select on esquema2.tabla1 to esquema1 with grant option

    y

    grant references on esquema2.tabla1 to esquema1 with grant option

    Sin embargo nunca habia que tenido que darle un grant create table, hasta que tuve que migrar a otro esquema, y el grant a las tablas no dio resultado.

    Lo que paso es que al crear el nuevo esquema, simplemente le di un
    grant resource, connect to esquema2, y nunca pense que seria necesario un grant create table explicito.

    Muchas gracias, tu solucion me fue de gran ayuda.

  5. […] Creando Vistas Materializadas en otros esquemas: ORA-01031 (II) Recientemente me ha llegado a mi ‘mail’ una duda sobre el mismo error (ORA-01031: privilegios insuficientes) al crear una vista materializada en otro esquema. […]

  6. Julepe dice:

    Eres un genio!! gracias por esta información.

  7. victor dice:

    creo que me parese mui interesante estapajina

  8. Omare dice:

    Es muy interesante tu publicación, recien me solicitaron hacer este tipo de vista materializada y me ayudaste mucho, solo como comentario cuando haces una VM de este tipo.

    Ademas de los privilegios que ya mencionaste tuve que agregarle “GRANT SELECT ON “usuario.log_de_vm” TO “usuario_de_vm”” esto por que cuando queria hacer un update a la tabla maestra sin esta clausula me aparecia que no existia la tabla “VM.USUARIO_DE_VM”. y no podia hacer el update.

    Saludos

  9. rodrigo dice:

    Estimado, una consulta. Tengo una tabla en un esquema y la vista materializada en otro, esto funciona a la perfección.

    Dado el volumen de datos estoy tratando de que se refresque on commit, y para ello cree el log de la vista materializada en el mismo esquema de la tabla, pero al alterar la vista materializada con On Commit me reclama.

    Tendras algún consejo.?

    Saludos
    Rodrigo

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: