CREATE SCHEMA AUTHORIZATION INFORMATION_SCHEMA


CREATE VIEW SCHEMATA  ( SCHEMA_OWNER )
       AS (SELECT
          OWNER
          FROM DEFINITION_SCHEMA.SYSTABLES
          WHERE OWNER = USER )
          
CREATE VIEW TABLES  ( TABLE_NAME ,TABLE_TYPE, NUM_COLUMNS, NUM_INDEXES )
       AS
     (
       SELECT  T.TABNAME, T.TABTYPE, T.NCOLS , -1
       FROM  DEFINITION_SCHEMA.SYSTABLES T
       WHERE T.UNTABID IN 
            ( SELECT UNTABID
             FROM   DEFINITION_SCHEMA.SYSTABAUTH
             WHERE  GRANTEE IN ('PUBLIC', USER ) 
            )
     )

CREATE VIEW VIEWS   ( TABLE_NAME )
       AS ( SELECT
          TABNAME 
       FROM  DEFINITION_SCHEMA.SYSTABLES
       WHERE TABTYPE = 'V' )
       

CREATE VIEW COLUMNS
       AS ( SELECT
          COLNAME, UNTABID , COLNO, COLTYPE, DEFNULL
       FROM  DEFINITION_SCHEMA.SYSCOLUMNS
       WHERE UNTABID
             IN
              ( SELECT UNTABID
                FROM   DEFINITION_SCHEMA.SYSCOLAUTH          
                WHERE  GRANTEE
                       IN ( 'PUBLIC' , USER )
              )
       )

CREATE VIEW COLUMN_PRIVILEGES
       AS ( SELECT
          GRANTOR, GRANTEE, COLNO, UNTABID, COLAUTH
       FROM   DEFINITION_SCHEMA.SYSCOLAUTH
       WHERE   GRANTEE IN ('PUBLIC', USER )
            OR GRANTOR = USER
       )

CREATE VIEW TABLE_PRIVILEGES
       AS ( SELECT
          GRANTOR, GRANTEE, UNTABID, TABAUTH          
       FROM  DEFINITION_SCHEMA.SYSTABAUTH
       WHERE  GRANTEE IN ('PUBLIC', USER )
            OR GRANTOR = USER
       )

CREATE VIEW CHECK_CONSTRAINTS
       AS ( SELECT
             UNTABID, NCOLS
       FROM  DEFINITION_SCHEMA.SYSCHCONSTR
       )

CREATE VIEW REFERENTIAL_CONSTRAINTS
       AS  ( SELECT  
             TABFROM, TABTO, NCOLS
       FROM  DEFINITION_SCHEMA.SYSREFCONSTR
       )

CREATE VIEW VIEW_COLUMN_USAGE
       AS ( SELECT
             COLNAME
       FROM  DEFINITION_SCHEMA.SYSCOLUMNS
       WHERE UNTABID 
             IN
           ( SELECT UNTABID
             FROM  DEFINITION_SCHEMA.SYSTABLES
             WHERE TABTYPE = 'V'
                   AND
                   OWNER = USER
           )
      ) 


----------------------------------------------------------

GRANT SELECT ON SCHEMATA                TO PUBLIC
GRANT SELECT ON TABLES                  TO PUBLIC
GRANT SELECT ON VIEWS                   TO PUBLIC
GRANT SELECT ON COLUMNS                 TO PUBLIC
GRANT SELECT ON COLUMN_PRIVILEGES       TO PUBLIC
GRANT SELECT ON TABLE_PRIVILEGES        TO PUBLIC
GRANT SELECT ON CHECK_CONSTRAINTS       TO PUBLIC
GRANT SELECT ON REFERENTIAL_CONSTRAINTS TO PUBLIC
GRANT SELECT ON VIEW_COLUMN_USAGE       TO PUBLIC


