课程第22次（2018-07-31星期二）

botang

1. select username, password  from dba_users order by 1;
   
2. 
   
3. select  u.name,u.password
   
4. from user$ u
   
5. order by u.name;
   
6. 
   
7. --48DF04E7BAB11018
   
8. 
   
9. alter user hr identified by oracle_4U;   口令验证
   
10. 
    
11. alter user hr identified using .....(PL/SQL , JAVA); / alter user  .... identified externally ;    外部 验证
    
12. 
    
13. alter user hr identified global .....(LDAP ,WINDOWS DOMAIN);  全局验证
    

复制代码

1. create user ops$oracle identified externally;
   
2. 
   
3. create user user1 identified by  oracle_4U;
   
4. 
   
5. select  u.username,
   
6.            u.account_status,
   
7.            u.lock_date,
   
8.            u.expiry_date,
   
9.            u.authentication_type
   
10. from dba_users u
    
11. where u.username  in ('OPS$ORACLE','USER1');
    
12. 
    
13. grant create session to ops$oracle;
    
14. 
    
15. grant connect to user1;

复制代码

1. SQL> conn /
   
2. ERROR:
   
3. ORA-01045: user OPS$ORACLE lacks CREATE SESSION privilege; logon denied
   
4. 
   
5. 
   
6. Warning: You are no longer connected to ORACLE.
   
7. SQL> conn user1/oracle_4U
   
8. ERROR:
   
9. ORA-01045: user USER1 lacks CREATE SESSION privilege; logon denied
   
10. 
    
11. 
    
12. SQL> conn /
    
13. Connected.
    
14. SQL> show user
    
15. USER is "OPS$ORACLE"
    
16. SQL> conn user1/oracle_4U
    
17. Connected.
    
18. SQL> show user
    
19. USER is "USER1"
    
20. SQL> select * from session_privs;
    
21. 
    
22. PRIVILEGE
    
23. ----------------------------------------
    
24. CREATE SESSION
    
25. 
    
26. SQL> conn /
    
27. Connected.
    
28. SQL> select * from session_privs;
    
29. 
    
30. PRIVILEGE
    
31. ----------------------------------------
    
32. CREATE SESSION
    
33. 
    
34. SQL>
    

复制代码

1. select  * from role_sys_privs rsp
   
2. where rsp.role='CONNECT';

复制代码

	ROLE	PRIVILEGE	ADMIN_OPTION
1	CONNECT	CREATE SESSION	NO

1. select  * from system_privilege_map;
   
2. 
   
3. select  * from dba_sys_privs;

复制代码

1. grant select_catalog_role to hr;

复制代码

1. select  * from dba_role_privs rp
   
2. where rp.grantee='HR';

复制代码

	GRANTEE	GRANTED_ROLE	ADMIN_OPTION	DEFAULT_ROLE
1	HR	RESOURCE	NO	YES
2	HR	SELECT_CATALOG_ROLE	NO	YES

1. grant create session to user2 identified by oracle_4U;

复制代码

1. grant create table to user1;
   
2. 
   
3. select  * from dba_ts_quotas;
   
4. 
   
5. alter user user1 quota 1M on example;
   
6. 
   
7. select  * from dba_ts_quotas;
   
8. 
   
9. alter user user1 quota 0 on example;
   
10. 
    
11. select  * from dba_ts_quotas;

复制代码

1. select  u.username,
   
2.            u.account_status,
   
3.            u.lock_date,
   
4.            u.expiry_date,
   
5.            u.authentication_type
   
6. from dba_users u
   
7. where u.username  in ('OPS$ORACLE','USER1');
   
8. 
   
9. alter user user1 account  lock;
   
10. 
    
11. alter user user1 account unlock;
    
12. 
    
13. alter user user1 password expire;
    
14. 
    
15. select  u.username,
    
16.            u.account_status,
    
17.            u.lock_date,
    
18.            u.expiry_date,
    
19.            u.authentication_type
    
20. from dba_users u
    
21. where u.username  in ('OPS$ORACLE','USER1');
    

复制代码

1. select  * from dba_roles;
   
2. 
   
3. create role role1;
   
4. 
   
5. grant select  on sh.sales to role1;
   
