Sun Asserts MySQL Will Remain Open Source

By Brian Prince

Marten Mickos, head of Sun Microsystems' database group, says MySQL will remain an open-source product.

After several days of blog posts and headlines, the dust that kicked up over Sun Microsystems' commitment to keeping MySQL open source seems to have settled.

At the center of the controversy were reports of plans to close-source backup features in Version 6.0 of the MySQL database, making them available only to subscription-paying users of its enterprise version. In an effort to clear the air, Marten Mickos, senior vice president of Sun's Database Group, responded to blog posts on Slashdot and elsewhere.

In an interview with eWEEK, he stated emphatically that Sun will not withhold or close-source any features that would make the MySQL community server less functional for users.

The core backup functionality and a backup API in Version 6.0 will be available to users of both the community and enterprise versions of the database, he said. However, the company plans to develop high-end add-ons such as encryption and compression solely for its MySQL Enterprise subscription customers, Mickos said.

Mickos calls MySQL the Ferrari of databases. Read why here.

“It is not a question of close-sourcing any existing code, nor anything in the core server,” Mickos said. “Everything MySQL has released under the GPL license continues to be under GPL, and the core MySQL server will always be under GPL, or some other free and open-source software [FOSS] license.”

Mickos said it has not been decided yet what license the add-ons will be released under. Sun, he added, was not part of the initial plans regarding the backup functionality—those discussions were taking place well before the acquisition by Sun earlier this year.

“At all times, because the main backup functionality goes into the core server under GPL, anyone can use the API and build their own add-ons or other modifications,” he said. “We are also offering them a convenient, low-cost option through our add-ons, but not forcing them or restricting them from building their own.”

Several MySQL users objected on blogs last week to talk of making key functionality available only to paying customers. It wasn’t the first time open-source advocates took MySQL to task for such moves; there was a similar, albeit more subdued, reaction when MySQL AB announced it was removing Enterprise source tarballs from ftp.mysql.com last August, analyst Matt Aslett noted.

“MySQL has been quick to respond to the criticism this time, and has been very open in explaining its strategy on user blogs, and the company does appear to have been able to stop the resentment from growing,” said Aslett, who is with The 451 Group. “It would be prudent for the company to prioritize deciding on which licenses it will use for the new functionality so that it can explain its strategy properly before doubt continues to spread.”

Mickos admitted the initial information was released awkwardly by the company and said he understood why some members of the open-source community were upset at first. This is why the company sought to aggressively address some of their concerns, he said.

“Open-source software is really just now reaching the radar of mainstream IT,” Mickos said. “There is still work to be done in formulating business models around open source, and so some degree of experimentation is essential. Keep in mind, open source itself began as an experiment in the software industry. So all vendors should not be afraid to continue to experiment both in software and in business models.”

Understanding account names and hostnames

Together, the account name and hostname (the name of the computer that is authorized to connect to the database) identify a unique account. Two accounts with the same name but different hostnames can exist and can have different passwords and permissions. However, you cannot have two accounts with the same name and the same hostname.
The MySQL server will accept connections from a MySQL account only when it is connecting from hostname. When you build the GRANT or REVOKE query (which I describe later in this chapter), you identify the MySQL account by using both the account name and the hostname in the following format: accountname@hostname (for instance, root@localhost). The MySQL account name is completely unrelated in any way to the Unix, Linux, or Windows user name (also sometimes called the login name). If you’re using an administrative MySQL account named root, it is not related to the Unix or Linux root login name. Changing the MySQL login name does not affect the Unix, Linux, or Windows login name — and vice versa.
MySQL account names and hostnames are defined as follows:

