Working as a database administrator sooner or later we will get confronted with the customer or developer request about executing a SQL Statement for them within the production database.

Sure, as the DBA we are and with all the rights we have, it isn’t really a big deal for us to manipulate objects within the context of another schema owner. We all know that it just needs fully qualified the object like ‘schema_name.object_name’ and the database will know which database object we reference to.

Just how often we will get such full specified scripts to execute from our customers? Guess never! So it needs us to overwork all the scripts and at the end even to overtaking responsibility for the scripts we changed.

How much more convenient would it be for us to directly work within the user context in question? Impossible?! We need the user password first! The password the user has forgotten to share with us first!

And unfortunately we are also not able to change it, because it is hard coded within the application. And changing it would leave the application inoperable later on?!

Now working with Oracle this situation can get solved for sure! You wonder how?

Within Oracle versions older than 11g, we have a DES hash password which can queried from database with the following SQL Command:

• DBA_USERS : SELECT username, password FROM DBA_USERS;

• SYS.USER$ : SELECT name,password FROM SYS.USER$ WHERE password is not null;

Starting with Oracle 11g we can query the new SHA-1 hash by using this SQL Command:

• SYS.USER$ : SELECT name,spare4 FROM SYS.USER$ WHERE password is not null;

   
Now it is possible to change a password temporarily. And using this little trick here, we will be able to act as a different user.

SQL> select username,password from dba_users where username=’SCOTT’;

USERNAME PASSWORD
——– —————-
SCOTT F894844C34402B67

SQL> alter user scott identified by mypassword;

Now we can login with the following credentials: scott/mypassword

And after our work is done we can change the password back by using an undocumented feature called “by values”

SQL> alter user scott identified by values ‘F894844C34402B67’;

And now we just revert our password manipulation by using exactly the password hash we have queried earlier from the database and all will be fine again.

Many webdevs are doing an excellent stuff and as we all know their webpages do impress us every day again and again. But neing experts one hand, at other topics, like doing necessary database backups, they often act like bloody beginners.

Often when it comes to database interactions using some CMS (Content Management System) they often neglect even the basics like doing backups frequently. But it can be that easy to have well ordered backups of your CMS’ underlaying database done automatically in an most convenient way. And in case you are in some ‘need’ – just restore your database backup and you are back in business again.

Sure you can code that backup all by yourself, but today we wll show you how easy the combination of just a few things can do all that backup stuff automatically for you. At least when it comes to MySQL databases which are widely common for all kind of web related database activities.

We first start with some free avalable PHP based backup tool in which we will define the backup to take, while later on we will use some easy to implement tricks to automise the backups taken for you.

The tool of our choise is called phpMyBackupPro which is available for free at phpMyBackupPro.net. (The tool itself is for free, but the author who has really made a great job offers to to donate some little money helping him to continue his excellent work.)

Installing the tool is easy done by extracting the archive and uploading it’s content to your webserver, but for sure it also works for you on your local system as well.

(enlarge view)
Once uploaded it can get directly invoke and we do start with some basic configuration questions. It isn’t much about to know or perhaps guessing it needs  and filling out at that page – you should anyway know that already.

From there we will head over the the more        (enlarge view)
interesting part of scheduling a backup. There we will now first select the databases to backup at the left side. Depending on your environment there might be just one, but in a more complex situation you and also choose more or all backing up them all together later on.

It’s not problem going with all other setting as they are defined by default, but feel free to play with it if wished. Even the dangerous appearing question about to add a ‘drop table’ command is fine and should be checked, as for it is referencing to a possible later restore of a database and in this case for sure a prior drop table instruction should be executed.

When we now press the button ‘show script’, as a result we will see some PHP code we can just copy&paste into some new file (lets call it cron.php) we later on will upload into the root folder of our phpMyBackupPro installation.

Depending on our basic installation invoking this script within any of the usual browsers will now generate us the wished database backup.

The result can then either get stored within some subfolder inside the tools’ folder, emailed to one of our email account or pretty much handy transfered via FTP to some remote server really creating an offline backup there.

Now calling this script agan and again out of your browser is not what I really would call automated at all. But honestly it does not need much more now.

Once we tested the script as working we do now just need some automatim calling it for us again and again.

This can be done by the OS itself when we’re going to help it a bit. What we now need is some line mode browser invoking the script. On UNIX platform this can be done by using either lynx or curl.

An easy and simple command line script will do:

• for lynx (which is pretty much common on RedHat systems) we use:

    /usr/bin/lynx -source http://www.yourdomain.com/phpMyBackupPro/cron.php > /dev/null 2>&1

•   and with curl (mostly used on Debian distributions) we use:

    /usr/bin/curl http://www.yourdomain.com/phpMyBackupPro/cron.php
   
So either one of these lines we will put into another new file (lets call it cron.sh and don’t forget about to proper ‘chmod’ it) which we then finally schedule out of the UNIX usual cron utility.

So with the command crontab -e we will invoke our crontab file and with adding there a line like:

0 0 * * * /home/ /cron.sh

the system will do an automated backup for us seven days a week around midnight.

For a higher frequence and or other specialties please refer into the separate cron documentation first.

General Differences between DB2 and Oracle

• A DB2-Instance can serve several independent  DB2-databases
• One DB2-Instance is referenced by an OS-User with similar name
• The home directory of the OS-User is the instance home directory
• DB2 does not know about user administration
• DB2 user are authenticated by Operating System Users or
  Directory Services e.q. NIS, LDAP,AD
• There is one Alertlog per Instance called db2diag.log
• A DB2-database is started on demand by the first connection
• there is no ROLLBACK-/UNDO-Management in DB2
  • Oracle:   Multi Version Read Consistency
  • DB2:       ANSI standard Isolation levels
                   Repeatable Read, Read Stability, Cursor Stability
                   and Uncommitted Read (Dirty Read)

  Further Information about: DB2 Online Documentation