课程第44/45次

secoug

1. select  * from dba_tablespaces;
   
2. 
   
3. select  * from dba_extents e where e.owner='HR' and e.segment_name='T04209_UNAME';
   
4. 
   
5. select  * from dba_segments s where s.owner='HR' and s.segment_name='T04209_UNAME';
   
6. 
   
7. create tablespace tbs10g datafile size 10M
   
8.   segment space management auto;
   
9. 
   
10. 
    
11. create tablespace tbs11g datafile size 10M uniform size 512K;
    
12. 
    
13. select  t.pct_free, t.pct_used, t.ini_trans
    
14.   from dba_tables t where t.owner='HR' and t.table_name='EMPLOYEES';
    
15.   
    
16.   
    
17. alter table hr.employees initrans  10;
    
18. 
    
19. alter table hr.employees  pctfree 20;

复制代码

查表空间用量：

1. select tablespace_name,  sum(bytes)/1024/1024  from dba_data_files
   
2. group by tablespace_name;
   
3. 
   
4. 
   
5. 
   
6. select  tablespace_name, sum(bytes)/1024/1024 from dba_free_space
   
7.   group by tablespace_name;
   
8.   
   
9. 
   
10. select sum(bytes)/1024/1024  from dba_undo_extents
    
11. where status <> 'EXPIRED';
    
12. 
    
13. 
    
14. select  sum(tfs.free_space)/1024/1024  from dba_temp_free_space   tfs;

复制代码

1. select  * from dba_users u
   
2. where u.username='HR';
   
3. 
   
4. alter user hr account lock;
   
5. 
   
6. alter user hr account unlock;
   
7. 
   
8. alter user hr password expire;
   
9. 
   
10. select  * from system_privilege_map;
    
11. 
    
12. select  * from  table_privilege_map;

复制代码

1. select  * from dba_sys_privs;
   
2. 
   
3. select  * from dba_tab_privs;
   
4. 
   
5. select  * from dba_col_privs;
   
6. 
   
7. select * from dba_role_privs;
   
8. 
   
9. select  * from role_sys_privs;
   
10. 
    
11. select  * from role_tab_privs;
    
12. 
    
13. select  * from role_role_privs;

复制代码

1. 
   
2. User altered.
   
3. 
   
4. SQL> alter role role1 identified by oracle_4U;
   
5. 
   
6. Role altered.
   
7. 
   
8. SQL>  alter user hr default role none;
   
9. 
   
10. User altered.
    
11. 
    
12. SQL>
    

复制代码

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

复制代码

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

复制代码

1. ALTER PROFILE "PROFILE1" LIMIT PASSWORD_REUSE_MAX 1
   
2. PASSWORD_REUSE_TIME .0069444

复制代码

上完了1Z0-052：0、1、2、3、4、5、6、7、8、9、10、11、12、13、14、15、16、17、18；上完了1Z0-053：0、1、2、3、4、5、6、7、8、9、10、11、12、13、14、15、16、17、18、19、20

1. select  * from dba_obj_audit_opts;
   
2. 
   
3. select  * from dba_priv_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. select  count(*) from  dba_audit_trail;
   
10. 
    
11. select count(*) from aud$;
    
12. 
    
13. select bytes, tablespace_name  from dba_segments where segment_name='AUD
    
14. [/color]
    
15. 
    
16. ;
    
17. 
    
18. create tablespace tbsaudit datafile size 50M ;
    
19. 
    
20. 
    
21. alter table aud$ move tablespace tbsaudit;
    
22. 
    
23. 
    
24. select  * from  dba_audit_trail
    
25.   where action_name not in ('LOGOFF','LOGON') ;
    
26.   
    
27. noaudit create session ;
    
28. 
    
29. audit create session by hr whenever successful;
    
30. 
    
31. audit create session by sh  whenever not successful;
    
32. 
    
33. audit select  on  hr.employees   by session   whenever successful   ;
    
34. 
    
35. 
    
36. audit select any table by oe by session   whenever successful   ;

复制代码

1. select  * from  dba_audit_trail
   
2.   where username='OE'
   
3.   order by 5 desc ;

复制代码

1. SQL>  alter system set audit_trail=db,extended  scope=spfile;
   
2. 
   
3. System altered.
   
4. 
   

复制代码

-----------------------------

1. select  * from  dba_audit_trail
   
2.   where username='OE'
   
3.   order by 5 desc ;
   
4.   
   
5.   
   
6.   select  * from dba_obj_audit_opts;
   
7. 
   
8. select  * from dba_priv_audit_opts;
   
9. 
   
10. select  s.user_name, s.audit_option  from dba_stmt_audit_opts  s
    
11. minus
    
12. select p.user_name ,  p.privilege  from dba_priv_audit_opts  p;
    
13. 
    
14. 
    
15. 
    
16. select  * from dba_stmt_audit_opts  s;
    
17. 
    
