课程第50/51次（2017-07-08星期六上下午）

botang · 发表于 2017-7-10 17:00:22

上完1Z0-052第11章
开始导入和导出
1Z0-052共19章（上完16章），1Z0-053共21章（上完17章）和1Z0-063多租户部分共9章（上完0章）
总共上完全部49章中的33章

select u.USERNAME,u.ACCOUNT_STATUS,u.EXPIRY_DATE,u.LOCK_DATE
from dba_users u
where u.USERNAME='HR';
ALTER PROFILE "PROFILE1" LIMIT PASSWORD_REUSE_MAX 1
PASSWORD_REUSE_TIME 0.013888889;
select * from dba_profiles p where p.RESOURCE_TYPE='PASSWORD'
and p.PROFILE='PROFILE1';

复制代码

CREATE OR REPLACE FUNCTION verify_function_11G
(username varchar2,
password varchar2,
old_password varchar2)
RETURN boolean IS
n boolean;
m integer;
differ integer;
isdigit boolean;
ischar boolean;
ispunct boolean;
db_name varchar2(40);
digitarray varchar2(20);
punctarray varchar2(25);
chararray varchar2(52);
i_char varchar2(10);
simple_password varchar2(10);
reverse_user varchar2(32);
BEGIN
digitarray:= '0123456789';
chararray:= 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
-- Check for the minimum length of the password
IF length(password) < 8 THEN
raise_application_error(-20001, 'Password length less than 8');
END IF;
-- Check if the password is same as the username or username(1-100)
IF NLS_LOWER(password) = NLS_LOWER(username) THEN
raise_application_error(-20002, 'Password same as or similar to user');
END IF;
FOR i IN 1..100 LOOP
i_char := to_char(i);
if NLS_LOWER(username)|| i_char = NLS_LOWER(password) THEN
raise_application_error(-20005, 'Password same as or similar to user name ');
END IF;
END LOOP;
-- Check if the password is same as the username reversed
FOR i in REVERSE 1..length(username) LOOP
reverse_user := reverse_user || substr(username, i, 1);
END LOOP;
IF NLS_LOWER(password) = NLS_LOWER(reverse_user) THEN
raise_application_error(-20003, 'Password same as username reversed');
END IF;
-- Check if the password is the same as server name and or servername(1-100)
select name into db_name from sys.v$database;
if NLS_LOWER(db_name) = NLS_LOWER(password) THEN
raise_application_error(-20004, 'Password same as or similar to server name');
END IF;
FOR i IN 1..100 LOOP
i_char := to_char(i);
if NLS_LOWER(db_name)|| i_char = NLS_LOWER(password) THEN
raise_application_error(-20005, 'Password same as or similar to server name ');
END IF;
END LOOP;
-- Check if the password is too simple. A dictionary of words may be
-- maintained and a check may be made so as not to allow the words
-- that are too simple for the password.
IF NLS_LOWER(password) IN ('welcome1', 'database1', 'account1', 'user1234', 'password1', 'oracle123', 'computer1', 'abcdefg1', 'change_on_install') THEN
raise_application_error(-20006, 'Password too simple');
END IF;
-- Check if the password is the same as oracle (1-100)
simple_password := 'oracle';
FOR i IN 1..100 LOOP
i_char := to_char(i);
if simple_password || i_char = NLS_LOWER(password) THEN
raise_application_error(-20007, 'Password too simple ');
END IF;
END LOOP;
-- Check if the password contains at least one letter, one digit
-- 1. Check for the digit
isdigit:=FALSE;
m := length(password);
FOR i IN 1..10 LOOP
FOR j IN 1..m LOOP
IF substr(password,j,1) = substr(digitarray,i,1) THEN
isdigit:=TRUE;
GOTO findchar;
END IF;
END LOOP;
END LOOP;
IF isdigit = FALSE THEN
raise_application_error(-20008, 'Password must contain at least one digit, one character');
END IF;
-- 2. Check for the character
<<findchar>>
ischar:=FALSE;
FOR i IN 1..length(chararray) LOOP
FOR j IN 1..m LOOP
IF substr(password,j,1) = substr(chararray,i,1) THEN
ischar:=TRUE;
GOTO endsearch;
END IF;
END LOOP;
END LOOP;
IF ischar = FALSE THEN
raise_application_error(-20009, 'Password must contain at least one \
digit, and one character');
END IF;
<<endsearch>>
-- Check if the password differs from the previous password by at least
-- 3 letters
IF old_password IS NOT NULL THEN
differ := length(old_password) - length(password);
differ := abs(differ);
IF differ < 3 THEN
IF length(password) < length(old_password) THEN
m := length(password);
ELSE
m := length(old_password);
END IF;
FOR i IN 1..m LOOP
IF substr(password,i,1) != substr(old_password,i,1) THEN
differ := differ + 1;
END IF;
END LOOP;
IF differ < 3 THEN
raise_application_error(-20011, 'Password should differ from the \
old password by at least 3 characters');
END IF;
END IF;
END IF;
-- Everything is fine; return TRUE ;
RETURN(TRUE);
END;
/

复制代码

CREATE OR REPLACE FUNCTION verify_function
(username varchar2,
password varchar2,
old_password varchar2)
RETURN boolean IS
n boolean;
m integer;
differ integer;
isdigit boolean;
ischar boolean;
ispunct boolean;
digitarray varchar2(20);
punctarray varchar2(25);
chararray varchar2(52);
BEGIN
digitarray:= '0123456789';
chararray:= 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
punctarray:='!"#$%&()``*+,-/:;<=>?_';
-- Check if the password is same as the username
IF NLS_LOWER(password) = NLS_LOWER(username) THEN
raise_application_error(-20001, 'Password same as or similar to user');
END IF;
-- Check for the minimum length of the password
IF length(password) < 4 THEN
raise_application_error(-20002, 'Password length less than 4');
END IF;
-- Check if the password is too simple. A dictionary of words may be
-- maintained and a check may be made so as not to allow the words
-- that are too simple for the password.
IF NLS_LOWER(password) IN ('welcome', 'database', 'account', 'user', 'password', 'oracle', 'computer', 'abcd') THEN
raise_application_error(-20002, 'Password too simple');
END IF;
-- Check if the password contains at least one letter, one digit and one
-- punctuation mark.
-- 1. Check for the digit
isdigit:=FALSE;
m := length(password);
FOR i IN 1..10 LOOP
FOR j IN 1..m LOOP
IF substr(password,j,1) = substr(digitarray,i,1) THEN
isdigit:=TRUE;
GOTO findchar;
END IF;
END LOOP;
END LOOP;
IF isdigit = FALSE THEN
raise_application_error(-20003, 'Password should contain at least one digit, one character and one punctuation');
END IF;
-- 2. Check for the character
<<findchar>>
ischar:=FALSE;
FOR i IN 1..length(chararray) LOOP
FOR j IN 1..m LOOP
IF substr(password,j,1) = substr(chararray,i,1) THEN
ischar:=TRUE;
GOTO findpunct;
END IF;
END LOOP;
END LOOP;
IF ischar = FALSE THEN
raise_application_error(-20003, 'Password should contain at least one \
digit, one character and one punctuation');
END IF;
-- 3. Check for the punctuation
<<findpunct>>
ispunct:=FALSE;
FOR i IN 1..length(punctarray) LOOP
FOR j IN 1..m LOOP
IF substr(password,j,1) = substr(punctarray,i,1) THEN
ispunct:=TRUE;
GOTO endsearch;
END IF;
END LOOP;
END LOOP;
IF ispunct = FALSE THEN
raise_application_error(-20003, 'Password should contain at least one \
digit, one character and one punctuation');
END IF;
<<endsearch>>
-- Check if the password differs from the previous password by at least
-- 3 letters
IF old_password IS NOT NULL THEN
differ := length(old_password) - length(password);
IF abs(differ) < 3 THEN
IF length(password) < length(old_password) THEN
m := length(password);
ELSE
m := length(old_password);
END IF;
differ := abs(differ);
FOR i IN 1..m LOOP
IF substr(password,i,1) != substr(old_password,i,1) THEN
differ := differ + 1;
END IF;
END LOOP;
IF differ < 3 THEN
raise_application_error(-20004, 'Password should differ by at \
least 3 characters');
END IF;
END IF;
END IF;
-- Everything is fine; return TRUE ;
RETURN(TRUE);
END;
/
-- This script alters the default parameters for Password Management
-- This means that all the users on the system have Password Management
-- enabled and set to the following values unless another profile is
-- created with parameter values set to different value or UNLIMITED
-- is created and assigned to the user.
-- Enable this if you want older version of the Password Profile parameters
-- ALTER PROFILE DEFAULT LIMIT
-- PASSWORD_LIFE_TIME 60
-- PASSWORD_GRACE_TIME 10
-- PASSWORD_REUSE_TIME 1800
-- PASSWORD_REUSE_MAX UNLIMITED
-- FAILED_LOGIN_ATTEMPTS 3
-- PASSWORD_LOCK_TIME 1/1440
-- PASSWORD_VERIFY_FUNCTION verify_function;

复制代码

AUDIT：

