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列表
PRIVILEGENAME 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 ROLE0
-107 ALTER ANY SEQUENCE0
-174 ALTER ANY SNAPSHOT0
-42 ALTER ANY TABLE 0
-153 ALTER ANY TRIGGER 0
-182 ALTER ANY TYPE0
-135 ALTER DATABASE0
-202 ALTER OPERATOR0
-161 ALTER PROFILE 0
-163 ALTER RESOURCE COST 0
-31 ALTER ROLLBACK SEGMENT0
-6 ALTER SESSION 0
-3 ALTER SYSTEM0
-11 ALTER TABLESPACE0
-22 ALTER USER0
-165 ANALYZE ANY 0
-130 AUDIT ANY 0
-4 AUDIT SYSTEM0
-43 BACKUP ANY TABLE0
-21 BECOME USER 0
-46 COMMENT ANY TABLE 0
-61 CREATE ANY CLUSTER0
-222 CREATE ANY CONTEXT0
-215 CREATE ANY DIMENSION0
-177 CREATE ANY DirectorY0
-71 CREATE ANY INDEX0
-206 CREATE ANY INDEXTYPE0
-189 CREATE ANY LIBRARY0
-201 CREATE ANY OPERATOR 0
-224 CREATE ANY OUTLINE0
-141 CREATE ANY PROCEDURE0
-106 CREATE ANY SEQUENCE 0
-173 CREATE ANY SNAPSHOT 0
-81 CREATE ANY SYNONYM0
-41 CREATE ANY TABLE0
-152 CREATE ANY TRIGGER0
-181 CREATE ANY TYPE 0
-91 CREATE ANY VIEW 0
-60 CREATE CLUSTER0
-115 CREATE DATABASE LINK0
-214 CREATE DIMENSION0
-205 CREATE INDEXTYPE0
-188 CREATE LIBRARY0
-200 CREATE OPERATOR 0
-140 CREATE PROCEDURE0
-160 CREATE PROFILE0
-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 SESSION0
-172 CREATE SNAPSHOT 0
-80 CREATE SYNONYM0
-40 CREATE TABLE0
-10 CREATE TABLESPACE 0
-151 CREATE TRIGGER0
-180 CREATE TYPE 0
-20 CREATE USER 0
-90 CREATE VIEW 0
-50 DELETE ANY TABLE0
-220 DEQUEUE ANY QUEUE 1
-63 DROP ANY CLUSTER0
-223 DROP ANY CONTEXT0
-217 DROP ANY DIMENSION0
-178 DROP ANY DIRECTORY0
-73 DROP ANY INDEX0
-208 DROP ANY INDEXTYPE0
-191 DROP ANY LIBRARY0
-203 DROP ANY OPERATOR 0
-226 DROP ANY OUTLINE0
-143 DROP ANY PROCEDURE0
-126 DROP ANY ROLE 0
-108 DROP ANY SEQUENCE 0
-175 DROP ANY SNAPSHOT 0
-82 DROP ANY SYNONYM0
-44 DROP ANY TABLE0
-154 DROP ANY TRIGGER0
-183 DROP ANY TYPE 0
-92 DROP ANY VIEW 0
-162 DROP PROFILE0
-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 OPERATOR0
-144 EXECUTE ANY PROCEDURE 0
-184 EXECUTE ANY TYPE0
-209 EXTENDS ANY TYPE0
-186 EXTENDS TYPE0
-139 FORCE ANY TRANSACTION 0
-138 FORCE TRANSACTION 0
-211 GLOBAL QUERY REWRITE0
-167 GRANT ANY PRIVILEGE 0
-127 GRANT ANY ROLE0
-48 INSERT ANY TABLE0
-45 LOCK ANY TABLE0
-218 MANAGE ANY QUEUE1
-12 MANAGE TABLESPACE 0
-210 QUERY REWRITE 0
-198 READUP0
-195 READUP DBHIGH 0
-7 RESTRICTED SESSION0
-109 SELECT ANY SEQUENCE 0
-47 SELECT ANY TABLE0
-83 SYSDBA0
-84 SYSOPER 0
-15 UNLIMITED TABLESPACE0
-49 UPDATE ANY TABLE0
-197 WRITEDOWN 0
-194 WRITEDOWN DBLOW 0
-199 WRITEUP 0
-196 WRITEUP DBHIGH