18. select  * from v$xml_audit_trail order by 6 desc ;
    
19. 
    
20. select  * from dba_common_audit_trail  order by  6 desc;
    
21. 
    
22. 
    
23. audit table by hr ;

复制代码

--------------------------------------

1. select  * from dba_audit_policies;
   
2. 
   
3. begin
   
4.   dbms_fga.add_policy(object_schema => 'HR',
   
5.   object_name => 'EMPLOYEES',
   
6.   policy_name => 'MYPOLICY1',
   
7.   audit_condition => 'department_id=20',
   
8.   audit_column => 'salary,commission_pct',
   
9.   handler_schema => 'SYS',
   
10.   handler_module => 'PROC_FGA',
    
11.   statement_types => 'SELECT,UPDATE',
    
12.   audit_trail => dbms_fga.XML+dbms_fga.EXTENDED,
    
13.   audit_column_opts => dbms_fga.ANY_COLUMNS);
    
14. end;
    
15. 
    
16. 
    
17. select  * from v$xml_audit_trail order by 6 desc ;
    

复制代码

	AUDIT_TYPE	SESSION_ID	PROXY_SESSIONID	STATEMENTID	ENTRYID	EXTENDED_TIMESTAMP	GLOBAL_UID	DB_USER	CLIENTIDENTIFIER	EXT_NAME	OS_USER	OS_HOST	OS_PROCESS	TERMINAL	INSTANCE_NUMBER	OBJECT_SCHEMA	OBJECT_NAME	POLICY_NAME	NEW_OWNER	NEW_NAME	ACTION	STATEMENT_TYPE	TRANSACTIONID	RETURNCODE	SCN	COMMENT_TEXT	AUTH_PRIVILEGES	GRANTEE	PRIV_USED	SES_ACTIONS	OS_PRIVILEGE	ECONTEXT_ID	SQL_BIND	SQL_TEXT	OBJ_EDITION_NAME	DBID
1	2	1180527	0	10	3	27-8月 -19 09.10.30.978840 上午 +08:00		HR		oracle	oracle	station76.example.com	17347		0	HR	EMPLOYEES	MYPOLICY1			0	1		0	6444734				0					select salary from employees where department_id=20		1541741703
2	1	1180527	0	10	2	27-8月 -19 09.10.30.977847 上午 +08:00		HR			oracle	station76.example.com	17347	pts/2	0	HR	EMPLOYEES				103	0		0	6444734				0	---------S------				select salary from employees where department_id=20		1541741703
3	1	1180527	0	1	1	27-8月 -19 09.10.14.459562 上午 +08:00		HR			oracle	station76.example.com	17347	pts/2	0						100	0	0000000000000000	0	0	Authenticated by: DATABASE			5							1541741703
4	8	0	0	0	1	27-8月 -19 09.09.46.370170 上午 +08:00		SYS			Apple	WORKGROUP\DESKTOP-69JBF6I	17304	DESKTOP-69JBF6I	0						0	0		0	0				0		SYSDBA			CONNECT		1541741703
5	8	0	0	0	1	27-8月 -19 09.09.20.904543 上午 +08:00		SYS			Apple	WORKGROUP\DESKTOP-69JBF6I	17283	DESKTOP-69JBF6I	0						0	0		0	0				0		SYSDBA			CONNECT		1541741703
6	8	0	0	0	1	27-8月 -19 09.09.20.592377 上午 +08:00		SYS			Apple	WORKGROUP\DESKTOP-69JBF6I	17281	DESKTOP-69JBF6I	0						0	0		0	0				0		SYSDBA			CONNECT		1541741703
7	8	0	0	0	1	27-8月 -19 09.09.11.620919 上午 +08:00		SYS			Apple	WORKGROUP\DESKTOP-69JBF6I	17272	DESKTOP-69JBF6I	0						0	0		1017	0				0		NONE			CONNECT		1541741703
8	8	0	0	0	1	27-8月 -19 09.08.28.825830 上午 +08:00		/			oracle	station76.example.com	17194	pts/2	0						0	0		0	0				0		SYSDBA			CONNECT		1541741703
9	8	0	0	0	1	27-8月 -19 09.08.21.676245 上午 +08:00		/			oracle	station76.example.com	17170	pts/2	0						0	0		0	0				0		SYSDBA			CONNECT		0

SQL> conn hr/oracle_4U
Connected.
SQL> select salary from employees where department_id=20 ;
select salary from employees where department_id=20
                   *
ERROR at line 1:
ORA-28144: Failed to execute fine-grained audit handler
ORA-06550: line 1, column 7:
PLS-00201: identifier 'SYS.PROC_FGA' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
------------------------------------------------------------

1. select  * from dba_obj_audit_opts;
   
2. 
   
3. noaudit select  on hr.employees;
   
4. 
   
5. select  * from dba_audit_policies;
   
6. 
   
7. begin
   
