One of the major concerns in the security industry is that DBAs typically have complete access to all the data.In high turnaround development environ ments,even developers can have full reign in the production databases.More and more developers are requesting DBA-level access in production and pre-production data base environments.Inflexible delivery dates lead to compromise in database and system security.
Even though the database industry has a clear delineation of the roles and responsibilities between a production and development DBA,lots of DBAs perform both jobs because of budgetary constraints or the relative size of the company.This is especially true in small to medium-sized businesses.What companies fail to realize is that this makes data theft even easier if the person responsible for backups,audits,andoperations also happens to know where the personallyidentifiable information resides.
With more and more security breaches and internal companyemployees selling sensitive data to competitors, databasesecurity is becoming a sensitive topic for a lot of companies.Regulators and auditors pressure database and systemadministration for compliance to Sarbanes-Oxley (SOX) and the Payment Card Industry (PCI) guidelines.Compliance to SOX and PCI are tedious and consume resources to enable auditing and lockdown processes.If there is sensitive data such as credit card numbers, Social Security numbers,or bank account information,encryption is also involved to protect both data at rest and data in motion.Even with the additional consumption of resources,companies are choosing to be compliant to mitigate internal threat.
Stronger Password Hash Algorithm
Starting in Oracle Database 11g,the SHA cryptographic hash function SHA-1 became the newalgorithm for passwordhashing. SHA-1 is a 160-bit hash employed in several widely used security applications and protocols,including TLS andSSL,PGP,SSH,S/MIME,and IPsec.MD5,an earlier 128-bit widely used hash function, is considered its successor.From a performance perspective,SHA-1 is slightly slower than MD5 but produces a larger message digest,thus making SHA-1 more secure against brute-force attacks.
Hash functions generate the same output for a specified input and produce the same text for password values.SALT is a random value added to the data before it is encrypted.SALT in hashing algorithms strengthens the security of encrypted data by making it more difficult for hackers to crack using standard pattern-matching techniques.SALT is employed by the Oracle Database 11g user password hashing algorithm.
SHA-1 encryption commonly used in the industry provides much better security without forcing a network up grade.Known in the industry as the strong hash algorithm,SHA-1 encryption algorithm enables Oracle Database 11g to meet stringent compliance regulations and strong password requirements.
Security Out of the Box
Oracle Database 11g heightens database security to another level.Oracle wants to make itscustomers aware that database security is critical.In recent years,internal employees contribute more and more to information security breaches.Now,when you create a database using the Database Configuration Assistant (DBCA),Oracle by default will create the database with the new level of security settings.These security settings include database audits of relevant SQL statements and privileges and modifications to the default profile.Moreover,DBCA will automatically set the AUDIT_TRAIL initialization parameter to DB.
The new security settings checkbox will be set to default when creating a database using DBCA or when installing the Oracle Database 11g software.If you do not want to turn on Oracle Database 11g–enhanced auditing,Oracle provides another radio button to revert to the Oracle Database 10g and earlier security settings,which includes reverting the auditing and password profile options. Figure shows the new Oracle Database 11g Security Settings page for database creation.
DBCA 11g default security
You have the flexibility to turn on auditing and disable the password profile option.Likewise, you can enable strong passwords but disable database auditing.The privileges that are audited by default are as follows:SQL> select privilege, success, failure
These privileges plus the AUDIT ROLE BY ACCESS and AUDIT SYSTEM BY ACCESS privileges make up the new Oracle Database 11g auditing-enabled features.Oracle Database 11g audits all privileges and statements using BY ACCESS.
Some customers fret about the performance implications of turning ondatabase auditing.The default database auditing options are negligible to performance since these audits are not at the transaction level.The default database auditing option also identifies who is logging into the system and logging off the system.
The DBCA utility enables you to turn off the auditing provided by OracleDatabase 11g.Please note that setting and/or unsetting the Oracle Database 11g default security feature does impact the AUDIT_TRAIL initializationparameter,and the DBCA will ask permission to bounce the database.If you use the DBCA and unset the new auditing feature, you will revert to the default security features of Oracle Database10g Release 2.
When you have auditing enabled,it is imperative that you enable auditing of select,update,and delete on the SYS.AUD$ table by access.You do not want privileged accounts to be able to delete entries in the AUD$ table.Further more,you may want to know who is peeking into the auditing details.
In addition to the auditing settings, the default profile is also modified in the Oracle Database 11g automatic secure configuration. The new default profile is set with these settings:
The three profile settings,PASSWORD_LOCK_TIME, PASSWORD_GRACE_TIME, and PASSWORD_LIFE_TIME, are more restrictive in Oracle Database 11g.
Delayed Failed Logins
If a user tries to connect to the database multiple times using an erroneous password, the database will delay the response to the client after the third attempt. This is effective in Oracle Database 11g by default.The delays of response back to the client are repeated even if the connections are initiated from different IP addresses or hosts. Oracle preserves the performance of the database by increasing the delay for the user to try another password for up to ten seconds.
You can see in the following example that the first invalid password attempt produced an error output within a subsecond. Using the time or timex command, you can determine the amount of real time spent on waiting for the Unix prompt to return, as shown here:$ time echo "select sysdate from dual;" |sqlplus -s ckim/xx ERROR: ORA-01017: invalid username/password; logon denied ... 0.35s real 0.01s user 0.09s system
Continuing with this example, after the eighth iteration of the same invalidpassword login attempt,Oracle does not relinquish control back to the Unix session until after seven seconds.The delay is evident in the following example:$ time echo "select sysdate from dual;" |sqlplus -s ckim/xx ERROR: ORA-01017: invalid username/password; logon denied ... 7.26s real 0.02s user 0.05s system
For added security,Oracle introduces case-sensitive passwords for databasescreated with the default Oracle Database 11g enhanced security.Fordatabases not created with the default security,you can enable case sensitivity with the SEC_CASE_ SENSITIVE_LOGON initialization parameter. TheSEC_CASE_ SENTITIVE_LOGON parameter must be set to TRUE to enable case-sensitive database passwords.You can set the case sensitivity using the ALTER SYSTEM command:SQL> alter system set sec_case_sensitive_logon = TRUE;
Let’s look at the password case sensitivity in action. First,the password for the rodba user will be changed:1* alter user rodba identified by RockAndRoll567
The password of RockAndRoll567 is now case-sensitive since theSEC_CASE_ SENSITIVE_LOGON parameter is set to TRUE.If you attempt to log in to the database as rodba with an all-lowercase password, you will receive an invalid password error, as shown here:DBAPROD > sqlplus rodba/rockandroll567
All users created or passwords modified in Oracle Database 11g will automatically adopt case-sensitive passwords.There is an exception to the case-insensitivity governance. We’ll discuss this further in the upcoming section.
Basic Compliance with Industry Security Recommendations
The Center for Internet Security (CIS) is a nonprofit organization with the purpose of reducing the risk of business and e-commerce disruptions resulting frominadequate technical security controls.Many large corporations adopt CISguidelines.
Oracle Database 11g starts to provide some compliance to recommendations established by the CIS.Oracle Database 11g security enhancements such as strong passwords,auditing,and case-sensitive passwords are the initialbuilding blocks to compliance with the security guidelines established by the CIS.CISrecommendations are significantly more secure than the default Oracle Database 10g or 11g database settings.
When performing a database export and import of users from OracleDatabase 9i or 10g, users passwords will remain case-insensitive until you manually reset them.If you upgrade the database from a supported release using the DBUAor catupgrade.sql and want to leverage the new case-sensitive passwords,you must reset thepasswords of existing users during the data base up grade procedure.The new strong password authentication will not be enabled for upgraded databases until each userpassword is manually reset using the ALTER USER statement.
If the database user had the sysdba or sysoper role, their passwords will also be imported to the $ORACLE_HOME/ dbs/ orapw$ORACLE_SID password file.
You can query the DBA_USERS view’s PASSWORD_VERSIONS column to review the user password release level:SQL> select username,password_versions
if the PASSWORD_VERSIONS column reflects a value of 10g, this implies thedatabase account was upgraded to Oracle Database 11g but the password has not yet been modified.In this particular case,the PASSWORD_VERSIONS column remains case-insensitive.The password will remain case-insensitive until you change the password in Oracle Database 11g.
Case-Sensitive Password Files
You can incorporate password sensitivity into the orapw password file for sysdba and sysoper users.When creating the orapw password file,you can set theignorecase option to n to designate that the password file record password entries in case-sensitive format.Let’s see how you can set the case-sensitive password file with the ignorecase option:orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=ORAcle123 entries=25 ignorecase=n
Not specifying the ignorecase parameter while creating the orapw file will cause Oracle Database11g to default to case-insensitive passwords.
Change Default User Passwords
A new view called DBA_USERS_WITH_DEFPWD is provided to flag database accounts with default passwords.This view includes both locked and unlockedaccounts.As a best security practice,all users who show up as entries in this view should have their passwords changed.Safe password management will protect your database environment and make you less vulnerable to attacks byintruders. Let’s see all the database accounts that have default passwords:SQL> select username
A better query would be to cross-reference the DBA_USERS_WITH_DEFPWD view with the DBA_USERS view.This will identify database accounts with defaultpasswords and active status.You can use the following query to find such culprits:SQL> select username
For this particular example,the sales history (SH) database account wasintentionally enabled to provide this output.You can easily change thepasswords for any accounts that the DBA_USERS_WITH_DEFPWD view lists as offenders.Let’s change the password for the SH account to a strong password:SQL> alter user sh identified by Oracl3D3v3lop3r123; User altered.
This DBA_USERS_WITH_DEFPWD view reports only those accounts with default passwords for Oracle-supplied database accounts.It does not protect thedatabase from other.
Database Links and Case Sensitivity
Since Oracle Database 9i and 10g are not case-sensitive,connecting to Oracle Database 11g can pose some complications.Oracle provides a set of rules to govern password management,and connection negotiation is between Oracle Database 11g and other versions of the database.Obviously,if you areconnecting from Oracle Database 11g to another Oracle Database 11g using a database link,the password sensitivity remains case-sensitive.
First,look at what has to happen to make connections from a previous release of Oracle to a case-sensitive Oracle Database 11g.You must alter thepassword on Oracle Database 11g to the uppercase equivalent of the password designated in the database link’s CONNECT TO USERNAME IDENTIFIED BYclause section.Oracle stores the password for the database links in uppercase.
Because database link passwords are stored in uppercase, the password in Oracle Database 11g should be set with uppercase.
Now let’s see what kind of implications there are to initiate the database link from Oracle Database 11g to an older version of Oracle.Since Oracle Database 11g Release 1 is the first release to use password sensitivity,you do not have to worry about case sensitivity when creating database links to a prior release of Oracle.
In previous releases,if a hacker has select access to the DBA_USERS view, the hacker can easilycrack another user’s password if the passwords are weak. Hackers can create a copy of the DBA_USER view to a local database and perform an offline attack.Hackers can create a database account for username entries in theDBA_USERS view and can compare the hash value of the password column in the local DBA_USERS view with the production copy of the DBA_USERS view.There are various ways to determine passwords.One common practice is to compare passwords with millions of words in the dictionary to exploit weak passwords.Another common practice is to generate random alphanumericpasswords such as Oracle’s RANDOM function and compare hash values.
Luckily,Oracle provides another level of security to the underlying datadictionary.The DBA_USERS view in Oracle Database 11g has the password column blanked out.The following query confirms that the password column no longer provides the hashed value of the password:SQL> select username,password from dba_users order by username;
To obtain the hashed value of the encrypted password,you will need to have the select privilege on the USER$ table instead.Database users do not receive select privileges against the USER$ table via the SELECT_CATALOG_ROLE role.One of the ways to grant access to the USER$ table is by granting the SELECT ANY DICTIONARY role to the database account.
Here is the simple query of the USER$ table to view the hashed password values:SQL> select name,password from user$;
Create Password Verification Function
Oracle Database 11g addresses some of the weak password concerns by providing a stronger password verification function. The password verification function is in the $ORACLE_HOME/ rdbms/admin/utlpwdmg.sql file.This file comes with two password verification functions.When you execute the utlpwdmg.sql script,it will create two password verification functions,an updated Oracle Database 11g version with higher security configurations and the Oracle Database 10g predecessor password function.The previous database version of the default profile is commented out,and the default profile is set to utilize the new verify_function_11G function as the built-in password verification standard.
The password verification function is not enabled by default.To create and enable the strong password verification function,you must execute the utlp wdmg.sql script.When you execute the utlpwdmg.sql script,the output will look something like this:SQL> @?/rdbms/admin/utlpwdmg.sql
The first function created is the new Oracle Database 11g strong password verification function.It also creates the Oracle Database 10g password verification function for legacy support. Oracle Database 10g’s verify_function cont ains some of the password verification checks that are available in Oracle Database 11g, but the differences are significant. For example, the minimum expected password length is four characters in Oracle Database 10g, and the minimum expected password length in Oracle Database 11g is eightcharacters.
Oracle password verification checks for valid passwords when users are created and passwords are modified.The new verification function provides enhanced security to mitigate weak passwords or passwords easily guessed.By leveraging Oracle Database 11g’s revised password verification utility, securityadministrators can be rest assured that another facet of password management is in compliance. These are the out-of-the-box password rules from Oracle Database 11g:
As mentioned earlier,executing the utlpwdmg.sql script as sys enables the default profile with the new Oracle Database11g password verification algor ithm.At the end of the script, the ALTER PROFILE command sets the default profile with the Oracle 11g password verification function, as shown here:alter profile default limit
If the Oracle-supplied password verification function does not suit your security requirements,you can always customize Oracle’s function to tailor to your corporate security standards or even create your own personalized version.For example,if the minimum eight-character restriction is not adequate for your company,you can easily customize a component in the utlpwdmg.sql script to fit your security needs.The Oracle-supplied password length validation portion of the PL/SQL code reads as follows:IF length(password) < 8 THEN
The password length portion of the logic can be changed to 12 characters,as shown here:IF length(password) < 12 THEN
For some unforeseen reason,if you run into any problems after modifying the password verification function,you can disable the password verificationfunction by setting the default password_verify_function profile to null,as shown here:SQL> alter profile default
Change Database Account Passwords
Now we will look at the password verification utility at work.First we will attempt to change the rd that differs from the last password by three characters using the password command from SQL*Plus.SQL> password
Next,we attempt to change the password to something less than eight characters:SQL> alter user rodba identified by rodba;
You will receive an error message stating that the password length is too short.Another rule is that passwords must have a numeric character in them.If you attempt to change the password to something that is eight characters or more but does not have a numeric character,you will receive another error message:SQL> alter user rodba identified by ohmygodwhatishappening;
Likewise,if you add a number but the password is simple,then Oracle kindly lets you know:SQL> alter user rodba identified by welcome1;
You have seen examples of simple password verification checks.The simple password verification code of the stored function also checks to see whether the password equals any of the words in the following list:
If any of the attempted passwords matches this list, the password verifi cation function will let you know that you have a simple password. Another password security to consider is to disallow passwords that are in the dictionary.You can easily purchase text-based dictionaries and load their content into the database.The password verify function can easily be altered to check for an existence of a word in the dictionary.Of course,the dictionary word must be indexed for optimal data retrieval.The check can be complete in subseconds and would offer another level of security with stronger passwords.
Another password breach that Oracle will check for is if you have the same username and password.The password-length check of eight characters precedes the check for the same username and password check. In the next example, we have to create a username with a length greater than eight characters. Let’s try to create a username charleskim with the same password:SQL> create user charleskim identified by charleskim
The password verification function immediately output the ORA-20002 error message indicating that we could not create a database account with the same password.
The password verification function also checks to see whether the password is the same as the username with the digits 1–100 appended to it. You can see in the following example that Charles99 violates this security requirement:SQL> create user charles identified by charles99;
The password verification function also checks to see whether the password is the same as the username spelled backward.For example,if you attempt to create a username called dbaexpert,the password verification function will make sure that the password cannot be trepxeabd:SQL> create user dbaexpert identified by trepxeabd;
Oracle’s password verification utility has great potential and can be enhanced to fit your security requirements.The utlpwdmg.sql script has great examples to build upon and is configurable to fit any company.
Oracle 11g Related Interview Questions
|Oracle 10g Interview Questions||Oracle 9i Interview Questions|
|Oracle 8i Interview Questions||Informatica Interview Questions|
|PL/SQL Interview Questions||Oracle 11g Interview Questions|
|SQL Interview Questions||Oracle apps Interview Questions|
|Sybase Interview Questions||Oracle Apps ERP Interview Questions|
|Oracle 7.3 Interview Questions||Oracle Access Manager Interview Questions|
|Oracle Application Framework Interview Questions||Oracle Apps DBA Interview Questions|
Oracle 11g Related Practice Tests
|Oracle 10g Practice Tests||Oracle 9i Practice Tests|
|Oracle 8i Practice Tests||Informatica Practice Tests|
|PL/SQL Practice Tests||Oracle 11g Practice Tests|
|SQL Practice Tests||Oracle apps Practice Tests|
|Sybase Practice Tests||Oracle Apps ERP Practice Tests|
|Oracle 7.3 Practice Tests|
Oracle 11g Tutorial
Installing, Upgrading, And Managing Change
Database Diagnosability And Failure Repair
Backup And Recovery
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.