oracle實驗記錄 （許可權,role）
SQL> select * from system_privilege_map;
PRIVILEGE NAME PROPERTY
---------- ---------------------------------------- ----------
-3 ALTER SYSTEM 0
-4 AUDIT SYSTEM 0
-5 CREATE SESSION 0
-6 ALTER SESSION 0
-7 RESTRICTED SESSION 0
-10 CREATE TABLESPACE 0
-11 ALTER TABLESPACE 0
-12 MANAGE TABLESPACE 0
-13 DROP TABLESPACE 0
-15 UNLIMITED TABLESPACE 0
-20 CREATE USER 0
PRIVILEGE NAME PROPERTY
---------- ---------------------------------------- ----------
-21 BECOME USER 0
-22 ALTER USER 0
-23 DROP USER 0
-30 CREATE ROLLBACK SEGMENT 0
-31 ALTER ROLLBACK SEGMENT 0
-32 DROP ROLLBACK SEGMENT 0
-40 CREATE TABLE 0
-41 CREATE ANY TABLE 0
-42 ALTER ANY TABLE 0
-43 BACKUP ANY TABLE 0
-44 DROP ANY TABLE 0
PRIVILEGE NAME PROPERTY
---------- ---------------------------------------- ----------
-45 LOCK ANY TABLE 0
-46 COMMENT ANY TABLE 0
-47 SELECT ANY TABLE 0
-48 INSERT ANY TABLE 0
-49 UPDATE ANY TABLE 0
-50 DELETE ANY TABLE 0
-60 CREATE CLUSTER 0
-61 CREATE ANY CLUSTER 0
-62 ALTER ANY CLUSTER 0
-63 DROP ANY CLUSTER 0
-71 CREATE ANY INDEX 0
PRIVILEGE NAME PROPERTY
---------- ---------------------------------------- ----------
-72 ALTER ANY INDEX 0
-73 DROP ANY INDEX 0
-80 CREATE SYNONYM 0
-81 CREATE ANY SYNONYM 0
-82 DROP ANY SYNONYM 0
-83 SYSDBA 0
-84 SYSOPER 0
-85 CREATE PUBLIC SYNONYM 0
-86 DROP PUBLIC SYNONYM 0
-90 CREATE VIEW 0
-91 CREATE ANY VIEW 0
PRIVILEGE NAME PROPERTY
---------- ---------------------------------------- ----------
-92 DROP ANY VIEW 0
-105 CREATE SEQUENCE 0
-106 CREATE ANY SEQUENCE 0
-107 ALTER ANY SEQUENCE 0
-108 DROP ANY SEQUENCE 0
-109 SELECT ANY SEQUENCE 0
-115 CREATE DATABASE LINK 0
-120 CREATE PUBLIC DATABASE LINK 0
-121 DROP PUBLIC DATABASE LINK 0
-125 CREATE ROLE 0
-126 DROP ANY ROLE 0
PRIVILEGE NAME PROPERTY
---------- ---------------------------------------- ----------
-127 GRANT ANY ROLE 0
-128 ALTER ANY ROLE 0
-130 AUDIT ANY 0
-135 ALTER DATABASE 0
-138 FORCE TRANSACTION 0
-139 FORCE ANY TRANSACTION 0
-140 CREATE PROCEDURE 0
-141 CREATE ANY PROCEDURE 0
-142 ALTER ANY PROCEDURE 0
-143 DROP ANY PROCEDURE 0
-144 EXECUTE ANY PROCEDURE 0
PRIVILEGE NAME PROPERTY
---------- ---------------------------------------- ----------
-151 CREATE TRIGGER 0
-152 CREATE ANY TRIGGER 0
-153 ALTER ANY TRIGGER 0
-154 DROP ANY TRIGGER 0
-160 CREATE PROFILE 0
-161 ALTER PROFILE 0
-162 DROP PROFILE 0
-163 ALTER RESOURCE COST 0
-165 ANALYZE ANY 0
-167 GRANT ANY PRIVILEGE 0
-172 CREATE MATERIALIZED VIEW 0
PRIVILEGE NAME PROPERTY
---------- ---------------------------------------- ----------
-173 CREATE ANY MATERIALIZED VIEW 0
-174 ALTER ANY MATERIALIZED VIEW 0
-175 DROP ANY MATERIALIZED VIEW 0
-177 CREATE ANY DIRECTORY 0
-178 DROP ANY DIRECTORY 0
-180 CREATE TYPE 0
-181 CREATE ANY TYPE 0
-182 ALTER ANY TYPE 0
-183 DROP ANY TYPE 0
-184 EXECUTE ANY TYPE 0
-186 UNDER ANY TYPE 0
PRIVILEGE NAME PROPERTY
---------- ---------------------------------------- ----------
-188 CREATE LIBRARY 0
-189 CREATE ANY LIBRARY 0
-190 ALTER ANY LIBRARY 0
-191 DROP ANY LIBRARY 0
-192 EXECUTE ANY LIBRARY 0
-194 WRITEDOWN DBLOW 0
-195 READUP DBHIGH 0
-196 WRITEUP DBHIGH 0
-197 WRITEDOWN 0
-198 READUP 0
-199 WRITEUP 0
PRIVILEGE NAME PROPERTY
---------- ---------------------------------------- ----------
-200 CREATE OPERATOR 0
-201 CREATE ANY OPERATOR 0
-202 ALTER ANY OPERATOR 0
-203 DROP ANY OPERATOR 0
-204 EXECUTE ANY OPERATOR 0
-205 CREATE INDEXTYPE 0
-206 CREATE ANY INDEXTYPE 0
-207 ALTER ANY INDEXTYPE 0
-208 DROP ANY INDEXTYPE 0
-209 UNDER ANY VIEW 0
-210 QUERY REWRITE 0
PRIVILEGE NAME PROPERTY
---------- ---------------------------------------- ----------
-211 GLOBAL QUERY REWRITE 0
-212 EXECUTE ANY INDEXTYPE 0
-213 UNDER ANY TABLE 0
-214 CREATE DIMENSION 0
-215 CREATE ANY DIMENSION 0
-216 ALTER ANY DIMENSION 0
-217 DROP ANY DIMENSION 0
-218 MANAGE ANY QUEUE 1
-219 ENQUEUE ANY QUEUE 1
-220 DEQUEUE ANY QUEUE 1
-222 CREATE ANY CONTEXT 0
PRIVILEGE NAME PROPERTY
---------- ---------------------------------------- ----------
-223 DROP ANY CONTEXT 0
-224 CREATE ANY OUTLINE 0
-225 ALTER ANY OUTLINE 0
-226 DROP ANY OUTLINE 0
-227 ADMINISTER RESOURCE MANAGER 1
-228 ADMINISTER DATABASE TRIGGER 0
-229 CREATE SECURITY PROFILE 0
-230 CREATE ANY SECURITY PROFILE 0
-231 DROP ANY SECURITY PROFILE 0
-232 ALTER ANY SECURITY PROFILE 0
-233 ADMINISTER SECURITY 0
PRIVILEGE NAME PROPERTY
---------- ---------------------------------------- ----------
-234 ON COMMIT REFRESH 0
-235 EXEMPT ACCESS POLICY 0
-236 RESUMABLE 0
-237 SELECT ANY DICTIONARY 0
-238 DEBUG CONNECT SESSION 0
-239 DEBUG CONNECT USER 0
-240 DEBUG CONNECT ANY 0
-241 DEBUG ANY PROCEDURE 0
-243 FLASHBACK ANY TABLE 0
-244 GRANT ANY OBJECT PRIVILEGE 0
-245 CREATE EVALUATION CONTEXT 1
PRIVILEGE NAME PROPERTY
---------- ---------------------------------------- ----------
-246 CREATE ANY EVALUATION CONTEXT 1
-247 ALTER ANY EVALUATION CONTEXT 1
-248 DROP ANY EVALUATION CONTEXT 1
-249 EXECUTE ANY EVALUATION CONTEXT 1
-250 CREATE RULE SET 1
-251 CREATE ANY RULE SET 1
-252 ALTER ANY RULE SET 1
-253 DROP ANY RULE SET 1
-254 EXECUTE ANY RULE SET 1
-255 EXPORT FULL DATABASE 0
-256 IMPORT FULL DATABASE 0
PRIVILEGE NAME PROPERTY
---------- ---------------------------------------- ----------
-257 CREATE RULE 1
-258 CREATE ANY RULE 1
-259 ALTER ANY RULE 1
-260 DROP ANY RULE 1
-261 EXECUTE ANY RULE 1
-262 ANALYZE ANY DICTIONARY 0
-263 ADVISOR 0
-264 CREATE JOB 0
-265 CREATE ANY JOB 0
-266 EXECUTE ANY PROGRAM 0
-267 EXECUTE ANY CLASS 0
PRIVILEGE NAME PROPERTY
---------- ---------------------------------------- ----------
-268 MANAGE SCHEDULER 0
-269 SELECT ANY TRANSACTION 0
-270 DROP ANY SQL PROFILE 0
-271 ALTER ANY SQL PROFILE 0
-272 ADMINISTER SQL TUNING SET 0
-273 ADMINISTER ANY SQL TUNING SET 0
-274 CREATE ANY SQL PROFILE 0
-275 EXEMPT IDENTITY POLICY 0
173 rows selected.
關於O7_DICTIONARY_ACCESSIBILITY boolean FALSE~~~對sys物件的保護
為true 有 any table的user可以訪問sys 物件 false則不行
SQL> conn zz/a850624
Connected.
SQL> select * from test;
select * from test
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select * from sys.testsys;
select * from sys.testsys
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select * from xh.test;
A
----------
1
2
select * from session_privs;查詢使用者當前 許可權
SQL> show parameter o7
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
O7_DICTIONARY_ACCESSIBILITY boolean TRUE
SQL> conn zz/a850624
Connected.
SQL> select * from sys.testsys;
A
----------
10
10
10
10
SQL> select count(*) from sys.obj$;
COUNT(*)
----------
49388
另外false時 SYS 使用者必須as sysdba or sysoper連線
SQL> conn / as sysdba
Connected.
SQL> alter system set O7_DICTIONARY_ACCESSIBILITY=true scope=spfile;
System altered.
SQL> startup force
ORACLE instance started.
Total System Global Area 188743680 bytes
Fixed Size 788068 bytes
Variable Size 145488284 bytes
Database Buffers 41943040 bytes
Redo Buffers 524288 bytes
Database mounted.
Database opened.
SQL> conn sys/88711009
Connected.
SQL> conn xh/a831115 DBA
Connected.
SQL> shutdown immediate;
ORA-01031: insufficient privileges ~~~~~~~~~~~~~必須as sysdba 才行
帶 with admin option(還可以給別人) 使用者 可以revoke其它使用者的這個許可權~~即使不是他授予的
SQL>SQL> conn xh/a831115;
Connected.
SQL> grant select any table to zz with admin option;
Grant succeeded.
SQL> grant select any table to yy with admin option;
Grant succeeded.
SQL> conn zz/a850624
Connected.
SQL> revoke select any table from yy;
Revoke succeeded.
SQL> conn xh/a831115
Connected.
SQL> grant select on xh.test to zz with grant option;
Grant succeeded.
SQL> conn zz/a850624
Connected.
SQL> grant select on xh.test to yy;
Grant succeeded.
SQL> conn yy/a666666
Connected.
SQL> select * from xh.test;
A
----------
1
2
SQL> conn xh/a831115
Connected.
SQL> revoke select on xh.test from yy;
revoke select on xh.test from yy
*
ERROR at line 1:
ORA-01927: cannot REVOKE privileges you did not grant
SQL> conn zz/a850624
Connected.
SQL> revoke select on xh.test from yy;
Revoke succeeded.
必須是授予者撤消被授予著
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL> grant select on xh.test to yy;
Grant succeeded.
SQL> conn yy/a666666
Connected.
SQL> select * from xh.test;
A
----------
1
2
SQL> conn xh/a831115
Connected.
SQL> revoke select on xh.test from zz;~~~~~~~~~~刪除ZZ的 聯絡到刪除YY的
Revoke succeeded.
SQL> conn zz/a850624
Connected.
SQL> select * from xh.test;~~~~~~~~~~~~有select any table 許可權,物件大不過系統許可權
A
----------
1
2
SQL> conn zz/a850624
Connected.
SQL> select * from session_privs;
PRIVILEGE
----------------------------------------
CREATE SESSION
ALTER SESSION
UNLIMITED TABLESPACE
CREATE TABLE
SELECT ANY TABLE
CREATE CLUSTER
CREATE ANY INDEX
CREATE SYNONYM
CREATE VIEW
CREATE SEQUENCE
CREATE DATABASE LINK
PRIVILEGE
----------------------------------------
CREATE PROCEDURE
CREATE TRIGGER
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE
16 rows selected.
SQL> conn yy/a666666
Connected.
SQL> select * from xh.test;
select * from xh.test
*
ERROR at line 1:
ORA-00942: table or view does not exist
DBA_SYS_PRIVS：列出授予使用者和角色的系統許可權
SESSION_PRIVS：列出使用者當前可用的許可權
DBA_TAB_PRIVS：列出對於資料庫中所有物件的所有授權
DBA_COL_PRIVS：描述資料庫中的所有物件-列授權
SQL> select grantee ,PRIVILEGE from dba_tab_privs where grantee='YY';
GRANTEE PRIVILEGE
------------------------------ ----------------------------------------
YY SELECT
SQL> grant update on xh.test to yy;
Grant succeeded.
SQL> select grantee ,PRIVILEGE from dba_tab_privs where grantee='YY';
GRANTEE PRIVILEGE
------------------------------ ----------------------------------------
YY UPDATE
YY SELECT
角色
角色的特點：
可以通過授予和撤消系統許可權所用的命令來授予和撤消使用者的角色。
可以將角色授予任何使用者或角色。但是，不能將角色授予它本身，也不能迴圈授予。
角色可以由系統許可權和物件許可權組成。
對於被授予某種角色的每個使用者來說，該角色可以啟用，也可以禁用。
角色可要求通過口令啟用。
在現有的使用者名稱和角色名中，每個角色名必須唯一。
角色不屬於任何人，也不存在於任何方案中。
在資料字典中儲存了有關角色的說明。
SQL> conn / as sysdba
Connected.
SQL> drop role testr;
Role dropped.
SQL> create role testr;
Role created.
SQL> grant select any table to testr;
Grant succeeded.
SQL> grant testr to zz;
Grant succeeded.
SQL> grant testr to zz with admin option ;
Grant succeeded.
SQL> grant testr to yy ;
Grant succeeded.
SQL> conn zz/a850624
Connected.
SQL> revoke testr from yy;
Revoke succeeded.
SQL> alter role testr identified by a831115
2 ;
Role altered.
~~~~~~~~~~~~~~~~~~~~~~~~~~~SYS給ZZ WITH ADIMN OPTION ZZ可以ALTER 該角色 並且可以刪除擁有其它這個角色的 USER中的這個角色即便 該user也是with admin option
SQL> conn / as sysdba~~~~~~~~~~~~~~~~~~
Connected.
SQL> create role testr;
Role created.
SQL> grant testr to zz with admin option ;
Grant succeeded.
SQL> grant testr to yy with admin optin;
grant testr to yy with admin optin
*
ERROR at line 1:
ORA-00994: missing OPTION keyword
SQL> grant testr to yy with admin option;
Grant succeeded.
SQL> conn zz/a850624
Connected.
SQL>
SQL> revoke testr from yy;
Revoke succeeded.
SQL> conn yy/a666666
Connected.
SQL> select * from session_roles;
ROLE
------------------------------
CONNECT
SELECT_CATALOG_ROLE
HS_ADMIN_ROLE
SQL>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~設定default
SQL> conn / as sysdba
Connected.
SQL>
SQL> select default_role,granted_role from dba_role_privs where grantee='YY';
DEF GRANTED_ROLE
--- ------------------------------
YES CONNECT
YES SELECT_CATALOG_ROLE
SQL> alter user yy default role connect;
User altered.
SQL> select default_role,granted_role from dba_role_privs where grantee='YY';
DEF GRANTED_ROLE
--- ------------------------------
YES CONNECT
NO SELECT_CATALOG_ROLE
SQL> alter user yy default role connect,SELECT_CATALOG_ROLE;
User altered.
SQL> select default_role,granted_role from dba_role_privs where grantee='YY';
DEF GRANTED_ROLE
--- ------------------------------
YES CONNECT
YES SELECT_CATALOG_ROLE
SQL> alter user yy default role none;
User altered.
SQL> select default_role,granted_role from dba_role_privs where grantee='YY';
DEF GRANTED_ROLE
--- ------------------------------
NO CONNECT
NO SELECT_CATALOG_ROLE
SQL> alter user yy default role all;
User altered.
SQL> select default_role,granted_role from dba_role_privs where grantee='YY';
DEF GRANTED_ROLE
--- ------------------------------
YES CONNECT
YES SELECT_CATALOG_ROLE
SQL> alter user yy default role all except SELECT_CATALOG_ROLE;
User altered.
SQL> select default_role,granted_role from dba_role_privs where grantee='YY';
DEF GRANTED_ROLE
--- ------------------------------
YES CONNECT
NO SELECT_CATALOG_ROLE
SQL> alter user yy default role all;
User altered.
SQL> select default_role,granted_role from dba_role_privs where grantee='YY';
DEF GRANTED_ROLE
--- ------------------------------
YES CONNECT
YES SELECT_CATALOG_ROLE
SQL>
~~~~~~~~~~~~~~~~~~~~~~~~~~起用 禁用
SQL> conn yy/a666666
Connected.
SQL> select * fromsession_role;
select * fromsession_role
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
SQL> select * from session_roles;
ROLE
------------------------------
CONNECT
RESOURCE
SELECT_CATALOG_ROLE
HS_ADMIN_ROLE
SQL> select * from session_roles;
ROLE
------------------------------
CONNECT
RESOURCE
SELECT_CATALOG_ROLE
HS_ADMIN_ROLE
SQL> set role resource,connect;(帶密碼的要加identified by 密碼)
Role set.
SQL> select * from session_roles;
ROLE
------------------------------
RESOURCE
CONNECT
SQL> exec dbms_session.set_role('all');
PL/SQL procedure successfully completed.
SQL> select * from session_roles;
ROLE
------------------------------
CONNECT
RESOURCE
SELECT_CATALOG_ROLE
HS_ADMIN_ROLE
還有all except role等
SQL> set role all except resource;
Role set.
SQL> select * from session_roles;
ROLE
------------------------------
CONNECT
SELECT_CATALOG_ROLE
HS_ADMIN_ROLE
SQL> exec dbms_session.set_role('all');
PL/SQL procedure successfully completed.
SQL> select * from session_roles;
ROLE
------------------------------
CONNECT
RESOURCE
SELECT_CATALOG_ROLE
HS_ADMIN_ROLE
SQL> select * from session_roles;
ROLE
------------------------------
CONNECT
RESOURCE
SELECT_CATALOG_ROLE~~~~~~~~~~~~~~~~~~~~~可以查字典表 DBA_(靜態資料字典檢視)
HS_ADMIN_ROLE
SQL> conn yy/a666666
Connected.
SQL> select count(*) from dba_users;
COUNT(*)
----------
33
SQL> exec dbms_session.set_role('all except SELECT_CATALOG_ROLE');
PL/SQL procedure successfully completed.
SQL> select count(*) from dba_users;
select count(*) from dba_users
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select count(*) from dba_users;
COUNT(*)
----------
33
SQL> select count(*) from sys.obj$;~~~~~~~~~~~~~~不行
select count(*) from sys.obj$
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> conn / as sysdba
Connected.
SQL> grant select any table to yy;
Grant succeeded.
SQL> conn yy/a666666
Connected.
SQL> select count(*) from sys.obj$;~~~~~~~~~~~~~~~~~要SELECT ANY TABLE 才能查這些資料字典表
COUNT(*)
----------
49406
SQL> conn / as sysdba
Connected.
SQL> revoke select any table from yy;
Revoke succeeded.
SQL> conn yy/a666666
Connected.
SQL> select count(*) from sys.obj$;
select count(*) from sys.obj$
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select count(*) from obj$;
select count(*) from obj$
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select * from session_role;
select * from session_role
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select * from session_roles;
ROLE
------------------------------
CONNECT
RESOURCE
SELECT_CATALOG_ROLE
HS_ADMIN_ROLE
********************************PL/SQL 與role關係******************************
SQL> select * from xh.test;
A
----------
1
1
2
SQL> show user
USER is "SYS"
SQL> grant insert on xh.test to yy;
Grant succeeded.
SQL> conn xh/a831115
Connected.
SQL> create or replace procedure tr (aa in int) as begin insert into test values
(aa);
2 commit;
3 end;
4 /
Procedure created.
SQL> conn / as sysdba
Connected.
SQL> revoke insert on xh.test from yy;~~~~~~~~~~~~~~~~~~~~~~~~~~刪除了許可權
Revoke succeeded.
SQL> grant execute on xh.tr to yy;
Grant succeeded.
SQL> conn xh/a831115
Connected.
SQL> exec tr(1);
PL/SQL procedure successfully completed.
SQL> conn yy/a666666
Connected.
SQL> exec xh.tr(2);~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~有過程許可權,過程還可以執行(即便YY 沒有操作XH.TR的許可權,但有執行XH.TR過程的許可權 而XH.TR的定義者 XH有操作XH.TEST表的許可權 所以 YY一樣可以執行)若XH 操作TEST的許可權 則 YY 執行這個過程不行
SQL> insert into xh.test values(3);~~~~~~~~~~~沒有插入許可權
insert into xh.test values(3)
*
ERROR at line 1:
ORA-01031: insufficient privileges
PL/SQL procedure successfully completed.
~~~******************************************************************************
SQL> create or replace procedure tr2 (aa in int) authid current_user as begin in 加了authid current_user
sert into test values(aa);
2 commit;
3 end;
4 /
Procedure created.
SQL> conn / as sysdba
Connected.
SQL> grant insert on xh.test to yy;
Grant succeeded.
SQL> grant execute on xh.tr2 to yy;
Grant succeeded.
SQL> conn xh/a831115
Connected.
SQL> exec tr2(3);
PL/SQL procedure successfully completed.
SQL> conn yy/a666666
Connected.
SQL> exec xh.tr2(3);
BEGIN xh.tr2(3); END;
*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "XH.TR2", line 1
ORA-06512: at line 1
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ procedure已經不能使用~~~~~~~~雖然有INSERT 許可權 以及exec許可權 還是不行
SQL> create table test (a int);~~~~~~~~~~~~~但在YY 裡建立TEST 表 則可以使用XH.TR2過程
Table created.
SQL> exec xh.tr2(1);
PL/SQL procedure successfully completed.
SQL> show uer
SP2-0158: unknown SHOW option "uer"
SQL> show ur
SP2-0158: unknown SHOW option "ur"
SQL> show user
USER is "YY"
authid current_user:只針對當前使用者方案,若使用者方案中有過程中的表則可以執行過程
***************************直接授權***************************
SQL> conn / as sysdba
Connected.
SQL> revoke insert on xh.test from yy;
Revoke succeeded.
SQL> create role trole;
Role created.
SQL> grant insert on xh.test to trole;
Grant succeeded.
SQL> grant trole to yy;
Grant succeeded.
SQL> conn yy/a666666
Connected.
SQL> insert into xh.test values(1);
1 row created.
SQL> commit;
Commit complete.
SQL> create or replace procedure tr3 (aa in int) as begin insert into xh.test v
alues(aa);
2 commit;
3 end;
4 /
Warning: Procedure created with compilation errors.
SQL> show error
Errors for PROCEDURE TR3:
LINE/COL ERROR
-------- -----------------------------------------------------------------
1/37 PL/SQL: SQL Statement ignored
1/52 PL/SQL: ORA-01031: insufficient privileges
SQL> grant insert on xh.test to yy;
Grant succeeded.
SQL> conn yy/a666666
Connected.
SQL> create or replace procedure tr3 (aa in int) as begin insert into xh.test v
alues(aa);
2 commit;
3 end;
4 /
Procedure created.
SQL>~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~如果要在 PL/SQL 裡操作另一個schma的 object那麼 必須有對那個物件的直接授權 而不是通過role
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12020513/viewspace-610712/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 角色許可權（Role）和系統許可權（System）的幾個澄清實驗
- Oracle角色許可權之Default RoleOracle
- Oracle資料庫使用者許可權控制 - Role - SynonymOracle資料庫
- 【自然框架】許可權的視訊演示（二）：許可權到欄位、許可權到記錄框架
- Oracle的物件許可權、角色許可權、系統許可權Oracle物件
- oracle實驗記錄 （可恢復session）OracleSession
- ORACLE許可權Oracle
- Linux 許可權管理之目錄許可權限制Linux
- 【LIUNX】目錄或檔案許可權，許可權授予
- oracle 許可權集合Oracle
- oracle 許可權管理Oracle
- Oracle許可權（一）Oracle
- oracle許可權全集Oracle
- Oracle 目錄許可權丟失故障恢復Oracle
- 如何用 Vue 實現前端許可權控制（路由許可權 + 檢視許可權 + 請求許可權）Vue前端路由
- Oracle 使用者、物件許可權、系統許可權Oracle物件
- 檔案目錄許可權操作
- mysql之許可權驗證MySql
- Oracle中常用的目錄許可權設定命令Oracle
- Oracle 許可權（grant、revoke）Oracle
- Oracle系統許可權Oracle
- Oracle檢視許可權Oracle
- 如何在儲存過程中擁有role的許可權儲存過程
- 【許可權管理】Oracle中檢視、回收使用者許可權Oracle
- 基於LDAP&&Role-based Authorization Strategy實現Jenkins團隊許可權管理LDAJenkins
- Linux 目錄許可權研究Linux
- Excel巧錄許可權矩陣Excel矩陣
- linux目錄的許可權Linux
- oracle實驗記錄 （flashback）Oracle
- oracle實驗記錄 （OMF）Oracle
- oracle實驗記錄 （NET）Oracle
- oracle實驗記錄 （audit）Oracle
- Oracle 查詢許可權角色Oracle
- oracle許可權整理檔案Oracle
- oracle使用者許可權Oracle
- oracle列級許可權控制Oracle
- Oracle users / 許可權 / grant privOracle
- Oracle 中的references許可權Oracle