Category: Database

วิธีสร้าง User บน PostgreSQL ให้สามารถเข้าถึงฐานข้อมูลได้แบบ Read-Only
บันทึกเอาไว้สั้น ๆ ในที่นี้ ต้องการสร้างผู้ใช้ ‘username’ ให้มีรหัสผ่านเป็น ‘userpasswordgohere’ ให้สามารถใช้งาน ‘someschema’ โดยให้ใช้เฉพาะ ‘SELECT’ ได้กับทุก Tables ใน someschema

บน PostgreSQL ใช้คำสั่งนี้ ในการสร้างและ Grant สิทธิ์
```
CREATE USER username WITH ENCRYPTED PASSWORD 'userpasswordgohere';
GRANT USAGE ON SCHEMA someschema TO username;
GRANT SELECT ON ALL TABLES IN SCHEMA someschema TO username;
```
จบ
January 25, 2019
Oracle Label Security
Oracle Label Security (OLS) เป็นส่วนขยายของเทคโนโลยี Virtual Private Database (VPD) ซึ่งเริ่มนำมาใช้ตั้งแต่ Oracle 8i OLS อนุญาตให้มีการควบคุมการเข้าถึงลงในแถวต่างๆ ตามป้ายกำกับที่ระบุ ฟังก์ชันการทำงานที่คล้ายคลึงกันสามารถทำซ้ำโดยใช้ Fine Grained Access Control (FGAC) แต่ OLS ให้โซลูชันที่ง่ายกว่าสำหรับการรักษาความปลอดภัยระดับแถว (row-level security) ในบทความนี้ฉันจะนำเสนอตัวอย่างง่ายๆของการกำหนดค่า OLS

ตั้งค่าฐานข้อมูล
- หากไม่ได้เลือกตัวเลือก Label Security เมื่อครั้งติดตั้ง Oracle Database สามารถเรียกตัวติดตั้งและเลือกตัวเลือก Label Security เพิ่มเติมได้ภายหลัง
- เข้าระบบด้วยบัญชีผู้ใช้ Oracle เปิด terminal พิมพ์ dbca (Database Server เป็น Linux)
- เมื่อได้ Welcome Screen คลิก Next
- ที่หน้า Operation คลิก Configure Database Options แล้วคลิก Next
- หน้า Database เลือกฐานข้อมูลที่ต้องการ คลิก Next
- ที่หน้า Database Features คลิกเลือก Label Security คลิก Next
- คลิก Finish
- เมื่อจบการตั้งค่าให้ restart database
สร้างผู้ใช้ทดสอบ

คำสั่งทั้งหมดทำภายใน sqlplus สร้างผู้ใช้ ols_test มีรหัสผ่านว่า password โดยให้สิทธิ์ CONNECT, RESOURCE, SELECT_CATALOG_ROLE
sqlplus, / as sysdba
```
CONN / AS SYSDBA

CREATE USER ols_test IDENTIFIED BY password 

DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp

GRANT CONNECT, RESOURCE, SELECT_CATALOG_ROLE TO ols_test;
```
ต่อไปเป็นการให้สิทธิ์ในแพ็คเกจ OLS แก่ผู้ใช้ ols_test จำเป็นต้อง unlock ผู้ใช้ lbacsys และตั้งรหัสผ่านว่า lbacsys เพื่อใช้เป็นคนกำหนดสิทธิ์ต่างๆ ให้ ols_test ได้แก่สิทธิ์ execute บน sa_components, sa_user_admin, sa_label_admin, sa_policy_admin, sa_audit_admin, sa_sysdba, to_lbac_data_label และให้ ols_test เป็น lbac_dba
sqlplus, / as sysdba, lbacsys/lbacsys
```
ALTER USER lbacsys IDENTIFIED BY lbacsys ACCOUNT UNLOCK;

CONN lbacsys/lbacsys

GRANT EXECUTE ON sa_components TO ols_test WITH GRANT OPTION;
GRANT EXECUTE ON sa_user_admin TO ols_test WITH GRANT OPTION;
GRANT EXECUTE ON sa_user_admin TO ols_test WITH GRANT OPTION;
GRANT EXECUTE ON sa_label_admin TO ols_test WITH GRANT OPTION;
GRANT EXECUTE ON sa_policy_admin TO ols_test WITH GRANT OPTION;
GRANT EXECUTE ON sa_audit_admin  TO ols_test WITH GRANT OPTION;

GRANT LBAC_DBA TO ols_test;
GRANT EXECUTE ON sa_sysdba TO ols_test;
GRANT EXECUTE ON to_lbac_data_label TO ols_test;
```
สร้าง Policy

ต่อไปจะสร้าง policy ชื่อ region_policy ด้วยผู้ใช้ ols_test และระบุชื่อของ column ที่จะเป็นที่เก็บ label ว่า region_label
sqlplus, ols_test/password
```
CONN ols_test/password

BEGIN
	SA_SYSDBA.CREATE_POLICY(
	policy_name => 'region_policy',
	column_name => 'region_label');
END;
/

GRANT region_policy_DBA TO ols_test;
```
กำหนด component ของ label

สร้าง component ของ label เพื่อใช้สำหรับ policy ที่สร้างไว้ตอนต้น region_policy
– สร้างระดับไว้ 3 ระดับ คือ 20, 40 และ 60 มีชื่อเรียกสั้น ๆ ว่า L1, L2 และ L3 ชื่อเรียกยาว ๆ ว่า Level 1, Level 2 และ Level 3 ตามลำดับ
– สร้างสิทธิ์การใช้งานไว้ 2 ชนิดประกอบด้วย 100, 120 มีชื่อเรียกอย่างย่อว่า M และ E และชื่อเรียกยาวว่า Manage และ Employee ตามลำดับ
– สร้างกลุ่มของ policy ไว้ 4 กลุ่ม 20, 40, 60 และ 80 ชื่อย่อ R20, R40, R60 และ R80 ชื่อยาว Region North, Region South, Region East และ Region West ตามลำดับ
สุดท้ายกำหนดให้ ols_test เป็นผู้มีสิทธิ์สูงสุดใน policy region_policy
sqlplus, ols_test/password
```
EXECUTE SA_COMPONENTS.CREATE_LEVEL('region_policy',20,'L1','Level 1');
EXECUTE SA_COMPONENTS.CREATE_LEVEL('region_policy',40,'L2','Level 2');
EXECUTE SA_COMPONENTS.CREATE_LEVEL('region_policy',60,'L3','Level 3');

EXECUTE SA_COMPONENTS.CREATE_COMPARTMENT('region_policy',100,'M','MANAGEMENT');
EXECUTE SA_COMPONENTS.CREATE_COMPARTMENT('region_policy',120,'E','EMPLOYEE');

EXECUTE SA_COMPONENTS.CREATE_GROUP('region_policy',20,'R20','REGION NORTH');
EXECUTE SA_COMPONENTS.CREATE_GROUP('region_policy',40,'R40','REGION SOUTH');
EXECUTE SA_COMPONENTS.CREATE_GROUP('region_policy',60,'R60','REGION EAST');
EXECUTE SA_COMPONENTS.CREATE_GROUP('region_policy',80,'R80','REGION WEST');

EXECUTE SA_USER_ADMIN.SET_USER_PRIVS('region_policy','ols_test','FULL,PROFILE_ACCESS');
```
สร้าง Table ทดสอบ

