Archive for July, 2006

27
Jul
06

Consider using profiles for managing passwords… But not only!

When working with oracle, we come quick to realise we have different user types, different needs, different privileges coming with them.

The DBA will want to change dba and developer passwords’ more often than normal users. And normal users’ passwords more often than connection users. Users used for the connectivity of applications, batch job, this kind of stuff.

Now managing those passwords is a nightmare. When prompt to change passwords, users might use the same password by laziness. Or sometimes simply not have a password and just use the username as a password, or username123 which is just the same.

The profiles in Oracle help manage that.

I’ve created 2 profiles:

1) The connect_users with light privileges

Change password every 3 months

Is not allowed to use the same password before having had 3 different passwords

2) The normal users. People connecting to all sort of applications and inputing data all day long.

Change password every 2 months

Are not allowed to use the same password before having had 5 different passwords
Max session 8. We use some apps that opens 2 sessions by opened windows. I force them to use only 4 windows max. (And I still wonder how they can manage 4 at a time…)

3) DBAs and developers

Change password every months. Seriously we have a trained brain able to remember dozens of complexe minmum 10 digit passwords.

Are not allowed to use the same password before having had 10 different passwords.

Why so much different passwords? Well since it expires every months, a user would be able to use a new one in just 5 months compared to 10 months for a normal user. Doesn’t makes sense is it?

There’s something missing in this profile thing… The password complexity. It usually forces users to have at least a number, and at least a capital letter in their password. This is just plain crap. I can make a password like this: Passw0rd and go through the complexity check. Why bother…

Instead, I take time training my users. I’ve found out I’ve had much better result by advocating security rather than forcing it. What is seen as a hassle can be changed into an effort to avoid the bad hacker, the bad employee, the competitor, stealing our work.

You see. The reason why the users can’t be asked changing a password is because they don’t have a clue!

Answer the questions!

Why?

Why me?

Ask them. They’ll answer:

Who’s gonna steal my data?

I have nothing valuable.

The underlying question is how does people could make money with my data anyway. It’s worthless. Therefore, the hacker is a myth.

That usually can be heard at the beginning of the training. Then you bring it the slide with the common questions. They are usually impressed that it could be guessed :o)The reason is not you, your data. It’s your computer, for spamming. To turn your Oracle database in a Gigantic spamming machine. Since it does not concern only Oracle I like to stress the fact that very often it’s the computer which is hacked. Not the user in particular.

I explain the reasons too. Spamming. IRC channels selling hacked computers with admin rights and password. Here is the business model popping up! Making the thing real. Touchable.

2) Who?

It doesn’t need to be fully detailed.

I explain about the script kiddies. I explain how it’s easy to hack by downloading ready to use scripts on the internet. People not having a clue about what they are doing but harming innocent DBAs anyway!

3) How?

I explain the use of security flaws database, the automatic bot looking for it’s target on the web. The passwords dictionnaries. How common they are.

You’ve just thought about an easy-to-remember password? The hacker did that years before you even started thinking about it.

Defend yourself

Here we are. The enemy has a face. It can be identified by the user. Now when you got an enemy and a reason for his acts, you can start defending yourself.

A good password has to be complexe. <– That here is a tricky word. The second you turn your back on it and it kicks you in the face. Simply, complexe for a DBA is really not the same for a user.

Here are some sample of complexe passwords for a user:

a1b2c3d4

!@#$%^&* (This one is the worst. It gives feeling of security where there’s not. If you feel secure, you care less.)
John1978

Here are some sample of Complexe passwords for DBAs:

M02nA2s+3re

B5H-cl1ck-r4t

3le|<+R0n-C0nFuZi0n

Make sure your users understand what complexity means for you

How to choose a good password.

Here are my rules:

1) Change your password creation method from one to another.

You like to replace some letters by numbers looking like it? Don’t do it for the next letter.

2) You like to modify words with numbers or do some design (_|_)?

Find another way by concatenating substrings of different words, or mix them up together.

Make a sentence take the 3 letters of each words. Modify them with Caps, Special Characters and numbers.
3) Make it long. At least 8 for users. At least 10 for DBAs and Administrators.

4) Add it some numbers, letters, characters totally not related to any rules. Coming out of nowhere. :-)Or/cl3(-:

If you do all that, you’ll have an easy to remember password that is hard to break too.

And finding a password can become fun instead of a hassle.

Not as fun as playing WoW or enjoying yourself with your girlfriend. But as fun as something can be fun in the work place.

25
Jul
06

How to shutdown oracle if the server is too slow on windows

If for some reasons a server is very slow and the shutdown immediate command can be expected to take ages…

Go for the kill of the Oracle Service in control panel>configuration>services

Find OracleService[OracleSID] and stop it.

Shutting down Oracle by stopping the Oracle service makes a clean stop of the database as opposed to shutdown abort which must be avoided at all costs.

Shutting down with an abort can create fuzzy files which is all DBA’s nightmare.

24
Jul
06

Installing Oracle using what RAID system?

I realise it is vastly unknown what RAID system must be used to work with Oracle.

Actually the answer is: It depends.

But to answer it there’s just a simple question to ask:

Will my Oracle database write a lot on the disks or read a lot?

How to know?

1) The use of the database (Is it datawarehouse (READ) or some invoice keying (WRITE)?).

