Bo's Oracle Station

查看: 2189|回复: 0

课程第23次(2018-08-02星期四)

[复制链接]

1005

主题

1469

帖子

1万

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
12012
发表于 2018-8-2 19:31:00 | 显示全部楼层 |阅读模式
  1. PASSWORD_REUSE_TIME: Specifies that a user cannot reuse a password for a given number of days
  2. PASSWORD_REUSE_MAX: Specifies the number of password changes that are required before the current password can be reused
复制代码



以上两个在图形界面有BUG:
a.png

  1. [oracle@station90 oradata]$ sqlplus /nolog

  2. SQL*Plus: Release 11.2.0.3.0 Production on Thu Aug 2 19:24:23 2018

  3. Copyright (c) 1982, 2011, Oracle.  All rights reserved.

  4. SQL> conn / as sysdba
  5. Connected to an idle instance.
  6. SQL> startup
  7. ORACLE instance started.

  8. Total System Global Area 6664212480 bytes
  9. Fixed Size                    2240944 bytes
  10. Variable Size                 3674213968 bytes
  11. Database Buffers         2969567232 bytes
  12. Redo Buffers                   18190336 bytes
  13. Database mounted.
  14. Database opened.
  15. SQL> alter user user1 identified by oracle_4U;

  16. User altered.

  17. SQL> alter user user1 identified by oracle_4U;
  18. alter user user1 identified by oracle_4U
  19. *
  20. ERROR at line 1:
  21. ORA-28007: the password cannot be reused


  22. SQL>
复制代码

设置完以上这一对参数后,不管是SYS还是他本人,第一次都可以修改成oracle_4U一次(存入旧密码),之后都会同时遵守以上两个参数。

  1. [oracle@station90 admin]$ pwd
  2. /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/admin
  3. [oracle@station90 admin]$ ls -l utlpw*
  4. -rw-r--r-- 1 oracle oinstall 11555  8月 13 2006 utlpwdmg.sql
  5. [oracle@station90 admin]$ cp utlpwdmg.sql  /home/oracle/v1.sql
  6. [oracle@station90 admin]$ cp utlpwdmg.sql  /home/oracle/v2.sql
复制代码

b.png

  1. select s.audit_option  from dba_stmt_audit_opts s
  2. minus
  3. select  p.privilege   from dba_priv_audit_opts p;
复制代码
   AUDIT_OPTION
1DATABASE LINK
2PROFILE
3PUBLIC SYNONYM
4ROLE
5SYSTEM AUDIT
6SYSTEM GRANT

  1. select  * from dba_priv_audit_opts;

  2. select  * from dba_obj_audit_opts;

  3. select s.audit_option  from dba_stmt_audit_opts s
  4. minus
  5. select  p.privilege   from dba_priv_audit_opts p;

  6. noaudit CREATE SESSION;

  7. audit create session by hr by access whenever  successful;
复制代码
  1. select  * from dba_common_audit_trail  order by 6 desc ;

  2. select  * from dba_tables t where t.owner='SYS'  and t.table_name='AUD


  3. ;

  4. truncate table aud$;

  5. create tablespace tbsaudit datafile '/u01/app/oracle/oradata/orcl/tbsaudit.dbf' size 30M
  6.   reuse  autoextend on;
  7.   
  8.   alter table aud$ move tablespace tbsaudit;
  9.   
  10.   audit select  on hr.employees  by access    whenever successful;
  11.   
  12.   select  * from dba_obj_audit_opts;
  13.   
  14.   select  * from dba_common_audit_trail
  15.    where db_user='HR'  order by 6 desc ;


  16. select  * from v$xml_audit_trail;


复制代码
  1. select  * from dba_audit_policies;

  2. noaudit   select on hr.employees;

  3. begin
  4. dbms_fga.add_policy (
  5. object_schema  =>   'HR',
  6. object_name    =>   'EMPLOYEES',
  7. policy_name  =>   'audit_emps_salary',
  8. audit_condition=>  'department_id=20',
  9. audit_column   => 'SALARY,COMMISSION_PCT',
  10.   enable      =>   TRUE,
  11. statement_types =>        'SELECT,UPDATE',
  12. audit_column_opts => dbms_fga.ALL_COLUMNS
  13. );
  14. end;

  15.    select  * from dba_common_audit_trail
  16.    where db_user='HR'  order by 6 desc ;

复制代码
  1. begin
  2. dbms_fga.add_policy (
  3. object_schema  =>   'HR',
  4. object_name    =>   'EMPLOYEES',
  5. policy_name  =>   'audit_emps_salary2',
  6. audit_condition=>  'department_id=20',
  7. audit_column   => 'SALARY,COMMISSION_PCT',
  8.   enable      =>   TRUE,
  9. statement_types =>  'SELECT,UPDATE',
  10. audit_column_opts => dbms_fga.ALL_COLUMNS,
  11. audit_trail => dbms_fga.XML+dbms_fga.EXTENDED
  12. );
  13. end;
复制代码
  1. SQL*Plus: Release 11.2.0.3.0 Production on Thu Aug 2 21:49:14 2018

  2. Copyright (c) 1982, 2011, Oracle.  All rights reserved.

  3. SQL> conn hr/oracle_4U@orcl
  4. Connected.
  5. SQL> insert into t05211_a values ( 8) ;

  6. 1 row created.

  7. SQL> commit;

  8. Commit complete.

  9. SQL> update t05211_a set a=9 ;

  10. 1 row updated.

  11. SQL> commit;

  12. Commit complete.

  13. SQL>
复制代码




  1. create table hr.t05211_a(a  number);

  2. create table tvalue ( a varchar2(200)) tablespace tbsaudit ;

  3. create or replace trigger trgvalue
  4. after update of a on hr.t05211_a
  5. referencing new as new old as old
  6. for each row
  7. begin
  8.   if :old.a != :new.a then
  9.      insert into  tvalue
  10.       values (sys_context('userenv','os_user')||' '||
  11.                   sys_context('userenv','session_user')||' '||
  12.                    sys_context('userenv','current_user')||' '||
  13.                       to_char(sysdate,'YYYY-MM-DD:HH24:MI:SS')||' modified '||:new.a|| :old.a||
  14.                       sys_context('userenv','ip_address'));      
  15.   end if;
  16. end;


  17. select * from tvalue;
复制代码
A
oracle HR SYS 2018-08-02:21:49:45 modified 98192.168.0.90



回复

使用道具 举报

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

本版积分规则

QQ|手机版|Bo's Oracle Station   

GMT+8, 2024-3-29 13:10 , Processed in 0.036888 second(s), 27 queries .

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