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.