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

来源：这里教程网时间：2026-03-03 16:16:33 作者：

[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 --//已经生效.