Rechte,Tabellen und Tablespaces (DBSetup)

Rechte für den 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;

Es wird eine eigene Rolle ‚R_APP_PVSSRDB‘ kreiert und dieser werden auch Rechte zugewiesen:

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;

Public wird die Rolle nur dann wenn RDBconfig.sql-entry auf -> define public_grants = ‘yes‘ gesetzt wurde:

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‘ und ‚ALERT‘-Default-Archivgruppen:

Default-Archivgruppen, die physikalisch unter

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*)

angelegt werden sind: ‚EVENT‘ und ‚ALERT‘

Für die EVENT-Archivgruppe wird ein Tablespace für dynamische Werte und ein Tablespace für normale Werte angelegt. Diese heißen EVENTHISTORY_xxxxx und EVENTHISTORYVALUES_xxxxx

Bei den Alarmen ist es dasselbe – es wird ein Tablespace für dyns und ein Tablespace für normale Alarme angelegt. Diese heißen ALERTHISTORY_xxxxx und ALERTHISTORYVALUES_xxxxx

Werden jetzt noch andere Archivgruppen angelegt, verhält es sich so wie bei der ‚EVENT‘-Archivgruppe – per default werden 2 Tablespaces angelegt.

Welche Tabellen werden angelegt - siehe Kapitel Beschreibung der Tabellen und Views