About User Security
Each Oracle database has a list of valid database users. To access a database, a user must run a database application, and connect to the database instance using a valid user name defined in the database. Oracle Database enables you to set up security for your users in a variety of ways. When you create user accounts, you can specify limits to the user account. You can also set limits on the amount of various system resources available to each user as part of the security domain of that user. Oracle Database provides a set of database views that you can query to find information such as resource and session information.
Profile
A profile is collection of attributes that apply to a user. It enables a single point of reference for any of multiple users that share those exact attributes.
Default Oracle Passwords
By default Oracle creates a number of schemas, each with a default password. Although many of these users are locked, it is still good practice to switch to non-default passwords in case they are unlocked by mistake. In addition, regular users often switch their passwords to match their username. Both of these situations represent a security riskPassword Management
The Oracle database includes a range of functionilty to help secure database users. Unused accounts should be locked, while accounts that are used intermittantly should be unlocked as needed.ALTER USER scott ACCOUNT UNLOCK; -- Use the schema. ALTER USER scott ACCOUNT LOCK;
Creating Profile
Password aging, expiration and history is managed via profiles, as shown below.
CONN sys/password AS SYSDBA
CREATE PROFILE my_profile LIMIT
FAILED_LOGIN_ATTEMPTS 3 -- Account locked after 3 failed logins.
PASSWORD_LOCK_TIME 5 -- Number of days account is locked for. UNLIMITED required explicit unlock by DBA.
PASSWORD_LIFE_TIME 30 -- Password expires after 90 days.
PASSWORD_GRACE_TIME 3 -- Grace period for password expiration.
PASSWORD_REUSE_TIME 120 -- Number of days until a specific password can be reused. UNLIMITED means never.
PASSWORD_REUSE_MAX 10 -- The number of changes required before a password can be reused. UNLIMITED means never.
/
ALTER USER scott PROFILE my_profile;
The PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX parameters work in conjunction, so if either is set to unlimited password reuse is prevented.
PASSWORD_VERIFY_FUNCTION
Password complexity is enforced using a verification function. This must
accept three parameters (username, password and old_password) and
return a boolean value, where the value TRUE signifies the password is
valid. The example below forces the password to be at least 8 characters
long. CREATE OR REPLACE FUNCTION my_varification_function (
username VARCHAR2,
password VARCHAR2,
old_password VARCHAR2)
RETURN BOOLEAN AS
BEGIN
IF LENGTH(password) < 8 THEN
RETURN FALSE;
ELSE
RETURN TRUE;
END IF;
END my_varification_function;
/
Once the function is compiled under the SYS schema it can be referenced by the
PASSWORD_VERIFY_FUNCTION parameter of a profile.ALTER PROFILE my_profile LIMIT PASSWORD_VERIFY_FUNCTION my_varification_function;
The code below assigns the completed profile to a user and tests it.
SQL> ALTER USER scott PROFILE my_profile; User altered. SQL> ALTER USER scott IDENTIFIED BY small; ALTER USER scott IDENTIFIED BY small * ERROR at line 1: ORA-28003: password verification for the specified password failed ORA-28003: password verification for the specified password failed SQL> ALTER USER scott IDENTIFIED BY much_bigger; User altered. SQL>
Revoke Unnecessary Privileges
As a rule of thumb, you should grant users the smallest number of privileges necessary to do their job.
REVOKE CREATE DATABASE LINK FROM connect; REVOKE EXECUTE ON utl_tcp FROM public; REVOKE EXECUTE ON utl_smtp FROM public; REVOKE EXECUTE ON utl_http FROM public; REVOKE EXECUTE ON utl_mail FROM public; REVOKE EXECUTE ON utl_inaddr FROM public; REVOKE EXECUTE ON utl_file FROM public; REVOKE EXECUTE ON dbms_java FROm public;
Securing the Listener
In versions prior to 10g Release 1, the TNS listener should be password protected using the
lsnrctl utility or the netmgr GUI. When using the lsnrctl utility, the change_password command is used to set the password for the first time, or to change an existing password.LSNRCTL> change_password Old password: New password: Reenter new password: Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myserver.mydomain)(PORT=1521))) Password changed for LISTENER The command completed successfully LSNRCTL>
The "Old password:" value should be left blank if the password is being set for the first time. Once the new password is set, the configuration
should be saved using the save_config command.
LSNRCTL> save_config Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myserver.mydomain)(PORT=1521))) Saved LISTENER configuration parameters. Listener Parameter File /u01/app/oracle/product/10.1.0/db_1/network/admin/listener.ora Old Parameter File /u01/app/oracle/product/10.1.0/db_1/network/admin/listener.bak The command completed successfully LSNRCTL>
Once the password is set, subsequent attempts to perform privileged operations such as
save_config and stop will fail unless the password is set using the set password command.LSNRCTL> set password Password: The command completed successfully LSNRCTL>
