UNIX timestamp to Oracle Date Conversion

Earlier today a situation came up where a UNIX timestamp (a count of the number of seconds from January 1, 1970, midnight GMT) needed to be converted into an Oracle DATE format. The Oracle TO_DATE (covered in more detail in my article Oracle, SQL, Dates and Timestamps) does not support this type of conversion.

A Google search confirmed for me that their was no easy way to make the TO_DATE function do this, but I did find this article from the Oracle + PHP Cookbook on Oracle’s site which contained this simple function to convert UNIX timestamps to Oracle dates.

[SQL]CREATE OR REPLACE
FUNCTION unixts_to_date(unixts IN PLS_INTEGER) RETURN DATE IS
/**
* Converts a UNIX timestamp into an Oracle DATE
*/
unix_epoch DATE := TO_DATE(‘19700101000000′,’YYYYMMDDHH24MISS’);
max_ts PLS_INTEGER := 2145916799; — 2938-12-31 23:59:59
min_ts PLS_INTEGER := -2114380800; — 1903-01-01 00:00:00
oracle_date DATE;

BEGIN

IF unixts > max_ts THEN
RAISE_APPLICATION_ERROR(
-20901,
‘UNIX timestamp too large for 32 bit limit’
);
ELSIF unixts < min_ts THEN RAISE_APPLICATION_ERROR( -20901, 'UNIX timestamp too small for 32 bit limit' ); ELSE oracle_date := unix_epoch + NUMTODSINTERVAL(unixts, 'SECOND'); END IF; RETURN (oracle_date); END; / [/SQL] Once compiled, you can use the function to convert numerical UNIX timestamps into Oracle dates in the same manner you would use the TO_DATE function to convert text to dates. Here is an example: SQL> select unixts_to_date(1094165422) from dual;

UNIXTS_TO
---------
02-SEP-04

oracle, sql, dba, database administration, database development, unix, unix timestamp, timestamp

Oracle on Solaris: 32-bit or 64-bit

It is important for optimal performance to make sure you match up your Oracle RDBMS installation with your OS. Running a 32-bit version of Oracle on a 64-bit OS is may not give you peak performance, but also will not be able to address large segments of RAM and large files. So how do you know what your OS supports? How can you tell if that Oracle install from before you started is 64-bit? Here’s how:

Is my Operating System 64-bit?

In Solaris, from the command line (you don’t have to be root in most cases) run this command:

/usr/bin/isainfo -kv

If your OS is 64-bit, you will see output like:

64-bit sparcv9 kernel modules

If your OS is 32-bit, you will get this output:

32-bit sparc kernel modules

For Linux users

If you are running Linux, you can check your distribution with the uname command:

uname -m

The output will read x86_64 for 64-bit and i686 or similar for 32-bit.

How about this Oracle install? Is it 64-bit?

The question here is weather your Oracle binaries are 64-bit. While some of the binaries associated with Oracle may be 32-bit, the important ones will be 64 bit. To check those, follow these steps from the command line:

cd $ORACLE_HOME/bin
file oracl*

This will display the file type of your oracle binaries. If you are running 64-bit binaries, the output should look like this:

oracle: ELF 64-bit MSB executable SPARCV9 Version 1, dynamically linked, not stripped
oracleO: ELF 64-bit MSB executable SPARCV9 Version 1, dynamically linked, not stripped

If your binaries are 32-bit, the output will look like this:

oracle: ELF 32-bit MSB executable SPARC Version 1, dynamically linked, not stripped

If you find you are running 32-bit and decide to go to 64 be careful. The switch can be a bit tricky. Read the documentation closely and make sure your service contract is payed up!

oracle, dba, database administration, database, solaris, linux, sun, sun microsystems, 32-bit, 64-bit

Password Management in an Identity-Theft World

The problem

At Plymouth State University we, like many institutions and organizations are facing the challenges of password maintenance for our twenty-some-odd thousand constituents, many of whom may never visit our campus. As our systems become more integrated, password security becomes more important. Today a user accesses everything from address information to grades to financial information all with the same password.

Historically a system was used in which an initial password was set up for users when their accounts were created. In the case of a forgotten password, a user could present a college ID in person (which they had to present a government issued ID to obtain) and we could update their password. This has proven to be time consuming for the IT department and is inconvenient to our growing audience of distance education students and alumni.

Other popular solutions to this problem currently being used at other organizations include the use of security questions, alternate email addresses, or remote assurance of identity by a third party (e.g. notary.) None of these options provide a complete or ideal solution for the following reasons:

