Password Problems

All the members of my old team had their own development schemas. We were give the schema password so we could make changes as we saw fit. These schemas were special in that they all had the same password. That was to make the life of the DBAs easier. This of course was a security violation. But hey. What are you going to do?

The passwords were furthermore shorter than the normal required length. These accounts were supposed to be set up to allow that. Recently my password expired for my schema account. This was not supposed to happen. Okay. So I changed the password and tried to reset it to the old one. Multiple problems popped up - password too short and cannot reuse a recent password.

All right. Need to get around this. I just changed my password a lot of times to get around the recent password reuse limitation. However the password length required me to set up a new profile that had a NULL password_verify_function. This seems like a lot of work. But hey. You got to get stuff done. While I was at it, I reset the password for my old team lead so he could log in without further ado.

Big Numbers Cause Big Problems

I was tired this morning. Slept in an extra half hour. When I got in front of my computer, I started working on the task I had planned for today. My team lead calls me up and said there were problems during deployment to test. Okay. We looked at the logs for guidance.

The database error was that the code was trying to fit a large number into a NUMBER(9) column. Seems simple enough. I said we were probably selecting a column that was bigger than precision 9. We looked at the code. Nope. Source columns were also NUMBER(9).

Then I said we must be getting our insert and select order mixed up. This was a large insert statement. We pulled the column names and the values we were pulling into an Excel spreadsheet. Yep. Something got out of order in the select. Just a couple out of order can cause some pain.

A manager wanted to know what was going on. Why did this work in one environment and not another? Well it all depends on what type of data (and how big the numbers are) in the columns we got switched.

Further analysis of the rest of the SQL showed some more SQL statements out of whack.

The Mystery Table

Yes I know. I don't post much here any more. But I work in a PostgresSQL database now. Thought I would recount some challenges I had in the DB today. Needed to make some last minute changes in a function. This function dynamically inserts data into tables. Turns out it was using a mishmash of source columns as input. Needed to handle a couple of scenarios based on whether the primary input was NULL.

It was easy enough. I inspected the catalog to figure out the sets of data I needed to choose from. There is no NVL function in this DB. So instead I had to use CASE expressions. I coded up the changes, compiled them into the database, and executed my function. The good news is I got all the primary data I needed. Unfortunately the copy of production data I had did not exercise all paths.

Okay. I could just clone some records in my own schema, modify them to simulate the second data set, and rock and roll. Unfortunately I don't have my own schema. I just share a schema with my whole team. We usually just make sure all objects are owned by a role. And we all have that role. That works until somebody creates an object and forgets to reassign ownership.

Wouldn't you know it? Somebody created the table I needed in our common schema. But they did not share ownership. I could not even select rows from the table. I needed to modify data in that table. What was my solution? We were running out of time, so I created a similar table with another name. Then I modified my function to use this other table. Hack? Yes. But it worked to test out my logic.