2) The frequency of backups.

3) Consider the amount of users.

If it is something involving a lot of keying then it’s best to use a RAID1 or RAID1+0. Some simple mirroring would greatly improve the performance of the server.

If it is a datawarehouse type application where the data are pushed on the server once everyday and then it’s only SELECT statements all day long then RAID5 might do. But the process to push your data in the datawarehouse might take little longer as a consequence.

If you are considering RAID5 anyway, think about this:

1) Use a minimum of 5 disks on your server

2) Enable your writing cache (make sure it’s battery backed cache)

In the case you already use RAID5 with some keying applications, and there’s only a few users, then don’t forget your backups, your tape device. All these are very ressource consuming. At those times where you do backups, the users might feel the server very slow.

To summarize:

Not Datawarehouse> Use RAID1 or RAID1+0 Datawarehouse> Use RAID5 with a very minimum of 5 disks.

21
Jul
06

Make Oracle work with a local username on windows

Peole working on a windows domain gets used to having their username/password everywhere they go.

And the reflexe when installing Oracle on a windows server might be to prepare a user on the domain for Oracle. Or worst, use a domain administrator privileged user to install Oracle.Well it’s not the best idea.

It’s best to create a local user. Call that user oracle to make things simple and assign it to your ORA_DBA group.

The ORA_DBA group is a local user group by the way.

This user will have local administrator privilege too in order to be able to install Oracle.

Also, go to control panel>Administration>Local Security Policy>Local Policies>Users Rights Assignment>Log on as a batch job.

Add your new Oracle user to the “Log on as a batch job” list. This is required for backups.

Why using a local oracle user?

With Oracle 10g, a DBA avoids a lot of problems by using a local user at backups time.
You will avoid remote connections to your database using tools like MMC.

It makes hacking your server a little less easy.

Watch out

Since the Oracle server is using a local user, all the domain security policies applied using windows directory won’t apply on a local user.

All the domain security policies must be applied locally.

20
Jul
06

2 cool blogs

One blog that I’ve been reading avidly recently is Guy Kawasaki’s blog.

Another one is Presentation Zen.

I found the second one on the first one.

Guy Kawasaki is a former Apple Executive who talks about… Well I guess we can call it best practise. And it’s fun to read.

Presentation Zen study the art of presenting powerpoints. From the time where it’s done to the time when it’s told to an audiance. He uses the eye of a microscope to study famous presentation styles plus some other goodies.

20
Jul
06

What about an open source ETL?

An ETL is a software which Extract, Transform and Load data.

Extract is from different sources. It can be some databases as well as some text files or even excel documents. We have that a lot here.
Transform is when we have to concatenate data, match some data with others or simply filter them to make sure we have all we need before loading the data.

Load talks for itself I guess.

It’s name is Kettle and we like it 🙂 Well there are some other open source products on the market but Kettle demonstrated more maturity in the project.

When we feedback bugs it’s taken into account very quickly.

It has been recently bought by Pentaho.

All you need to try it is java runtime installed on your computer.

20
Jul
06

How to switch between SPFile and PFile

Any DBA who worked on oracle 8i knows where to find init[sid].ora.

The SPFile is located in the same folder just like the Pfile.

Except… It’s not manually managed. It’s managed by oracle. This is done to avoid human error in parameters.

There can be problems in parameters though. During the migration, a parameter (DB_BUFFER_SIZE), from the 8i era was left in the parameter file and was conflicting with another one (DB_CACHE_SIZE) in the parameter file.

When restarted, the database couldn’t even mount…

Don’t worry, when you get that error, there’ll be a list of conflicting parameters.

Usually, you should be able to get into OEM and remove the bad parameter then it would be possible to start again. But it was my luck and OEM was not working too.

There’s only one way out of it. It is possible to get back to the old 8i style and use an init.ora file which can be modified manually.

To do so, connect as sysdba on command line:

C:\SET ORACLE_SID = [Your Sid]

C:\SQLPLUS / as sysdba

SQL>CREATE PFILE=’C:\WHEREVER_YOU_WANT_IT_TO_BE]\INIT[SID]’ FROM SPFILE=’C:\WHERE_IT_IS\SPFILE[SID].ORA’

As it says, it will create the Pfile from the SPfile. Then it is manually manageable. When the culprit is removed, simply do the opposite thing:

SQL>CREATE SPFILE=’C:\[WHERE_IT_SHOULD_BE]\INIT[SID]’ FROM SPFILE=’C:\WHERE_IT_SHOULD_BE_TOO\SPFILE[SID].ORA’

This will create a new spfile from the modified init[sid].ora

Now all what is left, is to start the database

SQL>STARTUP