Database Scripts

IVS Scripts to be execute before using Upgrade Utility to Upgrade OIPA database to 11.x

Note: Below scripts contain queries to remove data from IVSSECURITYROLE and IVSSECURITYPRIVILEGES tables. So please take the backup of these table data and import it with the appropriate values for ENVIRONMENT and TRACK after upgrading it to 11.x

Rules Palette should be used to administer security for individual environments and tracks.

Scripts for Oracle Database

insert into IVSPRIVILEGE ( PRIVILEGEGUID, PRIVILEGENAME, DESCRIPTION ) values ('CB3A1963-60B9-4CF9-9031-CFFE11CE4F64', 'Upgrade Utility', 'Allows user to access Upgrade Utility Tool');

CREATE TABLE IVSDEPLOYMENTFLOW (DEPLOYMENTFLOWGUID CHAR(36 BYTE) NOT NULL, ENVIRONMENT VARCHAR(50 BYTE) NOT NULL, TRACK VARCHAR(50 BYTE) NOT NULL, SEQUENCE INTEGER NOT NULL);

ALTER TABLE IVSDEPLOYMENTFLOW ADD PRIMARY KEY (DEPLOYMENTFLOWGUID);

ALTER TABLE IVSDEPLOYMENTFLOW ADD CONSTRAINT IVSDEPLOYMENTFLOW_UK1 UNIQUE( SEQUENCE, ENVIRONMENT, TRACK);

ALTER TABLE IVSDEPLOYMENTFLOW ADD CONSTRAINT IVSDEPLOYMENTFLOW_UK2 UNIQUE( SEQUENCE, TRACK);

INSERT INTO IVSPRIVILEGE VALUES ('0EEA75BD-FD6C-4813-92F7-31197BF2ED53', 'Release Package - Rollback', null, 'Allow user to Roll back the deployed release package');

ALTER TABLE IVSSECURITY RENAME TO IVSUSER;

ALTER TABLE IVSUSER RENAME COLUMN SECURITYGUID TO USERGUID;

ALTER TABLE IVSUSER DROP COLUMN SECURITYROLEGUID;

DELETE FROM IVSSECURITYPRIVILEGES;

DELETE FROM IVSSECURITYROLE;

COMMIT;

ALTER TABLE IVSSECURITYROLE ADD(ENVIRONMENT VARCHAR2(50 BYTE), TRACK VARCHAR2 (50 BYTE));

CREATE TABLE IVSUSERSECURITY (USERSECURITYGUID CHAR(36 BYTE) NOT NULL ENABLE,USERGUID CHAR(36 BYTE) NOT NULL,SECURITYROLEGUID CHAR(36 BYTE));

ALTER TABLE IVSUSERSECURITY ADD PRIMARY KEY (USERSECURITYGUID);

ALTER TABLE IVSUSERSECURITY ADD CONSTRAINT FK_USERSEC_USERID FOREIGN KEY (USERGUID) REFERENCES IVSUSER (USERGUID);

ALTER TABLE IVSUSERSECURITY ADD CONSTRAINT FK_USERSEC_SECURITYROLEGUID FOREIGN KEY (SECURITYROLEGUID) REFERENCES IVSSECURITYROLE (SECURITYROLEGUID);

ALTER TABLE IVSSECURITYROLE DROP CONSTRAINT UX_SECURITYROLENAME;

DROP INDEX UX_SECURITYROLENAME;

ALTER TABLE IVSSECURITYROLE ADD CONSTRAINT UX_NAMEENVTRACK UNIQUE( SECURITYROLENAME, ENVIRONMENT, TRACK);

ALTER TABLE IVSUSERSECURITY ADD CONSTRAINT UX_USERROLEGUID UNIQUE ( USERGUID, SECURITYROLEGUID);

Add privilege script for product copy feature to upgrade OIPA Database to 10.2

Insert into IVSPRIVILEGE (PRIVILEGEGUID,PRIVILEGENAME,PRIVILEGETYPE,DESCRIPTION) values ('97DEE89C-8C81-4CC8-8C34-7B7121132340','Utilities-ProductCopy',null,'Allow access to Product Copy Tool');

Scripts for DB2 Database

INSERT INTO IVSPRIVILEGE ( PRIVILEGEGUID, PRIVILEGENAME, DESCRIPTION ) values ('CB3A1963-60B9-4CF9-9031-CFFE11CE4F64', 'Upgrade Utility', 'Allows user to access Upgrade Utility Tool');

CREATE TABLE IVSDEPLOYMENTFLOW (DEPLOYMENTFLOWGUID CHAR(36 BYTE) NOT NULL, ENVIRONMENT VARCHAR(50 BYTE) NOT NULL, TRACK VARCHAR(50 BYTE) NOT NULL, SEQUENCE INTEGER NOT NULL);

ALTER TABLE IVSDEPLOYMENTFLOW ADD PRIMARY KEY (DEPLOYMENTFLOWGUID);

ALTER TABLE IVSDEPLOYMENTFLOW ADD CONSTRAINT IVSDEPLOYMENTFLOW_UK1 UNIQUE( SEQUENCE, ENVIRONMENT, TRACK);

ALTER TABLE IVSDEPLOYMENTFLOW ADD CONSTRAINT IVSDEPLOYMENTFLOW_UK2 UNIQUE( SEQUENCE, TRACK);

INSERT INTO IVSPRIVILEGE VALUES ('0EEA75BD-FD6C-4813-92F7-31197BF2ED53', 'Release Package - Rollback', null, 'Allow user to Roll back the deployed release package');

RENAME TABLE IVSSECURITY TO IVSUSER;

ALTER TABLE IVSUSER RENAME COLUMN SECURITYGUID TO USERGUID;

ALTER TABLE IVSUSER DROP COLUMN SECURITYROLEGUID;

DELETE FROM IVSSECURITYPRIVILEGES;

DELETE FROM IVSSECURITYROLE;

COMMIT;

ALTER TABLE IVSSECURITYROLE ADD ENVIRONMENT VARCHAR(50) NOT NULL WITH DEFAULT

ALTER TABLE IVSSECURITYROLE ADD TRACK VARCHAR(50) NOT NULL WITH DEFAULT

CREATE TABLE IVSUSERSECURITY (USERSECURITYGUID CHAR(36) NOT NULL ,USERGUID CHAR(36) NOT NULL,SECURITYROLEGUID CHAR(36) NOT NULL);

ALTER TABLE IVSUSERSECURITY ADD PRIMARY KEY (USERSECURITYGUID);

CALL SYSPROC.ADMIN_CMD ('REORG TABLE IVSUSER');CALL SYSPROC.ADMIN_CMD ('REORG TABLE IVSSECURITYROLE');

ALTER TABLE IVSUSER ADD PRIMARY KEY (USERGUID);

ALTER TABLE IVSUSERSECURITY ADD CONSTRAINT FK_USERSEC_USERID FOREIGN KEY (USERGUID) REFERENCES IVSUSER (USERGUID);

ALTER TABLE IVSSECURITYROLE ADD PRIMARY KEY (SECURITYROLEGUID);

ALTER TABLE IVSUSERSECURITY ADD CONSTRAINT FK_USERSEC_SECURITYROLEGUID FOREIGN KEY (SECURITYROLEGUID) REFERENCES IVSSECURITYROLE (SECURITYROLEGUID);

ALTER TABLE IVSSECURITYROLE ALTER COLUMN SECURITYROLENAME SET NOT NULL;

ALTER TABLE IVSSECURITYROLE ADD CONSTRAINT UX_NAMEENVTRACK UNIQUE( SECURITYROLENAME, ENVIRONMENT, TRACK);

ALTER TABLE IVSUSERSECURITY ADD CONSTRAINT UX_USERROLEGUID UNIQUE ( USERGUID, SECURITYROLEGUID);

Add privilege script for product copy feature to upgrade OIPA Database to 10.2

