Which Temporary Tablespace is used for sorts?

Written by Paweł Barut
This time I will write about "Which Temporary Tablespace is used for sorts" ?. I was not wondering about this much, as usually there is only one Temporary tablespace in DB. Lets assume situation, as show on picture:

• User A
• Assigned to Temporary Tablespace TEMP_A
• has table TA
  
• User B
• Assigned to Temporary Tablespace TEMP_B
• has table TB
• owns procedure PB (definer rights)
  
• Both users has access rights for all above object (Select on Tables and Execute on Procedure)
  

So let's discuss some situations:

1. User A runs query on tables TA or TB (or any other) - when disk sort is needed then tablespace TEMP_A is used
2. User A executes procedure PB. Procedure PB opens cursor on table TB (or TA or any other). If disk sort is required then tablespace TEMP_B is used.
   

For me it was bit surprising. Especially that I did not find anywhere in Oracle documentation description for this behaviour:

TEMPORARY TABLESPACE Clause
Specify the tablespace or tablespace group for the user's temporary segments.

I was expecting that all sort segments will be created in tablespace that is assigned to that user. I was hopping to solve one of my issues that way. But it occurred that, sort segment is created by user B, because procedure PB uses user B rights. It is reasonable, as this is consistent with granting access to objects, and temporary objects are treated the same way as permanent ones. On the other hand select is run for user A - shouldn't TEMP_A be used in all cases? What is your opinion on that?

Cheers Paweł

--
Related Articles on Paweł Barut blog:

• How to shrink Oracle Context Index
• Using Named Parameters within SQL
• Alternative Syntax for Insert
• Extracting Data to Excel using only SQL*Plus
• Rows to single string or aggregating strings

Horrible practice of storing users passwords

Written by Paweł Barut
I've read today terrifying article about un-ethical storage of users password. It is often common practice that users passwords are stored unencrypted in database. But this case is even more dreadful. It about software, G-Archiver, that is available to everyone. If you ever used this software, you should change your G-mail password right now.
So what is the problem:

• G-Archiver has build in user-name and password for one of g-mail accounts.
• Whenever someone uses G-Archiver and provides his credentials for g-mail, user-name and password is send to author of this software
• Additionally, any one who finds out the user-name and password stored in G-Archiver, can get passwords of thousand of previous users.

Go and read. Hope there are not too many of software that works like this one. It is really terrifying, that some people are so irresponsible. It could be well planed action to collect gmail users and passwords, or it could be just lack of imagination what are the consequences. I would like to believe that it was the second one.

But also big corporations do not care for security enough. It is common practice, that users password is stored internally as plain text. It can be easily found out by using "I forgot my password" feature. If in return you will get email with your original password, then it is a crappy web side. And you should never reuse password passed to such web side as this is big risk for you. Take a look at Password Security: It’s Not That Hard (But You Still Can’t Get It Right) for further explanation and examples.

Cheers Paweł

--
Related Articles on Paweł Barut blog:

• Security: getting DBA rights quite easy
• „Do not use Linux, please, pirate our software” - Microsoft
• Spam, Spam filters, Being Spammer, Being Filtered-out ...
• Do not trust Google Maps