สร้าง table พร้อมข้อมูลทดสอบใน schema ols_test โดย table ชื่อ customers มีฟิลด์ id, cust_type, first_name, last_name, region, credit กำหนดให้ id เป็น Primary key และ insert ข้อมูลลง table ด้วย
sqlplus, ols_test/password
```
CONN ols_test/password

CREATE TABLE customers (
	id                  NUMBER(10) NOT NULL,
	cust_type           VARCHAR2(10),
	first_name          VARCHAR2(30),
	last_name           VARCHAR2(30),
	region              VARCHAR2(5),
	credit              NUMBER(10,2),
	CONSTRAINT customer_pk PRIMARY KEY (id));

GRANT SELECT, INSERT, UPDATE, DELETE ON customers TO PUBLIC;

INSERT INTO customers (id, cust_type, first_name, last_name, region, credit)
VALUES ( 1, 'SILVER', 'Harry', 'Hill', 'NORTH', 11000.00);
INSERT INTO customers (id, cust_type, first_name, last_name, region, credit)
VALUES ( 2, 'SILVER', 'Vic', 'Reeves', 'NORTH', 2000.00);
INSERT INTO customers (id, cust_type, first_name, last_name, region, credit)
VALUES ( 3, 'SILVER', 'Bob', 'Mortimer', 'WEST', 500.00);
INSERT INTO customers (id, cust_type, first_name, last_name, region, credit)
VALUES ( 4, 'SILVER', 'Paul', 'Whitehouse', 'SOUTH', 1000.00);
INSERT INTO customers (id, cust_type, first_name, last_name, region, credit)
VALUES ( 5, 'SILVER', 'Harry', 'Enfield', 'EAST', 20000.00);

INSERT INTO customers (id, cust_type, first_name, last_name, region, credit)
VALUES ( 6, 'GOLD', 'Jenifer', 'Lopez', 'WEST', 500.00);
INSERT INTO customers (id, cust_type, first_name, last_name, region, credit)
VALUES ( 7, 'GOLD', 'Kylie', 'Minogue', 'NORTH', 1000.00);
INSERT INTO customers (id, cust_type, first_name, last_name, region, credit)
VALUES ( 8, 'GOLD', 'Maria', 'Carey', 'WEST', 1000.00);
INSERT INTO customers (id, cust_type, first_name, last_name, region, credit)
VALUES ( 9, 'GOLD', 'Dani', 'Minogue', 'SOUTH', 20000.00);
INSERT INTO customers (id, cust_type, first_name, last_name, region, credit)
VALUES (10, 'GOLD', 'Whitney', 'Houston', 'EAST', 500.00);

INSERT INTO customers (id, cust_type, first_name, last_name, region, credit)
VALUES (11, 'PLATINUM', 'Robbie', 'Williams', 'SOUTH', 500.00);
INSERT INTO customers (id, cust_type, first_name, last_name, region, credit)
VALUES (12, 'PLATINUM', 'Thom', 'Yorke', 'NORTH', 2000.00);
INSERT INTO customers (id, cust_type, first_name, last_name, region, credit)
VALUES (13, 'PLATINUM', 'Gareth', 'Gates', 'WEST', 10000.00);
INSERT INTO customers (id, cust_type, first_name, last_name, region, credit)
VALUES (14, 'PLATINUM', 'Darius', 'Dinesh', 'EAST', 2000.00);
INSERT INTO customers (id, cust_type, first_name, last_name, region, credit)
VALUES (15, 'PLATINUM', 'Will', 'Young', 'EAST', 100.00);

COMMIT;
```
สร้าง label function

เป็นการสร้าง function ชื่อ get_customer_label สำหรับสร้าง label ประกอบด้วย p_cust_type, p_region, p_credit
– โดยกำหนด policy ระดับด้วย p_credit >2000 ก็จะมี v_label ว่า L3 ถ้า p_credit > 500 v_label คือ L2 และ p_credit < 500 v_label คือ L1
– กำหนด p_cust_type เป็น platinum หรือไม่ถ้าเป็นก็จะอยู่ในจะมีิสิทธิ์ M ถ้าไม่ก็จะเป็น E โดยเอาค่าที่ได้มาต่อกับ v_label ข้อที่แล้ว
– กำหนด p_region ถ้าเป็น north จะอยู่กลุ่ม R20 เป็นต้น และจะเอาค่าที่ได้ไปต่อกับ v_label ที่ได้จากข้อที่แล้ว
– ค่าที่ return จา function นี้จะเป็นชื่อ policy และ v_label สุดท้ายที่ได้จากข้อกำหนดที่กล่าวมาข้างต้น
sqlplus, ols_test/password
```
CREATE OR REPLACE FUNCTION get_customer_label (
	p_cust_type  IN  VARCHAR2,
	p_region     IN  VARCHAR2,
	p_credit     IN  NUMBER)
RETURN LBACSYS.LBAC_LABEL AS
	v_label  VARCHAR2(80);
BEGIN
	IF p_credit > 2000 THEN
		v_label := 'L3:';
	ELSIF p_credit > 500 THEN
		v_label := 'L2:';
	ELSE
		v_label := 'L1:';
	END IF;

	IF p_cust_type = 'PLATINUM' THEN
		v_label := v_label || 'M:';
	ELSE
		v_label := v_label || 'E:';
	END IF;
	IF p_region = 'NORTH' THEN
		v_label := v_label || 'R20';
	ELSIF p_region = 'SOUTH' THEN
		v_label := v_label || 'R40';
	ELSIF p_region = 'EAST' THEN
		v_label := v_label || 'R60';
	ELSIF p_region = 'WEST' THEN
		v_label := v_label || 'R80';
	END IF;

	RETURN TO_LBAC_DATA_LABEL('region_policy',v_label);
END get_customer_label;
/

SHOW ERRORS
```
สั่งให้ Policy ทำงานกับ table ที่สร้าง

เมื่อสั่งให้ Policy ทำงานจะเป็นการเพิ่ม column ที่เก็บ label ใน table เป้าหมาย
sqlplus, ols_test/password
```
CONN ols_test/password

BEGIN
	SA_POLICY_ADMIN.APPLY_TABLE_POLICY(
	policy_name   => 'REGION_POLICY',
	schema_name   => 'OLS_TEST',
	table_name    => 'CUSTOMERS',
	table_options => 'NO_CONTROL');
END;
/
```
เริ่มสร้าง label

