8.5 Setting Up Initialization Parameters for Security As we described in Chapter 2, the initialization file is a list of parameters supplied by Oracle with your database. You can change the values of these parameters to configure the database system. The file is used to modify parameters that affect performance, set global defaults and limits, and establish file names and locations. This file is generally referred to as the INIT.ORA file even though its actual name might vary. By default, when a database is created, a file with the naming convention of INIT<DATABASE SID>.ORA is also created. From version 7.3 on, some of the INIT.ORA parameters can be dynamically modified using the following commands: - ALTER SESSION
-
When specified, the parameters are only changed for that session and will not remain in effect after the session ends. - ALTER SYSTEM
-
When specified, the parameters will remain in effect until the database is shut down but may not affect the current session. - ALTER SYSTEM DEFFERED
-
When specified, will not affect the current sessions but will affect all future sessions until the database is shut down. 8.5.1 Viewing the Parameters There are more than 100 different parameters that can be set in the INIT.ORA file. When a value is changed in the INIT.ORA file, the value will not take effect until the database is "bounced," or shut down and then restarted using the modified INIT.ORA parameter file. Since the Oracle RDBMS has a default value for every parameter available, the absence of a value for a parameter in the file will signal that the default value is to be used. The Oracle-supplied default INIT.ORA file is a basic, sample file that gives information about database sizes in relation to parameter settings. The current values for parameters can be viewed by using the line mode of the Server Manager and issuing the command: SVRMGR> SHOW PARAMETERS There are parameters in Oracle that have minimum values. If these parameters are set too low, Oracle either will not start or will perform very badly . Take care when modifying INIT.ORA parameters. Here is a list of the parameters available from the SHOW PARAMETERS command in an Oracle8 version 8.0.4 default database on a Windows NT system. A few of these parameters need some explanation. As we mentioned earlier in this chapter, if OS_AUTHENT_PREFIX is set to an empty string ("") and REMOTE_OS_AUTHENT is set to "TRUE," then if an "identified externally" account is created, the potential is there for a breach of database security. In the list, you will notice that the parameter AUDIT_TRAIL is set to the value "db." See Chapter 10, for more information about this parameter. | There are a number of parameters you'll want to evaluate for their effect on database security. Each of these parameters is identified by an asterisk (*) in the following list. We've also starred the parameters that represent an operating system directory to remind you that the operating system directories must be protected from casual user interaction and possible destruction. | | NAME TYPE VALUE ----------------------------------- ------- ------------------------------ O7_DICTIONARY_ACCESSIBILITY boolean TRUE allow_partial_sn_results boolean FALSE always_anti_join string NESTED_LOOPS always_semi_join string standard aq_tm_processes integer 0 arch_io_slaves integer 0 *audit_trail string DB b_tree_bitmap_plans boolean FALSE *background_dump_dest string %RDBMS80%\trace backup_disk_io_slaves integer 0 backup_tape_io_slaves boolean FALSE bitmap_merge_area_size integer 1048576 blank_trimming boolean FALSE buffer_pool_keep string buffer_pool_recycle string cache_size_threshold integer 20 cleanup_rollback_entries integer 20 close_cached_open_cursors boolean FALSE commit_point_strength integer 1 compatible string 8.0.0 compatible_no_recovery string 0.0.0 complex_view_merging boolean FALSE control_file_record_keep_time integer 7 *control_files string C:\orant\DATABASE\ctl1ORCL.ora cpu_count integer 1 create_bitmap_area_size integer 8388608 cursor_space_for_time boolean FALSE db_block_buffers integer 200 db_block_checkpoint_batch integer 8 db_block_checksum boolean FALSE db_block_lru_extended_statistics integer 0 db_block_lru_latches integer 1 db_block_lru_statistics boolean FALSE db_block_max_dirty_target integer 4294967294 db_block_size integer 2048 db_domain string WORLD db_file_direct_io_count integer 64 db_file_multiblock_read_count integer 8 db_file_name_convert string db_file_simultaneous_writes integer 4 db_files integer 1024 db_name string ORCL db_writer_processes integer 1 dblink_encrypt_login boolean FALSE dbwr_io_slaves integer 0 delayed_logging_block_cleanouts boolean TRUE discrete_transactions_enabled boolean FALSE disk_asynch_io boolean TRUE distributed_lock_timeout integer 300 distributed_recovery_connection_hol integer 200 distributed_transactions integer 5 dml_locks integer 300 enqueue_resources integer 320 event string fast_full_scan_enabled boolean FALSE fixed_date string freeze_DB_for_fast_instance_recover boolean FALSE gc_defer_time integer 10 gc_files_to_locks string gc_latches integer 2 gc_lck_procs integer 1 gc_releasable_locks integer 0 gc_rollback_locks string *global_names boolean TRUE hash_area_size integer 0 hash_join_enabled boolean TRUE hash_multiblock_io_count integer 1 hi_shared_memory_address integer 0 ifile file instance_groups string instance_number integer 0 job_queue_interval integer 10 job_queue_keep_connections boolean FALSE job_queue_processes integer 2 large_pool_min_alloc string 16K large_pool_size string 0 lgwr_io_slaves integer 0 license_max_sessions integer 0 license_max_users integer 0 license_sessions_warning integer 0 lm_locks integer 12000 lm_procs integer 96 lm_ress integer 6000 local_listener string lock_name_space string lock_sga boolean FALSE lock_sga_areas integer 0 log_archive_buffer_size integer 127 log_archive_buffers integer 4 *log_archive_dest string %RDBMS80%\ *log_archive_duplex_dest string log_archive_format string ARC%s.%t log_archive_min_succeed_dest integer 1 log_archive_start boolean FALSE log_block_checksum boolean FALSE log_buffer integer 8192 log_checkpoint_interval integer 10000 log_checkpoint_timeout integer 0 log_checkpoints_to_alert boolean FALSE log_file_name_convert string log_files integer 255 log_simultaneous_copies integer 0 log_small_entry_max_size integer 80 max_commit_propagation_delay integer 90000 max_dump_file_size integer 10240 *max_enabled_roles integer 20 max_rollback_segments integer 30 max_transaction_branches integer 8 mts_dispatchers string mts_listener_address string mts_max_dispatchers integer 5 mts_max_servers integer 20 mts_multiple_listeners boolean FALSE mts_rate_log_size string mts_rate_scale string mts_servers integer 0 mts_service string ORCL nls_calendar string nls_currency string nls_date_format string nls_date_language string nls_iso_currency string nls_language string AMERICAN nls_numeric_characters string nls_sort string nls_territory string AMERICA object_cache_max_size_percent integer 10 object_cache_optimal_size integer 102400 ogms_home string open_cursors integer 50 open_links integer 4 open_links_per_instance integer 4 *ops_admin_group string optimizer_features_enable string 8.0.0 optimizer_mode string CHOOSE optimizer_percent_parallel integer 0 optimizer_search_limit integer 5 oracle_trace_collection_name string oracle_trace_collection_path string %OTRACE80%\ADMIN\CDF\ oracle_trace_collection_size integer 5242880 oracle_trace_enable boolean FALSE oracle_trace_facility_name string oracled oracle_trace_facility_path string %OTRACE80%\ADMIN\FDF\ *os_authent_prefix string OPS$ *os_roles boolean FALSE parallel_adaptive_multi_user boolean FALSE parallel_broadcast_enabled boolean FALSE parallel_default_max_instances integer 0 parallel_execution_message_size integer 2148 parallel_instance_group string parallel_max_servers integer 5 parallel_min_message_pool integer 48330 parallel_min_percent integer 0 parallel_min_servers integer 0 parallel_server boolean FALSE parallel_server_idle_time integer 5 parallel_transaction_resource_timeo integer 300 partition_view_enabled boolean FALSE plsql_v2_compatibility boolean FALSE pre_page_sga boolean FALSE *processes integer 59 push_join_predicate boolean FALSE read_only_open_delayed boolean FALSE recovery_parallelism integer 0 remote_dependencies_mode string TIMESTAMP *remote_login_passwordfile string SHARED *remote_os_authent boolean FALSE *remote_os_roles boolean FALSE replication_dependency_tracking boolean TRUE *resource_limit boolean FALSE rollback_segments string row_cache_cursors integer 10 row_locking string always sequence_cache_entries integer 10 sequence_cache_hash_buckets integer 10 serial_reuse string DISABLE serializable boolean FALSE session_cached_cursors integer 0 session_max_open_files integer 0 sessions integer 69 shared_memory_address integer 0 shared_pool_reserved_min_alloc string 4K shared_pool_reserved_size string 500000 shared_pool_size string 10000000 snapshot_refresh_interval integer 60 snapshot_refresh_keep_connections boolean FALSE snapshot_refresh_processes integer 0 sort_area_retained_size integer 0 sort_area_size integer 65536 sort_direct_writes string AUTO sort_read_fac integer 20 sort_spacemap_size integer 512 sort_write_buffer_size integer 32768 sort_write_buffers integer 2 spin_count integer 1 sql92_security boolean FALSE sql_trace boolean FALSE star_transformation_enabled boolean FALSE tape_asynch_io boolean TRUE temporary_table_locks integer 69 text_enable boolean TRUE thread integer 0 timed_os_statistics integer 0 timed_statistics boolean TRUE transaction_auditing boolean TRUE transactions integer 75 transactions_per_rollback_segment integer 11 use_indirect_data_buffers boolean FALSE use_ism boolean TRUE *user_dump_dest string %RDBMS80%\trace utl_file_dir string |