|
1Z0-052第8章
1Z0-052共19章(上完15章),1Z0-053共21章(上完16章)和1Z0-063多租户部分共9章(上完0章)
总共上完全部49章中的31章- select * from dba_roles order by role;
- select * from role_sys_privs p where p.ROLE='ROLE2';
- select * from role_tab_privs t where t.ROLE='ROLE2';
- select * from role_role_privs r where r.ROLE='ROLE2';
- select * from dba_role_privs p where p.GRANTED_ROLE='ROLE1';
- create role role2 ;
- grant role1 to role2;
- select * from dba_sys_privs sp where sp.GRANTEE='USER1';
- select * from dba_tab_privs tp where tp.GRANTEE='USER1';
- select * from dba_col_privs cp where cp.GRANTEE='USER1';
- grant update (email) on hr.employees to user1;
- grant insert(a) on hr.t05208_a to user1;
- alter user user1 account unlock;
- select * from dba_role_privs rp where rp.GRANTEE='USER1';
- grant role2 to user1;
- revoke role1 from role2;
- grant select on hr.employees to role2;
- revoke select on hr.employees from role2;
- grant role1 to role2;
- -------------------------------------
- select * from dba_role_privs rp where rp.GRANTEE='USER1';
- alter user user1 default role connect;
- revoke role2 from user1;
- drop role role2;
- create role role2 identified by oracle_4U;
- grant role2 to user1;
- select * from dba_role_privs rp where rp.GRANTEE='USER1';
- ----------------------------------
- grant select on hr.employees to role2;
- --------------------
- select u.USERNAME,
- u.ACCOUNT_STATUS,
- u.AUTHENTICATION_TYPE,
- u.EXPIRY_DATE,
- u.LOCK_DATE
- from dba_users u
- where u.USERNAME='USER3';
- select to_date('2018-05-08:19:51:31','YYYY-MM-DD:HH24:MI:SS')- sysdate
- from dual;
复制代码 登录图形界面需要的角色是:
grant select_catalog_role to hr;
- CREATE PROFILE "PROFILE1" LIMIT CPU_PER_SESSION 500
- CPU_PER_CALL DEFAULT
- CONNECT_TIME DEFAULT
- IDLE_TIME DEFAULT
- SESSIONS_PER_USER DEFAULT
- LOGICAL_READS_PER_SESSION DEFAULT
- LOGICAL_READS_PER_CALL DEFAULT
- PRIVATE_SGA DEFAULT
- COMPOSITE_LIMIT DEFAULT
- PASSWORD_LIFE_TIME DEFAULT
- PASSWORD_GRACE_TIME DEFAULT
- PASSWORD_REUSE_MAX DEFAULT
- PASSWORD_REUSE_TIME DEFAULT
- PASSWORD_LOCK_TIME DEFAULT
- FAILED_LOGIN_ATTEMPTS DEFAULT
- PASSWORD_VERIFY_FUNCTION DEFAULT
-
复制代码- SQL> select count(*) from t_big a , departments b , departments c ;
- COUNT(*)
- ----------
- 237906963
- SQL> /
- COUNT(*)
- ----------
- 237906963
- SQL> exit
- Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
- With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
- [oracle@station26 ~]$ sqlplus /nolog
- SQL*Plus: Release 12.1.0.2.0 Production on Tue Nov 14 20:47:56 2017
- Copyright (c) 1982, 2014, Oracle. All rights reserved.
- SQL> conn hr/oracle_4U
- Connected.
- SQL> select count(*) from t_big a , departments b , departments c ;
- select count(*) from t_big a , departments b , departments c
- *
- ERROR at line 1:
- ORA-02392: exceeded session limit on CPU usage, you are being logged off
复制代码- ALTER PROFILE "PROFILE1" LIMIT CPU_PER_SESSION DEFAULT
- CONNECT_TIME 1
复制代码- SQL> conn hr/oracle_4U
- Connected.
- SQL> select salary from employees;
- select salary from employees
- *
- ERROR at line 1:
- ORA-02399: exceeded maximum connect time, you are being logged off
- SQL>
复制代码- SQL> select * from employees;
- select * from employees
- *
- ERROR at line 1:
- ORA-02396: exceeded maximum idle time, please connect again
复制代码
组成成分越大,说明复合值越小。
- ALTER PROFILE "PROFILE1" LIMIT PASSWORD_LOCK_TIME 0.000694444
- FAILED_LOGIN_ATTEMPTS 1
复制代码- ALTER PROFILE "PROFILE1" LIMIT PASSWORD_LIFE_TIME 0.000694444
- PASSWORD_GRACE_TIME 0.000694444
复制代码
- ALTER PROFILE "PROFILE1" LIMIT password_reuse_max 1
- password_reuse_time 20 ;
复制代码- select * from dba_profiles p
-
- where p.PROFILE='PROFILE1' and p.RESOURCE_TYPE='PASSWORD';
复制代码
- select * from dba_profiles p
- where p.PROFILE='PROFILE1' and p.RESOURCE_TYPE='PASSWORD';
-
- select u.USERNAME,
- u.ACCOUNT_STATUS,
- u.AUTHENTICATION_TYPE,
- u.EXPIRY_DATE,
- u.LOCK_DATE
- from dba_users u
- where u.USERNAME='HR';
- select * from dba_objects o
- where o.OBJECT_NAME like '%VERIFY_FUNCTION%'
- and o.OBJECT_TYPE='FUNCTION';
-
- select * from dba_profiles p
- where p.PROFILE='DEFAULT' and p.RESOURCE_TYPE='PASSWORD';
-
-
- alter profile profile1 limit
- password_verify_function verify_function;
-
- select * from dba_profiles p
- where p.PROFILE='PROFILE1' and p.RESOURCE_TYPE='PASSWORD';
-
- alter profile profile1 limit
- password_verify_function verify_function_11g;
-
- select * from role_sys_privs sp
- where sp.ROLE='RESOURCE';
-
- create user user4 identified by oracle_4U ;
-
- grant create session, resource to user4;
-
- grant unlimited tablespace to user4;
复制代码 11g里头 要注意的unlimited tablespace:
|
|