GPO ( Grupo de Profissionais Oracle )
A maior comunidade Oracle do Brasil !

12c – READ Object Privilege and READ ANY TABLE System Privilege

No Oracle Database 12.1.0.2, podemos utilizar o privilégio de objetos READ e o privilégio de sistema READ ANY TABLE  para permitir consultas em tabelas, visões, visões materializadas e sinônimos.

Mas e qual a diferença entre estes privilégios e os privilégios que já utilizamos, ou seja, o SELECT e SELECT ANY TABLE?

O privilégio de objeto SELECT e o privilégio de sistema SELECT ANY TABLE permitem bloquear as linhas de uma tabela através da execução das seguintes operações:

  • LOCK TABLE table_name IN EXCLUSIVE MODE;
  • SELECT … FROM table_name FOR UPDATE;
[oracle@oracle01 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Wed Dec 3 18:04:05 2014
Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> create user teste identified by teste;
User created.

SQL> grant create session to teste;
Grant succeeded.

SQL> grant select on scott.emp to teste;
Grant succeeded.

[oracle@oracle01 ~]$ sqlplus teste/teste

SQL*Plus: Release 12.1.0.2.0 Production on Wed Dec 3 18:05:14 2014
Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> lock table scott.emp in exclusive mode;
Table(s) Locked.

SQL> rollback;
Rollback complete.

SQL> select * from scott.emp for update;

         EMPNO  ENAME      JOB    MGR  HIREDATE      SAL       COMM      DEPTNO
    ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
         7369   SMITH      CLERK   7902  17-DEC-80      800                    20

SQL> rollback;
Rollback complete.

O privilégio de objetos READ e o privilégio de sistema READ ANY TABLE não fornecem esses privilégios adicionais

[oracle@oracle01 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Dec 3 17:59:16 2014
Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> create user teste identified by teste;
User created.

SQL> grant create session to teste;
Grant succeeded.

SQL> grant read on scott.emp to teste;
Grant succeeded.

[oracle@oracle01 ~]$ sqlplus teste/teste

SQL*Plus: Release 12.1.0.2.0 Production on Wed Dec 3 18:00:09 2014
Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> lock table scott.emp in exclusive mode;

lock table scott.emp in exclusive mode

                 *

ERROR at line 1:
ORA-01031: insufficient privileges

SQL> select * from scott.emp for update;

select * from scott.emp for update

                    *
ERROR at line 1:
ORA-01031: insufficient privileges

Referências

Abraço

Share

You may also like...

Deixe um comentário

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *