/*********************************************************************************
**
** Source:      NCR Documentation
**              Data Dictionary
**              Release V2R6.1
**              B035-1092-115A
**              November 2005
**      
** Author of statements: Ulrich Arndt - www.data2knowledge.de
**                       ulrich.arndt@data2knowledge.de
**
** First written:       2006/02/15
**                      2006/03/16 Add comments/corrections of Mike Dempsey
**      
** NO WARRANTY ON CORRECTNESS AND COMPLETENESS OF  THIS SCRIPT AND IT CONTENT!
**
***********************************************************************************/

CREATE TABLE ACCESSRIGHT_V2R61_REF 
(
 ACCESSRIGHT CHAR(2) NOT NULL,
 ACCESSRIGHT_DESC VARCHAR(10000) NOT NULL
) UNIQUE PRIMARY INDEX (ACCESSRIGHT);

INSERT INTO ACCESSRIGHT_V2R61_REF VALUES ('AE','ALTER EXTERNAL PROCEDURE');
INSERT INTO ACCESSRIGHT_V2R61_REF VALUES ('AF','ALTER FUNCTION');
INSERT INTO ACCESSRIGHT_V2R61_REF VALUES ('AP','ALTER PROCEDURE');
INSERT INTO ACCESSRIGHT_V2R61_REF VALUES ('AS','ABORT SESSION');
INSERT INTO ACCESSRIGHT_V2R61_REF VALUES ('CA','CREATE AUTHORIZATION');
INSERT INTO ACCESSRIGHT_V2R61_REF VALUES ('CD','CREATE DATABASE');
INSERT INTO ACCESSRIGHT_V2R61_REF VALUES ('CE','CREATE EXTERNAL PROCEDURE');
INSERT INTO ACCESSRIGHT_V2R61_REF VALUES ('CF','CREATE FUNCTION');
INSERT INTO ACCESSRIGHT_V2R61_REF VALUES ('CG','CREATE TRIGGER');
INSERT INTO ACCESSRIGHT_V2R61_REF VALUES ('CM','CREATE MACRO');
INSERT INTO ACCESSRIGHT_V2R61_REF VALUES ('CO','CREATE PROFILE');
INSERT INTO ACCESSRIGHT_V2R61_REF VALUES ('CP','CHECKPOINT');
INSERT INTO ACCESSRIGHT_V2R61_REF VALUES ('CR','CREATE ROLE');
INSERT INTO ACCESSRIGHT_V2R61_REF VALUES ('CT','CREATE TABLE');
INSERT INTO ACCESSRIGHT_V2R61_REF VALUES ('CU','CREATE USER');
INSERT INTO ACCESSRIGHT_V2R61_REF VALUES ('CV','CREATE VIEW');
INSERT INTO ACCESSRIGHT_V2R61_REF VALUES ('D','DELETE');
INSERT INTO ACCESSRIGHT_V2R61_REF VALUES ('DA','DROP AUTHORIZATION');
INSERT INTO ACCESSRIGHT_V2R61_REF VALUES ('DD','DROP DATABASE');
INSERT INTO ACCESSRIGHT_V2R61_REF VALUES ('DF','DROP FUNCTION');
INSERT INTO ACCESSRIGHT_V2R61_REF VALUES ('DG','DROP TRIGGER');
INSERT INTO ACCESSRIGHT_V2R61_REF VALUES ('DM','DROP MACRO');
INSERT INTO ACCESSRIGHT_V2R61_REF VALUES ('DO','DROP PROFILE');
INSERT INTO ACCESSRIGHT_V2R61_REF VALUES ('DP','DUMP');
INSERT INTO ACCESSRIGHT_V2R61_REF VALUES ('DR','DROP ROLE');
INSERT INTO ACCESSRIGHT_V2R61_REF VALUES ('DT','DROP TABLE');
INSERT INTO ACCESSRIGHT_V2R61_REF VALUES ('DU','DROP USER');
INSERT INTO ACCESSRIGHT_V2R61_REF VALUES ('DV','DROP VIEW');
INSERT INTO ACCESSRIGHT_V2R61_REF VALUES ('E','EXECUTE (MACRO)');
INSERT INTO ACCESSRIGHT_V2R61_REF VALUES ('EF','EXECUTE FUNCTION');
INSERT INTO ACCESSRIGHT_V2R61_REF VALUES ('I','INSERT');
INSERT INTO ACCESSRIGHT_V2R61_REF VALUES ('IX','INDEX');
INSERT INTO ACCESSRIGHT_V2R61_REF VALUES ('MR','MONITOR RESOURCE');
INSERT INTO ACCESSRIGHT_V2R61_REF VALUES ('MS','MONITOR SESSION');
INSERT INTO ACCESSRIGHT_V2R61_REF VALUES ('PC','CREATE PROCEDURE');
INSERT INTO ACCESSRIGHT_V2R61_REF VALUES ('PD','DROP PROCEDURE');
INSERT INTO ACCESSRIGHT_V2R61_REF VALUES ('PE','EXECUTE PROCEDURE');
INSERT INTO ACCESSRIGHT_V2R61_REF VALUES ('R','RETRIEVE/SELECT');
INSERT INTO ACCESSRIGHT_V2R61_REF VALUES ('RF','REFERENCE');
INSERT INTO ACCESSRIGHT_V2R61_REF VALUES ('RS','RESTORE');
INSERT INTO ACCESSRIGHT_V2R61_REF VALUES ('RO','REPLICATION CONTROL or REPLICATION OVERIDE');
INSERT INTO ACCESSRIGHT_V2R61_REF VALUES ('SR','SET RESOURCE RATE');
INSERT INTO ACCESSRIGHT_V2R61_REF VALUES ('SS','SET SESSION RATE');
INSERT INTO ACCESSRIGHT_V2R61_REF VALUES ('U','UPDATE');
INSERT INTO ACCESSRIGHT_V2R61_REF VALUES ('UM','UDT METHOD');
INSERT INTO ACCESSRIGHT_V2R61_REF VALUES ('UT','UDT TYPE');
INSERT INTO ACCESSRIGHT_V2R61_REF VALUES ('UU','UDT USAGE');

COLLECT STATS ACCESSRIGHT_V2R61_REF INDEX (ACCESSRIGHT);

CREATE VIEW V_ACCESSRIGHT_REF
AS 
SELECT ACCESSRIGHT,
       ACCESSRIGHT_DESC
FROM ACCESSRIGHT_V2R61_REF;

/* CHECK IF CURRENTLY COMPLET */
SELECT T.ACCESSRIGHT
FROM DBC.ALLRIGHTS T
WHERE NOT EXISTS
      (SELECT *
       FROM V_ACCESSRIGHT_REF R
       WHERE R.ACCESSRIGHT = T.ACCESSRIGHT
      )
GROUP BY ACCESSRIGHT
ORDER BY ACCESSRIGHT
;