6. 
   
7. select * from role_tab_privs  tp
   
8. where tp.role='ROLE1' ;

复制代码

	ROLE	OWNER	TABLE_NAME	COLUMN_NAME	PRIVILEGE	GRANTABLE
1	ROLE1	SH	SALES		SELECT	NO

role延迟生效：

1. SQL> set role RESOURCE,SELECT_CATALOG_ROLE,HS_ADMIN_SELECT_ROLE, ROLE1;
   
2. 
   
3. Role set.
   
4. 
   
5. SQL>  select  count(*) from sh.sales;
   
6. 
   
7.   COUNT(*)
   
8. ----------
   
9.     918843
   

复制代码

危险：也是延迟回收的。

角色是动态的：

1. revoke select  on sh.sales from role1;

复制代码

1. SQL> /
   
2. select count(*) from sh.sales
   
3.                         *
   
4. ERROR at line 1:
   
5. ORA-00942: table or view does not exist
   

复制代码

1. 
   
2. alter user hr default role none;

复制代码

role的选择性获取：

1. create role role2  identified by oracle_4U ;
   
2. 
   
3. grant role1 to role2;
   
4. 
   
5. revoke role1 from hr;
   
6. 
   
7. grant role2 to hr;

复制代码

1. SQL> set role role2 identified by oracle_4U ;
   
2. 
   
3. Role set.
   
4. 
   
5. SQL>
   

复制代码

1. select  u.username,
   
2.            u.account_status,
   
3.            u.lock_date,
   
4.            u.expiry_date,
   
5.            u.authentication_type,
   
6.            u.profile
   
7. from dba_users u
   
8. where u.username  in ('OPS$ORACLE','USER1');
   
9. 
   
10. select  * from dba_profiles   p
    
11.   where  p.profile='DEFAULT'  and
    
12.     p.resource_type='PASSWORD' ;

复制代码

	PROFILE	RESOURCE_NAME	RESOURCE_TYPE	LIMIT
1	DEFAULT	FAILED_LOGIN_ATTEMPTS	PASSWORD	10
2	DEFAULT	PASSWORD_LIFE_TIME	PASSWORD	180
3	DEFAULT	PASSWORD_REUSE_TIME	PASSWORD	UNLIMITED
4	DEFAULT	PASSWORD_REUSE_MAX	PASSWORD	UNLIMITED
5	DEFAULT	PASSWORD_VERIFY_FUNCTION	PASSWORD	NULL
6	DEFAULT	PASSWORD_LOCK_TIME	PASSWORD	1
7	DEFAULT	PASSWORD_GRACE_TIME	PASSWORD	7

1. CREATE PROFILE "PROFILE1" LIMIT
   
2.     PASSWORD_LOCK_TIME .00069444
   
3.     FAILED_LOGIN_ATTEMPTS 1;
   
4.    
   
5.     select  * from dba_profiles   p
   
6.   where  p.profile='PROFILE1'  and
   
7.     p.resource_type='PASSWORD' ;
   
8.    
   
9.     alter user user1 profile profile1;
   
10.      select  u.username,
    
11.            u.account_status,
    
12.            u.lock_date,
    
13.            u.expiry_date,
    
14.            u.authentication_type,
    
15.            u.profile
    
16. from dba_users u
    
17. where u.username ='USER1';
    

复制代码

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

复制代码

1.     select  u.username,
   
2.            u.account_status,
   
3.            u.lock_date,
   
4.            u.expiry_date,
   
5.            u.authentication_type,
   
6.            u.profile
   
7. from dba_users u
   
8. where u.username ='USER1';

复制代码

	USERNAME	ACCOUNT_STATUS	LOCK_DATE	EXPIRY_DATE	AUTHENTICATION_TYPE	PROFILE
1	USER1	EXPIRED(GRACE)		7/31/2018 9:42:37 PM	PASSWORD	PROFILE1

过了grace回到28001:

1. select  u.username,
   
2.            u.account_status,
   
3.            u.lock_date,
   
4.            u.expiry_date,
   
5.            u.authentication_type,
   
6.            u.profile
   
7. from dba_users u
   
8. where u.username ='USER1';

复制代码