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 )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.

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.

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.

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.

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.

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.

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

Changing sysman and dbsnmp passwords – It ain’t that easy

And of course it’s not that easy!

Actually this is quite annoying.

I have 2 concerns: Having my backups working properly as well as the DB Console.

But also renew my passwords from time to time.

And here we are sys and system’s passwords are luckily easy to change.

But what about sysman and dbsnmp?

Those are not. Try to change it and you will have your backups stop working, OEM throws in tons of errors and soon you will be asking yourself why the hell you wanted to change those passwords in the first place.

The reason

Sysman user is the schema of the standalone repository of the 10g EM DB Control.

If you have change only dbsnmp password your backups will work and the DB console as well but all the cool graphics that I like to show to our customers will disappear and that’s not cool.
There are 2 procedures. 1 for each passwords

Changing sysman password

So, since it’s the schema of the standalone repository, the DB console must be shut down.

C:\emctl stop dbconsole

This should stop dbconsole and the agent.

Have a check first.

C:\emctl status dbconsole

C:\emctl status agent

On windows the agent is very often not shutdown properly… So do it using the services screen find your Oracle10Agent service and make sure it’s not started.

When this is done, open an sqlplus console and throw in the command to change password

C:\set oracle_sid= [your SID]

C:\sqlplus / as sysdba

SQL>alter user sysman identified by [New Password];

Then check the new password by connecting with sysman user. This is to make sure sysman is not locked. Because when the password is changed and you try to reach the OEM, it will locked the sysman user.

SQL>conn sysman/newpassword

connected.

Ah that’s cool

if you get

ora-28000: account is locked

Then unlock it first.

SQL> alter user sysman account unlock;

Now that you get your new password and you are sure the account is not locked, you have to modify the emoms.properties file.

It’s located at ORACLE_HOME\[HOST]_[SID]\sysman\config. Replace HOST with your computer name and SID with your SID.

There are 2 parameters to modify:

oracle.sysman.eml.mntr.emdRepPwd= [Your encrypted password]

oracle.sysman.eml.mntr.emdRepPwdEncrypted=True

Change [Your encrypted password] with your new password in the first parameter

and change True to False in the second parameter.

Wait a minute. That is not very safe! The password is not encrypted!

That’s where Oracle 10g is cool. It’s not like you have a choice here.

As soon as you restart Dbconsole and Oracle agent, it will change false to true and your password will look like a bunch of numbers and letters again (Geeks love that)

So just type in:

C:\emctl start dbconsole

C:\emctl start agent

Again, if the agent is not started, try to do it through the service window. I found it to work much better than the command line for Oracle Agent.

Changing dbsnmp password

It’s about the same thing except it’s another configuration file.

Stop Db Console and Oracle agent. Just like before.

Make sure it’s stopped just like before.

Modify your user password just like before except use alter dbsnmp instead of sysman.

Finally make sure it’s not locked.

We have to modify the file targets.xml this time and that one is located at:

ORACLE_HOME\[HOST]_[SID]\sysman\emd

Look for this line:

<Property NAME=”password” VALUE=”<[Your encrypted password]>” ENCRYPTED=”TRUE”/>

Just like before, change [Your encrypted password] to your new password and change TRUE to FALSE.

Just like before, restart your DB Console service and Oracle Agent service.

Make sure they are working.

C:\emctl status dbconsole

C:\emctl status agent

Voila! It’s all done.

The bottom line is: When you want to change a password that doesn’t look like a collegue name, find some documentation on it first. A good way is to google: Problem [user].

You’ll find a lot of them!

Finding my way

This is a new blog and like for any new blog, the author tries to find his line.

Since I’m in Thailand I guess this blog will get a little Thai oriented from time to time. How does Oracle 10g can be Thai oriented?

Well, there are trends. It’s not really Oracle 10g that will be thai oriented but discussions involving Oracle at large. So, sometimes it will be about what to watch out when you are in Thailand.

Also there are not much Oracle DBAs out there. So I hope this blog will enlighten a little bit on the way things can be sorted out in Thailand.

Welcome to The Tendjee

It might not be obvious at first sight but this blog is about Oracle. Yes. Oracle 10g. I just want to make some geek brain produce some brain juice here.

What are we going to talk about? Things I like.

Administration , Security, Tuning, and Issues, Tips. From that point it’s not quite clear yet but I’m pretty sure this blog will get an identity of its own as the writing goes by.