课程第23次（2018-08-02星期四）

botang

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：

1. [oracle@station90 oradata]$ sqlplus /nolog
   
2. 
   
3. SQL*Plus: Release 11.2.0.3.0 Production on Thu Aug 2 19:24:23 2018
   
4. 
   
5. Copyright (c) 1982, 2011, Oracle.  All rights reserved.
   
6. 
   
7. SQL> conn / as sysdba
   
8. Connected to an idle instance.
   
9. SQL> startup
   
10. ORACLE instance started.
    
11. 
    
12. Total System Global Area 6664212480 bytes
    
13. Fixed Size                    2240944 bytes
    
14. Variable Size                 3674213968 bytes
    
15. Database Buffers         2969567232 bytes
    
16. Redo Buffers                   18190336 bytes
    
17. Database mounted.
    
18. Database opened.
    
19. SQL> alter user user1 identified by oracle_4U;
    
20. 
    
21. User altered.
    
22. 
    
23. SQL> alter user user1 identified by oracle_4U;
    
24. alter user user1 identified by oracle_4U
    
25. *
    
26. ERROR at line 1:
    
27. ORA-28007: the password cannot be reused
    
28. 
    
29. 
    
30. 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
   

复制代码

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
1	DATABASE LINK
2	PROFILE
3	PUBLIC SYNONYM
4	ROLE
5	SYSTEM AUDIT
6	SYSTEM GRANT

1. select  * from dba_priv_audit_opts;
   
2. 
   
3. select  * from dba_obj_audit_opts;
   
4. 
   
5. select s.audit_option  from dba_stmt_audit_opts s
   
6. minus
   
7. select  p.privilege   from dba_priv_audit_opts p;
   
8. 
   
9. noaudit CREATE SESSION;
   
10. 
    
11. audit create session by hr by access whenever  successful;

复制代码

1. select  * from dba_common_audit_trail  order by 6 desc ;
   
2. 
   
3. select  * from dba_tables t where t.owner='SYS'  and t.table_name='AUD
   
4. 
   
5. 
   
6. ;
   
7. 
   
8. truncate table aud$;
   
9. 
   
10. create tablespace tbsaudit datafile '/u01/app/oracle/oradata/orcl/tbsaudit.dbf' size 30M
    
11.   reuse  autoextend on;
    
12.   
    
13.   alter table aud$ move tablespace tbsaudit;
    
14.   
    
15.   audit select  on hr.employees  by access    whenever successful;
    
16.   
    
17.   select  * from dba_obj_audit_opts;
    
18.   
    
19.   select  * from dba_common_audit_trail
    
20.    where db_user='HR'  order by 6 desc ;
    
21. 
    
22. 
    
23. select  * from v$xml_audit_trail;
    
24. 
    
25. 
    

复制代码

1. select  * from dba_audit_policies;
   
2. 
   
3. noaudit   select on hr.employees;
   
4. 
   
5. begin
   
6. dbms_fga.add_policy (
   
7. object_schema  =>   'HR',
   
8. object_name    =>   'EMPLOYEES',
   
9. policy_name  =>   'audit_emps_salary',
   
10. audit_condition=>  'department_id=20',
    
11. audit_column   => 'SALARY,COMMISSION_PCT',
    
12.   enable      =>   TRUE,
    
13. statement_types =>        'SELECT,UPDATE',
    
14. audit_column_opts => dbms_fga.ALL_COLUMNS
    
15. );
    
16. end;
    
17. 
    
18.    select  * from dba_common_audit_trail
    
19.    where db_user='HR'  order by 6 desc ;
    
20. 
    

复制代码

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. 
   
3. Copyright (c) 1982, 2011, Oracle.  All rights reserved.
   
4. 
   
5. SQL> conn hr/oracle_4U@orcl
   
6. Connected.
   
7. SQL> insert into t05211_a values ( 8) ;
   
8. 
   
9. 1 row created.
   
10. 
    
11. SQL> commit;
    
12. 
    
13. Commit complete.
    
14. 
    
15. SQL> update t05211_a set a=9 ;
    
16. 
    
17. 1 row updated.
    
18. 
    
19. SQL> commit;
    
20. 
    
21. Commit complete.
    
22. 
    
23. SQL>
    

复制代码

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

复制代码

A

oracle HR SYS 2018-08-02:21:49:45 modified 98192.168.0.90