Best practices for settings for Oracle database creation
- by Gary
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