เป็นการกำหนดค่าเริ่มต้นสำหรับ region_label ลงไปใน table ซึ่งถ้าไม่มีข้อมูลในฟิลด์นี้จะไม่มีใครสามารถเข้าถึงได้
sqlplus, ols_test/password
```
UPDATE customers
SET region_label = CHAR_TO_LABEL('REGION_POLICY','L1');

COMMIT;
```
สั่งให้ Policy ทำงานอีกครั้ง

ต่อไปจะเป็นการสั่งให้ policy ทำงานอีกครั้งใน table customers เพื่อเปลี่ยนค่าเป็นค่าที่ได้จะเป็นค่าจาก function ที่สร้างไว้
sqlplus, ols_test/password
```
BEGIN
	SA_POLICY_ADMIN.REMOVE_TABLE_POLICY('REGION_POLICY','OLS_TEST','CUSTOMERS');
	SA_POLICY_ADMIN.APPLY_TABLE_POLICY (
	policy_name => 'REGION_POLICY',
	schema_name => 'OLS_TEST',
	table_name  => 'CUSTOMERS',
	table_options => 'READ_CONTROL,WRITE_CONTROL,CHECK_CONTROL',
	label_function => 'ols_test.get_customer_label(:new.cust_type,:new.region,:new.credit)',
	predicate => NULL);
END;
/
```
สั่งให้ label ทำงานกับข้อมูลในแต่ละแถว

ปรับปรุงข้อมูลให้เป็นตามฟังก์ชันที่สร้างไว้กับข้อมูลแต่ละแถว
sqlplus, ols_test/password
```
UPDATE customers
SET    first_name = first_name;

COMMIT;
```
สร้าง user ทดสอบ

สร้าง user อื่นๆ เพื่อทดสอบ label ได้แก่ sales_manager, sales_north, sales_south, sales_east, sales_west
– sales_manager มี label ว่า L3:M,E:R20,R40,R60,R80 แปลว่า sales_manager สามารถอ่านและเขียนข้อมูลได้ทุก Region
– sales_north มี label ว่า L3:E:R20,R40 แปลว่า sales_north สามารถอ่านข้อมูลที่อยู่ใน Region North และ South
– sales_south มี label ว่า L3:E:R20,R40,R60,R80 แปลว่า sales_south อ่านข้อมูลได้ทุก Region
– sales_east มี label ว่า L3:E:R60 แปลว่า sales_east อ่านข้อมูล Region East ได้อย่างเดียว
– sales_west มี label ว่า L3:E:R80 แปลว่า sales_west อ่านข้อมูล Region West ได้อย่างเดียว
sqlplus, / as sysdba, (sales_manager, sales_nort, sales_south, sales_east, sales_west, ols_test)/password
```
CONN / AS SYSDBA;

CREATE USER sales_manager IDENTIFIED BY password;
CREATE USER sales_north IDENTIFIED BY password;
CREATE USER sales_south IDENTIFIED BY password;
CREATE USER sales_east IDENTIFIED BY password;
CREATE USER sales_west IDENTIFIED BY password;

GRANT CONNECT TO sales_manager, sales_north, sales_south, sales_east, sales_west;

CONN ols_test/password

BEGIN
	SA_USER_ADMIN.SET_USER_LABELS('region_policy','sales_manager','L3:M,E:R20,R40,R60,R80');
	SA_USER_ADMIN.SET_USER_LABELS('region_policy','sales_north','L3:E:R20,R40');
	SA_USER_ADMIN.SET_USER_LABELS('region_policy','sales_south','L3:E:R20,R40,R60,R80');
	SA_USER_ADMIN.SET_USER_LABELS('region_policy','sales_east','L3:E:R60');
	SA_USER_ADMIN.SET_USER_LABELS('region_policy','sales_west','L3:E:R80');
END;
/
```
ทดสอบ Label Security

เข้าระบบผ่าน sqlplus ด้วย user ต่างๆ ที่สร้างในข้อที่แล้วเพื่อดูข้อมูลว่าถูกต้องหรือไม่ เพื่อความสะดวกจึงเข้าผ่านโปรแกรมสำเร็จรูปอื่นเพื่อให้ดูง่าย ได้ผลดังรูปต่อไปนี้นี้
sqlplus, sales_manager, sales_north, sales_south, sales_east, sales_west

จบขอให้สนุก

ที่มา https://oracle-base.com/articles/9i/oracle-label-security-9i#Installation
August 6, 2018
Transpose rows into columns using the Oracle PIVOT operator
ก่อนหน้านี้เราเคยนำเสนอบทความวิธีการทรานสโพส (หมุน) ข้อมูลจากแถวเป็นคอลัมน์ ด้วยการใช้ฟังก์ชัน SUM และ DECODE กันมาแล้ววันนี้ลองมาใช้อีกวิธีในการแสดงผลดังกล่าวด้วย PIVOT ก่อนอื่นมาดูในส่วน syntax ของ PIVOT กันก่อน

PIVOT Syntax
```
SELECT * FROM
(
  SELECT column1, column2
  FROM tables
  WHERE conditions
)
PIVOT 
(
  aggregate_function(column2)
  FOR column2
  IN ( expr1, expr2, ... expr_n) | subquery
)
ORDER BY expression [ ASC | DESC ];
```
โดยที่

aggregate_function คือ aggregate function เช่น SUM, COUNT, MIN, MAX, หรือ AVG

IN ( expr1, expr2, … expr_n ) คือ รายการค่าของ column2 ที่ต้องการจะแสดงในส่วนคอลัมภ์

คราวนี้ลองมาดูตัวอย่างกัน

สมมติเรามีข้อมูลคะแนนภาษาอังกฤษของนักศึกษาใหม่ซึ่งประกอบด้วย 6 ฟิลด์ข้อมูลแสดงดังตัวอย่างด้านล่าง
ข้อมูล: ตาราง TEST_NEW_STUDENT เป็นตัวอย่างข้อมูลคะแนนภาษาอังกฤษของนักศึกษาใหม่จำนวน 16 รายการ

โจทย์ เราต้องการจะแสดงจำนวนของนักศึกษาแยกตามคณะที่สังกัด โดยให้แสดงข้อมูลอยู่ในรูปแบบคอลัมภ์

ดังนั้นจึงเราสามารถเขียนคิวรีโดยใช้ PIVOT เพื่อแก้ปัญหาข้างต้นได้ดังนี้

SELECT *
FROM (SELECT ‘TotalStudent’ fac_summary, fac_id FROM test_new_student)
PIVOT
(COUNT (fac_id)
FOR fac_id
IN (’06’ Engineering, ’08’ Science, ’12’ Natural_Resources))

ผลลัพธ์ที่ได้ : แสดงข้อมูลจำนวนนักศึกษาซึ่งประกอบด้วย จำนวนนักศึกษาคณะวิศวกรรมศาสตร์ คณะวิทยาศาสตร์ และคณะทรัพยากรธรรมชาติ ตามลำดับ โดยแสดงข้อมูลในรูปแบบคอลัมภ์
June 26, 2018
Oracle : Transpose rows into columns using SUM and DECODE
วันนี้เราจะนำเสนอวิธีการทรานสโพส (หมุน) ข้อมูลจากแถวเป็นคอลัมน์ โดยการประยุกต์ใช้ฟังก์ชัน SUM และ ฟังก์ชัน DECODE ก่อนอื่นจะขออธิบายในส่วนของฟังก์ชัน SUM และ DECODE กันก่อน

