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.