domingo, 14 de dezembro de 2014

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,

Alex Zaballa.

0 comentários: