日本オラクル株式会社

Size: px

Start display at page:

Download "日本オラクル株式会社"

ひろみいちぬの
5 years ago
Views:

1 FISC 6 Oracle Database 10g ~ ~ : : : 1.0

2 (* ) FISC ) (* ) FISC 6 (* FISC 6 ) FISC 6 Oracle g Database 10 (FISC) FISC * ) ( FISC Oracle g ( Oracle Database g 10 Enterprise Edition ) 138 FISC F

3 1. Oracle Database 10g FISC ID FISC 6 ) (

4 1. Oracle Database 10g FISC FISC ( 1) 4 Oracle Database g 10 Oracle OS Oracle Enterprise Manager 10g ( OEM SE/EE * EE ) OEM OEM SNMP agent DB agent Web OEM OEM Oracle DB : Oracle Enterprise Manager

5 FISC ( 1 2 4) 1) ( 2) ( CPU 4) (

6 Oracle Database g 10 1 Oracle Database 10g (SE/EE) DB DB 2 (SE/EE) 4 (PDA) Oracle Database Lite 10g ( ) O Database Lite Oracle Database 10g DBMS_CRYPTO Obfuscation Toolkit (* DBMS_CRYPTO ) PL/SQL DBMS_CRYPTO RAW Oracle DES Triple-DES RC4 Advanced Encryption Standard AES MD5 MD4 SHA-1 MD5 SHA-1 MAC Oracle Database 16 Oracle Database 10g OS DB ( ) Oracle Advanced Security Obfuscation Toolkit

7 1. create table creditcard (cardnumber varchar2(40)); key_str create or replace function string_encrypt (input_val varchar2) return varchar2 is key_str varchar2(16) := 'ABCDEFGHIJKLMNOP' ; * return_val varchar2(40) := NULL; begin dbms_obfuscation_toolkit.desencrypt(input_string =>input_val, key_string => key_str, encrypted_string => return_val ); return return_val ; end; / 2.2. key_str create or replace function string_decrypt (input_val varchar2) return varchar2 is key_str varchar2(16) := 'ABCDEFGHIJKLMNOP' ; * return_val varchar2(40) := NULL; begin dbms_obfuscation_toolkit.desdecrypt(input_string =>input_val, key_string => key_str, decrypted_string => return_val ); return return_val ; end; / 3. INSERT string_enctypr CREATE OR REPLACE TRIGGER TR_ENCRYPT_CREDITCARD BEFORE INSERT OR UPDATE of cardnumber ON CREDITCARD REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW BEGIN

8 :NEW.CARDNUMBER:=string_encrypt(:NEW.CARDNUMBER); END; / 4. * insert creditcard SQL> insert into creditcard values (' '); 1 SQL> select * from creditcard; CARDNUMBER y }e= pm $? mu * 5. * string_decrypt SQL> select string_decrypt(cardnumber) from creditcard; STRING_DECRYPT(CARDNUMBER) PL/SQL Oracle Database Windows Lite Windows 32 CE ENCRYPDB Oracle Databa ENCRYPDB CAST5 DES 128 DECRYPDB Oracle Database Lite Oracle Database Lite 10g 3

