课程第53次（2017-11-14星期二）

botang

1Z0-052第8章
1Z0-052共19章（上完15章），1Z0-053共21章（上完16章）和1Z0-063多租户部分共9章（上完0章）
总共上完全部49章中的31章

1. select * from dba_roles order by role;
   
2. 
   
3. select  * from role_sys_privs p where p.ROLE='ROLE2';
   
4. 
   
5. select  * from role_tab_privs t where t.ROLE='ROLE2';
   
6. 
   
7. select * from role_role_privs r where r.ROLE='ROLE2';
   
8. 
   
9. 
   
10. select  * from dba_role_privs p where p.GRANTED_ROLE='ROLE1';
    
11. 
    
12. create role role2 ;
    
13. 
    
14. grant role1 to role2;
    
15. 
    
16. select  * from dba_sys_privs sp  where sp.GRANTEE='USER1';
    
17. 
    
18. select  * from dba_tab_privs tp where tp.GRANTEE='USER1';
    
19. 
    
20. select  * from dba_col_privs cp where cp.GRANTEE='USER1';
    
21. 
    
22. grant update (email)  on hr.employees to user1;
    
23. grant insert(a) on  hr.t05208_a to user1;
    
24. 
    
25. alter user user1 account unlock;
    
26. 
    
27. select * from dba_role_privs rp where rp.GRANTEE='USER1';
    
28. 
    
29. grant role2 to user1;
    
30. 
    
31. revoke  role1 from role2;
    
32. grant select  on hr.employees to role2;
    
33. revoke select  on hr.employees from role2;
    
34. 
    
35. grant role1 to role2;
    
36. 
    
37. -------------------------------------
    
38. select * from dba_role_privs rp where rp.GRANTEE='USER1';
    
39. 
    
40. alter user user1 default role connect;
    
41. 
    
42. revoke role2 from user1;
    
43. 
    
44. drop role role2;
    
45. 
    
46. create role role2 identified by oracle_4U;
    
47. 
    
48. grant role2 to user1;
    
49. 
    
50. select * from dba_role_privs rp where rp.GRANTEE='USER1';
    
51. ----------------------------------
    
52. grant select  on hr.employees to role2;
    
53. --------------------
    
54. 
    
55. 
    
56. 
    
57. select  u.USERNAME,
    
58.            u.ACCOUNT_STATUS,
    
59.                    u.AUTHENTICATION_TYPE,
    
60.                    u.EXPIRY_DATE,
    
61.                    u.LOCK_DATE                  
    
62.                       from dba_users u
    
63. where u.USERNAME='USER3';
    
64. 
    
65. 
    
66. select   to_date('2018-05-08:19:51:31','YYYY-MM-DD:HH24:MI:SS')-  sysdate
    
67. from dual;

复制代码

登录图形界面需要的角色是：
grant select_catalog_role to hr;

1. CREATE PROFILE "PROFILE1" LIMIT CPU_PER_SESSION 500
   
2. CPU_PER_CALL DEFAULT
   
3. CONNECT_TIME DEFAULT
   
4. IDLE_TIME DEFAULT
   
5. SESSIONS_PER_USER DEFAULT
   
6. LOGICAL_READS_PER_SESSION DEFAULT
   
7. LOGICAL_READS_PER_CALL DEFAULT
   
8. PRIVATE_SGA DEFAULT
   
9. COMPOSITE_LIMIT DEFAULT
   
10. PASSWORD_LIFE_TIME DEFAULT
    
11. PASSWORD_GRACE_TIME DEFAULT
    
12. PASSWORD_REUSE_MAX DEFAULT
    
13. PASSWORD_REUSE_TIME DEFAULT
    
14. PASSWORD_LOCK_TIME DEFAULT
    
15. FAILED_LOGIN_ATTEMPTS DEFAULT
    
16. PASSWORD_VERIFY_FUNCTION DEFAULT
    
17. 
    
18.                         
    

复制代码

1. SQL> select  count(*)  from t_big a  , departments b , departments  c  ;
   
2. 
   
