Building the SAS MDM Repository

-- create new tablespace

create tablespace qmdm_data datafile
'C:\oracle\product\11.2.0\oradata\sas\qmdm_data.dbf'   size
10M autoextend
on extent management local autoallocate; 

create tablespace qmdm_indices datafile
'C:\oracle\product\11.2.0\oradata\sas\qmdm_indices.dbf'   size
10M autoextend
on extent management local autoallocate;

-- create mdm user and grant privileges 

create user MDM identified by SASMDMpw1 default tablespace
qmdm_data quota unlimited on qmdm_data quota unlimited on qmdm_indices;
grant connect, resource, create table, create view, create procedure, 
create trigger, create session, create sequence to MDM;

--Create database and login
create database mdm
create login mdm with password = 'SASMDMpw1',CHECK_POLICY=OFF;
go

-- Create user
use mdm;
create user mdm for login mdm with default_schema=mdm;
grant connect, create table, create view, create procedure, create schema,showplan to mdm;
grant execute on schema::dbo to mdm;
go

--Create schema

create schema mdm authorization mdm;
go

-- create mdm_secure schema

create user mdm_secure identified by SASMDMpw1 default tablespace
qmdm_data quota unlimited on qmdm_data quota unlimited on qmdm_indices;
grant connect, resource, create table to mdm_secure;
grant execute on UTL_RAW to mdm_secure;

-- create secure info table

create table mdm_secure.MDM_SECURE_INFO
(
MDM_ENCRYPTION_KEY RAW(2000)
)
tablespace QMDM_DATA;

-- Insert key into table

insert into mdm_secure.mdm_secure_info
 select utl_raw.cast_to_raw('sasmdm') from dual;

-- Grant privileges to MDM user 

grant execute on DBMS_CRYPTO to MDM;
grant execute on UTL_RAW to MDM;
grant select on mdm_secure.mdm_secure_info to MDM;

use mdm;

-- Create encryption certificate and key

create master key encryption by password='SASMDMpw1';
create certificate qMDMCer with subject='sasmdm';
create symmetric key qMDMKey with algorithm=triple_des encryption
by certificate qMDMCer;
go
grant control on certificate :: [qMDMCer] to [mdm];
grant view definition on symmetric key :: [qMDMKey] to [mdm]; go