Rights, Tables and Tabelspaces (DPSetup)
This chapter provides a short summary which rights, tables and tablespaces will be assigned/created during the DBSetup.
Create user:
For the schema-user and the application user a tablespace will be created (if define yesno_newuser = 'yes').
Rights for schema user:
GRANT ALTER TABLESPACE TO &schema_user;
GRANT CREATE ANY DIRECTORY TO &schema_user;
GRANT CREATE ANY INDEX to &schema_user;
GRANT CREATE ANY SYNONYM TO &schema_user;
GRANT CREATE ANY TABLE TO &schema_user;
GRANT CREATE ANY TRIGGER TO &schema_user;
GRANT CREATE ANY VIEW TO &schema_user;
GRANT CREATE ANY procedure to &schema_user;
GRANT CREATE ANY SYNONYM to &schema_user;
GRANT CREATE ANY type to &schema_user;
GRANT CREATE LIBRARY TO &schema_user;
GRANT CREATE SEQUENCE TO &schema_user;
GRANT CREATE SESSION TO &schema_user;
GRANT CREATE SYNONYM TO &schema_user;
GRANT CREATE PUBLIC SYNONYM TO &schema_user;
GRANT CREATE TABLE TO &schema_user;
GRANT CREATE TABLESPACE TO &schema_user;
GRANT CREATE VIEW TO &schema_user;
GRANT CREATE LIBRARY to &schema_user;
GRANT CREATE PROCEDURE to &schema_user;
GRANT CREATE TYPE to &schema_user;
GRANT CREATE JOB to &schema_user;
GRANT DEBUG CONNECT SESSION TO &schema_user;
GRANT DROP ANY SYNONYM TO &schema_user;
GRANT DROP ANY DIRECTORY TO &schema_user;
GRANT DROP PUBLIC SYNONYM to &schema_user;
GRANT DROP TABLESPACE TO &schema_user;
GRANT EXECUTE ON SYS.DBMS_LOCK TO &schema_user;
GRANT EXECUTE ON SYS.DBMS_PLUGTS TO &schema_user;
GRANT EXECUTE ON SYS.DBMS_UTILITY TO &schema_user;
GRANT MANAGE TABLESPACE TO &schema_user;
GRANT EXP_FULL_DATABASE TO &schema_user;
GRANT IMP_FULL_DATABASE TO &schema_user;
GRANT UNLIMITED TABLESPACE TO &schema_user;
GRANT SELECT ANY DICTIONARY to &schema_user;
GRANT EXECUTE ON SYS.UTL_FILE TO &schema_user;
GRANT CONNECT, RESOURCE to &schema_user;
GRANT SELECT ANY DICTIONARY to &schema_user;
A role ‚R_APP_PVSSRDB‘ will be created and has following rights:
create role R_APP_PVSSRDB
GRANT CREATE SESSION TO R_APP_PVSSRDB;
GRANT ALTER SESSION TO R_APP_PVSSRDB;
grant select on ARC_HISTTABLES to R_APP_PVSSRDB;
grant select on ALERTHISTORYDBLINK to R_APP_PVSSRDB;
grant select on VALERTSCREEN to R_APP_PVSSRDB;
grant execute on CS to R_APP_PVSSRDB;
grant select, update, delete, insert on CSFUNCTION to R_APP_PVSSRDB;
grant select, update, delete, insert on CSSTEP to R_APP_PVSSRDB;
grant select, update, delete, insert on CSCALCULATION to R_APP_PVSSRDB;
grant select, update, delete, insert on CSUNIT to R_APP_PVSSRDB;
grant select, update, delete, insert on CSINTERVAL to R_APP_PVSSRDB;
--grant select, update, delete, insert on CSJOB_NEXTDATE to R_APP_PVSSRDB;
grant select, update, delete, insert on CSTIMELOG to R_APP_PVSSRDB;
grant execute on ArcUtils to R_APP_PVSSRDB;
grant execute on ArchiveControl to R_APP_PVSSRDB;
grant execute on Valarch to R_APP_PVSSRDB;
grant execute on ARC_XPI to R_APP_PVSSRDB;
grant execute on LS to R_APP_PVSSRDB;
grant execute on StdTestRDB to R_APP_PVSSRDB;
grant execute on rdbHelpers to R_APP_PVSSRDB;
grant execute on LANGSTRING to R_APP_PVSSRDB;
grant execute on ARC_GRP_ARR to R_APP_PVSSRDB;
grant select, update, delete, insert on ARC_ARCHIVE to R_APP_PVSSRDB;
grant select, update, delete, insert on ARC_CONFIG to R_APP_PVSSRDB;
grant select, update, delete, insert on ARC_GROUP to R_APP_PVSSRDB;
grant select, update, delete, insert on ARC_GROUP_DEL to R_APP_PVSSRDB;
grant select, update, delete, insert on ARC_HISTORYPATH to R_APP_PVSSRDB;
grant select, update, delete, insert on ARC_LOG to R_APP_PVSSRDB;
grant select, update, delete, insert on ARC_SITE to R_APP_PVSSRDB;
grant select, update, delete, insert on ARC_STATEMENT to R_APP_PVSSRDB;
grant select, update, delete, insert on ARC_TEMPLATE to R_APP_PVSSRDB;
grant select, update, delete, insert on DP to R_APP_PVSSRDB;
grant select, update, delete, insert on DPE to R_APP_PVSSRDB;
grant select, update, delete, insert on DPT to R_APP_PVSSRDB;
grant select, update, delete, insert on ELEMENTS to R_APP_PVSSRDB;
grant select, update, delete, insert on JAVA_LOG to R_APP_PVSSRDB;
grant select, update, delete, insert on STDTESTOUTPUT to R_APP_PVSSRDB;
grant select, update, delete, insert on SYSTEMS to R_APP_PVSSRDB;
grant select on ARC_HISTTABLES to R_APP_PVSSRDB;
grant select on ALERTHISTORYDBLINK to R_APP_PVSSRDB;
grant select on VALERTSCREEN to R_APP_PVSSRDB;
-- Sequences
grant select on SEQ_ARC_ARCHIVE to R_APP_PVSSRDB;
grant select on SEQ_ARC_LOG to R_APP_PVSSRDB;
R_APP_PVSSRDB will be public only if the RDBconfig.sql-entry -> define public_grants = ‘yes‘ was set:
if ('&public_grants'='no') then (RDBconfig.sql-entry -> define public_grants = ‘no’)
execute immediate 'GRANT R_APP_PVSSRDB TO &app_user';
else
execute immediate 'GRANT R_APP_PVSSRDB TO &app_user'; -- application shall have the rights also when they are revoked from PUBLIC
execute immediate 'GRANT R_APP_PVSSRDB TO PUBLIC';
end if;
APPLICATION-User:
create or replace synonym &app_user..ArcUtils for &schema_user..ArcUtils;
create or replace synonym &app_user..ArchiveControl for &schema_user..ArchiveControl;
create or replace synonym &app_user..Valarch for &schema_user..Valarch;
create or replace synonym &app_user..ARC_XPI for &schema_user..ARC_XPI;
--create or replace synonym &app_user..RDB_COMMON for &schema_user..RDB_Common;
create or replace synonym &app_user..LS for &schema_user..LS;
create or replace synonym &app_user..StdTestRDB for &schema_user..StdTestRDB;
create or replace synonym &app_user..rdbHelpers for &schema_user..rdbHelpers;
-- Types
create or replace synonym &app_user..LANGSTRING for &schema_user..LANGSTRING;
create or replace synonym &app_user..ARC_GRP_ARR for &schema_user..ARC_GRP_ARR;
-- Tables
create or replace synonym &app_user..ARC_ARCHIVE for &schema_user..ARC_ARCHIVE;
create or replace synonym &app_user..ARC_CONFIG for &schema_user..ARC_CONFIG;
create or replace synonym &app_user..ARC_GROUP for &schema_user..ARC_GROUP;
create or replace synonym &app_user..ARC_GROUP_DEL for &schema_user..ARC_GROUP_DEL;
create or replace synonym &app_user..ARC_HISTORYPATH for &schema_user..ARC_HISTORYPATH;
create or replace synonym &app_user..ARC_LOG for &schema_user..ARC_LOG;
create or replace synonym &app_user..ARC_SITE for &schema_user..ARC_SITE;
create or replace synonym &app_user..ARC_STATEMENT for &schema_user..ARC_STATEMENT;
create or replace synonym &app_user..ARC_TEMPLATE for &schema_user..ARC_TEMPLATE;
create or replace synonym &app_user..DP for &schema_user..DP;
create or replace synonym &app_user..DPE for &schema_user..DPE;
create or replace synonym &app_user..DPT for &schema_user..DPT;
create or replace synonym &app_user..ELEMENTS for &schema_user..ELEMENTS;
create or replace synonym &app_user..JAVA_LOG for &schema_user..JAVA_LOG;
create or replace synonym &app_user..STDTESTOUTPUT for &schema_user..STDTESTOUTPUT;
create or replace synonym &app_user..SYSTEMS for &schema_user..SYSTEMS;
create or replace synonym &app_user..ARC_HISTTABLES for &schema_user..ARC_HISTTABLES;
create or replace synonym &app_user..ALERTHISTORYDBLINK for &schema_user..ALERTHISTORYDBLINK;
create or replace synonym &app_user..VALERTSCREEN for &schema_user..VALERTSCREEN;
create or replace synonym &app_user..ALERTADDVALUESPIVOT for &schema_user..ALERTADDVALUESPIVOT
create or replace synonym &app_user..VALERTSCREENPIVOT for &schema_user..VALERTSCREENPIVOT
EVENT‘ and ‚ALERT‘ Default Archive Groups:
Default archive groups, are saved in
define path_alert = ‘….' -- Path Alert, path of alertgrouptype-database-files (on DB server*)
define path_event = '…' -- Path Event, path of eventgrouptype-database-files (on DB server*)
for ‚EVENT‘ and ‚ALERT‘
For the EVENT archive group a tablespace is created for dynamic values and a tablespace for normal values. These are called EVENTHISTORY_xxxxx and EVENTHISTORYVALUES_xxxxx.
Also for the alerts a tablespace is created for dyns and a tablespace for normal alerts. These are called ALERTHISTORY_xxxxx and ALERTHISTORYVALUES_xxxxx.
If other archive groups are created, two tablespaces are created for a group by default, like for the EVENT archive group.
Which tables are created - see chapter Description of the Tables and Views.