ฟังก์ชัน SUM

SUMจัดเป็นฟังก์ชันประเภท Aggregate Function ทำหน้าที่ในการคำนวณผลรวมของค่าในคอลัมภ์ มีรูปแบบการใช้งานดังตัวอย่างข้างล่างนี้
```
SELECT SUM(aggregate_expression)
FROM tables
[WHERE conditions];
```
โดยที่

aggregate_expression คือ คอลัมภ์ที่ต้องการค่าผลรวม โดยที่ข้อมูลในคอลัมภ์จะต้องเป็นชนิดตัวเลขเท่านั้น

มาดูตัวอย่างการใช้งานกัน

SELECT fac_id, SUM (eng_score) sum_eng_score
FROM test_new_student
GROUP BY fac_id;

ผลลัพธ์ที่ได้ : แสดงผลรวมของคะแนนภาษาอังกฤษของแต่ละคณะ

ฟังก์ชัน DECODE

DECODE เป็นการเขียนเงื่อนไขบนชุดคำสั่ง select โดยมีลักษณะการทำงานเหมือน IF-THEN-ELSE โดยถ้าเข้าเงื่อนไขที่กำหนด ต้องการให้ทำอะไรก็ให้ระบุลงไป

Syntax:
```
DECODE( expression , search , result [, search , result]... [, default] )
```
โดยที่

expression คือ ค่าที่จะเอามาใช้เปรียบเทียบเงื่อนไข
compare_value คือ ค่าที่สามารถจะมาเปรียบเทียบได้กับ expression
return_value คือค่าที่จะแสดงออกมาเมื่อ ค่าของ expression = compare_value
default_return_value คือค่าที่จะแสดง เมื่อ expression <> compare_value

ลองมาดูตัวอย่างการใช้งานกัน

สมมติเรามีข้อมูลคะแนนภาษาอังกฤษของนักศึกษาใหม่ซึ่งประกอบด้วย 6 ฟิลด์ข้อมูลแสดงดังตัวอย่างด้านล่าง
ข้อมูล: ตาราง TEST_NEW_STUDENT เป็นตัวอย่างข้อมูลคะแนนภาษาอังกฤษของนักศึกษาใหม่จำนวน 16 รายการ

ตัวอย่างคิวรีในการใช้ฟังก์ชัน DECODE

SELECT fac_id,
DECODE (
fac_id,
’06’, ‘คณะวิศวกรรมศาสตร์’,
’08’, ‘คณะวิทยาศาสตร์’,
’12’, ‘คณะทรัพยากรธรรมชาติ’,’null’)
fac_name,stud_name_thai,stud_sname_thai,sn_code,eng_score
FROM test_new_student;

ผลลัพธ์ที่ได้

โดยความหมายของด้านบนคือ

ถ้าค่า fac_id ของตาราง test_new_student เป็น ’06’ ก็ให้แสดง คำว่า ‘คณะวิศวกรรมศาสตร์’

ถ้าค่า fac_id ของตาราง test_new_student เป็น ’08’ ก็ให้แสดง คำว่า ‘คณะคณะวิทยาศาสตร์’

ถ้าค่า fac_id ของตาราง test_new_student เป็น ’12’ ก็ให้แสดง คำว่า ‘คณะทรัพยากรธรรมชาติ’

โดยปกติถ้าเราเขียนคิวรีเพื่อนับจำนวนนักศึกษาโดยแยกตามคณะที่สังกัดของนักศึกษา เราสามารถเขียนคิวรีได้ดังนี้

SELECT fac_id, COUNT (*) fac_summary
FROM test_new_student
GROUP BY fac_id;

ผลลัพธ์ที่ได้ : แสดงจำนวนนักศึกษาแยกตามคณะที่สังกัด การแสดงผลปกติจะแสดงในรูปแบบแถว

คราวนี้ลองมาตั้งโจทย์ใหม่โดยยังมีความต้องการเหมือนเดิมคือ นับจำนวนนักศึกษาโดยแยกตามคณะที่สังกัดของนักศึกษา แต่เปลี่ยนจากการแสดงในรูปแบบแถวให้แสดงในรูปแบบคอลัมภ์แทน

จากโจทย์เราสามารถประยุกต์ใช้งานฟังก์ชัน sum และ decode เพื่อจะแปลงข้อมูลในรูปแบบแถวให้อยู่ในรูปแบบ
คอลัมภ์ได้ โดยเราสามารถเขียนคิวรีได้ดังนี้

SELECT ‘TotalStudent’ faculty,
SUM (DECODE (fac_id, ’06’, 1)) Engineering,
SUM (DECODE (fac_id, ’08’, 1)) Science,
SUM (DECODE (fac_id, ’12’, 1)) Natural_Resources
FROM test_new_student

ผลลัพธ์ที่ได้ : แสดงข้อมูลจำนวนนักศึกษาซึ่งประกอบด้วย จำนวนนักศึกษาคณะวิศวกรรมศาสตร์ คณะวิทยาศาสตร์ และคณะทรัพยากรธรรมชาติ ตามลำดับ โดยแสดงข้อมูลในรูปแบบคอลัมภ์
June 26, 2018
ปัญหาการเชื่อมต่อฐานข้อมูล Oracle ไม่ได้ เนื่องจาก Listener
สำหรับผู้ดูแลระบบที่เคยทำงานดูแลฐานข้อมูล Oracle คงเคยเจอกับปัญหาการเชื่อมต่อไปยังฐานข้อมูลไม่ได้ ซึ่งเมื่อไล่เรียงหาสาเหตุแล้ว ก็มีมากมายหลายสาเหตุด้วยกัน เช่น ปัญหาที่ตัวฐานข้อมูลเอง หรือ ปัญหาทางด้านระบบเครือข่าย ฯลฯ ซึ่งในบทความนี้จะเล่าถึงประสบการณ์ที่ได้พบเจอปัญหานี้เช่นกัน โดยสาเหตุนั้นเกี่ยวกับตัว Listener

เหตุการณ์ที่เกิดขึ้นช่วงที่พบปัญหา

