EGE GRA HIE
---------------------------------------- --- ---
SQL> update scott.emp set sal=sal+1;
14 rows updated.
SQL> update scott.emp set comm=1;
update scott.emp set comm=1
*
ERROR at line 1:
ORA-01031: insufficient privileges
对象权限的回收，级联的权限也会收回的。例如A对象的有某个权限，A对象有创建了B，当A的某个权限被收回时，B也相应该的权限也收回去了。
角色权限
create role manager;
create role c##manager; 12c
connect resource dba select_catlog_owner recovery_catalog_owner
SQL> desc dba_roles
Name Null? Type
----------------------------------------- -------- ----------------------------
ROLE NOT NULL VARCHAR2(30)
PASSWORD_REQUIRED VARCHAR2(8)
AUTHENTICATION_TYPE VARCHAR2(11)
SQL> select role from dba_roles
2 ;
ROLE
------------------------------
CONNECT
RESOURCE
DBA
SELECT_CATALOG_ROLE
EXECUTE_CATALOG_ROLE
DELETE_CATALOG_ROLE
EXP_FULL_DATABASE
IMP_FULL_DATABASE
LOGSTDBY_ADMINISTRATOR
DBFS_ROLE
AQ_ADMINISTRATOR_ROLE
ROLE
------------------------------
AQ_USER_ROLE
DATAPUMP_EXP_FULL_DATABASE
DATAPUMP_IMP_FULL_DATABASE
ADM_PARALLEL_EXECUTE_TASK
GATHER_SYSTEM_STATISTICS
JAVA_DEPLOY
RECOVERY_CATALOG_OWNER
SCHEDULER_ADMIN
HS_ADMIN_SELECT_ROLE
HS_ADMIN_EXECUTE_ROLE
HS_ADMIN_ROLE
ROLE
------------------------------
GLOBAL_AQ_USER_ROLE
OEM_ADVISOR
OEM_MONITOR
WM_ADMIN_ROLE
JAVAUSERPRIV
JAVAIDPRIV
JAVASYSPRIV
JAVADEBUGPRIV
EJBCLIENT
JMXSERVER
JAVA_ADMIN
ROLE
------------------------------
CTXAPP
XDBADMIN
XDB_SET_INVOKER
AUTHENTICATEDUSER
XDB_WEBSERVICES
XDB_WEBSERVICES_WITH_PUBLIC
XDB_WEBSERVICES_OVER_HTTP
OLAP_DBA
ORDADMIN
OLAP_XS_ADMIN
CWM_USER
ROLE
------------------------------
OLAP_USER
SPATIAL_WFS_ADMIN
WFS_USR_ROLE
SPATIAL_CSW_ADMIN
CSW_USR_ROLE
APEX_ADMINISTRATOR_ROLE
OWB$CLIENT
OWB_DESIGNCENTER_VIEW
OWB_USER
MGMT_USER
SQL> select * from role_role_privs where role='CONNECT';
no rows selected
SQL> c/_role/_sys
1* select * from role_sys_privs where role='CONNECT'
SQL> /
ROLE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
CONNECT CREATE SESSION NO
SQL> c/_sys/_tab
1* select * from role_tab_privs where role='CONNECT'
SQL> /
no rows selected
SQL> create role r1;
Role created.
SQL> create role r2 identified by oracle;
Role created.
SQL> grant create vicw to r1;
grant create vicw to r1
*
ERROR at line 1:
ORA-00990: missing or invalid privilege
SQL> grant create view to r1;
Grant succeeded.
SQL> grant create synonym to r2;
Grant succeeded.
SQL> grant select on scott.emp to r1;
Grant succeeded.
SQL> grant select on scott.dept to r2;
Grant succeeded.
SQL> grant connect to r1,r2;
Grant succeeded.
SQL> grant r1,r2 to u1;
Grant succeeded.
SQL> conn u1/oracle
Connected.
SQL> select * from session_roles;
ROLE
------------------------------
R1
CONNECT
SQL> select * from user_role_privs;
USERNAME GRANTED_ROLE ADM DEF OS_
------------------------------ ------------------------------ --- --- ---
U1 R1 NO YES NO
U1 R2 NO NO NO
SQL> set role r2 identified by oracle;
Role set.
SQL> select * from session_roles;
ROLE
------------------------------
R2
CONNECT
SQL> set role r1,r2 identified by oracle;
Role set.
SQL> select * from session_roles;
ROLE
------------------------------
R1
CONNECT
R2
简化用户管理，方便管理
drop role r2; 收回相应的权限
2.管理模式对象
alter table 语句增加修改删除列
alter table XXX add(s type);
alter table XXX drop column s;
alter table XXX set unused (s); //打标签
alter table XXX set unused column s; //打标签
alter table XXX drop unused cilumns;
列位置修改
12c更改列的位置是很方便的
隐藏列：首先将需要增加的 |