• An account name can be up to 16 characters long. You can use special characters in account names, such as a space or a hyphen (-). However, you cannot use wildcards in the account name.
• An account name can be blank. If an account exists in MySQL with a blank account name, any account name will be valid for that account. A user could use any account name to connect to your database, given that the user is connecting from a hostname that is allowed to connect to the blank account name and uses the correct password, if required. You can use an account with a blank name to allow anonymous users to connect to your database.
• The hostname can be a name or an IP address. For example, it can be a name such as thor.mycompany.com or an IP (Internet protocol) address such as 192.163.2.33. The machine on which the MySQL server is installed is localhost.
• The hostname can contain wildcards. You can use a percent sign (%) as a wildcard; % matches any hostname. If you add an account for george@%, someone using the account named george can connect to the MySQL server from any computer.
• The hostname can be blank. A blank hostname is the same as using % for the hostname.

An account with a blank account name and a blank hostname is possible. Such an account would allow anyone to connect to the MySQL server by using any account name from any computer. An account with a blank name and a percent sign (%) for the hostname is the same thing. It is unlikely that you would want such an account. Such an account is sometimes installed when MySQL is installed, but it’s given no privileges, so it can’t do anything.

When MySQL is installed, it automatically installs an account with all privileges:
root@localhost. Depending on your operating system, this account may be installed without a password. Anyone who is logged in to the computer on which MySQL is installed can access MySQL and do anything to it by using the account named root. (Of course, root is a well-known account name, so this account is not secure. If you’re the MySQL administrator, you should add a password to this account immediately.)
On some operating systems, additional accounts besides root@localhost are automatically installed. For instance, on Windows, an account called root@% might be installed with no password protection. This root account with all privileges can be used by anyone from any machine. You should remove this account immediately or, at the very least, give it a password.

How to Control Access to Your MySQL Data?

You need to control access to the information in your database. You need to decide who can see the data and who can change it. Imagine what would happen if your competitors could change the information in your online product catalog or copy your list of customers — you’d be out of business in no time flat. Clearly, you need to guard your data. MySQL provides a security system for protecting your data. No one can access the data in your database without an account. Each MySQL account has the following attributes:

• A name
• A hostname — the machine from which the account can access the MySQL server
• A password
• A set of permissions

To access your data, someone must use a valid account name and know the password associated with that account. In addition, that person must be connecting from a computer that is permitted to connect to your database via that specific account.
After the user is granted access to the database, what he or she can do to the data depends on what permissions have been set for the account. Each account is either allowed or not allowed to perform an operation in your database, such as SELECT, DELETE, INSERT, CREATE, or DROP. The settings that specify what an account can do are privileges, or permissions. You can set up an account with all permissions, no permissions, or anything in between. For instance, for an online product catalog, you want the customer to be able to see the information in the catalog but not be able to change it. When a user attempts to connect to MySQL and execute a query, MySQL controls access to the data in two stages:

1. Connection verification: MySQL checks the validity of the account name and password and checks whether the connection is coming from a host that is allowed to connect to the MySQL server by using the specified account. If everything checks out, MySQL accepts the connection.
2. Request verification: After MySQL accepts the connection, it checks whether the account has the necessary permissions to execute the specified query. If it does, MySQL executes the query. Any query that you send to MySQL can fail either because the connection is rejected in the first step or because the query is not permitted in the second step. An error message is returned to help you identify the source of the problem.

SQL injection attack hits hundreds of thousands of web page

An automated SQL injection attack has affected a large number of web sites - as in hundreds of thousands of pages - resulting in malware being downloaded to visitors' computers.

The attack appears to take advantage of features of Microsoft SQL Server, though according to security vendor F-Secure the problem lies in poor site coding, not in vulnerabilities in Microsoft's software. SQL injection is a well-known technique for attacking sites, and one that professional web developers should know they need to guard against.

Affected sites include some operated by the United Nations and the US, UK and Australian governments or their agencies.

According to F-Secure, the domains hosting the malicious content ultimately delivered by the hack are nmidahena.com, aspder.com and nihaorr1.com.

