-- CREATE THE SCHEMA (MUST BE EXECUTED AS SINGLE STATEMENT) CREATE SCHEMA PERMISSIONANALYZER; /* DROP TABLE PERMISSIONANALYZER.ACLITEM; DROP TABLE PERMISSIONANALYZER.MEMBERSHIP; DROP TABLE PERMISSIONANALYZER.MEMBER; DROP TABLE PERMISSIONANALYZER.FILEINFO; DROP TABLE PERMISSIONANALYZER.FILTER; DROP TABLE PERMISSIONANALYZER.REPORTFILTERSET; DROP TABLE PERMISSIONANALYZER.FILTERSET; DROP TABLE PERMISSIONANALYZER.REPORTHISTORY; DROP TABLE PERMISSIONANALYZER.REPORT; DROP TABLE PERMISSIONANALYZER.RDN; DROP TABLE PERMISSIONANALYZER.DOMAIN; DROP TABLE PERMISSIONANALYZER.INFO; DROP TABLE PERMISSIONANALYZER.PERMISSIONLOOKUP; DROP TABLE PERMISSIONANALYZER.SCANLOGITEM; */ -- PERMISSION LOOKUP TABLE CREATE TABLE PERMISSIONANALYZER.PERMISSIONLOOKUP (ID INT NOT NULL, P0 TINYINT NOT NULL, P1 TINYINT NOT NULL, P2 TINYINT NOT NULL, P3 TINYINT NOT NULL, P4 TINYINT NOT NULL, P5 TINYINT NOT NULL, P6 TINYINT NOT NULL, P7 TINYINT NOT NULL, P8 TINYINT NOT NULL, P9 TINYINT NOT NULL, P10 TINYINT NOT NULL, P11 TINYINT NOT NULL, P12 TINYINT NOT NULL, PRIMARY KEY (ID)); -- RDN TABLE CREATE TABLE PERMISSIONANALYZER.RDN (ID INT NOT NULL, RDN VARCHAR(1000) NOT NULL, PRIMARY KEY (ID)); -- DOMAIN TABLE CREATE TABLE PERMISSIONANALYZER.DOMAIN (ID INT NOT NULL, DOMAIN VARCHAR(255), PRIMARY KEY (ID)); -- MEMBER TABLE CREATE TABLE PERMISSIONANALYZER.MEMBER (ID INT NOT NULL, NAME VARCHAR(255), NAME_LOWERCASE VARCHAR(255), DISPLAY_NAME VARCHAR(1024), DISPLAY_NAME_LOWERCASE VARCHAR(1024), IS_GROUP BOOLEAN, IS_ENABLED BOOLEAN, GROUP_TYPE INT, RDN_ID INT, DOMAIN_ID INT, CN VARCHAR(255), IS_BUILTIN BOOLEAN, PRIMARY KEY (ID)); CREATE INDEX MEMBER_NAME_I ON PERMISSIONANALYZER.MEMBER(NAME_LOWERCASE); CREATE INDEX MEMBER_DISPLAY_NAME_I ON PERMISSIONANALYZER.MEMBER(DISPLAY_NAME_LOWERCASE); CREATE INDEX MEMBER_CN_I ON PERMISSIONANALYZER.MEMBER(CN); CREATE INDEX MEMBER_TYPE_I ON PERMISSIONANALYZER.MEMBER(GROUP_TYPE); ALTER TABLE PERMISSIONANALYZER.MEMBER ADD CONSTRAINT MEMBER_RDN_ID_FK FOREIGN KEY (RDN_ID) REFERENCES PERMISSIONANALYZER.RDN(ID); ALTER TABLE PERMISSIONANALYZER.MEMBER ADD CONSTRAINT MEMBER_DOMAIN_ID_FK FOREIGN KEY (DOMAIN_ID) REFERENCES PERMISSIONANALYZER.DOMAIN(ID); -- MEMBERSHIP TABLE CREATE TABLE PERMISSIONANALYZER.MEMBERSHIP (ID INT NOT NULL, GROUP_ID INT NOT NULL, MEMBER_ID INT NOT NULL, VIA_GROUP_ID INT, PRIMARY KEY (ID)); ALTER TABLE PERMISSIONANALYZER.MEMBERSHIP ADD CONSTRAINT MEMBERSHIP_GROUP_ID_FK FOREIGN KEY (GROUP_ID) REFERENCES PERMISSIONANALYZER.MEMBER(ID); ALTER TABLE PERMISSIONANALYZER.MEMBERSHIP ADD CONSTRAINT MEMBERSHIP_MEMBER_ID_FK FOREIGN KEY (MEMBER_ID) REFERENCES PERMISSIONANALYZER.MEMBER(ID); -- FILE TABLE CREATE TABLE PERMISSIONANALYZER.FILEINFO (ID INT NOT NULL, PARENT_ID INT, NAME VARCHAR(255) NOT NULL, NAME_LOWERCASE VARCHAR(255) NOT NULL, IS_DIRECTORY BOOLEAN NOT NULL, PARENT_FILE_IDS VARCHAR(255), IS_DACL_PROTECTED BOOLEAN NOT NULL, OWNER_ID INT, PRIMARY KEY (ID)); ALTER TABLE PERMISSIONANALYZER.FILEINFO ADD CONSTRAINT FILEINFO_OWNER_ID_FK FOREIGN KEY (OWNER_ID) REFERENCES PERMISSIONANALYZER.MEMBER(ID); CREATE INDEX FILE_NAME_I ON PERMISSIONANALYZER.FILEINFO(NAME_LOWERCASE); CREATE INDEX PARENT_FILE_IDS_I ON PERMISSIONANALYZER.FILEINFO(PARENT_FILE_IDS); CREATE INDEX PARENTID_I ON PERMISSIONANALYZER.FILEINFO(PARENT_ID) -- ACLITEM TABLE CREATE TABLE PERMISSIONANALYZER.ACLITEM (ID INT NOT NULL, MEMBER_ID INT NOT NULL, FILEINFO_ID INT NOT NULL, PERMISSION_ID INT NOT NULL, INHERITED_FROM_FOLDER_ID INT, IS_DENY BOOLEAN NOT NULL, FLAG_DIRECTORY_INHERIT BOOLEAN NOT NULL, FLAG_FILE_INHERIT BOOLEAN NOT NULL, FLAG_INHERIT_ONLY BOOLEAN NOT NULL, FLAG_NO_PROPAGATE_INHERIT BOOLEAN NOT NULL, FLAG_INHERITED_ACE BOOLEAN NOT NULL, GENERIC_ALL BOOLEAN NOT NULL, GENERIC_EXECUTE BOOLEAN NOT NULL, GENERIC_WRITE BOOLEAN NOT NULL, GENERIC_READ BOOLEAN NOT NULL, IS_DIRECTORY BOOLEAN NOT NULL, PRIMARY KEY (ID)); ALTER TABLE PERMISSIONANALYZER.ACLITEM ADD CONSTRAINT ACLITEM_MEMBER_ID_FK FOREIGN KEY (MEMBER_ID) REFERENCES PERMISSIONANALYZER.MEMBER(ID); ALTER TABLE PERMISSIONANALYZER.ACLITEM ADD CONSTRAINT ACLITEM_FILEINFO_ID_FK FOREIGN KEY (FILEINFO_ID) REFERENCES PERMISSIONANALYZER.FILEINFO(ID) ON DELETE CASCADE; ALTER TABLE PERMISSIONANALYZER.ACLITEM ADD CONSTRAINT ACLITEM_PERMISSION_ID_FK FOREIGN KEY (PERMISSION_ID) REFERENCES PERMISSIONANALYZER.PERMISSIONLOOKUP(ID); -- REPORT TABLE CREATE TABLE PERMISSIONANALYZER.REPORT (ID INT NOT NULL, NAME VARCHAR(255) NOT NULL, DESCRIPTION VARCHAR(3000), LAST_RUN_DATE TIMESTAMP, LAST_COUNT INT, LAST_FILE_PATH VARCHAR(3000), REPORT_TYPE INT NOT NULL, FILE_TYPE INT NOT NULL, FILE_PATH VARCHAR(3000) NOT NULL, TEMPLATE_PATH VARCHAR(3000), EMAIL_ADDRESS VARCHAR(255), EMAIL_THRESHOLD INT, LAST_STATUS_MESSAGE VARCHAR(3000), USE_SIMPLE_PRESENTATION BOOLEAN, PRIMARY KEY (ID)); -- REPORTHISTORY TABLE CREATE TABLE PERMISSIONANALYZER.REPORTHISTORY (ID INT NOT NULL, REPORT_ID INT NOT NULL, RUN_DATE TIMESTAMP, RESULT_COUNT INT, FILE_PATH VARCHAR(3000) NULL, REPORT_TYPE INT NOT NULL, FILE_TYPE INT NOT NULL, EMAIL_ADDRESS VARCHAR(255), THRESHOLD INT, STATUS_MESSAGE VARCHAR(3000), PRIMARY KEY (ID)); ALTER TABLE PERMISSIONANALYZER.REPORTHISTORY ADD CONSTRAINT REPORTHISTORY_REPORT_ID_FK FOREIGN KEY (REPORT_ID) REFERENCES PERMISSIONANALYZER.REPORT(ID); -- FILTERSET TABLE CREATE TABLE PERMISSIONANALYZER.FILTERSET (ID INT NOT NULL, NAME VARCHAR(255) NOT NULL, FILTER_SET_TYPE INT NOT NULL, PRIMARY KEY (ID)); -- FILTER TABLE CREATE TABLE PERMISSIONANALYZER.FILTER (ID INT NOT NULL, NAME VARCHAR(255) NOT NULL, FILTER_TYPE INT NOT NULL, INCLUDE BOOLEAN NOT NULL, FILTERSET_ID INT, REPORT_ID INT, FILTER_VALUE VARCHAR(255) NOT NULL, PRIMARY KEY (ID)); ALTER TABLE PERMISSIONANALYZER.FILTER ADD CONSTRAINT FILTER_FILTERSET_ID_FK FOREIGN KEY (FILTERSET_ID) REFERENCES PERMISSIONANALYZER.FILTERSET(ID) ON DELETE CASCADE; ALTER TABLE PERMISSIONANALYZER.FILTER ADD CONSTRAINT FILTER_REPORT_ID_FK FOREIGN KEY (REPORT_ID) REFERENCES PERMISSIONANALYZER.REPORT(ID) ON DELETE CASCADE; -- REPORTFILTER TABLE CREATE TABLE PERMISSIONANALYZER.REPORTFILTERSET (ID INT NOT NULL, REPORT_ID INT NOT NULL, FILTERSET_ID INT, PRIMARY KEY (ID)); ALTER TABLE PERMISSIONANALYZER.REPORTFILTERSET ADD CONSTRAINT REPORTFILTERSET_REPORT_ID_FK FOREIGN KEY (REPORT_ID) REFERENCES PERMISSIONANALYZER.REPORT(ID) ON DELETE CASCADE; ALTER TABLE PERMISSIONANALYZER.REPORTFILTERSET ADD CONSTRAINT REPORTFILTERSET_SET_ID_FK FOREIGN KEY (FILTERSET_ID) REFERENCES PERMISSIONANALYZER.FILTERSET(ID); -- SCANLOGITEM TABLE CREATE TABLE PERMISSIONANALYZER.SCANLOGITEM (ID INT NOT NULL, SERVER VARCHAR(255) NOT NULL, LOG_DATE TIMESTAMP NOT NULL, SEVERITY INT NOT NULL, MESSAGE VARCHAR(2000) NOT NULL, DETAILS VARCHAR(3000), PRIMARY KEY (ID)); CREATE INDEX SCANLOGITEM_SERVER_I ON PERMISSIONANALYZER.SCANLOGITEM(SERVER); CREATE INDEX SCANLOGITEM_LOG_DATE_I ON PERMISSIONANALYZER.SCANLOGITEM(LOG_DATE); -- INFO TABLE CREATE TABLE PERMISSIONANALYZER.INFO (VERSION INT); INSERT INTO PERMISSIONANALYZER.INFO VALUES (16);