----------------------------------------------------------------------------- -- SECURITY_PRINCIPAL ----------------------------------------------------------------------------- CREATE TABLE SECURITY_PRINCIPAL ( PRINCIPAL_ID NUMBER(10,0) NOT NULL, CLASSNAME VARCHAR2(254) NOT NULL, IS_MAPPING_ONLY NUMBER(1,0) NOT NULL, IS_ENABLED NUMBER(1,0) NOT NULL, FULL_PATH VARCHAR2(254) NOT NULL, CREATION_DATE TIMESTAMP NOT NULL, MODIFIED_DATE TIMESTAMP NOT NULL, CONSTRAINT UIX_SECURITY_PRINCIPAL UNIQUE (FULL_PATH) ); ALTER TABLE SECURITY_PRINCIPAL ADD CONSTRAINT SECURITY_PRINCIPAL_PK PRIMARY KEY (PRINCIPAL_ID); ----------------------------------------------------------------------------- -- SECURITY_PERMISSION ----------------------------------------------------------------------------- CREATE TABLE SECURITY_PERMISSION ( PERMISSION_ID NUMBER(10,0) NOT NULL, CLASSNAME VARCHAR2(254) NOT NULL, NAME VARCHAR2(254) NOT NULL, ACTIONS VARCHAR2(254) NOT NULL, CREATION_DATE TIMESTAMP NOT NULL, MODIFIED_DATE TIMESTAMP NOT NULL ); ALTER TABLE SECURITY_PERMISSION ADD CONSTRAINT SECURITY_PERMISSION_PK PRIMARY KEY (PERMISSION_ID); ----------------------------------------------------------------------------- -- PRINCIPAL_PERMISSION ----------------------------------------------------------------------------- CREATE TABLE PRINCIPAL_PERMISSION ( PRINCIPAL_ID NUMBER(10,0) NOT NULL, PERMISSION_ID NUMBER(10,0) NOT NULL ); ALTER TABLE PRINCIPAL_PERMISSION ADD CONSTRAINT PRINCIPAL_PERMISSION_PK PRIMARY KEY (PRINCIPAL_ID,PERMISSION_ID); ----------------------------------------------------------------------------- -- SECURITY_CREDENTIAL ----------------------------------------------------------------------------- CREATE TABLE SECURITY_CREDENTIAL ( CREDENTIAL_ID NUMBER(10,0) NOT NULL, PRINCIPAL_ID NUMBER(10,0) NOT NULL, COLUMN_VALUE VARCHAR2(254) NOT NULL, TYPE NUMBER(5,0) NOT NULL, CLASSNAME VARCHAR2(254), UPDATE_REQUIRED NUMBER(1,0) NOT NULL, IS_ENCODED NUMBER(1,0) NOT NULL, IS_ENABLED NUMBER(1,0) NOT NULL, AUTH_FAILURES NUMBER(5,0) NOT NULL, IS_EXPIRED NUMBER(1,0) NOT NULL, CREATION_DATE TIMESTAMP NOT NULL, MODIFIED_DATE TIMESTAMP NOT NULL, PREV_AUTH_DATE TIMESTAMP, LAST_AUTH_DATE TIMESTAMP, EXPIRATION_DATE DATE ); ALTER TABLE SECURITY_CREDENTIAL ADD CONSTRAINT SECURITY_CREDENTIAL_PK PRIMARY KEY (CREDENTIAL_ID); ----------------------------------------------------------------------------- -- SSO_SITE ----------------------------------------------------------------------------- CREATE TABLE SSO_SITE ( SITE_ID NUMBER(10,0) NOT NULL, NAME VARCHAR2(254) NOT NULL, URL VARCHAR2(254) NOT NULL, ALLOW_USER_SET NUMBER(1,0) default 0, REQUIRES_CERTIFICATE NUMBER(1,0) default 0, CHALLENGE_RESPONSE_AUTH NUMBER(1,0) default 0, FORM_AUTH NUMBER(1,0) default 0, FORM_USER_FIELD VARCHAR2(128), FORM_PWD_FIELD VARCHAR2(128), REALM VARCHAR2(128), CONSTRAINT UIX_SITE_URL UNIQUE (URL) ); ALTER TABLE SSO_SITE ADD CONSTRAINT SSO_SITE_PK PRIMARY KEY (SITE_ID); ----------------------------------------------------------------------------- -- SSO_COOKIE ----------------------------------------------------------------------------- CREATE TABLE SSO_COOKIE ( COOKIE_ID NUMBER(10,0) NOT NULL, COOKIE VARCHAR2(1024) NOT NULL, CREATE_DATE TIMESTAMP NOT NULL ); ALTER TABLE SSO_COOKIE ADD CONSTRAINT SSO_COOKIE_PK PRIMARY KEY (COOKIE_ID); ----------------------------------------------------------------------------- -- SSO_SITE_TO_PRINCIPALS ----------------------------------------------------------------------------- CREATE TABLE SSO_SITE_TO_PRINCIPALS ( SITE_ID NUMBER(10,0) NOT NULL, PRINCIPAL_ID NUMBER(10,0) NOT NULL ); ALTER TABLE SSO_SITE_TO_PRINCIPALS ADD CONSTRAINT SSO_SITE_TO_PRINCIPALS_PK PRIMARY KEY (SITE_ID,PRINCIPAL_ID); ----------------------------------------------------------------------------- -- SSO_PRINCIPAL_TO_REMOTE ----------------------------------------------------------------------------- CREATE TABLE SSO_PRINCIPAL_TO_REMOTE ( PRINCIPAL_ID NUMBER(10,0) NOT NULL, REMOTE_PRINCIPAL_ID NUMBER(10,0) NOT NULL ); ALTER TABLE SSO_PRINCIPAL_TO_REMOTE ADD CONSTRAINT SSO_PRINCIPAL_TO_REMOTE_PK PRIMARY KEY (PRINCIPAL_ID,REMOTE_PRINCIPAL_ID); ----------------------------------------------------------------------------- -- SSO_SITE_TO_REMOTE ----------------------------------------------------------------------------- CREATE TABLE SSO_SITE_TO_REMOTE ( SITE_ID NUMBER(10,0) NOT NULL, PRINCIPAL_ID NUMBER(10,0) NOT NULL ); ALTER TABLE SSO_SITE_TO_REMOTE ADD CONSTRAINT SSO_SITE_TO_REMOTE_PK PRIMARY KEY (SITE_ID,PRINCIPAL_ID); ----------------------------------------------------------------------------- -- SSO_COOKIE_TO_REMOTE ----------------------------------------------------------------------------- CREATE TABLE SSO_COOKIE_TO_REMOTE ( COOKIE_ID NUMBER(10,0) NOT NULL, REMOTE_PRINCIPAL_ID NUMBER(10,0) NOT NULL ); ALTER TABLE SSO_COOKIE_TO_REMOTE ADD CONSTRAINT SSO_COOKIE_TO_REMOTE_PK PRIMARY KEY (COOKIE_ID,REMOTE_PRINCIPAL_ID); ----------------------------------------------------------------------------- -- SECURITY_USER_ROLE ----------------------------------------------------------------------------- CREATE TABLE SECURITY_USER_ROLE ( USER_ID NUMBER(10,0) NOT NULL, ROLE_ID NUMBER(10,0) NOT NULL ); ALTER TABLE SECURITY_USER_ROLE ADD CONSTRAINT SECURITY_USER_ROLE_PK PRIMARY KEY (USER_ID,ROLE_ID); ----------------------------------------------------------------------------- -- SECURITY_USER_GROUP ----------------------------------------------------------------------------- CREATE TABLE SECURITY_USER_GROUP ( USER_ID NUMBER(10,0) NOT NULL, GROUP_ID NUMBER(10,0) NOT NULL ); ALTER TABLE SECURITY_USER_GROUP ADD CONSTRAINT SECURITY_USER_GROUP_PK PRIMARY KEY (USER_ID,GROUP_ID); ----------------------------------------------------------------------------- -- SECURITY_GROUP_ROLE ----------------------------------------------------------------------------- CREATE TABLE SECURITY_GROUP_ROLE ( GROUP_ID NUMBER(10,0) NOT NULL, ROLE_ID NUMBER(10,0) NOT NULL ); ALTER TABLE SECURITY_GROUP_ROLE ADD CONSTRAINT SECURITY_GROUP_ROLE_PK PRIMARY KEY (GROUP_ID,ROLE_ID); ALTER TABLE PRINCIPAL_PERMISSION ADD CONSTRAINT FK_PRINCIPAL_PERMISSION_1 FOREIGN KEY (PERMISSION_ID) REFERENCES SECURITY_PERMISSION (PERMISSION_ID) ON DELETE CASCADE ; ALTER TABLE PRINCIPAL_PERMISSION ADD CONSTRAINT FK_PRINCIPAL_PERMISSION_2 FOREIGN KEY (PRINCIPAL_ID) REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID) ON DELETE CASCADE ; ALTER TABLE SECURITY_CREDENTIAL ADD CONSTRAINT FK_SECURITY_CREDENTIAL_1 FOREIGN KEY (PRINCIPAL_ID) REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID) ON DELETE CASCADE ; ALTER TABLE SSO_SITE_TO_PRINCIPALS ADD CONSTRAINT SSO_SITE_TO_PRINC_FK1 FOREIGN KEY (SITE_ID) REFERENCES SSO_SITE (SITE_ID) ON DELETE CASCADE ; ALTER TABLE SSO_SITE_TO_PRINCIPALS ADD CONSTRAINT SSO_SITE_TO_PRINC_FK2 FOREIGN KEY (PRINCIPAL_ID) REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID) ON DELETE CASCADE ; ALTER TABLE SSO_PRINCIPAL_TO_REMOTE ADD CONSTRAINT FK_SSO_PRINCIPAL_TO_REMOTE_1 FOREIGN KEY (PRINCIPAL_ID) REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID) ON DELETE CASCADE ; ALTER TABLE SSO_PRINCIPAL_TO_REMOTE ADD CONSTRAINT FK_SSO_PRINCIPAL_TO_REMOTE_2 FOREIGN KEY (REMOTE_PRINCIPAL_ID) REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID) ON DELETE CASCADE ; ALTER TABLE SSO_SITE_TO_REMOTE ADD CONSTRAINT FK_SSO_SITE_TO_REMOTE_1 FOREIGN KEY (SITE_ID) REFERENCES SSO_SITE (SITE_ID) ON DELETE CASCADE ; ALTER TABLE SSO_SITE_TO_REMOTE ADD CONSTRAINT FK_SSO_SITE_TO_REMOTE_2 FOREIGN KEY (PRINCIPAL_ID) REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID) ON DELETE CASCADE ; ALTER TABLE SSO_COOKIE_TO_REMOTE ADD CONSTRAINT FK_SSO_COOKIE_TO_REMOTE_1 FOREIGN KEY (COOKIE_ID) REFERENCES SSO_COOKIE (COOKIE_ID) ON DELETE CASCADE ; ALTER TABLE SSO_COOKIE_TO_REMOTE ADD CONSTRAINT FK_SSO_COOKIE_TO_REMOTE_2 FOREIGN KEY (REMOTE_PRINCIPAL_ID) REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID) ON DELETE CASCADE ; ALTER TABLE SECURITY_USER_ROLE ADD CONSTRAINT FK_SECURITY_USER_ROLE_1 FOREIGN KEY (ROLE_ID) REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID) ON DELETE CASCADE ; ALTER TABLE SECURITY_USER_ROLE ADD CONSTRAINT FK_SECURITY_USER_ROLE_2 FOREIGN KEY (USER_ID) REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID) ON DELETE CASCADE ; ALTER TABLE SECURITY_USER_GROUP ADD CONSTRAINT FK_SECURITY_USER_GROUP_1 FOREIGN KEY (GROUP_ID) REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID) ON DELETE CASCADE ; ALTER TABLE SECURITY_USER_GROUP ADD CONSTRAINT FK_SECURITY_USER_GROUP_2 FOREIGN KEY (USER_ID) REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID) ON DELETE CASCADE ; ALTER TABLE SECURITY_GROUP_ROLE ADD CONSTRAINT FK_SECURITY_GROUP_ROLE_1 FOREIGN KEY (GROUP_ID) REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID) ON DELETE CASCADE ; ALTER TABLE SECURITY_GROUP_ROLE ADD CONSTRAINT FK_SECURITY_GROUP_ROLE_2 FOREIGN KEY (ROLE_ID) REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID) ON DELETE CASCADE ;