
CREATE VIEW SYSCOLCOUNT (OBJ#, COLCOUNT)
	AS
SELECT C.OBJ#, COUNT(*)
FROM SYS.COL$ C
GROUP BY C.OBJ#
;
GRANT SELECT ON SYSCOLCOUNT TO PUBLIC
;

CREATE VIEW SYSTABLES1 (CREATOR, NAME, TYPE, REMARKS, PERCENTFREE)
       AS 
SELECT U.NAME, O.NAME, 
	DECODE(O.TYPE, 2, 'T', 4, 'V', 'S'), 
	C.COMMENT$,
	0
FROM SYS.OBJ$ O, SYS.USER$ U, SYS.COM$ C
WHERE O.OWNER# = U.USER#
  AND O.OBJ# = C.OBJ#(+)
  AND C.COL#(+) IS NULL
  AND O.TYPE IN (2, 4)
  AND (O.OWNER# = UID
        OR
        O.OBJ# IN ( SELECT OBJ#
                    FROM SYS.TABAUTH$
                    WHERE GRANTEE# IN ( UID, 1 )
                  )
       )
  AND O.NAME NOT IN ('SYSCOLCOUNT', 'SYSIND_COL#')
;
GRANT SELECT ON SYSTABLES1 TO PUBLIC;

CREATE VIEW SYSTABLES (CREATOR, NAME, COLCOUNT, TYPE, REMARKS, PERCENTFREE)
       AS 
SELECT U.NAME, O.NAME, CO.COLCOUNT, 
	DECODE(O.TYPE, 2, 'T', 4, 'V', 'S'), 
	C.COMMENT$,
	0
FROM SYS.OBJ$ O, SYS.USER$ U, SYS.COM$ C, SYSCOLCOUNT CO
WHERE O.OWNER# = U.USER#
  AND O.OBJ# = C.OBJ#(+)
  AND C.COL#(+) IS NULL
  AND O.TYPE IN (2, 4)
  AND O.OBJ# = CO.OBJ#
  AND (O.OWNER# = UID
        OR
        O.OBJ# IN ( SELECT OBJ#
                    FROM SYS.TABAUTH$
                    WHERE GRANTEE# IN ( UID, 1 )
                  )
       )
  AND O.NAME NOT IN ('SYSCOLCOUNT', 'SYSIND_COL#')
;
GRANT SELECT ON SYSTABLES TO PUBLIC;

CREATE VIEW SYSCOLUMNS                                         
       	    (TBCREATOR, NAME, TBNAME, COLNO, COLTYPE, LENGTH,
             NULLS, UPDATES, REMARKS, SCALE)
       AS
SELECT U.NAME, C.NAME, O.NAME, C.COL#,
       DECODE(C.TYPE#, 1, 'CHAR',
                       2, DECODE(C.SCALE, NULL,
                                 DECODE(C.PRECISION, NULL, 'NUMBER', 'FLOAT'),
                                 'NUMBER'),
                       8, 'LONG', 9, 'VARCHAR',
                       12, 'DATE', 23, 'RAW', 24, 'LONG RAW',
                       69, 'ROWID',  'UNDEFINED'),
	C.LENGTH,
	DECODE(C.NULL$, 0, 'Y', 'N'),
	'Y',
	CO.COMMENT$,
	C.SCALE
FROM SYS.COL$ C, SYS.OBJ$ O, SYS.USER$ U, SYS.COM$ CO
WHERE O.OBJ# = C.OBJ#
  AND O.OWNER# = U.USER#
  AND O.TYPE IN (2, 3, 4)
  AND C.OBJ# = CO.OBJ#(+)
  AND C.COL# = CO.COL#(+)
  AND (O.OWNER# = UID
        OR
        O.OBJ# IN ( SELECT OBJ#
                    FROM SYS.TABAUTH$
                    WHERE GRANTEE# IN ( UID, 1 )
                  )
       )
;
GRANT SELECT ON SYSCOLUMNS TO PUBLIC;

CREATE VIEW SYSIND_COL#
	(IND_OBJ, COL_COUNT)
AS 
SELECT OBJ#, COUNT(*)
FROM SYS.ICOL$
GROUP BY OBJ#
;
GRANT SELECT ON SYSIND_COL# TO PUBLIC
; 

CREATE VIEW SYSINDEXES
         (TBCREATOR, NAME, TBNAME, CREATOR, UNIQUERULE, COLCOUNT, IXTYPE,
	  CLUSTERRULE, IXSIZE, PERCENTFREE)
	 AS
SELECT IU.NAME, O.NAME, IO.NAME, U.NAME,
       DECODE(I.UNIQUE$, 0, 'D', 1, 'U', 'D'), 
       COL_COUNT, 'B', 'N', 0, 0
FROM SYS.SEG$ S, SYS.USER$ IU, SYS.OBJ$ IO, 
     SYS.USER$ U, SYS.IND$ I, SYS.OBJ$ O, SYSIND_COL# CNT