Some reports have suggested the problem is linked to an unpatched flaw in IIS that surfaced last week, but Microsoft has categorically denied that is the case. "We have also determined that these attacks are in no way related to Microsoft Security Advisory (951306)," wrote communications manager Bill Sisk in the Microsoft Security Response Center blog.

"The attacks are facilitated by SQL injection exploits and are not issues related to IIS 6.0, ASP, ASP.Net or Microsoft SQL technologies," he added.

Removing information

Keep the information in your database up to date by deleting obsolete information.
You can remove a row from a table with the DELETE query:

DELETE FROM tablename WHERE clause

Be extremely careful when using DELETE. If you use a DELETE query without a WHERE clause, it will delete all the data in the table. I mean all the data. I repeat, all the data. The data cannot be recovered. This function of the DELETE query is right at the top of my don’t-try-this-at-home list.
You can delete a column from a table by using the ALTER query:

ALTER TABLE tablename DROP columnname

Or you could remove the whole thing and start over again with

DROP TABLE tablename
or
DROP DATABASE databasename

Updating information

Changing information in an existing row is updating the information. For instance, you might need to change the address of a member because she has moved, or you might need to add a fax number that a member left blank when he originally entered his information.
The UPDATE query is straightforward:

UPDATE tablename SET column=value,column=value,...WHERE clause

In the SET clause, you list the columns to be updated and the new values to be inserted. List all the columns that you want to change in one query. Without a WHERE clause, the values of the column(s) would be changed in all rows. But with the WHERE clause, you can specify which rows to update.
For instance, to update an address in the Member table, use this query:

UPDATE Member SET street=”3333 Giant St”,
phone=”555-555-5555”
WHERE loginName=”bigguy”

So You Think You Know SQL?

I was reading an old post by a buddy of mine about bad SQL being the cause of a lot of application problems and decided to weigh in my two cents.

A lot of developers get forced into writing SQL as part of their jobs. Should they be doing it? I don't think so. It's not necessarily the best of ideas, and in MOST cases should probably be avoided at all costs.

Besides, developers cannot be experts in every language or technology right? Something has got to give somewhere. It's usually their SQL skills that suffer. Developers are, sometimes, forced into situations where they have no choice but to write their own SQL. There is either no DBA, a DBA who isn't interested in helping developers with their queries, or a DBA who isn't even in the development loop (never a good sign). In these cases, developers may have to write their own SQL.

Sometimes developers have to know their limitations when it comes to writing queries, especially complex queries. I don't think a lot of developers do truly know their skill limitations. Yet, these intrepid souls will trudge on thinking they can write SQL just fine. When, in reality, they really and truly do not know the little tricks and tweaks that can make the SQL perform better.

I've seen it a thousand times. It's not an indictment of the developer, just a limitation of their skills that needs to be recognized.

Don't lose hope though, there are ways to combat this and make your SQL as good as it should be, or as good as it can be. What are these answers, then? Personally, I opt for something like Hibernate (Java) or Transfer (ColdFusion) to abstract my SQL for me. What does this mean? It means that my queries will be optimized and I can spend my time focused on developing the business logic rather than spending that time mired in persistence.

It also gives me the agility of being (most of the time) database agnostic. This lets us easily develop in one database locally, and port the same code to the server on a different database with minimal configuration changes. If ORMs are not an option for you and you have access to a good DBA, engage them. Talk to them and mine them for all the information they're worth. It's their job to know the database you're using and how to optimize queries. Make them earn their money by at least helping you write your queries, if not transferring the database functionality to them altogether (good luck).

None of these two options are viable? Well maybe you'll have to engage an outside consultancy to help you with your persistence layer optimizations, or pursue another path for optimizing your queries that has not been mentioned here. The bottom line is this: If you optimize your persistence today, then you'll have to spend less time dealing with it later when it causes you problems tomorrow. You may be able to write SQL, you may be able to do complex joins and create some wicked stored procedures, but are you a SQL expert? If so, then never mind this post. If not, then don't be afraid to swallow your pride and ask for help.