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;
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;
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
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 ต่างๆ ที่สร้างในข้อที่แล้วเพื่อดูข้อมูลว่าถูกต้องหรือไม่ เพื่อความสะดวกจึงเข้าผ่านโปรแกรมสำเร็จรูปอื่นเพื่อให้ดูง่าย ได้ผลดังรูปต่อไปนี้นี้
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
cat >>fix.sql<<EOF
CREATE OR REPLACE NONEDITIONABLE TYPE "SYS"."KU$_RADM_FPTM_T" as object
(
vers_major char(1), /* UDT major version # */
vers_minor char(1), /* UDT minor version # */
numbercol number, /* number */
binfloatcol binary_float, /* binary float */
bindoublecol binary_double, /* binary double */
charcol char(1), /* fixed-size character */
varcharcol varchar2(1), /* variable-size character */
ncharcol nchar(1), /* fixed-size national character */
nvarcharcol nvarchar2(1), /* variable-size national character */
datecol date, /* date */
ts_col timestamp, /* timestamp */
tswtz_col timestamp with time zone, /* timestamp with time zone */
fpver number /* version of default fixed point values */
)
/
CREATE OR REPLACE FORCE NONEDITIONABLE VIEW "SYS"."KU$_RADM_FPTM_VIEW" OF "SYS"."KU$_RADM_FPTM_T"
WITH OBJECT IDENTIFIER (fpver) AS
select '1','0',
numbercol, binfloatcol, bindoublecol,
charcol, varcharcol, ncharcol, nvarcharcol,
datecol,ts_col,tswtz_col, fpver
from sys.radm_fptm$
where fpver=1
/
EOF
เชื่อมต่อกับฐานข้อมูลด้วย sqlplus ด้วยบัญชีผู้ใช้ / as sysdba
ที่มาที่ไปคือ ในบางครั้ง เราต้องการ grant สิทธิ์ในการเข้าถึงข้อมูลของ schema หนึ่งไปให้กับอีก schema หนึ่ง เช่น ต้องการ grant สิทธิ์การเรียกดูข้อมูลใน table ชื่อ TABLE01 ของ schema ที่ชื่อ SCHEMA01 ให้ schema ที่ชื่อ SCHEMA02 สามารถเรียกดูข้อมูลได้ คำสั่งที่ใช้ในการ grant คือGRANT SELECT ON SCHEMA01.TABLE01 TO SCHEMA02;
แต่หากต้องการ grant หลาย ๆ table ก็ต้องพิมพ์คำสั่งเหล่านี้ทีละคำสั่งซ้ำๆ ไปจนครบตามชื่อ talbe ที่ต้องการ grant ซึ่งถ้า table ที่ต้องการ grant มีเป็นจำนวนมากก็จะยิ่งใช้เวลามากยิ่งขึ้น และอีกปัญหาคือ มีโอกาสพิมพ์ชื่อ table ผิด
เทคนิคในการสร้างสคริปต์เพื่อช่วยให้การทำงานรวดเร็วขึ้นจะมีรูปแบบดังนี้คือSELECT ‘GRANT SELECT ON OWNER_SCHEMA.’ || OBJECT_NAME || ‘ TO OTHER_SCHEMA;’
FROM ALL_OBJECTS
WHERE OBJECT_TYPE = ‘TABLE’;
SELECT a.*, ROW_NUMBER () OVER (PARTITION BY fac_id ORDER BY eng_score DESC) AS val_row_number FROM test_new_student a
จุดสำคัญของ query ข้างต้นก็คือฟังก์ชัน ROW_NUMBER ซึ่งเป็นฟังก์ชันที่จะให้เลขบรรทัดของผลลัพธ์ออกมาตามการจัดกลุ่มข้อมูลหรือการเรียงลำดับที่เรากำหนดไว้ด้วยคำสั่ง OVER, PARTITION BY และ ORDER BY ที่ตามมา
SELECT * FROM (SELECT a.*, ROW_NUMBER () OVER (PARTITION BY fac_id ORDER BY eng_score DESC) AS val_row_number FROM test_new_student a) WHERE val_row_number <= 1;
ผลลัพธ์:
สรุปจากความต้องการที่ตั้งไว้ เราสามารถแก้ได้โดยใช้ Window Function ซึ่งมันสามารถหาเลขลำดับบางอย่างภายในกลุ่มข้อมูลออกมาให้ได้รูปแบบทั่วไปของ query คือ
1SELECT *, 2 WFUNCTION OVER (PARTITION BY GROUP_FIELDS ORDER BY ORDER_FIELDS) n 3FROM SOURCE
โดยที่
SOURCE คือตัวข้อมูล
WFUNCTION หมายถึง Window Function ตัวอย่างที่เราเลือกใช้คือ ROW_NUMBER()
GROUP_FIELDS คือรายการฟิลด์ที่จะแบ่งกลุ่ม เขียนเหมือนตอนที่เราจะ group by สามารถแบ่งด้วยหลายฟิลด์ก็ได้
ORDER_FIELDS คือรายการฟิลด์ที่ใช้เรียงลำดับ, สามารถเรียงด้วยหลายฟิลด์ก็ได้, ใส่ ASC หรือ DESC ได้เหมือนคำสั่ง order by ปกติเราไม่จำเป็นต้องใส่ทั้ง partition by และ order by อาจจะใส่แค่อันใดอันหนึ่ง แล้วแต่ว่าต้องการแบ่งกลุ่มหรือเรียงลำดับหรือไม่
n เป็นชื่อ alias ของผลลัพธ์ จะตั้งชื่ออย่างไรก็ได้ เมื่อเราได้เลขลำดับ n ของแต่ละกลุ่มออกมาแล้ว ทีนี้จะเอาไปหา Top N หรือพลิกแพลงยังไง ก็แล้วแต่จะ query ออกมา
