Rem
Rem $Header: catrule.sql 07-jan-99.19:11:08 kmeiyyap Exp $
Rem
Rem catrule.sql
Rem
Rem  Copyright (c) Oracle Corporation 1998, 1999. All Rights Reserved.
Rem
Rem    NAME
Rem      catrule.sql - Rules Catalog creation
Rem
Rem    DESCRIPTION
Rem      This loads the catalog and plsql packages for the rule engine
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    kmeiyyap    01/07/99 - make catrule compatible for sqlplus
Rem    nbhatt      06/23/98 - remove type sys.rule$_hits
Rem    esoyleme    04/28/98 - fix security                                     
Rem    esoyleme    04/15/98 - normalize tables
Rem    esoyleme    04/15/98 - add dbmsread.sql
Rem    esoyleme    04/13/98 - add rulehits type
Rem    esoyleme    04/06/98 - create rules library
Rem    esoyleme    03/15/98 - add rules set cat creation
Rem    esoyleme    03/15/98 - Created
Rem

--create views on ruleset$
create or replace view USER_RULESETS
(RULESET_NAME, RULESET_STORAGE_TABLE, BASE_TABLE)
as
SELECT o.name, ru.name||'.'||ro.name, bu.name||'.'||bo.name 
FROM ruleset$ r, obj$ o, user$ u, obj$ ro, user$ ru, obj$ bo, user$ bu
WHERE   u.user# = USERENV('SCHEMAID') 
        and o.owner#=u.user#     AND O.OBJ# = R.OBJ# 
        and bo.owner# = bu.user# and bo.obj# = r.baseobj
        and ro.owner# = ru.user# and ro.obj# = r.rules_table
/
comment on table USER_RULESETS is
'Rulesets owned by the user'
/
comment on column USER_RULESETS.RULESET_NAME is
'Name of the ruleset'
/
comment on column USER_RULESETS.RULESET_STORAGE_TABLE is
'Name of the table that stores the rules in the ruleset'
/
comment on column USER_RULESETS.BASE_TABLE is
'Name of the table on that the rules are defined'
/
drop public synonym USER_RULESETS
/
create public synonym USER_RULESETS for USER_RULESETS
/
grant select on USER_RULESETS to public with grant option
/

create or replace view DBA_RULESETS
(OWNER, RULESET_NAME, RULESET_STORAGE_TABLE, BASE_TABLE)
as
SELECT u.name, o.name, ru.name||'.'||ro.name, bu.name||'.'||bo.name 
FROM ruleset$ r, obj$ o, user$ u, obj$ ro, user$ ru, obj$ bo, user$ bu
WHERE   o.owner#=u.user#     AND O.OBJ# = R.OBJ# 
        and bo.owner# = bu.user# and bo.obj# = r.baseobj
        and ro.owner# = ru.user# and ro.obj# = r.rules_table
/
drop public synonym DBA_RULESETS
/
create public synonym DBA_RULESETS for DBA_RULESETS
/
grant select on DBA_RULESETS to select_catalog_role
/
comment on table DBA_RULESETS is
'All rulesets in the database'
/
comment on column DBA_RULESETS.OWNER is
'Owner of the ruleset'
/
comment on column DBA_RULESETS.RULESET_NAME is
'Name of the ruleset'
/
comment on column DBA_RULESETS.RULESET_STORAGE_TABLE is
'Name of the table that stores the rules in the ruleset'
/
comment on column DBA_RULESETS.BASE_TABLE is
'Name of the table on that the rules are defined'
/


-- run package creation scripts externally.

