Oracle 11g透明数据加密安全特性解析

核心提示： 清单4 创建加密的SecureFile LOB存储敏感信息创建一个用户(MIPS)DROPUSERmipsCASCADE;CREATEUSERmipsIDENTIFIEDBYmipsDEFAULTTABLESPACEpatimagesTEMPORARYTABLESPACEtempQUOTA

清单4 创建加密的SecureFile LOB存储敏感信息　

创建一个用户(MIPS)

DROP　USER　mips　CASCADE;
　
CREATE　USER　mips
IDENTIFIED　BY　mips
DEFAULT　TABLESPACE　patimages
TEMPORARY　TABLESPACE　temp
QUOTA　UNLIMITED　ON　patimages;
GRANT　CONNECT,　RESOURCE　TO　mips;
GRANT　EXECUTE　ANY　PROCEDURE　to　mips;
GRANT　CREATE　ANY　DIRECTORY　TO　mips;
创建一个新的目录
DROP　DIRECTORY　mips_imgs;
CREATE　OR　REPLACE　DIRECTORY　mips_imgs
AS　'/home/oracle/dicom';
GRANT　READ　ON　DIRECTORY　mips_imgs　TO　mips;
创建一个新表MIPS.PATIENT_IMAGES使用SecureFile　LOB存储DICOM文件。
　DROP　TABLE　mips.patient_images　PURGE;
CREATE　TABLE　mips.patient_images　(
patient_id　NUMBER
,name　VARCHAR2(30)
,ssn　VARCHAR2(11)
,dob　DATE
,dicom_image　ORDSYS.ORDDICOM
,anonymous　ORDSYS.ORDDICOM
,thumbprint　ORDSYS.ORDIMAGE
)
TABLESPACE　patimages
LOB(dicom_image.source.localData)
STORE　AS　SECUREFILE　(
TABLESPACE　patimages
DISABLE　STORAGE　IN　ROW
DEDUPLICATE
COMPRESS　HIGH
CACHE　READS
)
LOB(anonymous.source.localData)
STORE　AS　SECUREFILE　(
TABLESPACE　patimages
DISABLE　STORAGE　IN　ROW
DEDUPLICATE
COMPRESS　HIGH
CACHE　READS
)
LOB(thumbprint.source.localData)
STORE　AS　SECUREFILE　(
TABLESPACE　patimages
DISABLE　STORAGE　IN　ROW
DEDUPLICATE
COMPRESS　HIGH
CACHE　READS
)
;
--　注释
COMMENT　ON　TABLE　mips.patient_images
IS　'Contains　Patient　metadata　and　DICOM　images';
COMMENT　ON　COLUMN　mips.patient_images.patient_id
IS　'Unique　identifier　for　a　Patient';
COMMENT　ON　COLUMN　mips.patient_images.name
IS　'Patient　Name';
COMMENT　ON　COLUMN　mips.patient_images.ssn
IS　'Patient　Social　Security　Number';
COMMENT　ON　COLUMN　mips.patient_images.dob
IS　'Patient　Date　of　Birth';
COMMENT　ON　COLUMN　mips.patient_images.dicom_image
IS　'DICOM　LOB';
COMMENT　ON　COLUMN　mips.patient_images.anonymous
IS　'Anonymous　DICOM　LOB';
COMMENT　ON　COLUMN　mips.patient_images.thumbprint
IS　'Thumbprint　of　DICOM　image';
--　创建索引和约束
CREATE　UNIQUE　INDEX　mips.patient_images_pk_idx
ON　mips.patient_images(patient_id)
TABLESPACE　patimages;
ALTER　TABLE　mips.patient_images
ADD　CONSTRAINT　patient_images_pk
PRIMARY　KEY　(patient_id);