[20201106]11g修改表无需修改权限.txt

--//11.2.0.4可以利用with命令修改表而不需要修改权限，做一个演示：

1.环境：
SCOTT@book> @ ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

2.建立测试例子：
SCOTT@book> create table empx as select * from emp;
Table created.

create user test identified by test;
grant create session,create table to test;
grant select on scott.empx to test;

3.测试：
--//打开新的session：

TEST@book> show user
USER is "TEST"

TEST@book> select * from scott.empx where rownum=1;
     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 1980-12-17 00:00:00        800                    20


TEST@book> update  scott.empx set sal=sal+100 where empno=7369;
update  scott.empx set sal=sal+100 where empno=7369
              *
ERROR at line 1:
ORA-01031: insufficient privileges

--//仅仅有select权限。

TEST@book> update (select * from scott.empx where empno=7369) set sal=sal+100;
update (select * from scott.empx where empno=7369) set sal=sal+100
                            *
ERROR at line 1:
ORA-01031: insufficient privileges
--//依旧不行。


TEST@book> update (with tmp as （select * from scott.empx where empno=7369) select * from tmp ) set sal=sal+100;

1 row updated.

TEST@book> select * from scott.empx where rownum=1;
     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 1980-12-17 00:00:00        900                    20

--//已经修改了sal＝900.

TEST@book> commit ;
Commit complete.

TEST@book> select * from scott.empx where empno=7369;
     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 1980-12-17 00:00:00        900                    20

--//已经生效.