Special Role&User

（一）特权用户
sysdba和sysoper均为权限，但它们比较特殊,在dba_sys_privs查不到，但在SYSTEM_PRIVILEGE_MAP有
这些权限不要授予包含non-ASCII字符的用户，否则在实例down后会无法认证连接

12c加入了以下特权用户，除了sysrac包含以下权限的用户都会使用password file认证，而不是数据库的数据字典，连接时需要使用as sysoper|sysdba|sysbackup|sysdg|syskm|sysrac
1. SYSOPER：to perform STARTUP, SHUTDOWN, ALTER DATABASE OPEN/MOUNT, ALTER DATABASE BACKUP, ARCHIVE LOG, and RECOVER operations. SYSOPER also includes the RESTRICTED SESSION privilege.
2. SYSDBA has all system privileges with ADMIN OPTION, including the SYSOPER administrative privilege, and permits CREATE DATABASE and time-based recovery.
下面是12c加入的，用于分摊SYSDBA权限：
3. SYSBACKUP to perform backup and recovery operations from either Oracle Recovery Manager (RMAN) and or through SQL*Plus. 
4. SYSDG administrative privilege to perform Data Guard operations.
5. SYSKM to manage Transparent Data Encryption (TDE) wallet operations.
6. SYSRAC Administrative Privilege for Oracle Real Application Clusters
不要直接使用sysbackup, sysdg或syskm用户，而是授权其它用户使用：
Do not use the SYSBACKUP, SYSDG, or SYSKM user account for these purposes. These accounts are locked by default and should remain locked.
The SYSRAC privilege cannot be granted to users because it is used only by the Oracle agent of Oracle Clusterware to connect to the database using operating system authentication.
The SYSBACKUP, SYSDG, SYSKM, and SYSRAC user accounts cannot be dropped.
另外DBA角色并不包含以上权限，它只包含基本的数据库管理权限
The DBA role does not include the SYSDBA, SYSOPER, SYSBACKUP, SYSDG, or SYSKM system privileges. These are special administrative privileges that allow an administrator to perform basic database administration tasks, such as creating the database and instance startup and shutdown. 

使用特权用户连接时实际的schema与user: SELECT SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA'), SYS_CONTEXT('USERENV', 'SESSION_USER') FROM DUAL;
如conn mydba连接后创建一个表，再通过conn mydba as sysdba来查看select * from tab，是没有这个表的
When you connect with an administrative privilege, you connect with a current schema that is not generally associated with your username. For SYSDBA, the current schema is SYS. For SYSOPER, the current schema is PUBLIC. For SYSBACKUP, SYSDG, and SYSRAC, the current schema is SYS for name resolution purposes. However, the current schema for SYSKM is SYSKM.
Also, when you connect with an administrative privilege, you connect with a specific session user. When you connect as SYSDBA, the session user is SYS. For SYSOPER, the session user is PUBLIC. For SYSBACKUP, SYSDG, SYSKM, and SYSRAC, the session user is SYSBACKUP, SYSDG, SYSKM, and SYSRAC, respectively.

使用：
create user hr identified by hr;
grant sysdba to hr;
select * from dba_sys_privs where grantee='HR';  --null
sqlplus hr/hr   ---没有登陆权限
sqlplus hr/hr as sysdba
select * from dba_sys_privs where grantee='HR';  --null
select user from dual;   ---SYS

（二）ALL PRIVILEGES与DBA
all privileges只分配系统权限，DBA不仅分配了系统权限，还包括数据字典及动态视图基表的权限 
授予all privileges的用户不能访问数据字典，也不能分析数据字典表；all privileges没有sysdba或sysoper权限
所以DBA role权限> all privileges
all privileges权限与其它权限一样，在收回时只能对应，不会收回其它单授予的权限或角色权限
GRANT RESOURCE TO HR;
GRANT CREATE SESSION TO HR;
REVOKE ALL PRIVILEGES FROM HR;
ORA-01952: system privileges not granted to 'HR'

测试：
create user hr identified by hr;
select * from dba_sys_privs where grantee='HR';   --NULL
select * from dba_tab_privs where grantee='HR';   --NULL
grant all privileges to hr;
select count(*) from dba_sys_privs where grantee='HR';  --200
select * from dba_tab_privs where grantee='HR';               --NULL

select * from dba_sys_privs where grantee='HR' and PRIVILEGE not in (select PRIVILEGE from dba_sys_privs where grantee='DBA');   --UNLIMITED TABLESPACE，这个权限其实是dba角色额外授予的
select * from dba_sys_privs where grantee='DBA' and PRIVILEGE not in (select PRIVILEGE from dba_sys_privs where grantee='HR');  --ANALYZE ANY DICTIONARY，SELECT ANY DICTIONARY     
select * from dba_tab_privs where grantee='HR' and PRIVILEGE not in (select PRIVILEGE from dba_tab_privs where grantee='DBA');  
select * from dba_tab_privs where grantee='DBA' and PRIVILEGE not in (select PRIVILEGE from dba_tab_privs where grantee='HR');   ---x_动态底层

sqlplus hr/hr
select count(*) from dba_objects;
ORA-00942: table or view does not exist
select * from v$parameter;
ORA-00942: table or view does not exist

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
可对对象使用all privileges, 对象权限可直接用all
可以revoke all收回所有单独授予的对象权限：

grant select on t1 to hr;
select TABLE_NAME,PRIVILEGE from user_tab_privs t where grantee='HR';
TABLE_NAME                     PRIVILEGE
------------------------------ ----------------------------------------
T1                                         SELECT
revoke all on t1 from hr;
select TABLE_NAME,PRIVILEGE from user_tab_privs t where grantee='HR';  --null
grant all on scott.* to hr;
ORA-00903: invalid table name