SQL Injection for parents

Which PC Memory Module type (SDRAM, DDR, or DDR2) do I need?

Adding more memory to PCs is a cheap and easy upgrade method.

But there are different types of PC memory modules available - SDRAM, DDR, DDR2 - and computers are limited to being compatible with one type only. So you have to know which type you want before buying, especially if buying online.

1) To see how much memory a windows PC has, either run:

Start -> Control Panel -> System

or right click on ‘My Computer’ and select ‘Properties’

The general tab shows the amount of memory:

In this case, 192 MB of memory (RAM = random access memory).

2) Look at the existing memory module, which means removing the outer case of the computer, normally held on by a few small Phillips head screws. Power off at the mains beforehand.

Inside there will be a memory module looking like one of these:

Which is held in the motherboard with white edge clips like this:

It’s easy to take that in and out, but for full instructions see Morris Rosenthal’s illustrated guide.

3) The memory module should be labelled with which type it is, but you can tell even without that by looking at the number of small metal pins at the base of the memory module:

SDRAM - 168 pins - 2 notches

DDR - 184 pins - 1 notch

DDR2 - 240 pins - 1 notch

The pins run on both sides of the memory module, so a DDR module has pins 1-92 on one side, and pins 93-184 on the other. Their numbers should be printed beside the first and last pin on each side to save you counting.

Listener passwords: always for 9i, never for 10g

My rule of thumb:

• For 9i and below: always set a listener password (= change from default)
• For 10g and above: never set a listener password (= leave at default)

And here’s why:

9i and below

Without a listener password set, any ‘oracle’ account on any machine (even a client) can stop and change listeners on any other machine on your network, including production servers.

So, running a command like this from a dev box as the oracle userid:

$ lsnrctl stop liveserver

will stop the listener running on server “liveserver“. Which you probably don’t want.

And its not enough to make every “oracle” account on every server secured to DBAs: people can and will create their own clients or virtual clients with oracle userids. As long as they are plugged into your network, they control all your listeners. And there’s nothing you can do about it - except set a listener password.

With a listener password, nothing can be done to any of your listeners except by someone who knows your password.

To set a listener password, use the change_password command in listener control:

$ lsnrctl
LSNRCTL> change_password
LSNRCTL> save config

The fist time you do this, no password is set, so just press return when you are asked for the existing password.

Once that has been done, you have to specify the password before attempting most listener operations. Use the “set password” command for that:

$ lsnrctl
LSNRCTL> set password

“lsnrctl start” still works without the password being specified, but pretty much everything (even status!) requires the password set first.

That makes system shutdown commands a little more complex, see this post for details.

10g and above

The above is such a glaring security hole that oracle changed the default behaviour with 10g and made everything secure. More secure even than the above setup of a password protected listener. Because with 10g, the default is to refuse lsnrctl operations from other machines.

If you actually wanted lsnrctl operations from other machines (very unusual in my experience), then setting a listener password will allow that.

So notice the behaviour has switched:

• Most secure = no password, the default
• A little less secure = set a password

Setting a password doesn’t make anything more sucure - passwords are ignored if you are the oracle userid/member of dba group on the local machine. While on 9i and below the password, if set, would always be required.

Oracle Support have some carefully worded (”customers may wish to consider“) advice on that:

For database releases prior to Oracle Database 10g Release 1: you should set an administrative password for the TNS listener. For Oracle Database 10g Release 1 and later releases, the default authentication mode for the TNS listener is local OS authentication, which requires the TNS listener administrator to be a member of the local dba group. Setting a password for the TNS listener in Oracle Database 10g Release 1 and higher simplifies administration. However, setting a password requires good password management to prevent unauthorized users from guessing the password and potentially gaining access to privileged listener operations. Because the listener acts as the database’s gateway to and from the network, it is important to secure it. Customers may wish to consider not setting a password for the TNS listener starting with Oracle Database 10g Release 1, because in the interest of increasing security and reducing risks, the default behavior changed in this and later releases.
[Metalink note 340009.1]

That is: for 10g, don’t set a listener password, unless you really need/want to control listeners from different machines.

Thanks to Beth, Howard Rogers, Mark Ashby for the info on 10g behaviour.

plan_table changes

In versions 10g, plan_table exists as a public synonym of the global temporary table sys.plan_table$. That is all created automatically at database create time and when a database is upgraded to 10g (creation is in catplan.sql, which is called from catproc.sql and u*.sql scripts in $ORACLE_HOME/rdbms/admin).

In versions 9i and below, plan_table is not created automatically. It has to be created manually by running script utlxplan.sql in $ORACLE_HOME/rdbms/admin. That should be done both at creation time and after each upgrade (since plan_table structure is changed/improved with each new release).

Another difference from 10g: in 9i and below, plan_table is a real table rather than a global temporary table.

It is possible to allow each user to have their own personal plan_table, but I think it is better to mimic the 10g behaviour and have a single central plan_table used by all users. To do that, run SQL like:

conn / as sysdba
@?/rdbms/admin/utlxplan
grant select, insert, update, delete on sys.plan_table to public ;
create public synonym plan_table for sys.plan_table ;

One thing to watch is that copies of old plan_table’s can be left lying around in users schemas. That applies even to 10g, because a users’ table will be accessed in preference to a public synonym of the same name.

It’s not just upgrades that can leave those in place - export/import can bring users’ old plan_table’s across as well. Old versions of plan_table tend to work without reporting an error, but do not generate as much detail as new versions, so should be avoided.

Utlxplan.sql is still shipped with 10g, but there is no reason to continue using it at that version.

For extra confusion, TOAD by default will attempt to use its own version of a plan table. See previous post for details.

Fix for TOAD error ORA-02404: specified plan table not found

TOAD can report error “ORA-02404: specified plan table not found” when trying to display execution plans:

Two options to fix:

1. Run script toadprep.sql. This creates the table toad_plan_table, among other things, and is meant to be run by a user with DBA privileges.
   or
2. Change TOAD to use the same plan table (= plan_table) that other applications use. Do this in TOAD through the View -> Options -> Oracle -> General window, then change the Explain Plan Table name to “plan_table”, removing the “TOAD_” from the beginning:

Plan_table itself ought to be in every database, although in versions 9i and earlier that required DBA intervention, was not created by default. See next post for details on that.

TOAD error fix from:

• Bert Scalzo, Dan Hotka, TOAD Handbook; and
  
• Patrick MacGrath, Jeff Smith, Bert Scalzo, Toad Pocket Reference for Oracle, O’Reilly.

Quick SQL Server Training for Oracle DBAs

Short SQL Server Training course uploaded in MS-Word format.

It’s a bit dated now - is for SQL Server version 7.0 - but still relevant. I’ll update for version 2005 and convert to html format when I get the chance.

I produced that for a 1.5 day training course to give Oracle DBAs the knowledge they needed to work on SQL Server. They are similar enough relational database management systems for a quick conversion course like this to be enough to make the switch. And they are both common enough that a good DBA ought to be comfortable with both.

SQL Server 2005

SQL Server 2005 doesn’t prompt during install for locations of datafiles and logfiles, meaning they will end up on C:, which you won’t normally want. You can alter the locations during install, but it’s not especially intuitive. Or at least not intuitive enough for me :(

To fix them after the event, see http://www.campus.ncl.ac.uk/databases/sqlser/SQL2005/setupsql2005.html and http://msdn2.microsoft.com/en-us/library/ms345408.aspx
and http://msdn2.microsoft.com/en-us/library/ms143547.aspx