ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle实验记录 (权限,role)

oracle实验记录 (权限,role)

原创 Linux操作系统 作者:fufuh2o 时间:2009-07-29 11:42:19 0 删除 编辑

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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-06-26

  • 博文量
    182
  • 访问量
    426949