Rem drv:
Rem
Rem $Header: user_model_tables.sql 29-aug-2005.06:08:37 dcawley Exp $
Rem
Rem user_model_tables.sql
Rem
Rem Copyright (c) 2002, 2005, Oracle. All rights reserved.
Rem
Rem NAME
Rem user_model_tables.sql -
Rem
Rem DESCRIPTION
Rem Tables for the User Model
Rem
Rem NOTES
Rem
Rem
Rem MODIFIED (MM/DD/YY)
Rem dcawley 08/29/05 - use IOT for flat role grants
Rem gsbhatia 06/26/05 - New repmgr header impl
Rem dcawley 06/13/05 - Add primary key to callback table
Rem dcawley 02/24/05 - Compress tables
Rem gsbhatia 02/13/05 - updating repmgr header
Rem gsbhatia 02/09/05 - updating repmgr header
Rem gsbhatia 02/07/05 - updating repmgr header
Rem scgrover 02/01/05 - compress index
Rem scgrover 01/26/05 - make table iot
Rem ktlaw 01/11/05 - add repmgr header
Rem dcawley 09/08/04 - Add flat role grants
Rem dcawley 07/23/04 - Update comments
Rem dcawley 07/07/04 - increase user name size
Rem dcawley 11/13/03 - Add table for contexts
Rem aholser 09/09/03 - add 'deleting' to mgmt_created_users for async
Rem user job deletion
Rem dcawley 09/01/03 - Add job cache
Rem dcawley 11/12/02 - Add Job privileges
Rem dcawley 09/11/02 - Update comments for MGMT_CREATED_USERS
Rem dcawley 09/05/02 - Add MONITORING
Rem dcawley 07/09/02 - Change callback table
Rem aholser 05/20/02 - .
Rem rpinnama 05/15/02 - rpinnama_reorg_rep_scripts
Rem rpinnama 05/14/02 - Restructured.
Rem rpinnama 04/16/02 - Create a table to track users created by EM.
Rem dcawley 01/31/02 - Add table for callbacks.
Rem aholser 01/30/02 - bugs 2198831 and 2198860.
Rem dcawley 01/23/02 - Rename VIEW_ALL_TARGETS_GROUP to VIEW_ANY_TARGET.
Rem dcawley 01/17/02 - Change SYSTEM to .
Rem dcawley 01/16/02 - Rename MGMT_USER_PRIVS to MGMT_PRIV_GRANTS.
Rem dcawley 01/16/02 - Remove DELETE_TARGET_FROM_GROUP.
Rem dcawley 01/16/02 - Remove MGMT_ROLE_PRIVS.
Rem dcawley 01/14/02 - Add FULL_TARGET_GROUP privilege.
Rem dcawley 12/19/01 - Update target group privileges.
Rem dcawley 12/18/01 - Add support for granting implicit privilege.
Rem dcawley 12/05/01 - Add target group privs.
Rem dcawley 11/27/01 - Merged dcawley_add_user_model
Rem dcawley 11/27/01 - Created
Rem
rem
rem PURPOSE
rem
rem The MGMT_PRIVS table contains the list of privileges
rem
rem COLUMNS
rem
rem PRIV_NAME - the name of the privilege
rem
rem PRIV_TYPE - the type of the privilege
rem 0 - SYSTEM
rem 1 - TARGET
rem 2 - TARGET GROUP
rem 3 - JOB
rem
rem CREATOR - the user who created the privilege. This name is used
rem when an Integrator defined privilege is added
rem
rem DESCRIPTION - a description of the privilege
rem
rem NOTES
rem
rem Only the creator of a privilege can drop it. Privileges owned by
rem SYSTEM cannot be dropped.
rem
CREATE TABLE MGMT_PRIVS
(priv_name VARCHAR2(30) NOT NULL,
priv_type NUMBER(1) DEFAULT 1 NOT NULL,
creator VARCHAR2(256) DEFAULT '' NOT NULL,
description VARCHAR2(256) DEFAULT ''
) MONITORING;
rem
rem PURPOSE
rem
rem The MGMT_PRIV_INCLUDES table contains the list of additional
rem privileges that are implictly included for a particular
rem privilege. For example a user with OPERATOR_TARGET also gets VIEW_TARGET
rem
rem COLUMNS
rem
rem PRIV_NAME - the name of the privilege
rem
rem IPRIV_NAME - the name of the included privilege
rem
rem NOTES
rem
rem For the complete list of privileges see the MGMT_PRIV
rem package
rem
CREATE TABLE MGMT_PRIV_INCLUDES
(priv_name VARCHAR2(30),
ipriv_name VARCHAR2(30),
CONSTRAINT MGMT_PRIV_INCLUDES_PK PRIMARY KEY (priv_name, ipriv_name)
) ORGANIZATION INDEX MONITORING;
rem
rem The MGMT_ROLES table contains the list of roles
rem
rem COLUMNS
rem
rem ROLE_NAME - the name of the role
rem
rem DESCRIPTION - a description of the role
rem
rem NOTES
rem
rem
CREATE TABLE MGMT_ROLES
(role_name VARCHAR2(30) NOT NULL,
description VARCHAR2(256) DEFAULT ''
) MONITORING;
rem
rem PURPOSE
rem
rem The MGMT_ROLE_GRANTS table contains the list of roles granted to a user
rem or another role
rem
rem COLUMNS
rem
rem GRANTEE - the user or role that has been granted the role
rem
rem ROLE_NAME - the name of the role
rem
rem WITH_ADMIN - a flag to indicate whether the grantee is allowed to
rem grant this role to another user
rem
rem GRANTEE_IS_ROLE - flag to indicate if the grantee is a role or a user
rem
rem NOTES
rem
rem
CREATE TABLE MGMT_ROLE_GRANTS
(grantee VARCHAR2(256) NOT NULL,
role_name VARCHAR2(30) NOT NULL,
with_admin NUMBER(1) DEFAULT 0 NOT NULL,
grantee_is_role NUMBER(1) NOT NULL,
CONSTRAINT MGMT_ROLE_GRANTS_PK PRIMARY KEY (grantee, role_name)
) ORGANIZATION INDEX COMPRESS 1 MONITORING;
rem
rem PURPOSE
rem
rem The MGMT_FLAT_ROLE_GRANTS table contains the list of flattened roles
rem granted to a role. It does not contain the flattened list granted to a
rem user.
rem
rem COLUMNS
rem
rem ROLE_GRANTEE - the role that has been granted the role
rem
rem ROLE_NAME - the name of the granted role
rem
rem NOTES
rem The purpose of this table is to avoid the use of recursive queries
rem that use CONNECT BY
rem
CREATE TABLE MGMT_FLAT_ROLE_GRANTS
(role_grantee VARCHAR2(256) NOT NULL,
role_name VARCHAR2(30) NOT NULL,
CONSTRAINT MGMT_FLAT_ROLE_GRANTS_PK PRIMARY KEY (role_grantee, role_name)
) ORGANIZATION INDEX COMPRESS 1 MONITORING;
rem
rem PURPOSE
rem
rem The MGMT_PRIV_GRANTS table contains the list of privileges granted to a
rem user or role
rem
rem COLUMNS
rem
rem GRANTEE - the user or role that has been granted the privilege
rem
rem PRIV_NAME - the name of the privilege
rem
rem GUID - target guid in the case of a target privilege
rem target group guid in the case of target group privilege
rem job guid in the case of a job privilege
rem '0000000000000000' in the case of a system privilege
rem
rem GRANTEE_IS_ROLE - flag to indicate if the grantee is a role or a user
rem
rem DIRECT_GRANT - set to 1 to indicate the privilege was granted directly
rem via GRANT_PRIV
rem
rem REF_COUNT - the reference count for the number of times the privilege
rem has been implicitly granted e.g. VIEW_TARGET would be
rem granted implicitly if OPERATOR_TARGET was granted
rem
rem NOTES
rem
CREATE TABLE MGMT_PRIV_GRANTS
(grantee VARCHAR2(256) NOT NULL,
priv_name VARCHAR2(30) NOT NULL,
guid RAW(16) DEFAULT '0000000000000000' NOT NULL,
grantee_is_role NUMBER(1) NOT NULL,
direct_grant NUMBER(1) DEFAULT 1 NOT NULL,
ref_count NUMBER DEFAULT 0 NOT NULL,
CONSTRAINT MGMT_PRIV_GRANTS_PK PRIMARY KEY (grantee, priv_name, guid)
) ORGANIZATION INDEX COMPRESS 2 MONITORING;
rem
rem PURPOSE
rem
rem The MGMT_USER_TARGETS table contains the list of target guids on which
rem a user has VIEW_TARGET privilege.
rem
rem COLUMNS
rem
rem GRANTEE - the user who has been granted the privilege
rem
rem TARGET_GUID - target guid
rem
CREATE TABLE MGMT_USER_TARGETS
(grantee VARCHAR2(256),
target_guid RAW(16),
CONSTRAINT MGMT_USER_TARGETS_PK PRIMARY KEY (grantee, target_guid)
) ORGANIZATION INDEX COMPRESS 1 MONITORING;
rem
rem PURPOSE
rem
rem The MGMT_USER_JOBS table contains the list of jobs guids on which
rem a user has VIEW_JOB privilege
rem
rem COLUMNS
rem
rem GRANTEE - the user who has been granted the privilege
rem
rem JOB_ID - job id
rem
CREATE TABLE MGMT_USER_JOBS
(grantee VARCHAR2(256),
job_id RAW(16),
CONSTRAINT MGMT_USER_JOBS_PK PRIMARY KEY (grantee, job_id)
) ORGANIZATION INDEX COMPRESS 1 MONITORING;
rem
rem PURPOSE
rem
rem The MGMT_USER_TEMPLATES table contains the list of template guids on which
rem a user has VIEW_TEMPLATE privilege
rem
rem COLUMNS
rem
rem GRANTEE - the user who has been granted the privilege
rem
rem TEMPLATE_GUID - template guid
rem
CREATE TABLE MGMT_USER_TEMPLATES
(grantee VARCHAR2(256),
template_guid RAW(16),
CONSTRAINT MGMT_USER_TEMPLATES_PK PRIMARY KEY (grantee, template_guid)
) ORGANIZATION INDEX COMPRESS 1 MONITORING;
rem
rem PURPOSE
rem
rem The MGMT_USER_REPORT_DEFS table contains the list of report defintion
rem guids on which a user has VIEW_REPORT privilege
rem
rem COLUMNS
rem
rem GRANTEE - the user who has been granted the privilege
rem
rem REPORT_GUID - report guid
rem
CREATE TABLE MGMT_USER_REPORT_DEFS
(grantee VARCHAR2(256),
report_guid RAW(16),
CONSTRAINT MGMT_USER_REPORT_DEFS_PK PRIMARY KEY (grantee, report_guid)
) ORGANIZATION INDEX COMPRESS 1 MONITORING;
rem
rem PURPOSE
rem
rem The MGMT_USER_CAS table contains the list of corrective actions ids on
rem which a user has VIEW_CA privilege
rem
rem COLUMNS
rem
rem GRANTEE - the user who has been granted the privilege
rem
rem JOB_ID - job id
rem
CREATE TABLE MGMT_USER_CAS
(grantee VARCHAR2(256),
job_id RAW(16),
CONSTRAINT MGMT_USER_CAS_PK PRIMARY KEY (grantee, job_id)
) ORGANIZATION INDEX COMPRESS 1 MONITORING;
rem
rem PURPOSE
rem
rem The MGMT_USER_CONTEXT table contains the context value for each
rem EM User. The context value is used to determine the list of targets,
rem a user can see
rem
rem COLUMNS
rem
rem CLIENT_IDENTIFIER - the user
rem
rem ATTRIBUTE - the predicate which can be one of the following:
rem MGMT_USER.EM_TARGET_PREDICATE
rem MGMT_USER.EM_JOB_PREDICATE
rem MGMT_USER.EM_TEMPLATE_PREDICATE
rem MGMT_USER.EM_REPORT_DEF_PREDICATE
rem MGMT_USER.EM_REPORT_PREDICATE
rem MGMT_USER.EM_CA_PREDICATE
rem
rem VALUE - the value which can be one of the following:
rem MGMT_USER.CREATE_PREDICATE
rem MGMT_USER.ANY_PREDICATE
rem MGMT_USER.PRIV_PREDICATE
rem
rem Predicate value specially for targets
rem EM_MONITOR_PREDICATE
rem
CREATE TABLE MGMT_USER_CONTEXT
(client_identifier VARCHAR2(256),
attribute NUMBER,
value NUMBER,
CONSTRAINT MGMT_USER_CONTEXT_PK PRIMARY KEY (client_identifier, attribute)
) ORGANIZATION INDEX COMPRESS 1 MONITORING;
rem
rem PURPOSE
rem
rem The MGMT_USER_CALLBACKS table contains the name of PL/SQL
rem procedures that are called when one of the following happens:
rem * a user is created
rem * a user is dropped
rem * a user is granted a privilege
rem * a user has a privilege revoked
rem * a user is granted a role
rem * a user has a role revoked
rem
rem COLUMNS
rem
rem CALLBACK - the name of the callback procedure which must be a procedure
rem that is defined as follows :
rem
rem PROCEDURE USER_CALLBACK(user_name_in IN VARCHAR2,
rem type_in IN NUMBER);
rem
rem PROCEDURE PRIV_CALLBACK(user_name_in IN VARCHAR2,
rem priv_names_in IN SMP_EMD_STRING_ARRAY,
rem guids_in IN MGMT_USER_GUID_ARRAY,
rem type_in IN NUMBER);
rem
rem PROCEDURE ROLE_CALLBACK(user_name_in IN VARCHAR2,
rem role_name_in IN VARCHAR2,
rem type_in IN NUMBER);
rem
rem TYPE - the type of the callback
rem
CREATE TABLE MGMT_USER_CALLBACKS
(callback VARCHAR2(100),
type NUMBER,
CONSTRAINT MGMT_USER_CALLBACKS_PK PRIMARY KEY (callback, type)
) ORGANIZATION INDEX MONITORING;
rem
rem PURPOSE
rem
rem The MGMT_CREATED_USERS table contains the names of user
rem that is created by the EM.
rem DropRep uses this table and deletes only users created by EM.
rem i.e., users listed in this table.
rem
rem COLUMNS
rem
rem USER_NAME - the name of the user.
rem SYSTEM_USER - whether or not the user was created outside em.
rem 0 - em-created user
rem 1 - system-created user
rem 2 - this is a single-sign-on user
rem DELETING - whether or not this user is currently being deleted
rem 0 - not deleting
rem 1 - deleting
rem
CREATE TABLE MGMT_CREATED_USERS
(user_name VARCHAR2(256) PRIMARY KEY,
system_user NUMBER(1) DEFAULT 0,
deleting NUMBER(1) DEFAULT 0) MONITORING;
rem
rem MGMT_LOGIN_ASSISTANTS
rem
rem PURPOSE
rem The MGMT_LOGIN_ASSISTANTS table holds information about all
rem currently available login assistants. An EM login assistant is
rem a java class that provides support for one kind of authentication.
rem
rem COLUMNS
rem login_method_name The "name" for this login assistant
rem login_order The index of this assistant: determines the
rem order in which the assistants will be called to
rem perform authentication. If set to -1, this login
rem assistant will not be used for authentication
rem login_asst_classname The name of the java class that
rem implements this login assistant
rem
CREATE TABLE MGMT_LOGIN_ASSISTANTS (
login_asst_name VARCHAR2(64),
login_index NUMBER(2),
login_asst_classname VARCHAR2(512),
CONSTRAINT PK_LOGIN_ASSTS PRIMARY KEY(login_asst_name)
) MONITORING;