3.   COUNT(*)
   
4. ----------
   
5. 237906963
   
6. 
   
7. SQL> /
   
8. 
   
9.   COUNT(*)
   
10. ----------
    
11. 237906963
    
12. 
    
13. SQL> exit
    
14. Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
    
15. With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
    
16. [oracle@station26 ~]$ sqlplus /nolog
    
17. 
    
18. SQL*Plus: Release 12.1.0.2.0 Production on Tue Nov 14 20:47:56 2017
    
19. 
    
20. Copyright (c) 1982, 2014, Oracle.  All rights reserved.
    
21. 
    
22. SQL> conn hr/oracle_4U
    
23. Connected.
    
24. SQL> select  count(*)  from t_big a  , departments b , departments  c  ;
    
25. select        count(*)  from t_big a        , departments b , departments  c
    
26. *
    
27. ERROR at line 1:
    
28. ORA-02392: exceeded session limit on CPU usage, you are being logged off
    

复制代码

1. ALTER PROFILE "PROFILE1" LIMIT CPU_PER_SESSION DEFAULT
   
2. CONNECT_TIME 1

复制代码

1. SQL> conn hr/oracle_4U
   
2. Connected.
   
3. SQL> select  salary from employees;
   
4. select        salary from employees
   
5.                     *
   
6. ERROR at line 1:
   
7. ORA-02399: exceeded maximum connect time, you are being logged off
   
8. 
   
9. 
   
10. SQL>
    

复制代码

1. SQL> select  * from employees;
   
2. select        * from employees
   
3. *
   
4. ERROR at line 1:
   
5. ORA-02396: exceeded maximum idle time, please connect again
   

复制代码

组成成分越大，说明复合值越小。

1. ALTER PROFILE "PROFILE1" LIMIT PASSWORD_LOCK_TIME 0.000694444
   
2. FAILED_LOGIN_ATTEMPTS 1

复制代码

1. ALTER PROFILE "PROFILE1" LIMIT PASSWORD_LIFE_TIME 0.000694444
   
2. PASSWORD_GRACE_TIME 0.000694444

复制代码

1. ALTER PROFILE "PROFILE1" LIMIT password_reuse_max 1
   
2. password_reuse_time 20 ;

复制代码

1. select  * from dba_profiles p
   
2. 
   
3. where p.PROFILE='PROFILE1' and p.RESOURCE_TYPE='PASSWORD';

复制代码

1.     select  * from dba_profiles p
   
2.     where p.PROFILE='PROFILE1' and p.RESOURCE_TYPE='PASSWORD';
   
3.         
   
4.         select  u.USERNAME,
   
5.            u.ACCOUNT_STATUS,
   
6.                    u.AUTHENTICATION_TYPE,
   
7.                    u.EXPIRY_DATE,
   
8.                    u.LOCK_DATE                  
   
9.                       from dba_users u
   
10. where u.USERNAME='HR';
    
11. 
    
12. select  * from dba_objects o
    
13. where o.OBJECT_NAME like '%VERIFY_FUNCTION%'
    
14.   and o.OBJECT_TYPE='FUNCTION';
    
15.   
    
16.       select  * from dba_profiles p
    
17.     where p.PROFILE='DEFAULT' and p.RESOURCE_TYPE='PASSWORD';
    
18.         
    
19.         
    
20.         alter profile profile1 limit
    
21.         password_verify_function  verify_function;
    
22.   
    
23.       select  * from dba_profiles p
    
24.     where p.PROFILE='PROFILE1' and p.RESOURCE_TYPE='PASSWORD';
    
25.         
    
26.         alter profile profile1 limit
    
27.         password_verify_function  verify_function_11g;
    
28.         
    
29.         select  * from role_sys_privs sp
    
30.          where sp.ROLE='RESOURCE';
    
31.          
    
32.          create user user4 identified by oracle_4U ;
    
33.          
    
34.          grant create session, resource  to user4;
    
35.          
    
36.          grant unlimited tablespace to user4;

复制代码

11g里头 要注意的unlimited tablespace：