WHERE U.USER# = O.OWNER#
  AND O.OBJ# = I.OBJ#
  AND O.OBJ# = CNT.IND_OBJ
  AND I.BO# = IO.OBJ#
  AND IO.OWNER# = IU.USER# 
  AND I.FILE# = S.FILE#
  AND I.BLOCK# = S.BLOCK# 
  AND (IO.OWNER# = UID
        OR
       IO.OBJ# IN ( SELECT OBJ#
                    FROM SYS.TABAUTH$
                    WHERE GRANTEE# IN ( UID, 1 )
                  )
       )
;
GRANT SELECT ON SYSINDEXES TO PUBLIC;

CREATE VIEW SYSKEYS           
            (IXCREATOR, IXNAME, COLNAME, COLNO, COLSEQ, ORDERING, FUNCTION) 
	 AS
SELECT IO.NAME, IDX.NAME, C.NAME, C.COL#, IC.POS#, 'A', ''
FROM SYS.COL$ C, SYS.OBJ$ IDX, 
SYS.OBJ$ BASE, SYS.ICOL$ IC,
     SYS.USER$ IO, SYS.USER$ BO
WHERE BASE.OBJ# = C.OBJ#
  AND IC.COL# = C.COL#
  AND IC.BO# = BASE.OBJ#
  AND IO.USER# = IDX.OWNER#
  AND BO.USER# = BASE.OWNER#
  AND IC.OBJ# = IDX.OBJ#
  AND (IDX.OWNER# = UID OR
       BASE.OWNER# = UID
       OR
       BASE.OBJ# IN ( SELECT OBJ#
                     FROM SYS.TABAUTH$
                     WHERE GRANTEE# IN ( UID, 1 )
                   )
       )
;
GRANT SELECT ON SYSKEYS TO PUBLIC;

CREATE VIEW SYSSYNONYMS (NAME, CREATOR, TBNAME, TBCREATOR)
	 AS                                       
SELECT O.NAME, U.NAME, S.NAME, S.OWNER
FROM SYS.USER$ U, SYS.SYN$ S, SYS.OBJ$ O
WHERE O.OBJ# = S.OBJ#
  AND O.TYPE = 5
  AND O.OWNER# = U.USER#
  AND O.OWNER# IN (UID, 1)
;
GRANT SELECT ON SYSSYNONYMS TO PUBLIC;

CREATE VIEW SYSTABAUTH 
   	   (GRANTEE, TCREATOR, TTNAME, UPDATECOLS, SELECTCOLS, ALTERAUTH, DELETEAUTH,
	    INDEXAUTH, INSERTAUTH, SELECTAUTH, UPDATEAUTH)
   AS
SELECT UE.NAME, U.NAME, O.NAME,
       DECODE(TA.UPDATE$, 0, 'N', 1, '*', 2, 'Y', 3, 'Y', '?'),
       DECODE(TA.REFERENCES$, 0, 'N', 1, '*', 2, 'N', 3, 'N', '?'),
       DECODE(TA.ALTER$, 0, 'N', 2, 'Y', 3, 'Y', '?'),
       DECODE(TA.DELETE$, 0, 'N', 2, 'Y', 3, 'Y', '?'),
       DECODE(TA.INDEX$, 0, 'N', 2, 'Y', 3, 'Y', '?'),
       DECODE(TA.INSERT$, 0, 'N', 1, 'Y', 2, 'Y', 3, 'Y', '?'),
       DECODE(TA.SELECT$, 0, 'N', 2, 'Y', 3, 'Y', '?'),
       DECODE(TA.UPDATE$, 0, 'N', 1, 'N', 2, 'Y', 3, 'Y', '?')
FROM SYS.TABAUTH$ TA, SYS.OBJ$ O, SYS.USER$ U, SYS.USER$ UR, SYS.USER$ UE
WHERE TA.OBJ# = O.OBJ#
  AND TA.GRANTOR# = UR.USER#
  AND TA.GRANTEE# = UE.USER#
  AND U.USER# = O.OWNER#
;
GRANT SELECT ON SYSTABAUTH TO PUBLIC;


CREATE VIEW SYSCOLAUTH 
   	(GRANTEE, CREATOR, PRIVILEGE, TNAME, COLNAME)
   AS
SELECT UE.NAME, U.NAME,
	decode(ca.update$, 0, 'SELECT', 2, 'UPDATE', 3, 'UPDATE', '?'),
        O.NAME, CA.NAME
FROM SYS.COLAUTH$ CA, SYS.OBJ$ O, SYS.USER$ U, SYS.USER$ UR, SYS.USER$ UE
WHERE CA.OBJ# = O.OBJ#
  AND CA.GRANTOR# = UR.USER#
  AND CA.GRANTEE# = UE.USER#
  AND U.USER# = O.OWNER#
;
GRANT SELECT ON SYSCOLAUTH TO PUBLIC;

