I wrote this entry in 2010 and never posted it. However, with Oracle 12c about to be released, I figured I’d post it now. It will serve as a point of comparison to the security features that will come with Oracle 12c.

Pressure is increasing to make computer deployments more secure. 15 to 10 years ago, corporate computing security focused far away from the database layer. Implementing firewalls for web servers and https protocol was enough to satisfy many security requirements in those days. Now, security specialists routinely examine database deployments to see if whether security best practices are in place. Oracle 11G brings several notable improvements to the Oracle security feature set. This article will focus on non-encryption security enhancements. A subsequent posting will describe the encryption new features available in 11G R1 and R2.

Several of the new 11G security features are available with the core Oracle database license and do not require purchasing the extra cost Advanced Security option. Five features are implemented as init.ora parameters. Another feature is implemented via a new mechanism to govern who gets access to sensitive packages that can be used to hack into the database.

Want your passwords to be case-sensitive? Just set the init.ora parameter SEC_CASE_SENSITIVE_LOGON to true. Here are sql statements run in SQL*PLUS illustrating how this works. Note that this parameter affects the entire instance.

-- Connect as system, examine password case sensitivity setting, make a new user.
SQL> connect system/password@TEST;
SQL> show parameter sec_case_sensitive_logon
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon             boolean     TRUE
SQL> create user freddy identified by Freddy;
SQL> grant create session to freddy;

-- Get an error, then connect OK.
SQL> connect freddy/freddy@TEST
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> connect freddy/Freddy@TEST

A word of caution when using database links with case sensitive passwords turned on. When connecting from a pre-11G database to an 11G with case sensitivity turned on, you need to make the password within the 11G system in upper case so that the older database can successfully login via the link to the 11G system. And while on the topic of passwords and database links, know that the password hash formerly visible in the DBA_USERS.PASSWORD column can no longer be viewsed. What about viewing the password provided when creating a database link? Didn’t you used to be able to see that in the password column of USER_DB_LINKS? As of 10G R2 (not 11G, but the release before), passwords for database links are stored in encrypted format, and even the encrypted value is only visible to users with access to the view SYS.LINK$.

What else can you do with new init.ora parameters for security in 11G? The parameter SEC_MAX_FAILED_LOGIN_ATTEMPTS governs how many times a user can attempt to login with an incorrect password before getting their account locked. The default value is 10. SEC_PROTOCOL_ERROR_TRACE_ACTION determines what type of notification should occur when the database gets bad packets, as might happen during a denial of service attack. You can specify that no notification be sent (NONE), or that it be sent as a short message to the alert log (LOG) OR a detailed trace file (TRACE) OR as a short alert log message plus notification to OEM (ALERT). The default value is TRACE. SEC_PROTOCOL_ERROR_FURTHER_ACTION specifies the fate of connections that send bad packet. They can CONTINUE, get DROPped or be DELAYed. The default value is CONTINUE. Lastly, SEC_RETURN_SERVER_RELEASE_BANNER allows the DBA to avoid sending the complete database version information to an incoming session, making it harder for hackers to figure out the exact version the database is running. The default value is FALSE, sending only minimal information about the database version.

The last security feature to be discussed here is the new method of enabling users to invoke the system packages UTL_HTTP, UTL_MAIL, UTL_SMTP and UTL_TCP. Hackers target these packages since they make it possible for database sessions to send email, communicate via HTTP protocol and via TCP/IP. In previous database releases, the Oracle PUBLIC user had EXECUTE privileges on these packages by default, thereby making it possible for any user to use them. In 11G, the PUBLIC user still has EXECUTE privileges on them. However, now more is needed for database users to successfully invoke the procedures that belong to these packages. In addition to the EXECUTE privileges, users must also have privileges from an access control list (ACL) that gets stored in XML format in the Oracle XDB. DBAs can administer this ACL using the DBMS_NETWORK_ACL_ADMIN and the DBMS_NETWORK_ACL_UTILITY package, which naturally are new to 11G.

In summary, Oracle has improved security in 11G. Implementing several features via init.ora parameters departs from the approach of using the PROFILE feature, where several similar security were in place already and continue to stay. A problem with implementing features like locking a user after invalid login attempts is that if a user doesn’t belong to the correct profile, that user can attempt an infinite # of incorrect passwords. Using init.ora parameters provides a blanket mechanism to cover all users. Want to know more? I recommend going straight to the source: the 11G Release 1 Security Guide and the 11G Release 2 Security Guide.