(读书笔记 – Student Guide)12c:了解CDB的结构
在新版(12c)的多租户容器数据库中,我们的数据库实例的后台进程是以如下的方式呈现的:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 |
[root@rhel7 ~]# ps -ef | grep ora_ oracle 3937 1 0 Apr18 ? 00:00:05 ora_pmon_callah oracle 3939 1 0 Apr18 ? 00:00:15 ora_psp0_callah oracle 3941 1 1 Apr18 ? 00:23:59 ora_vktm_callah oracle 3945 1 0 Apr18 ? 00:00:04 ora_gen0_callah oracle 3949 1 0 Apr18 ? 00:00:03 ora_mman_callah oracle 3951 1 0 Apr18 ? 00:00:05 ora_diag_callah oracle 3953 1 0 Apr18 ? 00:00:23 ora_dbrm_callah oracle 3955 1 0 Apr18 ? 00:00:00 ora_vkrm_callah oracle 3957 1 0 Apr18 ? 00:01:01 ora_dia0_callah oracle 3959 1 0 Apr18 ? 00:01:33 ora_dbw0_callah oracle 3961 1 0 Apr18 ? 00:00:56 ora_lgwr_callah oracle 3963 1 0 Apr18 ? 00:00:29 ora_ckpt_callah oracle 3965 1 0 Apr18 ? 00:00:29 ora_smon_callah oracle 3967 1 0 Apr18 ? 00:00:01 ora_reco_callah oracle 3969 1 0 Apr18 ? 00:00:02 ora_lreg_callah oracle 3971 1 0 Apr18 ? 00:00:02 ora_pxmn_callah oracle 3973 1 0 Apr18 ? 00:00:16 ora_mmon_callah oracle 3975 1 0 Apr18 ? 00:00:59 ora_mmnl_callah oracle 3994 1 0 Apr18 ? 00:00:01 ora_tmon_callah oracle 3996 1 0 Apr18 ? 00:00:03 ora_tt00_callah oracle 4014 1 0 Apr18 ? 00:00:06 ora_smco_callah oracle 4020 1 0 Apr18 ? 00:00:01 ora_aqpc_callah oracle 4022 1 0 Apr18 ? 00:00:01 ora_qm02_callah oracle 12537 1 0 Apr18 ? 00:00:00 ora_q001_callah oracle 12869 1 0 Apr18 ? 00:00:01 ora_w005_callah oracle 12910 1 0 Apr18 ? 00:00:01 ora_w006_callah oracle 13384 1 0 Apr18 ? 00:00:01 ora_p000_callah oracle 13386 1 0 Apr18 ? 00:00:01 ora_p001_callah oracle 13388 1 0 Apr18 ? 00:00:01 ora_p002_callah oracle 13390 1 0 Apr18 ? 00:00:01 ora_p003_callah oracle 14384 1 0 Apr18 ? 00:00:00 ora_w007_callah oracle 14399 1 0 Apr18 ? 00:00:00 ora_w000_callah oracle 14401 1 0 Apr18 ? 00:00:00 ora_w003_callah oracle 14403 1 0 Apr18 ? 00:00:00 ora_w002_callah oracle 14405 1 0 Apr18 ? 00:00:00 ora_w004_callah oracle 28782 1 0 10:23 ? 00:00:00 ora_q003_callah root 32499 28818 0 14:46 pts/1 00:00:00 grep --color=auto ora_ [root@rhel7 ~]# [root@rhel7 ~]# pstree -p | grep --color ora_ |-ora_aqpc_callah(4020) |-ora_ckpt_callah(3963) |-ora_dbrm_callah(3953) |-ora_dbw0_callah(3959) |-ora_dia0_callah(3957) |-ora_diag_callah(3951) |-ora_gen0_callah(3945) |-ora_lgwr_callah(3961) |-ora_lreg_callah(3969) |-ora_mman_callah(3949) |-ora_mmnl_callah(3975) |-ora_mmon_callah(3973) |-ora_p000_callah(13384) |-ora_p001_callah(13386) |-ora_p002_callah(13388) |-ora_p003_callah(13390) |-ora_pmon_callah(3937) |-ora_psp0_callah(3939) |-ora_pxmn_callah(3971) |-ora_q001_callah(12537) |-ora_q003_callah(28782) |-ora_qm02_callah(4022) |-ora_reco_callah(3967) |-ora_smco_callah(4014) |-ora_smon_callah(3965) |-ora_tmon_callah(3994) |-ora_tt00_callah(3996) |-ora_vkrm_callah(3955) |-ora_vktm_callah(3941) |-ora_w000_callah(14399) |-ora_w002_callah(14403) |-ora_w003_callah(14401) |-ora_w004_callah(14405) |-ora_w005_callah(12869) |-ora_w006_callah(12910) |-ora_w007_callah(14384) [root@rhel7 ~]# [root@rhel7 ~]# ps -ef | grep ora_ | wc -l 37 [root@rhel7 ~]# |
通过SQL*Plus连接到多租户数据库:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
[root@rhel7 ~]# su - oracle Last login: Tue Apr 19 14:59:15 CST 2016 on pts/1 [oracle@rhel7 ~]$ [oracle@rhel7 ~]$ env | grep SID ORACLE_SID=callah [oracle@rhel7 ~]$ [oracle@rhel7 ~]$ env | grep ORACLE_BASE ORACLE_BASE=/u01/app/oracle [oracle@rhel7 ~]$ [oracle@rhel7 ~]$ env | grep ORACLE_HOME ORACLE_HOME=/u01/app/oracle/product/12/db_1 [oracle@rhel7 ~]$ [oracle@rhel7 ~]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Tue Apr 19 15:00:52 2016 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options (检查是否是多租户容器数据库 - “cdb列”) SQL> select name,dbid,cdb,con_id from v$database; NAME DBID CDB CON_ID --------- ---------- --- ---------- CALLAH 653000119 YES 0 SQL> SQL> col instance_name for a18 SQL> col status for a12 SQL> SQL> set linesize 400 SQL> (检查实例名称 - “instance_name列”) SQL> select instance_name,status,con_id from v$instance; INSTANCE_NAME STATUS CON_ID ------------------ ------------ ---------- callah OPEN 0 SQL> |
了解当前环境的服务(监听器的对外服务)状态:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
[oracle@rhel7 ~]$ lsnrctl status LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 19-APR-2016 15:04:30 Copyright (c) 1991, 2014, Oracle. All rights reserved. Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production Start Date 17-APR-2016 14:12:29 Uptime 2 days 0 hr. 52 min. 1 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Log File /u01/app/oracle/diag/tnslsnr/rhel7/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rhel7)(PORT=1521))) Services Summary... Service "callah" has 1 instance(s). Instance "callah", status READY, has 1 handler(s) for this service... The command completed successfully [oracle@rhel7 ~]$ [oracle@rhel7 ~]$ [oracle@rhel7 ~]$ lsnrctl services LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 19-APR-2016 15:04:52 Copyright (c) 1991, 2014, Oracle. All rights reserved. Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) Services Summary... Service "callah" has 1 instance(s). Instance "callah", status READY, has 1 handler(s) for this service... Handler(s): "DEDICATED" established:5 refused:0 state:ready LOCAL SERVER The command completed successfully [oracle@rhel7 ~]$ [oracle@rhel7 ~]$ |
列出自动为每个容器创建的服务:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
[oracle@rhel7 ~]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Tue Apr 19 15:06:20 2016 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> SQL> show parameter db_name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_name string callah SQL> SQL> col name for a18 SQL> SQL> select con_id,name from v$services; CON_ID NAME ---------- ------------------ 1 callah 1 SYS$BACKGROUND 1 SYS$USERS SQL> |
注意,这里是没有针对“PDB$SEED”的服务的,因为SEED只是作为模板存在,用户不应该连接到该PDB,也不需要对该PDB执行任何操作。
显示可插入的数据库,即:PDB。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
SQL> select con_id,name,open_mode from v$pdbs; CON_ID NAME OPEN_MODE ---------- ------------------ ---------- 2 PDB$SEED READ ONLY SQL> SQL> set linesize 400 SQL> SQL> col pdb_name for a18 SQL> SQL> select pdb_id,pdb_name,dbid,guid,con_id from cdb_pdbs; PDB_ID PDB_NAME DBID GUID CON_ID ---------- ------------------ ---------- -------------------------------- ---------- 2 PDB$SEED 3126348575 30A5037378070F88E0539EE8A8C0EE77 2 SQL> |
注意,“PDB$SEED”的CON_ID,始终是:2。
了解当前的会话连接情况:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
SQL> show con_name CON_NAME ------------------------------ CDB$ROOT SQL> SQL> show con_id CON_ID ------------------------------ 1 SQL> SQL> select sys_context('userenv','CON_NAME') from dual; SYS_CONTEXT('USERENV','CON_NAME') -------------------------------------------------------------------------------- CDB$ROOT SQL> select sys_context('userenv','CON_ID') from dual; SYS_CONTEXT('USERENV','CON_ID') -------------------------------------------------------------------------------- 1 SQL> |
文件相关 – CDB的重做日志文件:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
SQL> col member for a50 SQL> SQL> select con_id,group#,member from v$logfile order by con_id,group#; CON_ID GROUP# MEMBER ---------- ---------- -------------------------------------------------- 0 1 /u01/app/oracle/oradata/callah/redo01a.log 0 1 /u01/app/oracle/oradata/callah/redo01b.log 0 2 /u01/app/oracle/oradata/callah/redo02a.log 0 2 /u01/app/oracle/oradata/callah/redo02b.log 0 3 /u01/app/oracle/oradata/callah/redo03a.log 0 3 /u01/app/oracle/oradata/callah/redo03b.log 6 rows selected. SQL> |
文件相关 – CDB的控制文件:
1 2 3 4 5 6 7 8 9 10 |
SQL> col name for a70 SQL> SQL> select con_id,name from v$controlfile order by con_id; CON_ID NAME ---------- ---------------------------------------------------------------------- 0 /u01/app/oracle/recovery_dest/CALLAH/controlfile/o1_mf_ck5s1r0c_.ctl 0 /u01/app/oracle/oradata/CALLAH/controlfile/o1_mf_ck5s1qh5_.ctl SQL> |
文件相关 – 所有的数据文件,包括PDB的数据文件:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
SQL> set linesize 900 SQL> set pagesize 100 SQL> SQL> col file_name for a70 SQL> col tablespace_name for a16 SQL> col file_id for 9999 SQL> col con_id for 999 SQL> SQL> select con_id,file_id,file_name,tablespace_name from cdb_data_files order by con_id,tablespace_name,file_id; CON_ID FILE_ID FILE_NAME TABLESPACE_NAME ------ ------- ---------------------------------------------------------------------- ---------------- 1 3 /u01/app/oracle/oradata/callah/sysaux01.dbf SYSAUX 1 1 /u01/app/oracle/oradata/callah/system01.dbf SYSTEM 1 5 /u01/app/oracle/oradata/callah/undotbs01a.dbf UNDOTBS01 1 6 /u01/app/oracle/oradata/callah/users01.dbf USERS SQL> SQL> col name for a12 SQL> select ts.con_id,file#,ts.name,ts.ts# from v$datafile d,v$tablespace ts where d.ts#=ts.ts# and d.con_id=ts.con_id order by ts.con_id,ts.ts#,file#; CON_ID FILE# NAME TS# ------ ---------- ------------ ---------- 1 1 SYSTEM 0 1 3 SYSAUX 1 1 5 UNDOTBS01 2 1 6 USERS 4 2 2 SYSTEM 0 2 4 SYSAUX 1 2 7 USERS 3 7 rows selected. SQL> |
通过上面的命令反馈,可以看到,所有的数据文件,都放在“/u01/app/oracle/oradata/callah”,这个路径下面。
在文件系统中查看数据文件:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
SQL> !ls -ltr /u01/app/oracle/oradata/callah total 3616180 -rw-r----- 1 oracle oinstall 209723392 Apr 17 09:18 pdb_seed_datatemptbs01a.dbf -rw-r----- 1 oracle oinstall 524296192 Apr 17 09:19 pdb_seed_datausers01.dbf -rw-r----- 1 oracle oinstall 220209152 Apr 17 09:19 pdb_seed_datasystem01.dbf -rw-r----- 1 oracle oinstall 173023232 Apr 17 09:19 pdb_seed_datasysaux01.dbf -rw-r----- 1 oracle oinstall 104858112 Apr 17 20:59 redo01b.log -rw-r----- 1 oracle oinstall 104858112 Apr 17 20:59 redo01a.log -rw-r----- 1 oracle oinstall 104858112 Apr 17 21:00 redo02a.log -rw-r----- 1 oracle oinstall 104858112 Apr 17 21:00 redo02b.log -rw-r----- 1 oracle oinstall 524296192 Apr 17 21:05 users01.dbf -rw-r----- 1 oracle oinstall 576724992 Apr 19 15:15 sysaux01.dbf -rw-r----- 1 oracle oinstall 314580992 Apr 19 15:20 undotbs01a.dbf -rw-r----- 1 oracle oinstall 734011392 Apr 19 15:21 system01.dbf -rw-r----- 1 oracle oinstall 209723392 Apr 19 15:22 temptbs01a.dbf -rw-r----- 1 oracle oinstall 104858112 Apr 19 15:23 redo03a.log -rw-r----- 1 oracle oinstall 104858112 Apr 19 15:23 redo03b.log SQL> SQL> !du -sh /u01/app/oracle/oradata/callah/* 166M /u01/app/oracle/oradata/callah/pdb_seed_datasysaux01.dbf 211M /u01/app/oracle/oradata/callah/pdb_seed_datasystem01.dbf 1.0M /u01/app/oracle/oradata/callah/pdb_seed_datatemptbs01a.dbf 501M /u01/app/oracle/oradata/callah/pdb_seed_datausers01.dbf 101M /u01/app/oracle/oradata/callah/redo01a.log 101M /u01/app/oracle/oradata/callah/redo01b.log 101M /u01/app/oracle/oradata/callah/redo02a.log 101M /u01/app/oracle/oradata/callah/redo02b.log 101M /u01/app/oracle/oradata/callah/redo03a.log 101M /u01/app/oracle/oradata/callah/redo03b.log 551M /u01/app/oracle/oradata/callah/sysaux01.dbf 701M /u01/app/oracle/oradata/callah/system01.dbf 5.4M /u01/app/oracle/oradata/callah/temptbs01a.dbf 301M /u01/app/oracle/oradata/callah/undotbs01a.dbf 501M /u01/app/oracle/oradata/callah/users01.dbf SQL> SQL> !du -sh /u01/app/oracle/oradata/callah 3.5G /u01/app/oracle/oradata/callah SQL> |
只是查询根容器的数据文件的相关信息:
1 2 3 4 5 6 7 8 9 10 11 12 |
SQL> col file_name for a50 SQL> SQL> select file_id,file_name,tablespace_name from dba_data_files order by tablespace_name,file_id; FILE_ID FILE_NAME TABLESPACE_NAME ------- -------------------------------------------------- ---------------- 3 /u01/app/oracle/oradata/callah/sysaux01.dbf SYSAUX 1 /u01/app/oracle/oradata/callah/system01.dbf SYSTEM 5 /u01/app/oracle/oradata/callah/undotbs01a.dbf UNDOTBS01 6 /u01/app/oracle/oradata/callah/users01.dbf USERS SQL> |
文件相关 – CDB的临时文件:
1 2 3 4 5 6 7 8 9 |
SQL> col file_name for a45 SQL> SQL> select file_id,file_name,tablespace_name from cdb_temp_files; FILE_ID FILE_NAME TABLESPACE_NAME ------- --------------------------------------------- ---------------- 1 /u01/app/oracle/oradata/callah/temptbs01a.dbf TEMPTBS01 SQL> |
用户相关:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
SQL> set linesize 400 SQL> SQL> col username for a24 SQL> SQL> set pagesize 30 SQL> SQL> select con_id,user_id,username,common,account_status from cdb_users order by con_id,account_status,user_id; CON_ID USER_ID USERNAME COM ACCOUNT_STATUS ---------- ---------- ------------------------ --- -------------------------------- 1 7 AUDSYS YES EXPIRED & LOCKED 1 13 OUTLN YES EXPIRED & LOCKED 1 21 GSMADMIN_INTERNAL YES EXPIRED & LOCKED 1 22 GSMUSER YES EXPIRED & LOCKED 1 23 DIP YES EXPIRED & LOCKED 1 36 ORACLE_OCM YES EXPIRED & LOCKED 1 48 DBSNMP YES EXPIRED & LOCKED 1 49 APPQOSSYS YES EXPIRED & LOCKED 1 50 XDB YES EXPIRED & LOCKED 1 61 GSMCATUSER YES EXPIRED & LOCKED 1 62 WMSYS YES EXPIRED & LOCKED 1 2147483617 SYSBACKUP YES EXPIRED & LOCKED 1 2147483618 SYSDG YES EXPIRED & LOCKED 1 2147483619 SYSKM YES EXPIRED & LOCKED 1 2147483638 XS$NULL YES EXPIRED & LOCKED 1 51 ANONYMOUS YES LOCKED 1 0 SYS YES OPEN 1 8 SYSTEM YES OPEN 18 rows selected. SQL> |
其中,“COMMON”为“YES”的用户,是CDB的公共用户。
其中,“COMMON”为“YES”的用户,是CDB或PDB的本地用户。
查看CDB中的角色的相关信息:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 |
SQL> desc cdb_roles; Name Null? Type ----------------------------------------- -------- ---------------------------- ROLE NOT NULL VARCHAR2(128) PASSWORD_REQUIRED VARCHAR2(8) AUTHENTICATION_TYPE VARCHAR2(11) COMMON VARCHAR2(3) ORACLE_MAINTAINED VARCHAR2(1) CON_ID NUMBER SQL> SQL> set linesize 400 SQL> SQL> set pagesize 300 SQL> SQL> col role for a30 SQL> SQL> select con_id,role,common from cdb_roles order by con_id,common,role; CON_ID ROLE COM ---------- ------------------------------ --- 1 ADM_PARALLEL_EXECUTE_TASK YES 1 AQ_ADMINISTRATOR_ROLE YES 1 AQ_USER_ROLE YES 1 AUDIT_ADMIN YES 1 AUDIT_VIEWER YES 1 AUTHENTICATEDUSER YES 1 CAPTURE_ADMIN YES 1 CDB_DBA YES 1 CONNECT YES 1 DATAPUMP_EXP_FULL_DATABASE YES 1 DATAPUMP_IMP_FULL_DATABASE YES 1 DBA YES 1 DBFS_ROLE YES 1 DELETE_CATALOG_ROLE YES 1 EM_EXPRESS_ALL YES 1 EM_EXPRESS_BASIC YES 1 EXECUTE_CATALOG_ROLE YES 1 EXP_FULL_DATABASE YES 1 GATHER_SYSTEM_STATISTICS YES 1 GDS_CATALOG_SELECT YES 1 GLOBAL_AQ_USER_ROLE YES 1 GSMADMIN_ROLE YES 1 GSMUSER_ROLE YES 1 GSM_POOLADMIN_ROLE YES 1 HS_ADMIN_EXECUTE_ROLE YES 1 HS_ADMIN_ROLE YES 1 HS_ADMIN_SELECT_ROLE YES 1 IMP_FULL_DATABASE YES 1 LOGSTDBY_ADMINISTRATOR YES 1 OEM_ADVISOR YES 1 OEM_MONITOR YES 1 OPTIMIZER_PROCESSING_RATE YES 1 PDB_DBA YES 1 PROVISIONER YES 1 RECOVERY_CATALOG_OWNER YES 1 RECOVERY_CATALOG_USER YES 1 RESOURCE YES 1 SCHEDULER_ADMIN YES 1 SELECT_CATALOG_ROLE YES 1 WM_ADMIN_ROLE YES 1 XDBADMIN YES 1 XDB_SET_INVOKER YES 1 XDB_WEBSERVICES YES 1 XDB_WEBSERVICES_OVER_HTTP YES 1 XDB_WEBSERVICES_WITH_PUBLIC YES 1 XS_CACHE_ADMIN YES 1 XS_NAMESPACE_ADMIN YES 1 XS_RESOURCE YES 1 XS_SESSION_ADMIN YES 49 rows selected. SQL> |
和上面一样,Common为YES的是公共用户,为NO的是本地用户。
注意,根容器中只有公共角色,根容器中没有本地角色,你也不能在根容器中创建本地角色:
1 2 3 4 5 |
SQL> select con_id,role,common from cdb_roles where con_id=1 and common='NO'; no rows selected SQL> |
关于权限:
验证权限在本质上,没有本地与共有的分别:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
SQL> desc sys.system_privilege_map Name Null? Type ----------------------------------------- -------- ---------------------------- PRIVILEGE NOT NULL NUMBER NAME NOT NULL VARCHAR2(40) PROPERTY NOT NULL NUMBER SQL> SQL> desc sys.table_privilege_map Name Null? Type ----------------------------------------- -------- ---------------------------- PRIVILEGE NOT NULL NUMBER NAME NOT NULL VARCHAR2(40) SQL> SQL> SQL> select count(*) from sys.system_privilege_map; COUNT(*) ---------- 237 SQL> SQL> select count(*) from sys.table_privilege_map; COUNT(*) ---------- 26 SQL> SQL> |
你可以看到,它们没有“COMMON”列来标记属性。
看看验证权限具体有哪些?
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 |
SQL> set linesize 300; SQL> set pagesize 300 SQL> SQL> select * from sys.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 -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 -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 -56 REDEFINE 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 -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 -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 -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 -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 -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 -188 CREATE LIBRARY 0 -189 CREATE ANY LIBRARY 0 -190 ALTER ANY LIBRARY 0 -191 DROP ANY LIBRARY 0 -192 EXECUTE ANY LIBRARY 0 -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 -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 -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 -233 MERGE ANY VIEW 0 -234 ON COMMIT REFRESH 0 -235 EXEMPT ACCESS POLICY 0 -236 RESUMABLE 0 -237 SELECT ANY DICTIONARY 0 -238 DEBUG CONNECT SESSION 0 -241 DEBUG ANY PROCEDURE 0 -243 FLASHBACK ANY TABLE 0 -244 GRANT ANY OBJECT PRIVILEGE 0 -245 CREATE EVALUATION CONTEXT 1 -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 -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 -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 -276 MANAGE FILE GROUP 1 -277 MANAGE ANY FILE GROUP 1 -278 READ ANY FILE GROUP 1 -279 CHANGE NOTIFICATION 0 -280 CREATE EXTERNAL JOB 0 -281 CREATE ANY EDITION 0 -282 DROP ANY EDITION 0 -283 ALTER ANY EDITION 0 -284 CREATE ASSEMBLY 0 -285 CREATE ANY ASSEMBLY 0 -286 ALTER ANY ASSEMBLY 0 -287 DROP ANY ASSEMBLY 0 -288 EXECUTE ANY ASSEMBLY 0 -289 EXECUTE ASSEMBLY 0 -290 CREATE MINING MODEL 0 -291 CREATE ANY MINING MODEL 0 -292 DROP ANY MINING MODEL 0 -293 SELECT ANY MINING MODEL 0 -294 ALTER ANY MINING MODEL 0 -295 COMMENT ANY MINING MODEL 0 -301 CREATE CUBE DIMENSION 0 -302 ALTER ANY CUBE DIMENSION 0 -303 CREATE ANY CUBE DIMENSION 0 -304 DELETE ANY CUBE DIMENSION 0 -305 DROP ANY CUBE DIMENSION 0 -306 INSERT ANY CUBE DIMENSION 0 -307 SELECT ANY CUBE DIMENSION 0 -308 CREATE CUBE 0 -309 ALTER ANY CUBE 0 -310 CREATE ANY CUBE 0 -311 DROP ANY CUBE 0 -312 SELECT ANY CUBE 0 -313 UPDATE ANY CUBE 0 -314 CREATE MEASURE FOLDER 0 -315 CREATE ANY MEASURE FOLDER 0 -316 DELETE ANY MEASURE FOLDER 0 -317 DROP ANY MEASURE FOLDER 0 -318 INSERT ANY MEASURE FOLDER 0 -319 CREATE CUBE BUILD PROCESS 0 -320 CREATE ANY CUBE BUILD PROCESS 0 -321 DROP ANY CUBE BUILD PROCESS 0 -322 UPDATE ANY CUBE BUILD PROCESS 0 -326 UPDATE ANY CUBE DIMENSION 0 -327 ADMINISTER SQL MANAGEMENT OBJECT 0 -328 ALTER PUBLIC DATABASE LINK 0 -329 ALTER DATABASE LINK 0 -334 CREATE SQL TRANSLATION PROFILE 0 -335 CREATE ANY SQL TRANSLATION PROFILE 0 -336 ALTER ANY SQL TRANSLATION PROFILE 0 -337 USE ANY SQL TRANSLATION PROFILE 0 -338 DROP ANY SQL TRANSLATION PROFILE 0 -340 SYSBACKUP 0 -341 SYSDG 0 -342 SYSKM 0 -343 ADMINISTER KEY MANAGEMENT 0 -344 KEEP DATE TIME 0 -345 KEEP SYSGUID 0 -346 EM EXPRESS CONNECT 0 -347 PURGE DBA_RECYCLEBIN 0 -350 FLASHBACK ARCHIVE ADMINISTER 0 -351 EXEMPT REDACTION POLICY 0 -352 INHERIT ANY PRIVILEGES 0 -355 TRANSLATE ANY SQL 0 -375 CREATE PLUGGABLE DATABASE 0 -377 SET CONTAINER 0 -387 CREATE CREDENTIAL 0 -388 CREATE ANY CREDENTIAL 0 -389 LOGMINING 0 -391 EXEMPT DML REDACTION POLICY 0 -392 EXEMPT DDL REDACTION POLICY 0 -393 SELECT ANY MEASURE FOLDER 0 -394 ALTER ANY MEASURE FOLDER 0 -395 SELECT ANY CUBE BUILD PROCESS 0 -396 ALTER ANY CUBE BUILD PROCESS 0 -397 READ ANY TABLE 0 237 rows selected. SQL> SQL> select * from sys.table_privilege_map; PRIVILEGE NAME ---------- ---------------------------------------- 0 ALTER 1 AUDIT 2 COMMENT 3 DELETE 4 GRANT 5 INDEX 6 INSERT 7 LOCK 8 RENAME 9 SELECT 10 UPDATE 11 REFERENCES 12 EXECUTE 16 CREATE 17 READ 18 WRITE 20 ENQUEUE 21 DEQUEUE 22 UNDER 23 ON COMMIT REFRESH 24 QUERY REWRITE 26 DEBUG 27 FLASHBACK 28 MERGE VIEW 29 USE 30 FLASHBACK ARCHIVE 26 rows selected. SQL> |
但确认权限被授予后,就有了公共或者本地的分别:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
SQL> desc cdb_sys_privs Name Null? Type ----------------------------------------- -------- ---------------------------- GRANTEE VARCHAR2(128) PRIVILEGE VARCHAR2(40) ADMIN_OPTION VARCHAR2(3) COMMON VARCHAR2(3) CON_ID NUMBER SQL> SQL> desc cdb_tab_privs Name Null? Type ----------------------------------------- -------- ---------------------------- GRANTEE VARCHAR2(128) OWNER VARCHAR2(128) TABLE_NAME VARCHAR2(128) GRANTOR VARCHAR2(128) PRIVILEGE VARCHAR2(40) GRANTABLE VARCHAR2(3) HIERARCHY VARCHAR2(3) COMMON VARCHAR2(3) TYPE VARCHAR2(24) CON_ID NUMBER SQL> SQL> select count(*) from cdb_sys_privs; COUNT(*) ---------- 727 SQL> SQL> select count(*) from cdb_tab_privs; COUNT(*) ---------- 8068 SQL> |
角色与权限一样是按照本地或者公用授予的,不过一个角色到底是本地还是公用,取决于角色的创建方式:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SQL> col grantee for a10 SQL> col granted_role for a28 SQL> SQL> set linesize 400 SQL> set pagesize 300 SQL> SQL> select con_id,grantee,granted_role,common from cdb_role_privs where grantee='SYSTEM'; CON_ID GRANTEE GRANTED_ROLE COM ---------- ---------- ---------------------------- --- 1 SYSTEM DBA YES 1 SYSTEM AQ_ADMINISTRATOR_ROLE YES SQL> |