GOCDB/Regional Module Technical Documentation/DeployOnOracle

From EGIWiki
Jump to: navigation, search

<<Back to Install

Oracle DB Preparation Example

We advise that you create a dedicated GOCDB5 user. For Oracle, you can create the user with the following script (substitute GOCDB5 for your username and <PASSWORD> with a sensible password). Run this script as the Oracle admin/system user:

-- Manage GOCDB5 user if already exists (optional) --
drop user gocdb5 cascade;
ALTER USER gocdb5 IDENTIFIED BY new_password;

-- CREATE USER SQL
CREATE USER GOCDB5 IDENTIFIED BY <PASSWORD> 
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP";
-- ROLES
GRANT "RESOURCE" TO GOCDB5 ;
-- SYSTEM PRIVILEGES
GRANT CREATE TRIGGER TO GOCDB5 ;
GRANT CREATE SEQUENCE TO GOCDB5 ;
GRANT CREATE TABLE TO GOCDB5 ;
GRANT CREATE JOB TO GOCDB5 ;
GRANT CREATE PROCEDURE TO GOCDB5 ;
GRANT CREATE TYPE TO GOCDB5 ;
GRANT CREATE SESSION TO GOCDB5 ;

Manage the GOCDB password expiry

By default, Oracle 11g will expire a password in 180 days. In previous versions of Oracle, the default policy was UNLIMITED, so please be aware of this change! As a system user, you can see your password expiry settings by looking at the PASSWORD_LIFE_TIME and PASSWORD_GRACE_TIME parameters in the DBA_PROFILES table:

-- select the profile for the GOCDB user (e.g. will return DEFAULT)
SELECT profile FROM dba_users WHERE username = 'GOCDB5'; 

-- select the password expiry settings for the profile assigned to the GOCDB5 user
select resource_name,resource_type, limit from dba_profiles where profile=DEFAULT; 


If you prefer, you can update the default expiry from 180days to UNLIMITED using the following (assumng GOCDB5 user profile is DEFAULT):

-- requires system privilege
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;