Bo's Oracle Station

查看: 2547|回复: 0

课程第22次(2018-07-31星期二)

[复制链接]

1005

主题

1469

帖子

1万

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
12012
发表于 2018-7-31 20:12:15 | 显示全部楼层 |阅读模式
  1. select username, password  from dba_users order by 1;

  2. select  u.name,u.password
  3. from user$ u
  4. order by u.name;

  5. --48DF04E7BAB11018

  6. alter user hr identified by oracle_4U;   口令验证

  7. alter user hr identified using .....(PL/SQL , JAVA); / alter user  .... identified externally ;    外部 验证

  8. alter user hr identified global .....(LDAP ,WINDOWS DOMAIN);  全局验证
复制代码
  1. create user ops$oracle identified externally;

  2. create user user1 identified by  oracle_4U;

  3. select  u.username,
  4.            u.account_status,
  5.            u.lock_date,
  6.            u.expiry_date,
  7.            u.authentication_type
  8. from dba_users u
  9. where u.username  in ('OPS$ORACLE','USER1');

  10. grant create session to ops$oracle;

  11. grant connect to user1;
复制代码
  1. SQL> conn /
  2. ERROR:
  3. ORA-01045: user OPS$ORACLE lacks CREATE SESSION privilege; logon denied


  4. Warning: You are no longer connected to ORACLE.
  5. SQL> conn user1/oracle_4U
  6. ERROR:
  7. ORA-01045: user USER1 lacks CREATE SESSION privilege; logon denied


  8. SQL> conn /
  9. Connected.
  10. SQL> show user
  11. USER is "OPS$ORACLE"
  12. SQL> conn user1/oracle_4U
  13. Connected.
  14. SQL> show user
  15. USER is "USER1"
  16. SQL> select * from session_privs;

  17. PRIVILEGE
  18. ----------------------------------------
  19. CREATE SESSION

  20. SQL> conn /
  21. Connected.
  22. SQL> select * from session_privs;

  23. PRIVILEGE
  24. ----------------------------------------
  25. CREATE SESSION

  26. SQL>
复制代码
  1. select  * from role_sys_privs rsp
  2. where rsp.role='CONNECT';
复制代码
   ROLEPRIVILEGEADMIN_OPTION
1CONNECTCREATE SESSIONNO

  1. select  * from system_privilege_map;

  2. select  * from dba_sys_privs;
复制代码

a.png

  1. grant select_catalog_role to hr;
复制代码
  1. select  * from dba_role_privs rp
  2. where rp.grantee='HR';
复制代码
   GRANTEEGRANTED_ROLEADMIN_OPTIONDEFAULT_ROLE
1HRRESOURCENOYES
2HRSELECT_CATALOG_ROLENOYES

b.png

  1. grant create session to user2 identified by oracle_4U;
复制代码
  1. grant create table to user1;

  2. select  * from dba_ts_quotas;

  3. alter user user1 quota 1M on example;

  4. select  * from dba_ts_quotas;

  5. alter user user1 quota 0 on example;

  6. 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. alter user user1 account  lock;

  9. alter user user1 account unlock;

  10. alter user user1 password expire;

  11. select  u.username,
  12.            u.account_status,
  13.            u.lock_date,
  14.            u.expiry_date,
  15.            u.authentication_type
  16. from dba_users u
  17. where u.username  in ('OPS$ORACLE','USER1');
复制代码
  1. select  * from dba_roles;

  2. create role role1;

  3. grant select  on sh.sales to role1;

  4. select * from role_tab_privs  tp
  5. where tp.role='ROLE1' ;
复制代码
   ROLEOWNERTABLE_NAMECOLUMN_NAMEPRIVILEGEGRANTABLE
1ROLE1SHSALES SELECTNO

role延迟生效:
Screenshot.png

  1. SQL> set role RESOURCE,SELECT_CATALOG_ROLE,HS_ADMIN_SELECT_ROLE, ROLE1;

  2. Role set.

  3. SQL>  select  count(*) from sh.sales;

  4.   COUNT(*)
  5. ----------
  6.     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. alter user hr default role none;
复制代码

role的选择性获取:
  1. create role role2  identified by oracle_4U ;

  2. grant role1 to role2;

  3. revoke role1 from hr;

  4. grant role2 to hr;
复制代码

  1. SQL> set role role2 identified by oracle_4U ;

  2. Role set.

  3. 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. select  * from dba_profiles   p
  10.   where  p.profile='DEFAULT'  and
  11.     p.resource_type='PASSWORD' ;
复制代码
   PROFILERESOURCE_NAMERESOURCE_TYPELIMIT
1DEFAULTFAILED_LOGIN_ATTEMPTSPASSWORD10
2DEFAULTPASSWORD_LIFE_TIMEPASSWORD180
3DEFAULTPASSWORD_REUSE_TIMEPASSWORDUNLIMITED
4DEFAULTPASSWORD_REUSE_MAXPASSWORDUNLIMITED
5DEFAULTPASSWORD_VERIFY_FUNCTIONPASSWORDNULL
6DEFAULTPASSWORD_LOCK_TIMEPASSWORD1
7DEFAULTPASSWORD_GRACE_TIMEPASSWORD7

  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';
复制代码

   USERNAMEACCOUNT_STATUSLOCK_DATEEXPIRY_DATEAUTHENTICATION_TYPEPROFILE
1USER1EXPIRED(GRACE) 7/31/2018 9:42:37 PMPASSWORDPROFILE1

过了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';
复制代码
Screenshot-1.png

回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ|手机版|Bo's Oracle Station   

GMT+8, 2024-3-29 00:33 , Processed in 0.035655 second(s), 27 queries .

快速回复 返回顶部 返回列表