Privilégios insuficientes ao criar view materializada em um esquema diferente

Recentemente um analista me trouxe uma questão interessante.  Ele reclamava que estava recebendo a mensagem ORA-01031: privilégios insuficientes ao criar uma view materializada sob um esquema diferente.

Eu também tentei criar a view materializada usando o usuário SYSTEM,  mas obtive o mesmo erro.

Depois de pesquisar no MOS, encontrei uma nota muito interessante: ORA-01031: Privilégios insuficientes quando criar um Mview em um esquema  diferente (Doc ID 749112.1) .

Segundo a nota do MOS, este não é um erro de código. Este é um comportamento esperado.
Conforme explicado acima,  ao owner da tabela base deve ser concedido  o privilégio ‘create table’ explicitamente.  Conceder o privilégio de ‘create table’ explicitamente ao owner do esquema nos permite criar views materializadas em um outro esquema.

Apos o grant, a instrução CREATE MATERIALIZED VIEW funciona como esperado:

1- Testes de criação de Visão Materializada sem o grant

No SQLPLUS tentei criar uma visão:

SQL> create materialized view SERGIO.MV_TESTE_POST
2 refresh force on demand
3 as
4 Select a.NI_ID, a.DT_TRANSACAO
5 from sergio.vw_arquivo a
6 where a.DT_TRANSACAO > to_date (’20/10/2016′) and
7 a.VALOR >= 100;

ERROR na linha 1:
ORA-01031: privilégios insuficientes

2 – Testes de criação de Visão Materializada como grant

No SQLPLUS após o grant,  criei a visão acima:

SQL> grant create table to SERGIO;

Grant succeeded

SQL>
SQL> create materialized view SERGIO.MV_TESTE_POST
2 refresh force on demand
3 as
4 Select a.NI_ID, a.DT_TRANSACAO
5 from sergio.vw_arquivo a
6 where a.DT_TRANSACAO > to_date (’20/10/2016′) and
7 a.VALOR >= 100;

Materialized view created

SQL>

Depois que o problema foi resolvido o analista me fez a seguinte pergunta:
“A função RESOURCE contém o privilégio CREATE TABLE, por que eu tenho que conceder esse privilégio de forma explícita?”.

Pesquisando no MOS, encontrei no documento 11740.1 (restrições de Roles)  a resposta para esta questão:

“Um usuário não pode criar um objeto usando privilégios recebidos por meio de uma role.
Por exemplo, um usuário não pode criar uma visão com base na tabela caso ele tenha recebido a permissão de select nesta tabela por meio de uma função.
Isso também se aplica a procedures, triggers e referências a tabela de outros usuários.
…Essa restrição é necessária porque as funções podem ser ativadas ou
desativadas dinamicamente, tornando a verificação de segurança em tempo de
execução ambígua.
…As roles são destinadas a serem usadas como uma ferramenta para administrar
privilégios para usuários finais.
Eles não são destinados a serem usados pelos desenvolvedores de aplicativos.

Espero que esta postagem seja útil.

Um abraço a todos!!!

Duplicando o controlfile usando o RMAN

Neste post mostrarei como duplicar um arquivo de configuração do banco de dados utilizando o utilitário do Oracle chamado RMAN. Você pode se encontrar em uma situação em que pode ser necessário multiplexar o arquivo de controle em vários diskgroups (no caso de ASM) ou em diferentes filesystems, especialmente depois que o banco de dados é criado.

É altamente recomendável configurar o  banco de dados Oracle com vários arquivos de controle a fim de reduzir o risco de perder um arquivo de controle devido a corrupção, remoção acidental.

A multiplexação é o processo de efetuar uma cópia dos mesmos arquivos de controle em diferentes drivers de disco ou em diferentes diskgroups no caso do ASM.

Abaixo, mostraremos como efetuar uma multiplexação de um controlfile usando o RMAN:

1 –  Multiplexando um controlfile em ambiente com o filesystem

1.1 – Entrar no SQLPLUS 

