|
-- SQL Test Suite, V6.0, SQL Module isi006.mco
-- 59-byte ID
--
MODULE ISI006
LANGUAGE COBOL
AUTHORIZATION FLATER
PROCEDURE SUB1 (SQLCODE, SQLSTATE,
:UIDX CHAR(18));
SELECT USER INTO :UIDX FROM HU.ECCO;
PROCEDURE SUB2 (SQLCODE, SQLSTATE);
ROLLBACK WORK;
PROCEDURE SUB3 (SQLCODE, SQLSTATE,
:CTR1 NUMERIC (9));
SELECT COUNT(*) INTO :CTR1
FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES
WHERE GRANTEE NOT IN ('PUBLIC', USER)
AND GRANTOR <> USER;
PROCEDURE SUB4 (SQLCODE, SQLSTATE,
:CTR1 NUMERIC (9));
SELECT COUNT(*) INTO :CTR1
FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES
WHERE IS_GRANTABLE IS NULL;
PROCEDURE SUB5 (SQLCODE, SQLSTATE,
:CTR1 NUMERIC (9));
SELECT COUNT(*) INTO :CTR1
FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES
WHERE IS_GRANTABLE NOT IN ('YES', 'NO');
PROCEDURE SUB6 (SQLCODE, SQLSTATE,
:CTR1 NUMERIC (9));
SELECT COUNT(*) INTO :CTR1
FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES
WHERE GRANTOR IS NULL
OR GRANTEE IS NULL
OR TABLE_SCHEMA IS NULL
OR TABLE_NAME IS NULL
OR PRIVILEGE_TYPE IS NULL
OR COLUMN_NAME IS NULL;
PROCEDURE SUB7 (SQLCODE, SQLSTATE,
:CTR1 NUMERIC (9));
SELECT COUNT(*) INTO :CTR1
FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES;
PROCEDURE SUB8 (SQLCODE, SQLSTATE,
:CTR2 NUMERIC (9));
SELECT COUNT(*) INTO :CTR2
FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES A,
INFORMATION_SCHEMA.COLUMN_PRIVILEGES B
WHERE A.GRANTOR = B.GRANTOR
AND A.GRANTEE = B.GRANTEE
AND A.TABLE_SCHEMA = B.TABLE_SCHEMA
AND A.TABLE_NAME = B.TABLE_NAME
AND A.PRIVILEGE_TYPE = B.PRIVILEGE_TYPE
AND A.COLUMN_NAME = B.COLUMN_NAME;
PROCEDURE SUB9 (SQLCODE, SQLSTATE,
:CTR1 NUMERIC (9));
SELECT COUNT(*) INTO :CTR1
FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES A
WHERE NOT EXISTS (
SELECT * FROM INFORMATION_SCHEMA.COLUMNS B
WHERE A.TABLE_SCHEMA = B.TABLE_SCHEMA
AND A.TABLE_NAME = B.TABLE_NAME
AND A.COLUMN_NAME = B.COLUMN_NAME);
PROCEDURE SUB10 (SQLCODE, SQLSTATE,
:CTR1 NUMERIC (9));
SELECT COUNT(*) INTO :CTR1
FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES
WHERE GRANTOR = '_SYSTEM'
AND GRANTEE = 'FLATER'
AND IS_GRANTABLE = 'YES'
AND TABLE_SCHEMA = 'FLATER'
AND TABLE_NAME = 'USIG'
AND COLUMN_NAME = 'C_1'
AND PRIVILEGE_TYPE = 'SELECT';
PROCEDURE SUB11 (SQLCODE, SQLSTATE,
:CTR1 NUMERIC (9));
SELECT COUNT(*) INTO :CTR1
FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES
WHERE GRANTOR = '_SYSTEM'
AND GRANTEE = 'FLATER'
AND IS_GRANTABLE = 'YES'
AND TABLE_SCHEMA = 'FLATER'
AND TABLE_NAME = 'USIG'
AND COLUMN_NAME = 'C_1'
AND PRIVILEGE_TYPE = 'INSERT';
PROCEDURE SUB12 (SQLCODE, SQLSTATE,
:CTR1 NUMERIC (9));
SELECT COUNT(*) INTO :CTR1
FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES
WHERE GRANTOR = '_SYSTEM'
AND GRANTEE = 'FLATER'
AND IS_GRANTABLE = 'YES'
AND TABLE_SCHEMA = 'FLATER'
AND TABLE_NAME = 'USIG'
AND COLUMN_NAME = 'C_1'
AND PRIVILEGE_TYPE = 'UPDATE';
PROCEDURE SUB13 (SQLCODE, SQLSTATE,
:CTR1 NUMERIC (9));
SELECT COUNT(*) INTO :CTR1
FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES
WHERE GRANTOR = '_SYSTEM'
AND GRANTEE = 'FLATER'
AND IS_GRANTABLE = 'YES'
AND TABLE_SCHEMA = 'FLATER'
AND TABLE_NAME = 'USIG'
AND COLUMN_NAME = 'C_1'
AND PRIVILEGE_TYPE = 'REFERENCES';
PROCEDURE SUB14 (SQLCODE, SQLSTATE,
:CTR1 NUMERIC (9));
SELECT COUNT(*) INTO :CTR1
FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES
WHERE GRANTOR = 'FLATER'
AND GRANTEE = 'SCHANZLE'
AND IS_GRANTABLE = 'NO'
AND TABLE_SCHEMA = 'FLATER'
AND TABLE_NAME = 'WCOV'
AND COLUMN_NAME = 'C1'
AND PRIVILEGE_TYPE = 'SELECT';
PROCEDURE SUB15 (SQLCODE, SQLSTATE,
:CTR1 NUMERIC (9));
SELECT COUNT(*) INTO :CTR1
FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES
WHERE GRANTOR = 'FLATER'
AND GRANTEE = 'SCHANZLE'
AND IS_GRANTABLE = 'NO'
AND TABLE_SCHEMA = 'FLATER'
AND TABLE_NAME = 'WCOV'
AND COLUMN_NAME = 'C1'
AND PRIVILEGE_TYPE = 'INSERT';
PROCEDURE SUB16 (SQLCODE, SQLSTATE,
:CTR1 NUMERIC (9));
SELECT COUNT(*) INTO :CTR1
FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES
WHERE GRANTOR = 'FLATER'
AND GRANTEE = 'SCHANZLE'
AND IS_GRANTABLE = 'NO'
AND TABLE_SCHEMA = 'FLATER'
AND TABLE_NAME = 'WCOV'
AND COLUMN_NAME = 'C1'
AND PRIVILEGE_TYPE = 'UPDATE';
PROCEDURE SUB17 (SQLCODE, SQLSTATE,
:CTR1 NUMERIC (9));
SELECT COUNT(*) INTO :CTR1
FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES
WHERE GRANTOR = 'FLATER'
AND GRANTEE = 'SCHANZLE'
AND IS_GRANTABLE = 'NO'
AND TABLE_SCHEMA = 'FLATER'
AND TABLE_NAME = 'WCOV'
AND COLUMN_NAME = 'C1'
AND PRIVILEGE_TYPE = 'REFERENCES';
PROCEDURE SUB18 (SQLCODE, SQLSTATE);
ROLLBACK WORK;
PROCEDURE SUB19 (SQLCODE, SQLSTATE);
INSERT INTO HU.TESTREPORT
VALUES('0573','pass','MCO');
PROCEDURE SUB20 (SQLCODE, SQLSTATE);
INSERT INTO HU.TESTREPORT
VALUES('0573','fail','MCO');
PROCEDURE SUB21 (SQLCODE, SQLSTATE);
COMMIT WORK;
[ Dauer der Verarbeitung: 0.8 Sekunden
(vorverarbeitet)
]
|
