Do you have a personal oracle to illuminate you on the world of database management? If not (and who does), see the light and subscribe to our free Oracle8i TechMail service today. Oracle8i is a database management system enhanced specifically for the Internet. This system provides increased performance for XML and Java, a JVM (Java Virtual Machine) for creating and executing triggers and stored procedures in Java instead of PL/SQL. You can also use the JVM to execute Enterprise JavaBeans (EJBs), which is like turning Oracle8i into an application server.

Let our Oracle8i TechMail service develop your database knowledge to its fullest capacity. You’ll receive a healthy dose of tips delivered directly to your Inbox daily. These tips will nourish your mind with the latest information and procedures, such as triggers, PL/SQL, UTL_FILE, and tabs. And don’t think we forgot about the code—we’re in the know.

After contemplating the usefulness of these three Oracle8i TechMails, sign up for our free e-mail service. Remember, you have only knowledge to gain.

User profile limits in Oracle8i
Many of today’s applications must be able to manage several aspects of user sessions and accounts, including the number of allowed concurrent sessions, session duration, and session timeout, to name a few.

Developers use a number of techniques to accomplish this, including cookies, JavaScript functions, and even custom-developed schemas. Well, there’s good news for Oracle8i users—all of these capabilities have been built into the database itself!

Using profile limits, one can predefine a set of parameters that would be applicable for any account that’s been assigned that profile. Consider the following profile example:
CREATE PROFILE clerical LIMIT
 SESSIONS_PER_USER 2
 CPU_PER_SESSION unlimited
 CPU_PER_CALL 6000
 PRIVATE_SGA 500k
 LOGICAL_READS_PER_SESSION unlimited
 LOGICAL_READS_PER_CALL 100
 IDLE_TIME 30
 CONNECT_TIME 480;

This will assign to the clerical profile these parameters:

  • A maximum of two concurrent sessions
  • An unlimited amount of CPU time per session
  • A limit of 6,000 seconds of CPU time per call
  • An allocation of up to 500 K of System Global Area (SGA) memory per call
  • An unlimited number of data block reads from memory and disk per session
  • A per-call read limit of 100 total data blocks from memory and disk
  • A limit of 30 minutes of inactivity per session
  • A limit of 480 minutes of total connect time

So you can see how easy it is with Oracle8i to enforce a number of session-related policies, and not a line of code was written!
Get the best of TechRepublic delivered straight to your Inbox. From weekly updates on what industry experts are saying to the best daily software tips on the Web, TechMails fit into your busy schedule. Check out all of our TechMail offerings, including the new Enterprise Storage, Java, and Morgan Stanley Dean Witter mails.
Password encryption with Oracle8i
If a transmission is intercepted that contains authentication credentials for a site at which you’ve “registered,” your data is typically ciphered to such a degree that the interceptor can’t make sense of it. However, because user passwords are usually stored in a site’s database for comparison purposes at login, an intruder who manages to gain entry to the database will get those passwords and will be able to access any confidential data contained within the database.

So how should you address this password security issue? With encryption, of course! Oracle8i provides a package called DBMS_OBFUSCATION for just that purpose. Using it, you can encrypt just about any data element you desire, providing an added level of security for your applications. Since they’re unreadable, encrypted passwords sitting in your company’s database are basically useless to an intruder—he or she would have to probe pretty deeply into your application to make use of the encrypted password data.

Additionally, using Oracle8i’s Virtual Private Database (VPD), security policies can be defined at the data level, ensuring that no user “accidentally” accesses another’s data. With the database itself enforcing these policies, the need for application logic is eliminated, and the overall security of the system is increased. Therefore, even if the application itself is compromised, your access policies are still in effect.

Save time—test your code
When working with Java code that’ll be used as a stored procedure or trigger, it’s imperative to test it rigorously. The main reason why is because, once stored in the database, it’s much more difficult to debug.

Let’s say you want to test the Java method logSalChanges discussed in our last two tips. How can it be tested from outside the database? Or, in other words, how can it be tested without having to invoke the trigger or by explicitly calling the database procedure?

You can execute any static Java method by having it called from within the optional main method of any Java class. The modified MyTriggers class would look like this:
class MyTriggers {
 public static void main(String[] args) {
 try {
 logSalChanges(1000,1200,1250);
 } catch(SQLException ex) {
 System.err.println(ex.getMessage());
 }
 }
 // rest as before
}

The try/catch block is necessary because logSalChanges may throw an SQLException that must be caught. The code can then be tested by directly invoking the JVM with the following:
java MyTriggers

This will start execution from within the main method, which in turn will call the method to be tested—logSalChanges, in this case.