ได้รับแจ้งจากผู้ใช้ว่าไม่สามารถเข้าใช้งานระบบได้ ซึ่งเมื่อได้รับแจ้งจึงทำการตรวจสอบและพบว่าปัญหาเกิดจากการที่ระบบไม่สามารถเชื่อมต่อไปยังฐานข้อมูลที่พัฒนาด้วย Oracle ได้ โดยที่ระบบเครือข่ายยังใช้งานได้ปกติ ดังนั้นสิ่งที่ทำต่อมาคือการตรวจสอบเฉพาะฐานข้อมูลว่าเกิดปัญหาอะไรขึ้น โดยสิ่งที่ทำคือ
- ทดลองเชื่อมต่อผ่าน SQL Developer ซึ่งเป็นเครื่องมือหนึ่งที่ใช้สำหรับบริหารจัดการฐานข้อมูล Oracle ผลลัพธ์คือ ไม่สามารถเชื่อมต่อได้ โดยมีข้อความแจ้งเตือนว่า socket timeout
- ทดลองเชื่อมต่อผ่าน Enterprise Manager ซึ่งเป็นเครื่องมือสำหรับบริหารจัดการฐานข้อมูลอีกตัวซึ่งได้ติดตั้งมาพร้อมกับตอนที่สร้างฐานข้อมูล ซึ่งก็ไม่สามารถเชื่อมต่อได้เช่นกัน และได้ลองสั่ง Startup DB ฐานข้อมูล จากเครื่องมือตัวนี้ อาการผิดปกติก็ยังคงเหมือนเดิม
- ทดลองเชื่อมต่อผ่าน ผ่าน SQL Plus ซึ่งใช้งานผ่าน command prompt ปราฏว่าสามารถเชื่อมต่อได้
- ใช้คำสั่ง expdp เพื่อ dump ข้อมูลมาสำรองไว้ก่อน สามารถทำได้เช่นกัน
- แสดงว่าตัวฐานข้อมูลยังสามารถเข้าถึงได้อยู่ จึงพุ่งประเด็นไปที่ Listener เพราะเป็นตัวกลางที่จัดการให้ระบบหรือเครื่องมือภายนอกสามารถเชื่อมต่อมายังฐานข้อมูลได้
- ทดลอง config Listener ใหม่ ด้วยเครื่องมือ Net Configuration Assistant พบว่าใช้เวลาตั้งค่านานมาก แต่ก็สามารถ config ได้
- ลอง restart Listener และ Windows แล้ว อาการยังเหมือนเดิม
- ใช้คำสั่งบน command prompt ที่เกี่ยวกับ Listener ได้แก่ lsnrctl และ tnsping เพื่อตรวจสอบ Listener และ TNS พบว่าใช้เวลานานมาก จึงสันนิษฐานว่า อาจจะเป็นปัญหาที่ Listener แน่นอน เพราะยังเข้าถึงฐานข้อมูลผ่านช่องทางบางช่องทางได้
การแก้ไขปัญหา
- เมื่อได้ข้อมูลจากการตรวจสอบแล้ว ว่ามีปัญหาจาก Listener จึงได้ค้นข้อมูลใน internet เกี่ยวกับการเชื่อมต่อ Listener ที่ใช้เวลานาน พบว่ามีผู้แนะนำให้ตรวจสอบที่ไฟล์ listener.log ใน \app\Administrator\diag\tnslsnr\Win-User\listener\trace (ตัวอย่างนี้จะเป็น path ที่เป็น oracle 11)
  ***หมายเหตุ โฟลเดอร์ Win-User เป็นโฟลเดอร์ที่ต้ังตามชื่อ user ใน Windows
- ถ้าขนาดไฟล์ถึง 4 GB จะมีปัญหาได้ ให้ทำการหยุด service ของ Listener แล้ว rename ชื่อไฟล์ listener.log ใหม่ เมื่อ start listener แล้ว ไฟล์ listener.log ก็จะถูกสร้างมาใหม่
- เมื่อได้ทำการตรวจสอบพบว่าไฟล์มีขนาด 4 GB จริง และได้ทำการแก้ไขตามวิธีที่มีการแนะนำ พบว่าสามารถเชื่อมต่อกับฐานข้อมูล Oracle ได้เป็นปกติ
- สำหรับกรณีศึกษาที่มีผู้นำเสนอไว้สามารถเข้าดูได้ผ่าน URL https://vjdba.wordpress.com/2013/09/24/93/
June 24, 2018
Oracle / PLSQL: LISTAGG Function
LISTAGG เป็นฟังก์ชันการรวมสตริงของ Oracle ที่นำค่าข้อมูลในคอลัมภ์ที่ระบุมาเรียงต่อกัน และดำเนินการจัดเรียงลำดับของข้อมูลที่นำมาต่อกันตามคอลัมภ์ใน order_by_clause ซึ่งฟังก์ชัน LISTAGG สามารถแสดงผลได้หลายรูปแบบดังนี้
- Single-set aggregate function : LISTAGG เป็นฟังก์ชันที่ดำเนินการกับข้อมูลแล้วคืนค่ากลับมาเพียงเร็คคอร์ดเดียว
- Group-set aggregate function : LISTAGG เป็นฟังก์ชันที่ดำเนินการกับข้อมูลและคืนค่ากลับมาหลายเร็คคอร์ดตามกลุ่มที่กำหนดในเงื่อนไข GROUP BY
- Analytic function : LISTAGG เป็นฟังก์ชันที่ดำเนินการจัดแยกผลการค้นหาออกเป็นกลุ่มตามเงื่อนไขที่กำหนดใน query_partition_clause
Syntax

LISTAGG (measure_column [, 'delimiter']) WITHIN GROUP (order_by_clause) [OVER (query_partition_clause)]
โดยที่
- measure_column คือ คอลัมภ์ที่ต้องการนำค่าข้อมูลมาเรียงต่อกัน โดยจะดำเนินเฉพาะค่าที่ไม่เป็น null เท่านั้น
- delimiter คือ ตัวเลือกที่ให้สามารถระบุตัวคั่นระหว่างค่าข้อมูลที่จะนำมาเรียงต่อกัน
- order_by_clause คือ ค่าที่นำมาเรียงต่อกันจะเรียงตามค่าในคอลัมภ์ที่กำหนดใน order_by_clause
ตัวอย่างการใช้งาน

สมมติเรามีข้อมูลคะแนนภาษาอังกฤษของนักศึกษาใหม่ซึ่งประกอบด้วย 6 ฟิลด์ข้อมูลแสดงดังตัวอย่างด้านล่าง
ข้อมูล: ตาราง TEST_NEW_STUDENT เป็นตัวอย่างข้อมูลคะแนนภาษาอังกฤษของนักศึกษาใหม่จำนวน 16 รายการ
Single-set aggregate function

ตัวอย่างในส่วนนี้จะแสดงชื่อ-นามสกุลนักศึกษาทุกคนที่อยู่ในคณะ ’06’ โดยจัดเรียงลำดับข้อมูลตามชื่อนักศึกษา(stud_name_thai)

SELECT LISTAGG (stud_name_thai || ‘ ‘ || stud_sname_thai, ‘,’)
WITHIN GROUP (ORDER BY stud_name_thai) “NAME LIST”
FROM test_new_student
WHERE fac_id = ’06’;

ผลลัพธ์ที่ได้ : จะคืนค่ากลับมาเพียงเร็คคอร์ดเดียวตามเงือนไขที่ระบุ

