■■Default Users
・EXPIRED：refers to the password: the first time a user logs on to the database with that username, he will be prompted to change the password.
・LOCKED：means that it is impossible to connect with that account anyway; it must be unlocked before it can be used.
*TIP:
The DBSNMP and SYSMAN accounts are for the use of Enterprise Manager, either Grid Control or Database Control. To change their passwords, you must use the emctl utility.
SQL>alter user XXX unlock;
■■Public Privileges
*You should always consider revoking the execution privileges on the UTL packages,though remember that application software may assume that the privilege is there.
SQL> revoke execute on utl_file from public;
• UTL_FILE:
allows users to read and write any file and directory that is accessible to the operating system user under whose identity the Oracle processes are running.
The package is to a certain extent controlled by the UTL_FILE_DIR instance parameter
• UTL_TCP:
This allows users to open TCP ports on the server machine for connections to any accessible address on the network.
•UTL_SMTP
this package lets users send mail messages;
restricted by the UTL_SMTP_SERVER instance parameter
•UTL_HTTP
HTTP messages and receive responses, in effect, converting your database into a web browser.
■■Security Critical Instance Parameters
*TIP:
All of the Security parameters described here are static:you must restart the instance for a change to take effect.
•UTL_FILE_DIR defaults to NULL
All users with execute permission on the UTL_FILE package have access to all the directories listed in the UTL_FILE_DIR parameter.
The UTL_FILE_DIR parameter can include wildcards. Never set it to '*',because that will allow all users access to everything that the database owner can see, including the ORACLE_HOME and all the database files
eg:
SQL> alter system set utl_file_dir='/oracle/tmp','/oracle/interface' scope=spfile;
•REMOTE_OS_AUTHENT defaults to FALSE
OS_AUTHENT_PREFIX default is “OPS$”
related to external authentication
SQL> create user jon identified externally;
SQL> alter system set os_authent_prefix='' scope=spfile;
It is generally considered to be bad practice to enable remote operating system authentication.
•O7_DICTIONARY_ACCESSIBILITY defaults to FALSE
controls the effect of granting object privileges with the ANY keyword.
defaults to false, meaning that the ANY privileges exclude objects owned by SYS, thus protecting the data dictionary;
If you change the parameter to TRUE, then ANY really does mean ANY, and JON will be able to see the data dictionary as well as all user data.
SQL> grant select any table to jon;
If you have users who really do need access to the data dictionary, rather than setting O7_DICTIONARY_ACCESSIBILITY to true, consider granting them the SELECT ANY DICTIONARY privilege.
•REMOTE_LOGIN_PASSWORDFILE default of NONE
controls whether it is possible to connect to the instance as a user with the SYSDBA privilege over the network
1)NONE:the only way to get a SYSDBA connection is to log on to the operating system of the server machine as a member of the operating system group that owns the Oracle software
2)EXCLUSIVE:means that the instance will look for a file whose name includes the instance name: PWD.ora on Windows, orapw on Unix, where is the instance name. This file will have instance-specific passwords.
3)SHARED:that all instances running of the same Oracle home directory will share a common password file This will have just one password within it for the SYS user that is common to all the instances.
※
V$PWFILE_USERS:shows you which users have their passwords entered in the password file, and whether they have the SYSOPER privilege, the SYSDBA privilege, or both.
orapwd：create the password file and embed within it a password for the SYS user
To add another user to the password file, grant him either the SYSDBA or SYSOPER privilege:
SQL>grant sysdba to XXX identified by XXX;
■■Password Profiles
・To enable resource profiles, the instance parameter RESOURCE_LIMIT (default faulse)must be set to TRUE.
・Password profiles are always enabled, irrespective of this parameter.
SQL> select username,profile from dba_users;
SQL> select * from dba_profiles where profile ='DEFAULT';
SQL> alter user jon password expire;
SQL> alter user jon account lock;
assign a profile to a user:
SQL> ALTER USER
PROFILE ;manage profiles:
CREATE PROFILE...;
ALTER PROFILE...;
DROP PROFILE...;
The Password Verify Function:
demonstration function:@?/rdbms/admin/utlpwdmg.sql
■■Auditing
①Auditing SYSDBA Activity
•AUDIT_SYS_OPERATIONS default is FALSE
If it is set to TRUE then every statement issued by a user connected AS SYSDBA or AS SYSOPER is written out to the operating system’s audit trail.
AUDIT_FILE_DEST -audit records directory
②Database Auditing
Can capture all accesses to a table, whether SELECT or DML operations.
•AUDIT_TRAIL:
• NONE (or FALSE) Database auditing is disabled, no matter what auditing you attempt to configure.
• OS Audit records will be written to the operating system’s audit trail: the Application Log on Windows, or the AUDIT_FILE_DEST directory on Unix.
• DB (or TRUE) The audit records are written to a data dictionary table,SYS.AUD$. There are views that let you see the contents of this table.
• DB_EXTENDED As DB, but including information on the SQL statements with bind variables that generated the audit records.
ocp10g> audit create any trigger;
ocp10g> audit select any table by session/BY ACCESS;
ocp10g> audit insert on ar.hz_parties whenever successful/WHENEVER NOT SUCCESSFUL;
•Results:
• * DBA_AUDIT_TRAIL
• DBA_AUDIT_OBJECT
• DBA_AUDIT_STATEMENT
• DBA_AUDIT_SESSION
③Value-Based Auditing
・using database triggers.capture the rows that were affected
・A database trigger is a block of PL/SQL code that will run automatically whenever an insert, update, or delete is executed against a table.
・Auditing through triggers is a much slower process than database auditing, but it does give you more information.
④Fine-Grained Auditing (FGA)
•allows tracking access to tables according to which rows(or which columns of the rows) were accessed.
•FGA is configured with the package DBMS_FGA.To create an FGA audit policy,use the ADD_POLICY procedure,
•To see the results of fine-grained auditing, query the DBA_FGA_AUDIT_TRAIL view.
eg:
exec dbms_fga.add_policy(
object_schema=>'system',
object_name=>'audit_test',
policy_name=>'high_sal',
audit_condition=>'salary > 100',
audit_column=>'salary',
statement_types=>'select');
