[20201106]11g修改表無需修改許可權.txt

lfree發表於2020-11-06

[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

--//已經生效.


來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-2732651/,如需轉載,請註明出處,否則將追究法律責任。

相關文章