Group-set aggregate function

ตัวอย่างในส่วนนี้จะแสดงชื่อ-นามสกุลนักศึกษาทุกคนแยกตามคณะที่สังกัด โดยจัดเรียงลำดับข้อมูลตามชื่อนักศึกษา(stud_name_thai)

SELECT fac_id,LISTAGG (stud_name_thai || ‘ ‘ || stud_sname_thai, ‘,’)
WITHIN GROUP (ORDER BY stud_name_thai) “NAME LIST”
FROM test_new_student
GROUP BY fac_id
ORDER BY fac_id;

ผลลัพธ์ที่ได้ : จะคืนค่ากลับมาหลายเร็คคอร์ดตามเงื่อนไข GROUP BY

Analytic function

ตัวอย่างในส่วนนี้จะแสดงข้อมูลเฉพาะคนที่ได้คะแนนมากกว่า 60 และจัดแยกผลการค้นหาตามคณะที่สังกัด

SELECT fac_id,stud_name_thai||’ ‘||stud_sname_thai student_name,eng_score,
LISTAGG (stud_name_thai||’ ‘||stud_sname_thai, ‘,’)
WITHIN GROUP (ORDER BY eng_score, stud_name_thai)
OVER (PARTITION BY fac_id) AS “NAME_LIST”
FROM test_new_student
WHERE eng_score > 60
ORDER BY fac_id, eng_score

ผลลัพธ์ที่ได้ : ดำเนินการจัดแยกผลการค้นหาออกเป็นกลุ่มตามเงื่อนไขที่กำหนดใน PARTITION BY
June 11, 2018
Oracle : ROLLUP Extension to GROUP BY

การจัดกลุ่มข้อมูลด้วย GROUP BY

เมื่อต้องการจัดกลุ่มข้อมูล เราสามารถใช้ประโยค GROUP BY เพื่อทำการแบ่งออกเป็นรายการย่อย ๆ การคิวรีที่รวมประโยค GROUP BY จะเรียกว่าการคิวรีแบบกลุ่ม เพราะว่ามันจะรวมกลุ่มข้อมูลจากคำสั่ง SELECT แล้วสร้างเป็นเร็คคอร์ดสรุปเพียงเร็คคอร์ดเดียวให้กับแต่ละกลุ่ม

ส่วนขยาย ROLLUP

ในการคิวรีข้อมูลเราสามารถค้นหาแถวข้อมูลผลรวมของแต่ละกลุ่ม รวมถึงสรุปผลรวมที่มาจากผลลัพธ์ทั้งหมดในตอนท้ายของการคิวรีอีกทีได้ โดยใช้ส่วนขยายที่เรียกว่า ROLLUP ซึ่งมีรูปแบบการใช้งานดังนี้

ROLLUP Syntax

SELECT…GROUP BY ROLLUP(grouping_column_reference_list)

ตัวอย่างการใช้งาน

สมมติเรามีข้อมูลคะแนนภาษาอังกฤษของนักศึกษาใหม่ซึ่งประกอบด้วย 6 ฟิลด์ข้อมูลแสดงดังตัวอย่างด้านล่าง
ข้อมูล: ตาราง TEST_NEW_STUDENT เป็นตัวอย่างข้อมูลคะแนนภาษาอังกฤษของนักศึกษาใหม่จำนวน 16 รายการ

โจทย์ เราต้องการนับจำนวนนักศึกษาใหม่แยกตามคณะที่นักศึกษาสังกัด และรหัส สน.ที่สอบได้ เราสามารถใช้ประโยค GROUP BY เพื่อแก้ปัญหาข้อนี้ ดังตัวอย่างคิวรีต่อไปนี้

SELECT fac_id, sn_code, COUNT (*) NUM_STUDENT
FROM test_new_student
GROUP BY fac_id, sn_code
ORDER BY fac_id, sn_code;

ผลลัพธ์ที่ได้ : แสดงจำนวนนักศึกษาแยกตามคณะที่สังกัด และรหัส สน.ที่สอบได้ แสดงเรียงตามรหัสคณะ และรหัส สน.ที่สอบได้

โจทย์ หากเราต้องการนับจำนวนนักศึกษาใหม่แยกตามคณะที่นักศึกษาสังกัด และรหัส สน.ที่สอบได้ พร้อมทั้งแสดงผลรวมของนักศึกษาแต่ละคณะ และหาผลรวมของนักศึกษาทั้งหมดด้วย

เราสามารถใช้ส่วนขยายที่เรียกว่า ROLLUP เพื่อแก้ปัญหาข้อนี้ ดังตัวอย่างคิวรีต่อไปนี้

SELECT fac_id, sn_code, COUNT (*) NUM_STUDENT
FROM test_new_student
GROUP BY ROLLUP (fac_id, sn_code)
ORDER BY fac_id, sn_code;

ผลลัพธ์ที่ได้ : แสดงจำนวนนักศึกษาแยกตามคณะที่สังกัด และรหัส สน.ที่สอบได้ แสดงเรียงตามรหัสคณะ และผลรวมของนักศึกษาแต่ละคณะ และผลรวมนักศึกษาทั้งหมด

ลองใช้งานกันดูนะคะ สำหรับส่วนขยาย ROLLUP เพื่อหาสรุปผลรวมที่มาจากผลลัพธ์ทั้งหมดในตอนท้ายของการคิวรี

June 11, 2018

Uploading Files into Database with ASP.NET MVC

การ upload file มีหลายรูปแบบ ไม่ว่าจะเป็นการ upload file แบบ copy ไว้บน server หรือจะเป็นการบันทึกลงในฐานข้อมูลเลยโดยตรง วันนี้จะขอนำเสนอในส่วนของการ upload file และบันทึกลงฐานข้อมูล โดยใช้ ASP.NET MVC ดังนี้

กำหนดไฟล์ที่ต้องการ upload ให้มีรูปแบบดังนี้

public class UploadModel

{

[Required]

public HttpPostedFileBase File { get; set; }

}

ในส่วนของ View

<a type=”submit” href=”#” onclick=”uploadConfirm();” class=”btn btn-info”><span class=”glyphicon glyphicon-save”></span> Upload</a>

</form>

ในส่วนของ java script (สำหรับเรียก Controller)

