Oracle dba_objects和all_objects 最大的區別
maohaiqing0304發表於2015-08-14
連結：http://blog.itpub.net/28602568/viewspace-1771547/
ALL_OBJECTS describes all objects accessible to the current user. 描述當前使用者有訪問許可權的所有物件
DBA_OBJECTS describes all objects in the database. 描述了資料庫中的所有物件
如下部分為2個檢視的建立語句：
USER_OBJECTS describes all objects owned by the current user. 描述了當前使用者所擁有的所有物件
千萬不要覺得all_*和dba_*檢視都是所有物件的意思， all_*和許可權有關；
所以出現【不同使用者訪問all_objects檢視，相同過濾條件，結果不同 (例如："A使用者訪問all_objects檢視過濾B.T1表有資料，而C使用者也訪問all_objects檢視過濾B.T1表卻沒有資料" ）】是正常的，是因為C使用者沒有訪問B.T1表許可權，用dba_objects可以解決；
所以適當的選擇 dba_*和all_*檢視。
如下部分為2個檢視的建立語句：
ALL_OBJECTS 檢視建立語句: ** 重點檢視各個object_type的privs部分
獲取建立語句命令: SELECT VIEW_NAME,TEXT FROM DBA_VIEWS WHERE VIEW_NAME=upper('ALL_OBJECTS');
獲取建立語句命令: SELECT VIEW_NAME,TEXT FROM DBA_VIEWS WHERE VIEW_NAME=upper('ALL_OBJECTS');
語句如下：
CREATE OR REPLACE VIEW ALL_OBJECTS
CREATE OR REPLACE VIEW ALL_OBJECTS
(owner, object_name, subobject_name , object_id, data_object_id, object_type, created , last_ddl_time, timestamp, status , temporary, generated, secondary , namespace, edition_name)
AS
select u.name , o.name, o.subname, o.obj# , o.dataobj#,
decode(o.type# , 0, 'NEXT OBJECT', 1 , 'INDEX', 2, 'TABLE' , 3, 'CLUSTER',
4, 'VIEW' , 5, 'SYNONYM', 6 , 'SEQUENCE',
7, 'PROCEDURE' , 8, 'FUNCTION', 9 , 'PACKAGE',
11, 'PACKAGE BODY' , 12, 'TRIGGER',
13, 'TYPE' , 14, 'TYPE BODY',
19, 'TABLE PARTITION' , 20, 'INDEX PARTITION', 21 , 'LOB',
22, 'LIBRARY' , 23, 'DIRECTORY', 24 , 'QUEUE',
28, 'JAVA SOURCE' , 29, 'JAVA CLASS', 30 , 'JAVA RESOURCE',
32, 'INDEXTYPE' , 33, 'OPERATOR',
34, 'TABLE SUBPARTITION' , 35, 'INDEX SUBPARTITION',
40, 'LOB PARTITION' , 41, 'LOB SUBPARTITION',
42, NVL ((SELECT 'REWRITE EQUIVALENCE'
FROM sum$ s
WHERE s.obj# =o.obj#
and bitand (s.xpflags, 8388608) = 8388608),
'MATERIALIZED VIEW'),
43, 'DIMENSION' ,
44, 'CONTEXT' , 46, 'RULE SET', 47 , 'RESOURCE PLAN',
48, 'CONSUMER GROUP' ,
55, 'XML SCHEMA' , 56, 'JAVA DATA',
57, 'EDITION' , 59, 'RULE',
60, 'CAPTURE' , 61, 'APPLY',
62, 'EVALUATION CONTEXT' ,
66, 'JOB' , 67, 'PROGRAM', 68 , 'JOB CLASS', 69, 'WINDOW',
72, 'SCHEDULER GROUP' , 74, 'SCHEDULE', 79 , 'CHAIN',
81, 'FILE GROUP' , 82, 'MINING MODEL', 87 , 'ASSEMBLY',
90, 'CREDENTIAL' , 92, 'CUBE DIMENSION', 93 , 'CUBE',
94, 'MEASURE FOLDER' , 95, 'CUBE BUILD PROCESS',
100, 'FILE WATCHER' , 101, 'DESTINATION',
'UNDEFINED'),
o.ctime, o.mtime ,
to_char(o.stime , 'YYYY-MM-DD:HH24:MI:SS'),
decode(o.status , 0, 'N/A', 1 , 'VALID', 'INVALID'),
decode(bitand (o.flags, 2), 0 , 'N', 2, 'Y' , 'N'),
decode(bitand (o.flags, 4), 0 , 'N', 4, 'Y' , 'N'),
decode(bitand (o.flags, 16), 0 , 'N', 16, 'Y' , 'N'),
o.namespace,
o.defining_edition
from sys."_CURRENT_EDITION_OBJ" o, sys.user$ u
where o.owner# = u.user#
and o.linkname is null
and ( o.type# not in ( 1 /* INDEX - handled below */,
10 /* NON-EXISTENT */ )
or
( o.type# = 1 and 1 = (select 1
from sys.ind$ i
where i.obj# = o.obj#
and i.type# in ( 1, 2 , 3, 4, 6 , 7, 9))))
and o.name != '_NEXT_OBJECT'
and o.name != '_default_auditing_options_'
and bitand(o.flags , 128) = 0
and
(
o.owner# in (userenv( 'SCHEMAID')/*當前使用者*/, 1 /* PUBLIC */)
or
(
/* non-procedural objects */
o.type# not in ( 7, 8 , 9, 11, 12 , 13, 14, 28 , 29, 30, 56 , 93)
and
o.obj# in (select obj# from sys.objauth$
where grantee# in ( select kzsrorol from x$kzsro)
and privilege# in ( 3 /* DELETE */ , 6 /* INSERT */,
7 /* LOCK */ , 9 /* SELECT */,
10 /* UPDATE */ , 12 /* EXECUTE */,
11 /* USAGE */ , 16 /* CREATE */,
17 /* READ */ , 18 /* WRITE */ ))
)
or
(
o.type# in (7, 8, 9 , 28, 29, 30 , 56) /* prc, fcn, pkg */
and
(
exists (select null from sys.objauth$ oa
where oa.obj# = o.obj#
and oa.grantee# in ( select kzsrorol from x$kzsro)
and oa.privilege# in ( 12 /* EXECUTE */ , 26 /* DEBUG */))
or
exists (select null from v$enabledprivs
where priv_number in (
-144 /* EXECUTE ANY PROCEDURE */,
-141 /* CREATE ANY PROCEDURE */ ,
-241 /* DEBUG ANY PROCEDURE */
)
)
)
)
or
(
o.type# in (19) /* partitioned table objects */
and
exists (select bo# from tabpart$ where obj# = o.obj# and
bo# in (select obj# from sys.objauth$
where grantee# in ( select kzsrorol from x$kzsro)
and privilege# in ( 9 /* SELECT */ ))
)
)
or
(
o.type# in (12) /* trigger */
and
(
exists (select null from sys.trigger$ t, sys.objauth$ oa
where bitand (t.property, 24) = 0
and t.obj# = o.obj#
and oa.obj# = t.baseobject
and oa.grantee# in ( select kzsrorol from x$kzsro)
and oa.privilege# = 26 /* DEBUG */)
or
exists (select null from v$enabledprivs
where priv_number in (
-152 /* CREATE ANY TRIGGER */ ,
-241 /* DEBUG ANY PROCEDURE */
)
)
)
)
or
(
o.type# = 11 /* pkg body */
and
(
exists (select null
from sys."_ACTUAL_EDITION_OBJ" specobj, sys.dependency$ dep ,
sys.objauth$ oa
where specobj.owner# = o.owner#
and specobj.name = o.name
and specobj.type# = 9 /* pkg */
and dep.d_obj# = o.obj# and dep.p_obj# = specobj.obj#
and oa.obj# = specobj.obj#
and oa.grantee# in ( select kzsrorol from x$kzsro)
and oa.privilege# = 26 /* DEBUG */)
or
exists (select null from v$enabledprivs
where priv_number in (
-141 /* CREATE ANY PROCEDURE */ ,
-241 /* DEBUG ANY PROCEDURE */
)
)
)
)
or
(
o.type# in (22) /* library */
and
exists (select null from v$enabledprivs
where priv_number in (
- 189 /* CREATE ANY LIBRARY */ ,
- 190 /* ALTER ANY LIBRARY */ ,
- 191 /* DROP ANY LIBRARY */ ,
- 192 /* EXECUTE ANY LIBRARY */
)
)
)
or
(
/* index, table, view, synonym, table partn, indx partn, */
/* table subpartn, index subpartn, cluster */
o.type# in (1, 2, 3 , 4, 5, 19 , 20, 34, 35 )
and
exists (select null from v$enabledprivs
where priv_number in (- 45 /* LOCK ANY TABLE */ ,
- 47 /* SELECT ANY TABLE */ ,
- 48 /* INSERT ANY TABLE */ ,
- 49 /* UPDATE ANY TABLE */ ,
- 50 /* DELETE ANY TABLE */ )
)
)
or
( o.type# = 6 /* sequence */
and
exists (select null from v$enabledprivs
where priv_number = -109 /* SELECT ANY SEQUENCE */)
)
or
( o.type# = 13 /* type */
and
(
exists (select null from sys.objauth$ oa
where oa.obj# = o.obj#
and oa.grantee# in ( select kzsrorol from x$kzsro)
and oa.privilege# in ( 12 /* EXECUTE */ , 26 /* DEBUG */))
or
exists (select null from v$enabledprivs
where priv_number in (- 184 /* EXECUTE ANY TYPE */ ,
- 181 /* CREATE ANY TYPE */ ,
- 241 /* DEBUG ANY PROCEDURE */ ))
)
)
or
(
o.type# = 14 /* type body */
and
(
exists (select null
from sys."_ACTUAL_EDITION_OBJ" specobj, sys.dependency$ dep ,
sys.objauth$ oa
where specobj.owner# = o.owner#
and specobj.name = o.name
and specobj.type# = 13 /* type */
and dep.d_obj# = o.obj# and dep.p_obj# = specobj.obj#
and oa.obj# = specobj.obj#
and oa.grantee# in ( select kzsrorol from x$kzsro)
and oa.privilege# = 26 /* DEBUG */)
or
exists (select null from v$enabledprivs
where priv_number in (
- 181 /* CREATE ANY TYPE */ ,
- 241 /* DEBUG ANY PROCEDURE */
)
)
)
)
or
(
o.type# = 23 /* directory */
and
exists (select null from v$enabledprivs
where priv_number in (
- 177 /* CREATE ANY DIRECTORY */ ,
- 178 /* DROP ANY DIRECTORY */
)
)
)
or
(
o.type# = 42 /* summary jjf table privs have to change to summary */
and
exists (select null from v$enabledprivs
where priv_number in (- 45 /* LOCK ANY TABLE */ ,
- 47 /* SELECT ANY TABLE */ ,
- 48 /* INSERT ANY TABLE */ ,
- 49 /* UPDATE ANY TABLE */ ,
- 50 /* DELETE ANY TABLE */ )
)
)
or
(
o.type# = 32 /* indextype */
and
exists (select null from v$enabledprivs
where priv_number in (
- 205 /* CREATE INDEXTYPE */ ,
- 206 /* CREATE ANY INDEXTYPE */ ,
- 207 /* ALTER ANY INDEXTYPE */ ,
- 208 /* DROP ANY INDEXTYPE */
)
)
)
or
(
o.type# = 33 /* operator */
and
exists (select null from v$enabledprivs
where priv_number in (
- 200 /* CREATE OPERATOR */ ,
- 201 /* CREATE ANY OPERATOR */ ,
- 202 /* ALTER ANY OPERATOR */ ,
- 203 /* DROP ANY OPERATOR */ ,
- 204 /* EXECUTE OPERATOR */
)
)
)
or
(
o.type# = 44 /* context */
and
exists (select null from v$enabledprivs
where priv_number in (
- 222 /* CREATE ANY CONTEXT */,
- 223 /* DROP ANY CONTEXT */
)
)
)
or
(
o.type# = 48 /* resource consumer group */
and
exists (select null from v$enabledprivs
where priv_number in ( 12) /* switch consumer group privilege */
)
)
or
(
o.type# = 46 /* rule set */
and
exists (select null from v$enabledprivs
where priv_number in (
- 251, /* create any rule set */
- 252, /* alter any rule set */
- 253, /* drop any rule set */
- 254 /* execute any rule set */
)
)
)
or
(
o.type# = 55 /* XML schema */
and
1 = (select /*+ NO_MERGE */ xml_schema_name_present.is_schema_present (o.name, u2.id2) id1 from (select /*+ NO_MERGE */ userenv( 'SCHEMAID') id2 from dual) u2 )
/* we need a sub-query instead of the directy invoking
* xml_schema_name_present, because inside a view even the function
* arguments are evaluated as definers rights.
*/
)
or
(
o.type# = 59 /* rule */
and
exists (select null from v$enabledprivs
where priv_number in (
- 258, /* create any rule */
- 259, /* alter any rule */
- 260, /* drop any rule */
- 261 /* execute any rule */
)
)
)
or
(
o.type# = 62 /* evaluation context */
and
exists (select null from v$enabledprivs
where priv_number in (
- 246, /* create any evaluation context */
- 247, /* alter any evaluation context */
- 248, /* drop any evaluation context */
- 249 /* execute any evaluation context */
)
)
)
or
(
o.type# IN (66, 100) /* scheduler job or file watcher */
and
exists (select null from v$enabledprivs
where priv_number = -265 /* create any job */
)
)
or
(
o.type# IN (67, 79) /* scheduler program or chain */
and
exists (select null from v$enabledprivs
where priv_number in (
- 265, /* create any job */
- 266 /* execute any program */
)
)
)
or
(
o.type# = 68 /* scheduler job class */
and
exists (select null from v$enabledprivs
where priv_number in (
- 268, /* manage scheduler */
- 267 /* execute any class */
)
)
)
or (o.type# in ( 69, 72 , 74, 101))
/* scheduler windows, scheduler groups, schedules and destinations */
/* no privileges are needed to view these objects */
or
(
o.type# = 81 /* file group */
and
exists (select null from v$enabledprivs
where priv_number in (
- 277, /* manage any file group */
- 278 /* read any file group */
)
)
)
or
(
o.type# = 57 /* edition */
)
or
(
o.type# = 82 /* mining model */
and
exists (select null from v$enabledprivs
where priv_number in (
- 292, /* drop any mining model */
- 293, /* select any mining model */
- 294 /* alter any mining model */
)
)
)
or
(
o.type# in (87) /* assembly */
and
exists (select null from v$enabledprivs
where priv_number in (
- 282 /* CREATE ANY ASSEMBLY */ ,
- 283 /* ALTER ANY ASSEMBLY */ ,
- 284 /* DROP ANY ASSEMBLY */ ,
- 285 /* EXECUTE ANY ASSEMBLY */
)
)
)
or
(
o.type# = 92 /* cube dimension */
and
exists (select null from v$enabledprivs
where priv_number in (
- 302, /* ALTER ANY PRIMARY DIMENSION */
- 303, /* CREATE ANY PRIMARY DIMENSION */
- 304, /* DELETE ANY PRIMARY DIMENSION */
- 305, /* DROP ANY PRIMARY DIMENSION */
- 306, /* INSERT ANY PRIMARY DIMENSION */
- 307 /* SELECT ANY PRIMARY DIMENSION */
)
)
)
or
(
o.type# = 93 /* cube */
and
( o.obj# in
( select obj# /* directly granted privileges */
from sys.objauth$
where grantee# in ( select kzsrorol from x$kzsro )
)
or
(
exists (select null from v$enabledprivs
where priv_number in (
-309, /* ALTER ANY CUBE */
-310, /* CREATE ANY CUBE */
-311, /* DROP ANY CUBE */
-312, /* SELECT ANY CUBE */
-313 /* UPDATE ANY CUBE */
)
)
)
)
and /* require access to all Dimensions of the Cube */
( 1 = ( SELECT decode( have_all_dim_access, null , 1, have_all_dim_access)
FROM
( SELECT
obj#,
MIN(have_dim_access ) have_all_dim_access
FROM
( SELECT
c.obj# obj# ,
( CASE
WHEN
( do.owner# in ( userenv( 'SCHEMAID'), 1 ) /* public objects */
or do.obj# in
( select obj# /* directly granted privileges */
from sys.objauth$
where grantee# in ( select kzsrorol from x$kzsro )
)
or /* user has system privileges */
( exists ( select null from v$enabledprivs
where priv_number in (
-302, /* ALTER ANY PRIMARY DIMENSION */
-303, /* CREATE ANY PRIMARY DIMENSION */
-304, /* DELETE ANY PRIMARY DIMENSION */
-305, /* DROP ANY PRIMARY DIMENSION */
-306, /* INSERT ANY PRIMARY DIMENSION */
-307 /* SELECT ANY PRIMARY DIMENSION */
)
)
)
)
THEN 1
ELSE 0
END ) have_dim_access
FROM
olap_cubes$ c ,
dependency$ d ,
obj$ do
WHERE
do.obj# = d.p_obj#
AND do.type# = 92 /* CUBE DIMENSION */
AND c.obj# = d.d_obj#
)
GROUP BY obj# ) da
WHERE
o.obj#=da.obj# (+)
)
)
)
or
(
o.type# = 94 /* measure folder */
and
exists (select null from v$enabledprivs
where priv_number in (
- 315, /* CREATE ANY MEASURE FOLDER */
- 316, /* DELETE ANY MEASURE FOLDER */
- 317, /* DROP ANY MEASURE FOLDER */
- 318 /* INSERT ANY MEASURE FOLDER */
)
)
)
or
(
o.type# = 95 /* cube build process */
and
exists (select null from v$enabledprivs
where priv_number in (
- 320, /* CREATE ANY BUILD PROCESS */
- 321, /* DROP ANY BUILD PROCESS */
- 322 /* UPDATE ANY BUILD PROCESS */
)
)
)
);
comment on table ALL_OBJECTS is 'Objects accessible to the user'; -->使用者可訪問的物件
comment on column ALL_OBJECTS.OWNER is 'Username of the owner of the object';
comment on column ALL_OBJECTS.OBJECT_NAME is 'Name of the object';
comment on column ALL_OBJECTS.SUBOBJECT_NAME is 'Name of the sub-object (for example, partititon)';
comment on column ALL_OBJECTS.OBJECT_ID is 'Object number of the object';
comment on column ALL_OBJECTS.DATA_OBJECT_ID is 'Object number of the segment which contains the object';
comment on column ALL_OBJECTS.OBJECT_TYPE is 'Type of the object';
comment on column ALL_OBJECTS.CREATED is 'Timestamp for the creation of the object';
comment on column ALL_OBJECTS.LAST_DDL_TIME is 'Timestamp for the last DDL change (including GRANT and REVOKE) to the object';
comment on column ALL_OBJECTS.TIMESTAMP is 'Timestamp for the specification of the object';
comment on column ALL_OBJECTS.STATUS is 'Status of the object';
comment on column ALL_OBJECTS.TEMPORARY is 'Can the current session only see data that it placed in this object itself?';
comment on column ALL_OBJECTS.GENERATED is 'Was the name of this object system generated?';
comment on column ALL_OBJECTS.SECONDARY is 'Is this a secondary object created as part of icreate for domain indexes?';
comment on column ALL_OBJECTS.NAMESPACE is 'Namespace for the object';
comment on column ALL_OBJECTS.EDITION_NAME is 'Name of the edition in which the object is actual';
DBA_OBJECTS 檢視建立語句：
獲取建立語句命令: SELECT VIEW_NAME,TEXT FROM DBA_VIEWS WHERE VIEW_NAME=upper('dba_objects');
建立語句：
CREATE OR REPLACE VIEW DBA_OBJECTS
CREATE OR REPLACE VIEW DBA_OBJECTS
(owner, object_name, subobject_name , object_id, data_object_id, object_type, created , last_ddl_time, timestamp, status , temporary, generated, secondary , namespace, edition_name)
AS
select u.name , o.name, o.subname, o.obj# , o.dataobj#,
decode(o.type# , 0, 'NEXT OBJECT', 1 , 'INDEX', 2, 'TABLE' , 3, 'CLUSTER',
4, 'VIEW' , 5, 'SYNONYM', 6 , 'SEQUENCE',
7, 'PROCEDURE' , 8, 'FUNCTION', 9 , 'PACKAGE',
11, 'PACKAGE BODY' , 12, 'TRIGGER',
13, 'TYPE' , 14, 'TYPE BODY',
19, 'TABLE PARTITION' , 20, 'INDEX PARTITION', 21 , 'LOB',
22, 'LIBRARY' , 23, 'DIRECTORY', 24 , 'QUEUE',
28, 'JAVA SOURCE' , 29, 'JAVA CLASS', 30 , 'JAVA RESOURCE',
32, 'INDEXTYPE' , 33, 'OPERATOR',
34, 'TABLE SUBPARTITION' , 35, 'INDEX SUBPARTITION',
40, 'LOB PARTITION' , 41, 'LOB SUBPARTITION',
42, NVL ((SELECT 'REWRITE EQUIVALENCE'
FROM sum$ s
WHERE s.obj# =o.obj#
and bitand (s.xpflags, 8388608) = 8388608),
'MATERIALIZED VIEW'),
43, 'DIMENSION' ,
44, 'CONTEXT' , 46, 'RULE SET', 47 , 'RESOURCE PLAN',
48, 'CONSUMER GROUP' ,
51, 'SUBSCRIPTION' , 52, 'LOCATION',
55, 'XML SCHEMA' , 56, 'JAVA DATA',
57, 'EDITION' , 59, 'RULE',
60, 'CAPTURE' , 61, 'APPLY',
62, 'EVALUATION CONTEXT' ,
66, 'JOB' , 67, 'PROGRAM', 68 , 'JOB CLASS', 69, 'WINDOW',
72, 'SCHEDULER GROUP' , 74, 'SCHEDULE', 79 , 'CHAIN',
81, 'FILE GROUP' , 82, 'MINING MODEL', 87 , 'ASSEMBLY',
90, 'CREDENTIAL' , 92, 'CUBE DIMENSION', 93 , 'CUBE',
94, 'MEASURE FOLDER' , 95, 'CUBE BUILD PROCESS',
100, 'FILE WATCHER' , 101, 'DESTINATION',
'UNDEFINED'),
o.ctime, o.mtime ,
to_char(o.stime , 'YYYY-MM-DD:HH24:MI:SS'),
decode(o.status , 0, 'N/A', 1 , 'VALID', 'INVALID'),
decode(bitand (o.flags, 2), 0 , 'N', 2, 'Y' , 'N'),
decode(bitand (o.flags, 4), 0 , 'N', 4, 'Y' , 'N'),
decode(bitand (o.flags, 16), 0 , 'N', 16, 'Y' , 'N'),
o.namespace,
o.defining_edition
from sys."_CURRENT_EDITION_OBJ" o, sys.user$ u
where o.owner# = u.user#
and o.linkname is null
and o.type# != 10 /* NON-EXISTENT */
and o.name != '_NEXT_OBJECT'
and o.name != '_default_auditing_options_'
and bitand(o.flags , 128) = 0
union all
select u.name , l.name, NULL, to_number (null), to_number(null ),
'DATABASE LINK',
l.ctime, to_date (null), NULL, 'VALID' ,'N', 'N', 'N' , NULL, NULL
from sys.link$ l, sys.user$ u
where l.owner# = u.user#;
comment on table DBA_OBJECTS is 'All objects in the database';
comment on column DBA_OBJECTS.OWNER is 'Username of the owner of the object';
comment on column DBA_OBJECTS.OBJECT_NAME is 'Name of the object';
comment on column DBA_OBJECTS.SUBOBJECT_NAME is 'Name of the sub-object (for example, partititon)';
comment on column DBA_OBJECTS.OBJECT_ID is 'Object number of the object';
comment on column DBA_OBJECTS.DATA_OBJECT_ID is 'Object number of the segment which contains the object';
comment on column DBA_OBJECTS.OBJECT_TYPE is 'Type of the object';
comment on column DBA_OBJECTS.CREATED is 'Timestamp for the creation of the object';
comment on column DBA_OBJECTS.LAST_DDL_TIME is 'Timestamp for the last DDL change (including GRANT and REVOKE) to the object';
comment on column DBA_OBJECTS.TIMESTAMP is 'Timestamp for the specification of the object';
comment on column DBA_OBJECTS.STATUS is 'Status of the object';
comment on column DBA_OBJECTS.TEMPORARY is 'Can the current session only see data that it place in this object itself?';
comment on column DBA_OBJECTS.GENERATED is 'Was the name of this object system generated?';
comment on column DBA_OBJECTS.SECONDARY is 'Is this a secondary object created as part of icreate for domain indexes?';
comment on column DBA_OBJECTS.NAMESPACE is 'Namespace for the object';
comment on column DBA_OBJECTS.EDITION_NAME is 'Name of the edition in which the object is actual';
privs_number 查詢表
/*V$ENABLEDPRIVS displays which privileges are enabled. These privileges can be found in the table SYSTEM_PRIVILEGE_MAP.*/select * from SYSTEM_PRIVILEGE_MAP
-3 ALTER SYSTEM
-4 AUDIT SYSTEM
-5 CREATE SESSION
-6 ALTER SESSION
-7 RESTRICTED SESSION
-10 CREATE TABLESPACE
-11 ALTER TABLESPACE
-12 MANAGE TABLESPACE
-13 DROP TABLESPACE
-15 UNLIMITED TABLESPACE
-20 CREATE USER
-21 BECOME USER
-22 ALTER USER
-23 DROP USER
-30 CREATE ROLLBACK SEGMENT
-31 ALTER ROLLBACK SEGMENT
-32 DROP ROLLBACK SEGMENT
-40 CREATE TABLE
-41 CREATE ANY TABLE
-42 ALTER ANY TABLE
-43 BACKUP ANY TABLE
-44 DROP ANY TABLE
-45 LOCK ANY TABLE
-46 COMMENT ANY TABLE
-47 SELECT ANY TABLE
-48 INSERT ANY TABLE
-49 UPDATE ANY TABLE
-50 DELETE ANY TABLE
-60 CREATE CLUSTER
-61 CREATE ANY CLUSTER
-62 ALTER ANY CLUSTER
-63 DROP ANY CLUSTER
-71 CREATE ANY INDEX
-72 ALTER ANY INDEX
-73 DROP ANY INDEX
-80 CREATE SYNONYM
-81 CREATE ANY SYNONYM
-82 DROP ANY SYNONYM
-83 SYSDBA
-84 SYSOPER
-85 CREATE PUBLIC SYNONYM
-86 DROP PUBLIC SYNONYM
-90 CREATE VIEW
-91 CREATE ANY VIEW
-92 DROP ANY VIEW
-105 CREATE SEQUENCE
-106 CREATE ANY SEQUENCE
-107 ALTER ANY SEQUENCE
-108 DROP ANY SEQUENCE
-109 SELECT ANY SEQUENCE
-115 CREATE DATABASE LINK
-120 CREATE PUBLIC DATABASE LINK
-121 DROP PUBLIC DATABASE LINK
-125 CREATE ROLE
-126 DROP ANY ROLE
-127 GRANT ANY ROLE
-128 ALTER ANY ROLE
-130 AUDIT ANY
-135 ALTER DATABASE
-138 FORCE TRANSACTION
-139 FORCE ANY TRANSACTION
-140 CREATE PROCEDURE
-141 CREATE ANY PROCEDURE
-142 ALTER ANY PROCEDURE
-143 DROP ANY PROCEDURE
-144 EXECUTE ANY PROCEDURE
-151 CREATE TRIGGER
-152 CREATE ANY TRIGGER
-153 ALTER ANY TRIGGER
-154 DROP ANY TRIGGER
-160 CREATE PROFILE
-161 ALTER PROFILE
-162 DROP PROFILE
-163 ALTER RESOURCE COST
-165 ANALYZE ANY
-167 GRANT ANY PRIVILEGE
-172 CREATE MATERIALIZED VIEW
-173 CREATE ANY MATERIALIZED VIEW
-174 ALTER ANY MATERIALIZED VIEW
-175 DROP ANY MATERIALIZED VIEW
-177 CREATE ANY DIRECTORY
-178 DROP ANY DIRECTORY
-180 CREATE TYPE
-181 CREATE ANY TYPE
-182 ALTER ANY TYPE
-183 DROP ANY TYPE
-184 EXECUTE ANY TYPE
-186 UNDER ANY TYPE
-188 CREATE LIBRARY
-189 CREATE ANY LIBRARY
-190 ALTER ANY LIBRARY
-191 DROP ANY LIBRARY
-192 EXECUTE ANY LIBRARY
-200 CREATE OPERATOR
-201 CREATE ANY OPERATOR
-202 ALTER ANY OPERATOR
-203 DROP ANY OPERATOR
-204 EXECUTE ANY OPERATOR
-205 CREATE INDEXTYPE
-206 CREATE ANY INDEXTYPE
-207 ALTER ANY INDEXTYPE
-208 DROP ANY INDEXTYPE
-209 UNDER ANY VIEW
-210 QUERY REWRITE
-211 GLOBAL QUERY REWRITE
-212 EXECUTE ANY INDEXTYPE
-213 UNDER ANY TABLE
-214 CREATE DIMENSION
-215 CREATE ANY DIMENSION
-216 ALTER ANY DIMENSION
-217 DROP ANY DIMENSION
-218 MANAGE ANY QUEUE
-219 ENQUEUE ANY QUEUE
-220 DEQUEUE ANY QUEUE
-222 CREATE ANY CONTEXT
-223 DROP ANY CONTEXT
-224 CREATE ANY OUTLINE
-225 ALTER ANY OUTLINE
-226 DROP ANY OUTLINE
-227 ADMINISTER RESOURCE MANAGER
-228 ADMINISTER DATABASE TRIGGER
-233 MERGE ANY VIEW
-234 ON COMMIT REFRESH
-235 EXEMPT ACCESS POLICY
-236 RESUMABLE
-237 SELECT ANY DICTIONARY
-238 DEBUG CONNECT SESSION
-241 DEBUG ANY PROCEDURE
-243 FLASHBACK ANY TABLE
-244 GRANT ANY OBJECT PRIVILEGE
-245 CREATE EVALUATION CONTEXT
-246 CREATE ANY EVALUATION CONTEXT
-247 ALTER ANY EVALUATION CONTEXT
-248 DROP ANY EVALUATION CONTEXT
-249 EXECUTE ANY EVALUATION CONTEXT
-250 CREATE RULE SET
-251 CREATE ANY RULE SET
-252 ALTER ANY RULE SET
-253 DROP ANY RULE SET
-254 EXECUTE ANY RULE SET
-255 EXPORT FULL DATABASE
-256 IMPORT FULL DATABASE
-257 CREATE RULE
-258 CREATE ANY RULE
-259 ALTER ANY RULE
-260 DROP ANY RULE
-261 EXECUTE ANY RULE
-262 ANALYZE ANY DICTIONARY
-263 ADVISOR
-264 CREATE JOB
-265 CREATE ANY JOB
-266 EXECUTE ANY PROGRAM
-267 EXECUTE ANY CLASS
-268 MANAGE SCHEDULER
-269 SELECT ANY TRANSACTION
-270 DROP ANY SQL PROFILE
-271 ALTER ANY SQL PROFILE
-272 ADMINISTER SQL TUNING SET
-273 ADMINISTER ANY SQL TUNING SET
-274 CREATE ANY SQL PROFILE
-275 EXEMPT IDENTITY POLICY
-276 MANAGE FILE GROUP
-277 MANAGE ANY FILE GROUP
-278 READ ANY FILE GROUP
-279 CHANGE NOTIFICATION
-280 CREATE EXTERNAL JOB
-281 CREATE ANY EDITION
-282 DROP ANY EDITION
-283 ALTER ANY EDITION
-284 CREATE ASSEMBLY
-285 CREATE ANY ASSEMBLY
-286 ALTER ANY ASSEMBLY
-287 DROP ANY ASSEMBLY
-288 EXECUTE ANY ASSEMBLY
-289 EXECUTE ASSEMBLY
-290 CREATE MINING MODEL
-291 CREATE ANY MINING MODEL
-292 DROP ANY MINING MODEL
-293 SELECT ANY MINING MODEL
-294 ALTER ANY MINING MODEL
-295 COMMENT ANY MINING MODEL
-301 CREATE CUBE DIMENSION
-302 ALTER ANY CUBE DIMENSION
-303 CREATE ANY CUBE DIMENSION
-304 DELETE ANY CUBE DIMENSION
-305 DROP ANY CUBE DIMENSION
-306 INSERT ANY CUBE DIMENSION
-307 SELECT ANY CUBE DIMENSION
-308 CREATE CUBE
-309 ALTER ANY CUBE
-310 CREATE ANY CUBE
-311 DROP ANY CUBE
-312 SELECT ANY CUBE
-313 UPDATE ANY CUBE
-314 CREATE MEASURE FOLDER
-315 CREATE ANY MEASURE FOLDER
-316 DELETE ANY MEASURE FOLDER
-317 DROP ANY MEASURE FOLDER
-318 INSERT ANY MEASURE FOLDER
-319 CREATE CUBE BUILD PROCESS
-320 CREATE ANY CUBE BUILD PROCESS
-321 DROP ANY CUBE BUILD PROCESS
-322 UPDATE ANY CUBE BUILD PROCESS
-326 UPDATE ANY CUBE DIMENSION
-327 ADMINISTER SQL MANAGEMENT OBJECT
-328 ALTER PUBLIC DATABASE LINK
-329 ALTER DATABASE LINK
-350 FLASHBACK ARCHIVE ADMINISTER
-351 EXEMPT REDACTION POLICY
-4 AUDIT SYSTEM
-5 CREATE SESSION
-6 ALTER SESSION
-7 RESTRICTED SESSION
-10 CREATE TABLESPACE
-11 ALTER TABLESPACE
-12 MANAGE TABLESPACE
-13 DROP TABLESPACE
-15 UNLIMITED TABLESPACE
-20 CREATE USER
-21 BECOME USER
-22 ALTER USER
-23 DROP USER
-30 CREATE ROLLBACK SEGMENT
-31 ALTER ROLLBACK SEGMENT
-32 DROP ROLLBACK SEGMENT
-40 CREATE TABLE
-41 CREATE ANY TABLE
-42 ALTER ANY TABLE
-43 BACKUP ANY TABLE
-44 DROP ANY TABLE
-45 LOCK ANY TABLE
-46 COMMENT ANY TABLE
-47 SELECT ANY TABLE
-48 INSERT ANY TABLE
-49 UPDATE ANY TABLE
-50 DELETE ANY TABLE
-60 CREATE CLUSTER
-61 CREATE ANY CLUSTER
-62 ALTER ANY CLUSTER
-63 DROP ANY CLUSTER
-71 CREATE ANY INDEX
-72 ALTER ANY INDEX
-73 DROP ANY INDEX
-80 CREATE SYNONYM
-81 CREATE ANY SYNONYM
-82 DROP ANY SYNONYM
-83 SYSDBA
-84 SYSOPER
-85 CREATE PUBLIC SYNONYM
-86 DROP PUBLIC SYNONYM
-90 CREATE VIEW
-91 CREATE ANY VIEW
-92 DROP ANY VIEW
-105 CREATE SEQUENCE
-106 CREATE ANY SEQUENCE
-107 ALTER ANY SEQUENCE
-108 DROP ANY SEQUENCE
-109 SELECT ANY SEQUENCE
-115 CREATE DATABASE LINK
-120 CREATE PUBLIC DATABASE LINK
-121 DROP PUBLIC DATABASE LINK
-125 CREATE ROLE
-126 DROP ANY ROLE
-127 GRANT ANY ROLE
-128 ALTER ANY ROLE
-130 AUDIT ANY
-135 ALTER DATABASE
-138 FORCE TRANSACTION
-139 FORCE ANY TRANSACTION
-140 CREATE PROCEDURE
-141 CREATE ANY PROCEDURE
-142 ALTER ANY PROCEDURE
-143 DROP ANY PROCEDURE
-144 EXECUTE ANY PROCEDURE
-151 CREATE TRIGGER
-152 CREATE ANY TRIGGER
-153 ALTER ANY TRIGGER
-154 DROP ANY TRIGGER
-160 CREATE PROFILE
-161 ALTER PROFILE
-162 DROP PROFILE
-163 ALTER RESOURCE COST
-165 ANALYZE ANY
-167 GRANT ANY PRIVILEGE
-172 CREATE MATERIALIZED VIEW
-173 CREATE ANY MATERIALIZED VIEW
-174 ALTER ANY MATERIALIZED VIEW
-175 DROP ANY MATERIALIZED VIEW
-177 CREATE ANY DIRECTORY
-178 DROP ANY DIRECTORY
-180 CREATE TYPE
-181 CREATE ANY TYPE
-182 ALTER ANY TYPE
-183 DROP ANY TYPE
-184 EXECUTE ANY TYPE
-186 UNDER ANY TYPE
-188 CREATE LIBRARY
-189 CREATE ANY LIBRARY
-190 ALTER ANY LIBRARY
-191 DROP ANY LIBRARY
-192 EXECUTE ANY LIBRARY
-200 CREATE OPERATOR
-201 CREATE ANY OPERATOR
-202 ALTER ANY OPERATOR
-203 DROP ANY OPERATOR
-204 EXECUTE ANY OPERATOR
-205 CREATE INDEXTYPE
-206 CREATE ANY INDEXTYPE
-207 ALTER ANY INDEXTYPE
-208 DROP ANY INDEXTYPE
-209 UNDER ANY VIEW
-210 QUERY REWRITE
-211 GLOBAL QUERY REWRITE
-212 EXECUTE ANY INDEXTYPE
-213 UNDER ANY TABLE
-214 CREATE DIMENSION
-215 CREATE ANY DIMENSION
-216 ALTER ANY DIMENSION
-217 DROP ANY DIMENSION
-218 MANAGE ANY QUEUE
-219 ENQUEUE ANY QUEUE
-220 DEQUEUE ANY QUEUE
-222 CREATE ANY CONTEXT
-223 DROP ANY CONTEXT
-224 CREATE ANY OUTLINE
-225 ALTER ANY OUTLINE
-226 DROP ANY OUTLINE
-227 ADMINISTER RESOURCE MANAGER
-228 ADMINISTER DATABASE TRIGGER
-233 MERGE ANY VIEW
-234 ON COMMIT REFRESH
-235 EXEMPT ACCESS POLICY
-236 RESUMABLE
-237 SELECT ANY DICTIONARY
-238 DEBUG CONNECT SESSION
-241 DEBUG ANY PROCEDURE
-243 FLASHBACK ANY TABLE
-244 GRANT ANY OBJECT PRIVILEGE
-245 CREATE EVALUATION CONTEXT
-246 CREATE ANY EVALUATION CONTEXT
-247 ALTER ANY EVALUATION CONTEXT
-248 DROP ANY EVALUATION CONTEXT
-249 EXECUTE ANY EVALUATION CONTEXT
-250 CREATE RULE SET
-251 CREATE ANY RULE SET
-252 ALTER ANY RULE SET
-253 DROP ANY RULE SET
-254 EXECUTE ANY RULE SET
-255 EXPORT FULL DATABASE
-256 IMPORT FULL DATABASE
-257 CREATE RULE
-258 CREATE ANY RULE
-259 ALTER ANY RULE
-260 DROP ANY RULE
-261 EXECUTE ANY RULE
-262 ANALYZE ANY DICTIONARY
-263 ADVISOR
-264 CREATE JOB
-265 CREATE ANY JOB
-266 EXECUTE ANY PROGRAM
-267 EXECUTE ANY CLASS
-268 MANAGE SCHEDULER
-269 SELECT ANY TRANSACTION
-270 DROP ANY SQL PROFILE
-271 ALTER ANY SQL PROFILE
-272 ADMINISTER SQL TUNING SET
-273 ADMINISTER ANY SQL TUNING SET
-274 CREATE ANY SQL PROFILE
-275 EXEMPT IDENTITY POLICY
-276 MANAGE FILE GROUP
-277 MANAGE ANY FILE GROUP
-278 READ ANY FILE GROUP
-279 CHANGE NOTIFICATION
-280 CREATE EXTERNAL JOB
-281 CREATE ANY EDITION
-282 DROP ANY EDITION
-283 ALTER ANY EDITION
-284 CREATE ASSEMBLY
-285 CREATE ANY ASSEMBLY
-286 ALTER ANY ASSEMBLY
-287 DROP ANY ASSEMBLY
-288 EXECUTE ANY ASSEMBLY
-289 EXECUTE ASSEMBLY
-290 CREATE MINING MODEL
-291 CREATE ANY MINING MODEL
-292 DROP ANY MINING MODEL
-293 SELECT ANY MINING MODEL
-294 ALTER ANY MINING MODEL
-295 COMMENT ANY MINING MODEL
-301 CREATE CUBE DIMENSION
-302 ALTER ANY CUBE DIMENSION
-303 CREATE ANY CUBE DIMENSION
-304 DELETE ANY CUBE DIMENSION
-305 DROP ANY CUBE DIMENSION
-306 INSERT ANY CUBE DIMENSION
-307 SELECT ANY CUBE DIMENSION
-308 CREATE CUBE
-309 ALTER ANY CUBE
-310 CREATE ANY CUBE
-311 DROP ANY CUBE
-312 SELECT ANY CUBE
-313 UPDATE ANY CUBE
-314 CREATE MEASURE FOLDER
-315 CREATE ANY MEASURE FOLDER
-316 DELETE ANY MEASURE FOLDER
-317 DROP ANY MEASURE FOLDER
-318 INSERT ANY MEASURE FOLDER
-319 CREATE CUBE BUILD PROCESS
-320 CREATE ANY CUBE BUILD PROCESS
-321 DROP ANY CUBE BUILD PROCESS
-322 UPDATE ANY CUBE BUILD PROCESS
-326 UPDATE ANY CUBE DIMENSION
-327 ADMINISTER SQL MANAGEMENT OBJECT
-328 ALTER PUBLIC DATABASE LINK
-329 ALTER DATABASE LINK
-350 FLASHBACK ARCHIVE ADMINISTER
-351 EXEMPT REDACTION POLICY
總結：
Oracle all_*檢視和許可權有關，完全不等於dba_*檢視，所以一定要適當的選擇 dba_*和all_*檢視。
Oracle all_*檢視和許可權有關，完全不等於dba_*檢視，所以一定要適當的選擇 dba_*和all_*檢視。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28602568/viewspace-1771547/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle DBA 和Oracle EBS DBA 的最大區別2008-09-03Oracle
- 【檢視】oracle 資料字典檢視之 DBA_OBJECTS / ALL_OBJECTS / USER_OBJECTS（OBJ）2009-03-18OracleObject
- 最大似然函式和最大後驗概率區別2020-04-06函式
- Oracle - @和@@、&與&& 的區別2018-01-15Oracle
- MySQL和Oracle的區別2018-04-13MySqlOracle
- 微信小程式和app最大區別在哪2021-05-21微信小程式APP
- Oracle User 和 Schema 的區別2017-08-04Oracle
- Oracle Purge和drop的區別2014-08-31Oracle
- oracle中in和exists的區別2008-09-03Oracle
- 微信小程式和app最大區別在哪裡2021-05-21微信小程式APP
- Oracle的redo 和undo的區別2010-09-04Oracle
- Oracle dba角色和sysdba的區別2019-11-13Oracle
- Oracle 和 mysql的9點區別2021-09-09OracleMySql
- oracle中distinct和group by的區別2018-12-30Oracle
- oracle中 DG和GG的區別2017-11-16Oracle
- Oracle中truncate和delete的區別2015-09-22Oracledelete
- （轉）ORACLE 中IN和EXISTS的區別2010-12-07Oracle
- Oracle中latch和lock的區別2016-03-28Oracle
- ORACLE_BASE和ORACLE_HOME的區別2016-04-26Oracle
- MySQL 中 VARCHAR 最大長度及 CHAR 和 VARCHAR 的區別2020-09-11MySql
- 【轉】oracle_base 和 oracle_home 的區別2013-03-17Oracle
- 無程式碼和低程式碼最大的區別是什麼？2020-04-28
- Oracle中Date和Timestamp的區別2018-11-13Oracle
- oracle中rownum和rowid的區別2016-07-28Oracle
- Oracle rman中recover和restore的區別：2015-02-28OracleREST
- Oracle 和 SQL Server 個別小區別2015-01-16OracleSQLServer
- Oracle中User和Schema的區別和聯絡2017-09-18Oracle
- oracle知識整理(1) union和union all的區別,left join和right join的區別(各種join的區別)2020-04-07Oracle
- NULL在oracle和mysql索引上的區別2021-12-25NullOracleMySql索引
- oracle之group by，having和where的區別2016-11-18Oracle
- Oracle中sys和system的區別小結2016-12-14Oracle
- 在Oracle中session和process的區別（轉）2017-10-26OracleSession
- Oracle中truncate和delete的區別(例項）2014-06-24Oracledelete
- oracle例項和資料庫的區別2011-08-31Oracle資料庫
- Oracle DV和OLS以及VPD的區別（轉）2011-10-20Oracle
- ../和./和/的區別2019-03-04
- oracle taf type型別為session和select的區別2010-01-29Oracle型別Session
- oracle10.2.0.1.0中與all_objects有關的bug2009-05-18OracleObject