select * from dba_audit_trail;
select *
from dba_views v
where v.VIEW_NAME='DBA_AUDIT_TRAIL';
create tablespace tbsaudit datafile '/u01/app/oracle/oradata/orcl/tbsaudit.dbf'
size 100M;
alter table aud$ move tablespace tbsaudit;
--------------------------------
select * from dict where table_name like '%AUDIT%OPTS';
select * from dba_priv_audit_opts;
select * from dba_obj_audit_opts ;
select * from dba_stmt_audit_opts;
-----
grant select any table to user1;
---
noaudit select any table by hr;
audit select any table by user1 by access;
---------------
--------
select * from dba_audit_trail order by 5 desc;
----------------
select * from dba_audit_trail;
select *
from dba_views v
where v.VIEW_NAME='DBA_AUDIT_TRAIL';
create tablespace tbsaudit datafile '/u01/app/oracle/oradata/orcl/tbsaudit.dbf'
size 100M;
alter table aud$ move tablespace tbsaudit;
--------------------------------
select * from dict where table_name like '%AUDIT%OPTS';
select * from dba_priv_audit_opts;
select * from dba_obj_audit_opts ;
select * from dba_stmt_audit_opts;
-----
grant select any table to user1;
---
noaudit select any table by hr;
audit select any table by user1 by access;
---------------
--------
select * from dba_audit_trail order by 5 desc;
----
noaudit select any table by user1;
----------------
audit select any table by user1 by session whenever successful ;
-------------------------------
select * from dba_priv_audit_opts;
select * from dba_obj_audit_opts where owner='HR' ;
select * from dba_stmt_audit_opts
minus
select * from dba_priv_audit_opts;
select * from dba_audit_trail order by 5 desc;
---
audit table by hr by access;
---------------------
select * from v$xml_audit_trail order by 6 desc ;
audit update on hr.employees;
-----
select * from dba_common_audit_trail order by 6 desc;
------------------
select * from v$xml_audit_trail where db_user='HR' order by 6 desc ;
audit update on hr.employees;
-----
select * from dba_common_audit_trail where db_user='HR' order by 6 desc;
begin
dbms_fga.add_policy(object_schema => 'HR',
object_name => 'EMPLOYEES',
policy_name => 'POLICY1',
audit_condition => 'department_id=20',
audit_column => 'SALARY,COMMISSION_PCT',
handler_schema => 'SYS',
handler_module => 'PROC_FGA',
enable => true,
statement_types => 'SELECT,UPDATE',
audit_trail => dbms_fga.DB_EXTENDED,
audit_column_opts => dbms_fga.ALL_COLUMNS);
end;
----
select * from dba_audit_policies;
-----
select sys_context('userenv','os_user')||' '||user||' '
||sysdate||' modified '' '||sys_context('userenv','ip_address')
from dual;
create or replace procedure proc_fga
(p_1 varchar2 , p_2 varchar2 , p_3 varchar2)
is
begin
insert into t_fga values (sys_context('userenv','os_user')||' '||user||' '
||sysdate||' modified '' '||sys_context('userenv','ip_address')||p_1||' '||
p_2||' '||p_3 );
end;
---------------
create or replace procedure proc_fga
(p_1 varchar2 , p_2 varchar2 , p_3 varchar2)
is
begin
insert into t_fga values (sys_context('userenv','os_user')||' '||user||' '
||sysdate||' modified '' '||sys_context('userenv','ip_address')||p_1||' '||
p_2||' '||p_3 );
end;
------------
select * from dba_common_audit_trail where db_user='HR' order by 6 desc;
----
select * from dba_obj_audit_opts o
where o.owner='HR';
select * from dba_common_audit_trail where db_user='HR' order by 6 desc;
-----
create table t_value ( a varchar2(200)) tablespace tbsaudit;
----
create or replace trigger trg_value
after update of salary on hr.employees
referencing new as new old as old
for each row
begin
if :old.salary != :new.salary then
insert into t_value
values ( sys_context('userenv','os_user')||
' '||user||'current_user '
||sys_context('userenv','current_user')
||'session user: '||sys_context('userenv','session_user')
||sysdate||' modified '||:new.salary||' '||:old.salary||' '
||sys_context('userenv','ip_address') );
end if;
end;
select * from t_value;
---------------------------
create table t_value ( a varchar2(200)) tablespace tbsaudit;
----
create or replace trigger trg_value
after update of salary on hr.employees
referencing new as new old as old
for each row
begin
if :old.salary != :new.salary then
insert into t_value
values ( sys_context('userenv','os_user')||
' '||user||'current_user '
||sys_context('userenv','current_user')
||'session user: '||sys_context('userenv','session_user')
||sysdate||' modified '||:new.salary||' '||:old.salary||' '
||sys_context('userenv','ip_address') );
end if;
end;
select * from t_value;
select * from dba_triggers;
alter trigger trg_value disable;
select * from dba_source s where s.OWNER='SYS'
and s.NAME='TRG_VALUE';
alter trigger trg_value enable;

复制代码