Security questions:
– Answers to standard questions like “What is your mother’s maiden name?” or “What is your pet’s name?” can be easily researched or even guessed.
– Offering a free-form question frequently results in overly simple question/answer pairs such as the question: “What color is the sky?” with the answer: “Blue.”

Alternate email address:
– As we provide email services we do not want to require the user to maintain a separate email service.
– Email accounts, especially those associated with an ISP are rarely permanent.
– Email addresses may be re-used resulting in password information being sent to a third party.

Remote identity providers:
– Time consuming, cumbersome and costly for the end user.
– Involves extensive manual processing at the institution.
– Difficult to identify remote identity providers globally.

Another potential solution which has become available is Faces. This is a commercial solution which presents the user with a series of faces to remember. To authorize the user to change their password, they identify the unique pattern of faces they were given to remember. The company claims users have no problem remembering their face-code after two years; however, our user relationship may last 80 years or more. This solution is also likely to be costly.

Our solution

Faced with this password management challenge, Zach Tirrell and I have formulated the following solution.

When a user obtains an account in our system, regardless of their relationship with the institution (student, faculty, alumni, guest) they will receive a username and Password Change Authorization Code (PCAC) through the mail. The PCAC is a 32 character code, unique to that user.

Upon receiving the PCAC, the user is instructed to keep it in a safe place, such as with their birth certificate or social security card. While the user’s account has been created it is initially locked. With PCAC in hand, the user accesses a secure web form on our site. They are prompted for their username, PCAC, and their desired password. Upon entering a password which fits our requirements (capitalization, numbers, etc.) the account is unlocked and the user may now log in with their password.

Users can change their passwords at any time with their current password. If the user has forgotten their current password they can change it with the same procedure as when they set it up, provided they have access to their PCAC. This offers the user the opportunity to change their password anytime from anywhere and frees them from the necessity of either providing personal identifying information over the phone or having to be physically on campus.

Of course we do expect some users will loose their PCAC. A user can request a new PCAC be sent to them at a known address at any time. Even without their current password we would mail a new code to the user. This cannot be done without the time lag of a few days in the mail; however if the user fulfils their responsibility to keep their PCAC in a safe place they should never encounter this delay.

This solution has the potential to increase the security of user passwords, decrease the time to reset passwords, and decrease the amount of human intervention and IT time involved in password maintenance. Perhaps more significantly the responsibility for securing and resetting passwords is put in the hands of the user.

This flowchart (pdf) outlines the entire password process. I have also provided an example of the PCAC here.

This process is still in the design stages here at Plymouth State University. While we are airing it internally we are also looking for outside opinions. If you have any suggestions or comments please leave a comment here, or email me at jon@lifeaftercoffee.com.

To read more about our procedure, check out Zach Tirrell’s post about this procedure on his blog.

Technorati tags: , , , ,

Search UNIX without the junk

If you have done much UNIX systems administration you have probably seen output like this from the ‘find’ command:

$ find / -name lifeaftercoffee.com
find: /proc/tty/driver: Permission denied
find: /proc/sys/kernel/pax: Permission denied
find: /proc/net: Permission denied
find: /proc/4680/fd: Permission denied
find: /usr/local/dh/apache/logs/basic-argon/fastcgi: Permission denied
find: /usr/local/dh/apache/logs/basic-bongo/fastcgi: Permission denied
find: /usr/local/dh/apache/logs/basic-cabo/fastcgi: Permission denied
find: /usr/local/dh/apache/logs/basic-dap/fastcgi: Permission denied
find: /usr/local/dh/apache/logs/basic-adamant/fastcgi: Permission denied
find: /usr/local/dh/apache/logs/basic-emu/fastcgi: Permission denied
find: /usr/local/dh/apache/logs/basic-fritz/fastcgi: Permission denied
find: /usr/local/dh/apache/logs/basic-grog/fastcgi: Permission denied
…

Annoying, to say the least, that your actual search results may be buried in pages upon pages of this.

Here’s a quick way around this. Redirect the error output to /dev/null (the black hole of data.) It’s as simple as appending ‘2>/dev/null’ to the end of the command.

$ find ./ -name lifeaftercoffee.com 2>/dev/null
/home/jonemmons/logs/lifeaftercoffee.com
/home/jonemmons/lifeaftercoffee.com

Any errors are ignored, which can complicate troubleshooting, but if things aren’t doing what you want them to, just drop the redirect and run the command to see the errors again.

The command may vary depending on your shell and breed of UNIX, but this has always worked for me.

Easy Linux CommandsFor more tips like this check out my book Easy Linux Commands, only $19.95 from Rampant TechPress.

Technorati tags: , , , ,