Insert into IVSPRIVILEGE (PRIVILEGEGUID,PRIVILEGENAME,PRIVILEGETYPE,DESCRIPTION) values ('97DEE89C-8C81-4CC8-8C34-7B7121132340','Utilities-ProductCopy',null,'Allow access to Product Copy Tool');

Scripts for SqlServer Database

insert into IVSPRIVILEGE ( PRIVILEGEGUID, PRIVILEGENAME, DESCRIPTION ) values ('CB3A1963-60B9-4CF9-9031-CFFE11CE4F64', 'Upgrade Utility', 'Allows user to access Upgrade Utility Tool');

CREATE TABLE IVSDEPLOYMENTFLOW (DEPLOYMENTFLOWGUID CHAR(36) NOT NULL, ENVIRONMENT VARCHAR(50) NOT NULL, TRACK VARCHAR(50) NOT NULL, SEQUENCE INTEGER NOT NULL);

ALTER TABLE IVSDEPLOYMENTFLOW ADD PRIMARY KEY (DEPLOYMENTFLOWGUID);

ALTER TABLE IVSDEPLOYMENTFLOW ADD CONSTRAINT IVSDEPLOYMENTFLOW_UK1 UNIQUE( SEQUENCE, ENVIRONMENT, TRACK);

ALTER TABLE IVSDEPLOYMENTFLOW ADD CONSTRAINT IVSDEPLOYMENTFLOW_UK2 UNIQUE( SEQUENCE, TRACK);

INSERT INTO IVSPRIVILEGE VALUES ('0EEA75BD-FD6C-4813-92F7-31197BF2ED53', 'Release Package - Rollback', null, 'Allow user to Roll back the deployed release package');

SP_RENAME 'IVSSECURITY', 'IVSUSER'

SP_RENAME 'IVSUSER.SECURITYGUID', 'USERGUID', 'COLUMN'

ALTER TABLE IVSUSER DROP COLUMN SECURITYROLEGUID;

DELETE FROM IVSSECURITYPRIVILEGES;

DELETE FROM IVSSECURITYROLE;

COMMIT;

ALTER TABLE IVSSECURITYROLE ADD ENVIRONMENT VARCHAR(50);

ALTER TABLE IVSSECURITYROLE ADD TRACK VARCHAR(50);

CREATE TABLE IVSUSERSECURITY (USERSECURITYGUID UNIQUEIDENTIFIER NOT NULL ,USERGUID UNIQUEIDENTIFIER NOT NULL,SECURITYROLEGUID UNIQUEIDENTIFIER NOT NULL);

ALTER TABLE IVSUSERSECURITY ADD PRIMARY KEY (USERSECURITYGUID);

ALTER TABLE IVSUSERSECURITY ADD CONSTRAINT FK_USERSEC_USERID FOREIGN KEY (USERGUID) REFERENCES IVSUSER (USERGUID);

ALTER TABLE IVSUSERSECURITY ADD CONSTRAINT FK_USERSEC_SECURITYROLEGUID FOREIGN KEY (SECURITYROLEGUID) REFERENCES IVSSECURITYROLE (SECURITYROLEGUID);

DROP INDEX IVSSECURITYROLE.UX_IVSSECURITYROLENAME;

ALTER TABLE IVSSECURITYROLE ADD CONSTRAINT UX_NAMEENVTRACK UNIQUE( SECURITYROLENAME, ENVIRONMENT, TRACK);

ALTER TABLE IVSUSERSECURITY ADD CONSTRAINT UX_USERROLEGUID UNIQUE ( USERGUID, SECURITYROLEGUID);

Add privilege script for product copy feature to upgrade OIPA Database to 10.2

Insert into IVSPRIVILEGE (PRIVILEGEGUID,PRIVILEGENAME,PRIVILEGETYPE,DESCRIPTION) values ('97DEE89C-8C81-4CC8-8C34-7B7121132340','Utilities-ProductCopy',null,'Allow access to Product Copy Tool');

 

Download version 1.8.10 of aspectjrt, aspectjtools and aspectjweaver.