9 ) ( 1) ( 2) (

10 Oracle Database g 10 1 Oracle Oracle Net Oracle Advanced Security (EE ) Oracle Oracle Net (SE/EE) Oracle Advanced Security Oracle Advanced Security Oracle Net Services sqlnet.ora Oracle Net Services sqlnet.ora Oracle Net Manager Oracle Advanced Security Oracle Advanced Security Advanced Encryption Standard AES DES 3DES RC4 MD5 Secure Hash Algorithm SHA-1 SSL JDBC Thin Oracle Advanced Security Java Oracle JDBC Thin (SE/EE) Oracle Net sqlnet.ora tcp.validnode_checking ON OFF tcp.invited_nodes tcp.excluded_nodes Oracle Net Services 9

11 ( ) ) ( Oracle Oracle Database g 10 Oracle OS Oracle Database 4 (SE/EE) OS (SE/EE ) OSOracle OS ) (EE Kerberos RADIUS Oracle Oracle Advanced Security CREATE USER Oracle Database SQL 17 OS/ OS CREATE IDENTIFIED EXTERNALLY USER S sqlnet.ora Oracle Database 4

12 ) ( Oracle Database g 10 Oracle Database 10g Oracle Advanced Security [ ]

13 ID 1) ( 5 ID Oracle Database g 10 Oracle Database 10g ( ) ID Oracle Database 10g ID 1 Oracle Database 4

14 Oracle Database 10g Oracle RESOURCE_LIMIT TRUE 10 CREATE PROFILE PROF1 LIMIT IDLE_TIME 10; ALTER USER SCOTT PROFILE PROF1; Oracle DB PL/SQL 8AM 6PM CREATE OR REPLACE TRIGGER LOGON_TIME AFTER LOGON ON DATABASE BEGIN if (to_char(sysdate,'d') not between '2' and '6') or (to_char(sysdate, 'HH24MI') not between '0800' and '1800') then RAISE_APPLICATION_ERROR(-20001, 'You are not allowed to log into database now.'); end if; END; ( ) 4 CREATE PROFILE prof LIMIT FAILED_LOGIN_ATTEMPTS 4 PASSWORD_LOCK_TIME 30; ALTER USER ashwini PROFILE prof;

15 CREATE PROFILE PASSWORD_LIFE_TIME CREATE PROFILE prof LIMIT PASSWORD_LIFE_TIME 90; ALTER USER ashwini PROFILE prof; CREATE PASSWORD_GRACE_TIME PROFILE CREATE PROFILE prof LIMIT PASSWORD_GRACE_TIME 3; ALTER USER ashwini PROFILE prof; Oracle PL/SQL PL/SQL UTLPWDMG.SQL (* $ORACLE_HOME/rdbms/admin/utlpwdmg.sql welcome account database user 3 CREATE PROFILE PASSWORD_VERIFY_FUNCTION Oracle Database SQL 17

16 ) ( Oracle Database g 10 Oracle Database 10g (SE/EE EE) * SQL Oracle Database 4 Oracle Database 11 8 DBA DBA ( ) OS DBA DBA

17 SQL 3 (EE) 1 PL/SQL (EE) Oracle BI ) Business Intelligence ( * BI Discoverer Discoverer DWH Oracle Database 10g ) root OS 36 DBA AUDIT_FILE_DEST DBA AUDIT_SYS_OPERATIONS SYS SQL AUDIT_TRAIL AUDIT (SYS.AUD$ ) OS 2

18 UTC SQL SQL (EE) AUDIT_TRAIL DBMS_FGA DBA_FGA_AUDIT_TRAIL DBMS_FGA hr.emp INSERT UPDATE DELETE SELECT sales salary DBMS_FGA.ADD_POLICY( object_schema => 'hr', object_name => 'emp', policy_name => 'chk_hr_emp', audit_condition => 'dept = ''SALES'' ', audit_column => 'salary' statement_types => 'insert,update,delete,select'); [ Oracle Flashback Version Query 1.8

19 ) ( Oracle Database 10g Oracle Database 10g Oracle Oracle Collaboration Suite Oracle SMTP POP3 IMAP mail Oracle Oracle Database Oracle Database / DB DB DB DB 42-1 Oracle Collaboration Suite Oracle Collaboration Suite

20 ) ( Oracle ) ( Oracle , Oracle

21 ) ( 48 Oracle Database 10g Oracle Database / / Oracle Database 10g (EE) 6 Oracle Database 9 Oracle Flashback Query

22 Oracle Flashback Version Query Oracle Flashback Transaction Query 1 Oracle Flashback Table Oracle Flashback Drop DROP TABLE Oracle Flashback Database Oracle Flashback Table Oracle Flashback Query Oracle Flashback Transaction Query Oracle Flashback Version Query UNDO UNDO Oracle Flashback Query SELECT AS OF JOHN SELECT * FROM EMPLOYEE AS OF TIMESTAMP TO_TIMESTAMP(' :30:00', 'YYYY-MM-DD HH:MI:SS') WHERE name = 'JOHN'; JOHN EMPLOYEE INSERT INTO employee (SELECT * FROM employee AS OF TIMESTAMP TO_TIMESTAMP(' :30:00', 'YYYY-MM-DD HH:MI:SS') WHERE name = 'JOHN');

23 Oracle Flashback Table 5 JOHN EMPLOYEE 2 JOHN Oracle Flashback Table 2 FLASHBACK TABLE EMPLOYEES TO TIMESTAMP TO_TIMESTAMP (' :00:00','YYYY-MM-DD HH:MI:SS') ENABLE TRIGGERS; Oracle Flashback Database Oracle Flashback Database REDO Point-in-Time Oracle Flashback Database UNDO Flashback Database ARCHIVELOG DB_FLASHBACK_RETENTION_TARGET ALTER DATABASE FLASHBACK ON SQL Flashback Database FLASHBACK DATABASE TO TIME timestamp (' :00:00');

24 2. FISC 6 ( ) FISC Oracle Database 10 g [ 35] 4-1 [ 36] ID [ 103] [ 104] 4-1 [ 26] [ 17] [ 16] [ 18] 4-2 [ 31] [ 44] [ 43] [ 45] [ 31]

25 [ 11] [ 18] 4-3 [ 16] [ 18] 4-3 [ 16] [ 18] [ 28] [ 29] [ 30] [ 49] [ 50] [ 63] [ 48] [ 51] [ 24]

26 [ 37] [ 42-1] [ 45] [ 22] [ 20] [ 91]

Agenda

Agenda Oracle データベースの監査機能株式会社アクアシステムズアプリケーション統括部 PAU001J-00-00SE 株式会社アクアシステムズ Oracle データベースを専門とする技術者集団 Oracle データベースチューニング & 監視ツール Performance Analyzer の開発 / 販売 Oracle データベース診断及びパフォーマンスチューニング Oracle データベースに関するコンサルティング