Best practices for settings for Oracle database creation

Posted by Gary on Server Fault See other posts from Server Fault or by Gary
Published on 2010-02-22T22:37:30Z Indexed on 2010/04/12 4:03 UTC
Read the original article Hit count: 460

Filed under:
|

When installing an Oracle Database, what non-default settings would you normally apply (or consider applying) ?

I'm not after hardware dependent setting (eg memory allocation) or file locations, but more general items. Similarly anything that is a particular requirement for a specific application rather than generally applicable isn't really useful.

Do you separate out code/API schemas (PL/SQL owners) from data schemes (table owners) ? Do you use default or non-default roles, and if the latter, do you password protect the role ?

I'm also interested in whether there's any places where you do a REVOKE of a GRANT that is installed by default. That may be version dependent as 11g seems more locked down for its default install.

These are ones I used in a recent setup. I'd like to know whether I missed anything or where you disagree (and why).

Database Parameters

  • Auditing (AUDIT_TRAIL to DB and AUDIT_SYS_OPERATIONS to YES)
  • DB_BLOCK_CHECKSUM and DB_BLOCK_CHECKING (both to FULL)
  • GLOBAL_NAMES to true
  • OPEN_LINKS to 0 (did not expect them to be used in this environment)

Character set - AL32UTF8

Profiles
I created an amended password verify function that used the apex dictionary table (FLOWS_030000.wwv_flow_dictionary$) as an extra check to prevent simple passwords.

Developer logins

CREATE PROFILE profile_dev LIMIT FAILED_LOGIN_ATTEMPTS 8 
PASSWORD_LIFE_TIME 32 PASSWORD_REUSE_TIME 366 PASSWORD_REUSE_MAX 12
PASSWORD_LOCK_TIME 6 PASSWORD_GRACE_TIME 8
PASSWORD_VERIFY_FUNCTION verify_function_11g
SESSIONS_PER_USER unlimited CPU_PER_SESSION   unlimited
CPU_PER_CALL      unlimited PRIVATE_SGA  unlimited
CONNECT_TIME 1080 IDLE_TIME 180
LOGICAL_READS_PER_SESSION unlimited LOGICAL_READS_PER_CALL unlimited;

Application login

CREATE PROFILE profile_app LIMIT FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LIFE_TIME 999 PASSWORD_REUSE_TIME 999 PASSWORD_REUSE_MAX 1
PASSWORD_LOCK_TIME 999 PASSWORD_GRACE_TIME 999
PASSWORD_VERIFY_FUNCTION verify_function_11g
SESSIONS_PER_USER unlimited CPU_PER_SESSION   unlimited
CPU_PER_CALL      unlimited PRIVATE_SGA  unlimited
CONNECT_TIME      unlimited IDLE_TIME  unlimited
LOGICAL_READS_PER_SESSION unlimited LOGICAL_READS_PER_CALL unlimited;

Privileges for a standard schema owner account

CREATE CLUSTER  
CREATE TYPE  
CREATE TABLE   
CREATE VIEW   
CREATE PROCEDURE   
CREATE JOB  
CREATE MATERIALIZED VIEW   
CREATE SEQUENCE  
CREATE SYNONYM  
CREATE TRIGGER  

© Server Fault or respective owner

Related posts about Oracle

Related posts about best-practices