Best practices for settings for Oracle database creation

Question

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  

Answer 1

Here is something I ran across once, which is an example of someone elses best practices on the older version of Oracle :

http://www.akadia.com/services/ora_linux_install_10g.html

Answer 2

Auditing -- off unless there's a requirement from the customer to have it enabled.

Separation of Code Schema from Data Schema: No, but definitely isolate the Code and Data schema from the users, where they access the underlying tables/code via roles or grants.