Difference between revisions of "GOCDB/Regional Module Technical Documentation/DeployOnOracle"
Jump to navigation
Jump to search
Line 5: | Line 5: | ||
<pre> | <pre> | ||
-- CREATE USER SQL | -- CREATE USER SQL | ||
CREATE USER | CREATE USER GOCDB5 IDENTIFIED BY <PASSWORD> | ||
DEFAULT TABLESPACE "USERS" | DEFAULT TABLESPACE "USERS" | ||
TEMPORARY TABLESPACE "TEMP"; | TEMPORARY TABLESPACE "TEMP"; | ||
-- ROLES | -- ROLES | ||
GRANT "RESOURCE" TO | GRANT "RESOURCE" TO GOCDB5 ; | ||
-- SYSTEM PRIVILEGES | -- SYSTEM PRIVILEGES | ||
GRANT CREATE TRIGGER TO | GRANT CREATE TRIGGER TO GOCDB5 ; | ||
GRANT CREATE SEQUENCE TO | GRANT CREATE SEQUENCE TO GOCDB5 ; | ||
GRANT CREATE TABLE TO | GRANT CREATE TABLE TO GOCDB5 ; | ||
GRANT CREATE JOB TO | GRANT CREATE JOB TO GOCDB5 ; | ||
GRANT CREATE PROCEDURE TO | GRANT CREATE PROCEDURE TO GOCDB5 ; | ||
GRANT CREATE TYPE TO | GRANT CREATE TYPE TO GOCDB5 ; | ||
GRANT CREATE SESSION TO | GRANT CREATE SESSION TO GOCDB5 ; | ||
</pre> | </pre> | ||
Line 25: | Line 25: | ||
<pre> | <pre> | ||
-- select the profile for the GOCDB user (e.g. will return DEFAULT) | -- select the profile for the GOCDB user (e.g. will return DEFAULT) | ||
SELECT profile FROM dba_users WHERE username = ' | SELECT profile FROM dba_users WHERE username = 'GOCDB5'; | ||
-- select the password expiry settings for the profile assigned to the | -- 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; | select resource_name,resource_type, limit from dba_profiles where profile=DEFAULT; | ||
</pre> | </pre> | ||
<br/> | <br/> | ||
If you prefer, you can update the default expiry from 180days to UNLIMITED using the following (assumng | If you prefer, you can update the default expiry from 180days to UNLIMITED using the following (assumng GOCDB5 user profile is DEFAULT): | ||
<pre> | <pre> | ||
-- requires system privilege | -- requires system privilege | ||
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED; | ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED; | ||
</pre> | </pre> |
Revision as of 17:22, 20 November 2013
Oracle DB Preparation Example
We advise that you create a dedicated GOCDB user. For Oracle, you can create the gocdb user with the following script (substitute <PASSWORD> with a sensible password and run as system user):
-- 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;