8.   dbms_fga.add_policy(object_schema => 'HR',
   
9.   object_name => 'EMPLOYEES',
   
10.   policy_name => 'MYPOLICY1',
    
11.   audit_condition => 'department_id=20',
    
12.   audit_column => 'salary,commission_pct',
    
13.   handler_schema => 'SYS',
    
14.   handler_module => 'PROC_FGA',
    
15.   statement_types => 'SELECT,UPDATE',
    
16.   audit_trail => dbms_fga.XML+dbms_fga.EXTENDED,
    
17.   audit_column_opts => dbms_fga.ANY_COLUMNS);
    
18. end;
    
19. 
    
20. 
    
21. select  * from v$xml_audit_trail order by 6 desc ;
    
22. 
    
23. declare
    
24.   v_filehandle UTL_FILE.FILE_TYPE;
    
25. begin
    
26.    v_filehandle:=utl_file.fopen(location => 'FGALOG',filename => 'FGA.log',open_mode => 'w');
    
27.   
    
28.   utl_file.put_line(file =>  v_filehandle,buffer => 'XXXXX');
    
29.   utl_file.new_line( v_filehandle);
    
30.   utl_file.fclose( v_filehandle);
    
31. end;
    
32. 
    
33. 
    
34. 
    
35. CREATE OR REPLACE PROCEDURE PROC_FGA  ( object_schema VARCHAR2, object_name VARCHAR2, policy_name
    
36. VARCHAR2 ) AS
    
37.   v_filehandle UTL_FILE.FILE_TYPE;
    
38. begin
    
39.   v_filehandle:=utl_file.fopen(location => 'FGALOG',filename => 'FGA.log',open_mode => 'w');
    
40.   
    
41.   utl_file.put_line(file =>  v_filehandle,buffer =>object_schema );
    
42.   utl_file.put_line(file =>  v_filehandle,buffer =>object_name);
    
43.   utl_file.put_line(file =>  v_filehandle,buffer =>policy_name);
    
44.   utl_file.put_line(file =>  v_filehandle,buffer =>sys_context('userenv','ip_address'));
    
45.   utl_file.put_line(file =>  v_filehandle,buffer => sys_context('userenv','session_user'));
    
46.   utl_file.put_line(file =>  v_filehandle,buffer =>sys_context('userenv','current_user'));
    
47.   
    
48.    utl_file.put_line(file =>  v_filehandle,buffer =>to_char(sysdate,'YYYY-MM-DD:HH24:MI:SS'));
    
49.    
    
50.   utl_file.put_line(file =>  v_filehandle,buffer =>sys_context('userenv','authentication_type'));
    
51.   utl_file.new_line( v_filehandle);
    
52.   
    
53.   
    
54.   
    
55.   utl_file.fclose( v_filehandle);
    
56. end;
    
57. 
    
58. 
    

复制代码

HR
EMPLOYEES
MYPOLICY1
192.168.0.76
HR
SYS
2019-08-27:10:02:18
DATABASE
--------------------------------------------------------

1. 
   
2. CREATE OR REPLACE TRIGGER hrsalary_audit
   
3.     AFTER UPDATE OF salary
   
4.     ON hr.employees
   
5.     REFERENCING NEW AS NEW OLD AS OLD
   
6.     FOR EACH ROW
   
7. declare
   
8.     v_filehandle UTL_FILE.FILE_TYPE;
   
9. BEGIN
   
10. IF :old.salary != :new.salary THEN
    
11.     v_filehandle:=utl_file.fopen(location => 'FGALOG',filename => 'VALUE.log',open_mode => 'w');
    
12.     utl_file.put_line(file =>  v_filehandle,buffer =>sys_context('userenv','ip_address'));
    
13.     utl_file.put_line(file =>  v_filehandle,buffer => sys_context('userenv','session_user'));
    
14.     utl_file.put_line(file =>  v_filehandle,buffer =>sys_context('userenv','current_user'));  
    
15.     utl_file.put_line(file =>  v_filehandle,buffer =>to_char(sysdate,'YYYY-MM-DD:HH24:MI:SS'));
    
16.     utl_file.put_line(file =>  v_filehandle,buffer =>sys_context('userenv','authentication_type'));
    
17.     utl_file.put_line(file =>  v_filehandle,buffer =>'OLD is: '||:old.salary);
    
18.     utl_file.put_line(file =>  v_filehandle,buffer =>'NEW is: '||:new.salary);
    
19.     utl_file.new_line( v_filehandle);
    
20.     utl_file.fclose( v_filehandle);   
    
21.   END IF;
    
22. END;
    
23. 
    
24. 
    
25. select  * from dba_errors;
    
26. 
    
27. select  * from dba_source s where s.owner='SYS' and s.name='HRSALARY_AUDIT';
    

复制代码

HR
SYS
2019-08-27:10:26:45
DATABASE
OLD is: 26000
NEW is: 27000