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 privilegesReferências
Abraço