function uploadConfirm() {

$.ajax({

type: ‘POST’,

contentType: ‘application/json; charset=utf-8’,

url: ‘@Url.Action(“CheckDataBeforeUpload”, “NoteUpload”)’,

data: “{ ‘periodID’:’” + $(‘#selectedlistPeriods’).val() +

“‘ ,’financeID’:’” + $(‘#selectedlistFinance’).val() +

“‘ }”,

success: function (resultSave) {

error: function (data) {

alert(data);

}

});

}

ในส่วนของ Controller

public async Task<ActionResult> UploadFile(UploadModel model, FormCollection form)

{

string fileName = Path.GetFileName(model.File.FileName); //แสดงชื่อไฟล์

string strFileName = Path.GetFileNameWithoutExtension(fileName); //แสดงชื่อไฟล์

string contentType = model.File.ContentType; //แสดงนามสกุลไฟล์

string FileExtension = fileName.Substring(fileName.LastIndexOf(‘.’) + 1).ToLower();

using (Stream fs = model.File.InputStream) //

{

using (BinaryReader br = new BinaryReader(fs))

{

byte[] bytes = br.ReadBytes((Int32)fs.Length);

//TO DO: Code ในส่วนที่ต้องการ insert ข้อมูลลง Database

}

return RedirectToAction(“Index”);

}

จากตัวอย่างข้างต้น จะเป็นการ upload file ลงฐานข้อมูลโดยตรงในส่วนของรูปแบบการพัฒนาแบบ MVC ผู้เขียนหวังว่าอาจจะเป็นอีกทางเลือกหนึ่งของผู้พัฒนา ในการนำไปพัฒนาโปรแกรมต่อไปนะคะ ^_^

แหล่งอ้างอิง

https://stackoverflow.com/questions/15106190/uploading-files-into-database-with-asp-net-mvc

https://stackoverflow.com/questions/21677038/mvc-upload-file-with-model-second-parameter-posted-file-is-null/21677156

April 24, 2018

Configuration of TCP/IP with SSL and TLS for Database Connections
สิ่งที่ต้องเตรียม
1. Oracle Database Server ในตัวอย่างนี้ใช้ Oracle database บน Oracle Enterprise Linux 7
2. Oracle Database Client ในตัวอย่างใช้ Windows Server 2008 R2
เริ่ม
- ฝั่ง Server
- เข้าระบบด้วยบัญชีผู้ใช้ oracle หรือบัญชีที่เป็นเจ้าของ Oracle Database
- เปิด terminal สร้าง Oracle Wallet ด้วยคำสั่ง
  - mkdir -p /u01/app/oracle/wallet
- ต่อด้วย
  - orapki wallet create -wallet "/u01/app/oracle/wallet" -pwd WalletPasswd123 -auto_login_local
  - โดย -pwd WalletPasswd123 ตัว WalletPasswd123 คือรหัสผ่านที่ใช้ป้องกันการเข้าถึงข้อมูลต่างๆ ใน wallet กฎการตั้งมีอยู่คือ ยาวอย่างน้อย 8 ตัวอักษร ไม่จำกัดความยาว จำเป็นต้องตั้งให้ตัวเลขผสมตัวอักษร
- สร้าง Certificate และใส่เข้าไปใน wallet ด้วยคำสั่ง
  - orapki wallet add -wallet "/u01/app/oracle/wallet" -pwd WalletPasswd123 \
    -dn "CN=`hostname`" -keysize 1024 -self_signed -validity 3650
- ตรวจสอบด้วยคำสั่ง
  - orapki wallet display -wallet "/u01/app/oracle/wallet" -pwd WalletPasswd123
- ได้ผลลัพธ์ประมาณ
  - Oracle PKI Tool : Version 12.1.0.2
    Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.
    
    Requested Certificates:
    User Certificates:
    Subject: CN=reis.psu.ac.th
    Trusted Certificates:
    Subject: CN=reis.psu.ac.th
- Export Certificate เพื่อนำไปใช้กับเครื่อง Oracle Client ด้วยคำสั่ง
  - orapki wallet export -wallet "/u01/app/oracle/wallet" -pwd WalletPasswd123 \
    -dn "CN=`hostname`" -cert /tmp/server-certificate.crt
- ตรวจสอบแฟ้มที่ export ไปด้วยคำสั่ง
  - cat /tmp/server-certificate.crt
- ได้ผลลัพธ์ประมาณว่า
  - -----BEGIN CERTIFICATE-----
    MIIBoTCCAQoCAQAwDQYJKoZIhvcNAQEEBQAwGTEXMBUGA1UEAxMOcmVpcy5wc3UuYWMudGgwHhcN
    MTgwMTA5MDcyNTA2WhcNMjgwMTA3MDcyNTA2WjAZMRcwFQYDVQQDEw5yZWlzLnBzdS5hYy50aDCB
    nzANBgkqhkiG9w0BAQEFAAOBjQAwgYkCgYEAj4x2/NviDaTlXuEJt0kZARY5fHiT2SiVX+a18hai
    I0stoUhKKefjOCgB85iuqjIk0rvcGXI0KXkbenTy2t40A+qGxB04mBhCLKaKeIe67BZKR6Zyw1dd
    oaesoaWChC01b+IW1X5WWtC53UxpIZQ4Zktj41sLGUnarIr9+9HFwncCAwEAATANBgkqhkiG9w0B
    AQQFAAOBgQAqSCF2Y8uyM4rSQHUC8MKEl3Ia3NJKnigMOUzDc2fP7grSaoeuQ4NvIntTD+s+IT5Y
    EpLVND4kSHFTwGRq0Py/ig8ybXZCXfHtvNZh7ZGziL/sYt5/8xYi/tOBKwVanBTUaseKIMovtmd7
    UyoOKrX8YBoFsB3UPRLudmFsksXRXw==
    -----END CERTIFICATE-----
- ฝั่ง Client
- เข้าระบบด้วยบัญชีผู้ใช้ administrator
- เปิด cmd แล้วสร้าง wallet ด้วยคำสั่ง
  - mkdir c:\app\oracle\wallet
สร้าง Certificate ด้วยคำสั่ง
- - orapki wallet add -wallet "c:\app\oracle\wallet" -pwd WalletPasswd123 -dn "CN=%computername%" -keysize 1024 -self_signed -validity 3650
  - โดย WalletPasswd123 คือพาสเวิร์ดที่ใช้ล็อค wallet ไม่จำเป็นต้องเหมือนกับของ Server
- ตรวจสอบด้วยคำสั่ง
  - orapki wallet display -wallet "c:\app\oracle\wallet" -pwd WalletPasswd123
- ได้ผลลัพธ์ประมาณว่า
  - Oracle PKI Tool : Version 12.2.0.1.0 Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved.
    
    Requested Certificates:
    User Certificates:
    Subject: CN=ENERAZAN
    Trusted Certificates:
    Subject: CN=ENERAZAN
- Export Certificate เพื่อนำไปใช้กับเครื่อง Oracle Database Server ด้วยคำสั่ง
  - orapki wallet export -wallet "c:\app\oracle\wallet" -pwd WalletPasswd123 -dn "CN=%computername%" -cert c:\client-certificate.crt
- ตรวจสอบแฟ้มที่ export ไปด้วยคำสั่ง
  - more c:\client-certificate.crt
- ได้ผลลัพธ์ประมาณว่า
  - -----BEGIN CERTIFICATE-----MIIBmTCCAQICAQAwDQYJKoZIhvcNAQEEBQAwEzERMA8GA1UEAxMIRU5FUkFaQU4wIhgPMDE1NTAxMDkwNzMzNDlaGA8wMTQ1MDEwNzA3MzM0OVowEzERMA8GA1UEAxMIRU5FUkFaQU4wgZ8wDQYJKoZIhvcNAQEBBQADgY0AMIGJAoGBAJHEEnB4kiI82QVyt0/GlrILF9dwd/jVRlgCQkLmLrpF6RKrIUntzvygyVhtTybeaShH751gYvtl1NFxDUWkBnfS5u2DN6ATP2WpNQgGM/skPWZOxoewy6OeeFsDPNUJTsI8+F5DIwtNhZcdQtBexZ/D3ALkSboR87t2Vy8OJpD7AgMBAAEwDQYJKoZIhvcNAQEEBQADgYEAV7xLLslRn2srS4A4Ks1KiuUWI/ackyNK7UiWgWD/by5Vx4Zw+0x3hdLzQllDR2O5Nef9it5WyYWrDQ47RadqMEVVfTGuAqQn47n/t+v24ljwm2yZ36Tj2lDVaFbE/ZtRyzPKgIwUy6RIt9XVX39vXFJVaENCHzZ0R3hs2bx1K3o=-----END CERTIFICATE-----
- ทำการส่งไฟล์ certificate แลกกันระหว่าง Server กับ Client โดยใช้วิธีการที่ชื่นชอบของแต่คน เมื่อแลกไฟล์กันเสร็จแล้ว
- ฝั่ง Server import certificate ด้วยคำสั่ง
  - orapki wallet add -wallet "/u01/app/oracle/wallet" -pwd WalletPasswd123 \ -trusted_cert -cert /tmp/client-certificate.crt
- สามารถตรวจสอบด้วยคำสั่ง
  - orapki wallet display -wallet "/u01/app/oracle/wallet" -pwd WalletPasswd123
- ได้ผลลัพธ์ประมาณ
  - Oracle PKI Tool : Version 12.1.0.2 Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.
    
    Requested Certificates:
    User Certificates:
    Subject: CN=reis.psu.ac.th
    Trusted Certificates:
    Subject: CN=ENERAZAN
    Subject: CN=reis.psu.ac.th
- ฝั่ง Client import certificatae ด้วยคำสั่ง
  - orapki wallet add -wallet "c:\app\oracle\wallet" -pwd WalletPasswd123 -trusted_cert -cert c:\server-certificate.crt
- สามารถตรวจสอบด้วยคำสั่ง
  - orapki wallet display -wallet “c:\app\oracle\wallet” -pwd WalletPasswd123
- ได้ผลลัพธ์ประมาณว่า
  - Oracle PKI Tool : Version 12.2.0.1.0 Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved.
    
    Requested Certificates:
    User Certificates:
    Subject: CN=ENERAZAN
    Trusted Certificates:
    Subject: CN=reis.psu.ac.th
    Subject: CN=ENERAZAN
- ฝั่ง Server แก้ไขแฟ้ม $ORACLE_HOME/network/admin/sqlnet.ora ด้วย editor ที่ชื่นชอบ
  - เพิ่มคำสั่งต่อไปนี้
  - WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY = /u01/app/oracle/wallet) ) )
    
    SQLNET.AUTHENTICATION_SERVICES = (TCPS,NTS,BEQ)
    SSL_CLIENT_AUTHENTICATION = FALSE
    SSL_CIPHER_SUITES = (SSL_RSA_WITH_AES_256_CBC_SHA, SSL_RSA_WITH_3DES_EDE_CBC_SHA)
แก้ไขแฟ้ม $ORACLE_HOME/network/admin/listener.ora
- - เพิ่มข้อความส่วนที่เป็นตัวหนาตามตำแหน่งต่อไปนี้
  - LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = reis.psu.ac.th)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) (ADDRESS = (PROTOCOL = TCPS)(HOST = reis.psu.ac.th)(PORT = 2484)) ) )
    
    SSL_CLIENT_AUTHENTICATION = FALSE
    
    WALLET_LOCATION =
    (SOURCE =
    (METHOD = FILE)
    (METHOD_DATA =
    (DIRECTORY = /u01/app/oracle/wallet)
    )
    )
