Oracle用户权限的管理

在Oracle数据库中,用户的权限分为两种(在这里我们不讨论dba或dbopr的权限,只考虑普通用户的权限),分别是System Privilege系统权限 和User Table Privilege用户数据表权限.

1.首先,创建用户,以下几条命令可以创建一个用户,前提是必须以DBA的身份登录(如果你不是DBA,不要看下去了):

create user DB_USER identified by DB_USER_PW    '创建用户DB_USER,密码为DB_USER_PW

grant create session to DB_USER     '给用户创建会话的权限

grant resource to DB_USER

2.当用户建立后,会自动在Oracle数据库系统中生成属于该用户的Scheme (可以理解为所有属于该用户的表,视图....等对象的集合).

该用户可以将对这些对象的访问权限赋予其它的系统用户.

3.该用户用sqlplus登录后,以下命令可以看到该用户的权限(该部分取自于CNOUG网站):

本用户读取其他用户对象的权限:
　　select * from user_tab_privs;
本用户所拥有的系统权限:
　　select * from user_sys_privs;

4. System Privilege列表

PRIVILEGE  NAME                                     PROPERTY 
---------- ---------------------------------------- ----------
      -228 ADMINISTER DATABASE TRIGGER                       0
      -227 ADMINISTER RESOURCE MANAGER                       1
       -62 ALTER ANY CLUSTER                                 0
      -216 ALTER ANY DIMENSION                               0
       -72 ALTER ANY INDEX                                   0
      -207 ALTER ANY INDEXTYPE                               0
      -190 ALTER ANY LIBRARY                                 0
      -225 ALTER ANY OUTLINE                                 0
      -142 ALTER ANY PROCEDURE                               0
      -128 ALTER ANY ROLE                                    0
      -107 ALTER ANY SEQUENCE                                0
      -174 ALTER ANY SNAPSHOT                                0
       -42 ALTER ANY TABLE                                   0
      -153 ALTER ANY TRIGGER                                 0
      -182 ALTER ANY TYPE                                    0
      -135 ALTER DATABASE                                    0
      -202 ALTER OPERATOR                                    0
      -161 ALTER PROFILE                                     0
      -163 ALTER RESOURCE COST                               0
       -31 ALTER ROLLBACK SEGMENT                            0
        -6 ALTER SESSION                                     0
        -3 ALTER SYSTEM                                      0
       -11 ALTER TABLESPACE                                  0
       -22 ALTER USER                                        0
      -165 ANALYZE ANY                                       0
      -130 AUDIT ANY                                         0
        -4 AUDIT SYSTEM                                      0
       -43 BACKUP ANY TABLE                                  0
       -21 BECOME USER                                       0
       -46 COMMENT ANY TABLE                                 0
       -61 CREATE ANY CLUSTER                                0
      -222 CREATE ANY CONTEXT                                0
      -215 CREATE ANY DIMENSION                              0
      -177 CREATE ANY DIRECTORY                              0
       -71 CREATE ANY INDEX                                  0
      -206 CREATE ANY INDEXTYPE                              0
      -189 CREATE ANY LIBRARY                                0
      -201 CREATE ANY OPERATOR                               0
      -224 CREATE ANY OUTLINE                                0
      -141 CREATE ANY PROCEDURE                              0
      -106 CREATE ANY SEQUENCE                               0
      -173 CREATE ANY SNAPSHOT                               0
       -81 CREATE ANY SYNONYM                                0
       -41 CREATE ANY TABLE                                  0
      -152 CREATE ANY TRIGGER                                0
      -181 CREATE ANY TYPE                                   0
       -91 CREATE ANY VIEW                                   0
       -60 CREATE CLUSTER                                    0
      -115 CREATE DATABASE LINK                              0
      -214 CREATE DIMENSION                                  0
      -205 CREATE INDEXTYPE                                  0
      -188 CREATE LIBRARY                                    0
      -200 CREATE OPERATOR                                   0
      -140 CREATE PROCEDURE                                  0
      -160 CREATE PROFILE                                    0
      -120 CREATE PUBLIC DATABASE LINK                       0
       -85 CREATE PUBLIC SYNONYM                             0
      -125 CREATE ROLE                                       0
       -30 CREATE ROLLBACK SEGMENT                           0
      -105 CREATE SEQUENCE                                   0
        -5 CREATE SESSION                                    0
      -172 CREATE SNAPSHOT                                   0
       -80 CREATE SYNONYM                                    0
       -40 CREATE TABLE                                      0
       -10 CREATE TABLESPACE                                 0
      -151 CREATE TRIGGER                                    0
      -180 CREATE TYPE                                       0
       -20 CREATE USER                                       0
       -90 CREATE VIEW                                       0
       -50 DELETE ANY TABLE                                  0
      -220 DEQUEUE ANY QUEUE                                 1
       -63 DROP ANY CLUSTER                                  0
      -223 DROP ANY CONTEXT                                  0
      -217 DROP ANY DIMENSION                                0
      -178 DROP ANY DIRECTORY                                0
       -73 DROP ANY INDEX                                    0
      -208 DROP ANY INDEXTYPE                                0
      -191 DROP ANY LIBRARY                                  0
      -203 DROP ANY OPERATOR                                 0
      -226 DROP ANY OUTLINE                                  0
      -143 DROP ANY PROCEDURE                                0
      -126 DROP ANY ROLE                                     0
      -108 DROP ANY SEQUENCE                                 0
      -175 DROP ANY SNAPSHOT                                 0
       -82 DROP ANY SYNONYM                                  0
       -44 DROP ANY TABLE                                    0
      -154 DROP ANY TRIGGER                                  0
      -183 DROP ANY TYPE                                     0
       -92 DROP ANY VIEW                                     0
      -162 DROP PROFILE                                      0
      -121 DROP PUBLIC DATABASE LINK                         0
       -86 DROP PUBLIC SYNONYM                               0
       -32 DROP ROLLBACK SEGMENT                             0
       -13 DROP TABLESPACE                                   0
       -23 DROP USER                                         0
      -219 ENQUEUE ANY QUEUE                                 1
      -212 EXECUTE ANY INDEXTYPE                             0
      -192 EXECUTE ANY LIBRARY                               0
      -204 EXECUTE ANY OPERATOR                              0
      -144 EXECUTE ANY PROCEDURE                             0
      -184 EXECUTE ANY TYPE                                  0
      -209 EXTENDS ANY TYPE                                  0
      -186 EXTENDS TYPE                                      0
      -139 FORCE ANY TRANSACTION                             0
      -138 FORCE TRANSACTION                                 0
      -211 GLOBAL QUERY REWRITE                              0
      -167 GRANT ANY PRIVILEGE                               0
      -127 GRANT ANY ROLE                                    0
       -48 INSERT ANY TABLE                                  0
       -45 LOCK ANY TABLE                                    0
      -218 MANAGE ANY QUEUE                                  1
       -12 MANAGE TABLESPACE                                 0
      -210 QUERY REWRITE                                     0
      -198 READUP                                            0
      -195 READUP DBHIGH                                     0
        -7 RESTRICTED SESSION                                0
      -109 SELECT ANY SEQUENCE                               0
       -47 SELECT ANY TABLE                                  0
       -83 SYSDBA                                            0
       -84 SYSOPER                                           0
       -15 UNLIMITED TABLESPACE                              0
       -49 UPDATE ANY TABLE                                  0
      -197 WRITEDOWN                                         0
      -194 WRITEDOWN DBLOW                                   0
      -199 WRITEUP                                           0
      -196 WRITEUP DBHIGH   

原文转自：http://www.ltesting.net