[oracle@local ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.1.0.6.0 – Production on Wed Oct 25 21:41:22 2017 Copyright (c) 1982, 2007, Oracle.  All rights reserved.   Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 – Production With the Partitioning, OLAP, Data Mining and Real Application Testing options

1.2 – Verificando o parametro control_files

SQL> show parameter control_files

NAME                                 TYPE        VALUE

———————————— ———– ——————————

control_files                        string      /u01/app/oracle/oradata/orcl/                                               control01.ctl,  /u01/app/oracle/oradata/orcl/control02.ctl,   01/app/oracle/oradata/orcl/control03.ctl

SQL>

SQL> select name from v$controlfile;

NAME

——————————————————————————–

/u01/app/oracle/oradata/orcl/control01.ctl

/u01/app/oracle/oradata/orcl/control02.ctl

/u01/app/oracle/oradata/orcl/control03.ctl

SQL>

1.3 – Configurar o parâmetro control_files no arquivo de inicialização

SQL> alter system set control_files=’/u01/app/oracle/oradata/orcl/control01.ctl’,’/u01/app/oracle/oradata/orcl/control02.ctl’,’/u01/app/oracle/oradata/orcl/control03.ctl’,’/u01/app/oracle/oradata/orcl/control04.ctl’ scope=spfile;

System altered.

1.4 – Baixar a instancia e inicia-la em modo nomount

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

SQL> startup nomount

ORACLE instance started.

Total System Global Area  422670336 bytes

Fixed Size                  1300352 bytes

Variable Size             297797760 bytes

Database Buffers          117440512 bytes

Redo Buffers                6131712 bytes

SQL>

1.5 – Entrar no RMAN 

[oracle@local ~]$ rman target /

Recovery Manager: Release 11.1.0.6.0 – Production on Wed Oct 25 21:54:05 2017

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

connected to target database: ORCL (not mounted)

1.6 – Efetuar uma cópia do controlfile

RMAN> restore controlfile from ‘/u01/app/oracle/oradata/orcl/control01.ctl’;

Starting restore at 25-OCT-17

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=152 device type=DISK

channel ORA_DISK_1: copied control file copy

output file name=/u01/app/oracle/oradata/orcl/control01.ctl

output file name=/u01/app/oracle/oradata/orcl/control02.ctl

output file name=/u01/app/oracle/oradata/orcl/control03.ctl

output file name=/u01/app/oracle/oradata/orcl/control04.ctl

Finished restore at 25-OCT-17

1.7 – Sair do RMAN

RMAN> exit

Recovery Manager complete.

[oracle@local ~]$ exit

exit

1.8 – Voltar ao SQLPLUS e efetuar um shutdown na instância

SQL> shutdown

ORA-01507: database not mounted

ORACLE instance shut down.

SQL>

SQL> shutdown

ORA-01507: database not mounted

ORACLE instance shut down.

1.9 – Efetuar  startup na instância

SQL> startup

ORACLE instance started.

Total System Global Area  422670336 bytes

Fixed Size                  1300352 bytes

Variable Size             297797760 bytes

Database Buffers          117440512 bytes

Redo Buffers                6131712 bytes

Database mounted.

Database opened.

SQL>

1.10 – Verificar os controlfiles 

SQL> select name from v$controlfile;

NAME

——————————————————————————–

/u01/app/oracle/oradata/orcl/control01.ctl

/u01/app/oracle/oradata/orcl/control02.ctl

/u01/app/oracle/oradata/orcl/control03.ctl

/u01/app/oracle/oradata/orcl/control04.ctl

SQL> show parameter control_files

NAME                                 TYPE        VALUE

———————————— ———– ——————————

control_files                        string      /u01/app/oracle/oradata/orcl/c

                                                 ontrol01.ctl, /u01/app/oracle/

                                                 oradata/orcl/control02.ctl, /u01/app/oracle/oradata/orcl/con

                                                 trol03.ctl, /u01/app/oracle/oradata/orcl/control04.ctl

SQL>

2 – Multiplexando um controlfile em ambiente com o ASM

2.1 – Entrar no SQLPLUS 

[oracle@LNXORA ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Oct 25 08:01:09 2017

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

Conectado a: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management,OLAP,Data Mining and Real Application Testing options

2.2 – Verificando o parametro control_files

SQL> show parameter control_files

NAME                                 TYPE        VALUE

———————————— ———– ——————————

control_files                        string      +REDO/mtrbildc/controlfile/current.260.945000645

SQL> select name from v$asm_diskgroup;

NAME

——————————

REDO

DATA

RECO

SQL> select name from v$controlfile;

NAME

——————————————————————————–

+REDO/mtrbildc/controlfile/current.260.945000645

2.3 – Configurar o parâmetro control_files no arquivo de inicialização

SQL> alter system set control_files=’+REDO/mtrbildc/controlfile/current.260.945000645′,’+DATA’ scope=spfile;

Sistema alterado.

2.4 – Baixar a instancia e inicia-la em modo nomount

SQL> shutdown immediate

SQL> startup nomount

2.5 – Entrar no RMAN 

[oracle@LNXORA ~]$ rman target /

Recovery Manager: Release 11.2.0.3.0 – Production on Wed Oct 25 13:27:59 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: MTRBILDC (not mounted)

2.6 – Efetuar uma cópia do controlfile

RMAN> restore controlfile from ‘+REDO/mtrbildc/controlfile/current.260.945000645’;

Starting restore at 25-OCT-17

using target database control file instead of recovery catalog allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=8 device type=DISK

channel ORA_DISK_1: copied control file copy

output file name=+REDO/mtrbildc/controlfile/current.260.945000645

output file name=+DATA/mtrbildc/controlfile/current.394.958310919

Finished restore at 25-OCT-17

RMAN>

2.7 – Sair do RMAN

RMAN> exit

Recovery Manager complete.

2.8 – voltar ao SQLPLUS e configurar o arquivo de inicialização com o nome correto do controlfile

SQL> alter system set control_files=’+REDO/mtrbildc/controlfile/current.260.945000645′,’+DATA/mtrbildc/controlfile/current.394.958310919′ scope=spfile;

Sistema alterado.

SQL>

2.9 – Efetuar um shutdown e um startup na instância

SQL> shutdown immediate

ORA-01507: banco de dados n?o montado

Instancia ORACLE desativada.

SQL> startup

Instancia ORACLE iniciada.

Total System Global Area 4275781632 bytes

Fixed Size                  2235208 bytes

Variable Size            2667578552 bytes

Database Buffers         1577058304 bytes

Redo Buffers               28909568 bytes

Banco de dados montado.

Banco de dados aberto.

SQL>

SQL>

2.10. – Verificar os controlfiles 

SQL> show parameter control_files

NAME                                 TYPE        VALUE

———————————— ———– ——————————

control_files                        string      +REDO/mtrbildc/controlfile/current.260.945000645, +DATA/mtrbildc/controlfile/current.394.958310919

SQL>

Conforme podemos verificar com os procedimentos acima, o processo de cópia do controlfile é basicamente o mesmo entre sistemas de arquivos em file system ou ASM.