- restart listener ด้วยคำสั่ง
  - lsnrctl stop
  - lsnrctl start
- ฝั่ง Client แก้ไขแฟ้ม C:\app\client\Administrator\product\12.2.0\client_1\network\admin\sqlnet.ora โดยเพิ่มข้อความต่อไปนี้
  - WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY = c:\app\oracle\wallet) ) )
    
    SQLNET.AUTHENTICATION_SERVICES = (TCPS,NTS)
    SSL_CLIENT_AUTHENTICATION = FALSE
    SSL_CIPHER_SUITES = (SSL_RSA_WITH_AES_256_CBC_SHA, SSL_RSA_WITH_3DES_EDE_CBC_SHA)
- แก้ไขแฟ้ม C:\app\client\Administrator\product\12.2.0\client_1\network\admin\tnsnames.ora เพิ่มข้อความต่อไปนี้
  - REIS_SSL = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCPS)(HOST = reis.psu.ac.th)(PORT = 2484)) ) (CONNECT_DATA = (SERVICE_NAME = reis.psu.ac.th) ) )
- ทดสอบใช้งาน
- ที่ Client พิมพ์คำสั่ง
  - sqlplus scott/tiger@reis_ssl
- ได้ผลลัพธ์ประมาณว่า
  - SQL*Plus: Release 12.2.0.1.0 Production on Wed Feb 7 16:54:05 2018 Copyright (c) 1982, 2016, Oracle. All rights reserved.
    
    Last Successful login time: Wed Feb 07 2018 16:52:50 +07:00
    
    Connected to:
    Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
    With the Partitioning, OLAP, Advanced Analytics and Real Application Testing opt
    ions
    
    SQL>
- หรือต่อด้วย SQL Developer ต้องตั้งค่าการเชื่อมต่อเป็นดังภาพ
- จากภาพเลือก Connection Type เป็น Advanced และใช้ช่อง Custom JDBC URL ใส่เป็น jdbc:oracle:oci:/@reis_ssl
- เนื่องจากไม่สามารถเชื่อมต่อผ่าน tns ได้ ดังภาพ
- วิธีเซ็ตค่าแบบนี้ใช้ได้กับ Oracle Database 10G R2 เป็นต้นมา
- จบขอให้สนุก
ที่มา:
https://oracle-base.com/articles/misc/configure-tcpip-with-ssl-and-tls-for-database-connections
February 7, 2018

Category: Database

ตั้งค่าฐานข้อมูล

สร้างผู้ใช้ทดสอบ

สร้าง Policy

กำหนด component ของ label

สร้าง Table ทดสอบ

สร้าง label function

สั่งให้ Policy ทำงานกับ table ที่สร้าง

เริ่มสร้าง label

สั่งให้ Policy ทำงานอีกครั้ง

สั่งให้ label ทำงานกับข้อมูลในแต่ละแถว

สร้าง user ทดสอบ

ทดสอบ Label Security

จบขอให้สนุก