Archive: March, 2006

Setup GRANT Tables for MySQL on Linux

I tried every tutorial out there on how to set up users to use MySQL with the GRANT Tables. The Tutorials almost got me there, but I kept getting the same error even after following the various tutorials to the “T”.

“Warning: mysql_connect(): Client does not support authentication protocol requested by server; consider upgrading MySQL client in…”

I tried everything that I could think of but still nothing. I finally got a solution on on a forum that I use a lot, frihost.net/forums. This was the simplest solution to a problem that Haunted me for a long time. So I thought that I would post it here.

If you still get the error Above after following these steps, just issue this command from the MySQL command-line as root:

SET PASSWORD FOR user@localhost = OLD_PASSWORD(‘yourpassword’);

These are the instructions that I followed to set the grant tables:
You can add new users to MySQL in two different ways: by using the GRANT statement or my manipulating the MySQL grant tables directly. The preferred method is to use the GRANT statement because they are more concise and less error-prone.
The following examples show how to use the mysql client to set up new users. These examples assume that privileges are set up according to the defaults provided in the previous MySQL DBA Tip, “Setting Up the Initial MySQL Privileges”. This means that to make changes, you must be on the same machine where mysqld is running, you must connect as teh MySQL root user, and the root user must have the insert privilege for the mysql database and the reload administrative privilege. Also, if you have changed the root user password, you must specify it for the following mysql commands:
You can add new users by issuing GRANT statements:

% mysql -u root mysql
mysql> GRANT ALL PRIVILEGES ON *.* TO oracle@localhost
-> IDENTIFIED BY ‘manager’ WITH GRANT OPTION;

mysql> GRANT ALL PRIVILEGES ON *.* TO oracle@”%”
-> IDENTIFIED BY ‘manager’ WITH GRANT OPTION;

mysql> GRANT RELOAD, PROCESS ON *.* TO admin@localhost;

mysql> GRANT USAGE ON *.* TO dummy@localhost;
The GRANT statements (above) create and set up three new users:
0. oracle
0. A full superuser who can connect to the server from anywhere, but who must use a password ‘manager’ to do so. Note that we must issue GRANT statements for both oracle@localhost and oracle@”%”. If we don’t add the entry with localhost, the anonymous user entry for localhost that is create by mysql_install_db will take precedence when we connect from the local host because it has a more specific Host field value and thus comes earlier in the user table sort order.
0. admin
0. A user who can connect from localhost without a password and who is granted the reload and process administrative privileges. This allows the user to execute the mysqladmin reload, mysqladmin refresh, and mysqladmin flush_* commands, as well as mysqladmin processlist. No database-related privileges are granted. (They can be granted later by issuing additional GRANT statements.)
0. dummy
0. A user who can connect without a password, but only from the local host. The global privileges are all set to ‘N’ – the usage privilege type allows you to create a user with no privileges. It is assumed that you will grant database-specific privileges later
You can also add the same user access information directly by issuing INSERT statements and then telling the server to reload the grant tables:
$ mysql -u root mysql

mysql> INSERT INTO user VALUES(‘localhost’, ‘oracle’, PASSWORD(‘manager’),
-> ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’);

mysql> INSERT INTO user VALUES(‘%’, ‘oracle’, PASSWORD(‘manager’),
-> ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’);

mysql> INSERT INTO user SET Host=’localhost’, User=’admin’,
-> Reload_priv=’Y', Process_priv=’Y';

mysql> INSERT INTO user (Host, User, Password)
-> VALUES(‘localhost’, ‘dummy’, ”);

mysql> FLUSH PRIVILEGES;
Depending on you version of MySQL, you may have to use a different number of ‘Y’ values (versions prior to Version 3.22.11 had fewer privilege columns). For the admin user, the more readable extended INSERT syntax that is available starting with Version 3.22.11 is used.
Note that to set up a superuser, you need only create a user table entry with teh privilege fields set to ‘Y’. No db or host table entries are necessary.
The privilege columns in the user table were not set explicitly in the last INSERT statement (for the dummy user), so those columns are assigned the default value of ‘N’. This is the same thing that GRANT USAGE does.
The following example adds a user custom who can connect from hosts localhost and sundev5.comanage.net. He wants to access the bankaccount database only from localhost , the expenses database only from sundev5.comanage.net, and the customer database from both hosts.
To set up this user’s privileges using GRANT statements, run these commands:

% mysql -u root mysql

mysql> create database bankaccount;

mysql> create database expenses;

mysql> create database customer;

mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP
-> ON bankaccount.*
-> TO custom@localhost
-> IDENTIFIED BY ‘manager’;

mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP
-> ON expenses.*
-> TO custom@sundev5.comanage.net
-> IDENTIFIED BY ‘manager’;

mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP
-> ON customer.*
-> TO custom@’%’
-> IDENTIFIED BY ‘manager’;
We do this to grant statements for teh user ‘custom’ because we want to give the user access to MySQL both from the local machine with Unix sockets and from the remote machine sundev5.comanage.net over TCP/IP.
To set up the user’s privileges by modifying the grant tables directly, run these commands:

$ mysql -u root mysql

mysql> create database bankaccount;

mysql> create database expenses;

mysql> create database customer;

mysql> INSERT INTO user (Host, User, Password)
-> VALUES(‘localhost’, ‘custom’, PASSWORD(‘manager’));

mysql> INSERT INTO user (Host, User, Password)
-> VALUES(‘sundev5.comanage.net’, ‘custom’, PASSWORD(‘manager’));

mysql> INSERT INTO db
-> (Host, Db, User, Select_priv, Insert_priv, Update_priv, Delete_priv,
-> Create_priv, Drop_priv)
-> VALUES
-> (‘localhost’, ‘bankaccount’, ‘custom’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’);

mysql> INSERT INTO db
-> (Host, Db, User, Select_priv, Insert_priv, Update_priv, Delete_priv,
-> Create_priv, Drop_priv)
-> VALUES
-> (‘sundev5.comanage.net’, ‘expenses’, ‘custom’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’);

mysql> INSERT INTO db
-> (Host, Db, User, Select_priv, Insert_priv, Update_priv, Delete_priv,
-> Create_priv, Drop_priv)
-> VALUES
-> (‘%’, ‘customer’, ‘custom’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’);

mysql> FLUSH PRIVILEGES;
The first two INSERT statements add user table entries that allow user custom to conect from the various hosts with the given password, but grant no permissions to him (all privileges are set to the default value of ‘N’). The next two INSERT statements add db table entries that grant privileges to custom for the bankaccount, expenses, and customer databases, but only from the proper hosts. As usual, when the grant tables are modified directly, the server must be told to reload them (with FLUSH PRIVILEGES) so that the privilege changes take effect.
If you want to give a specific user access from any machine in a given domain, you can issue a GRANT statement like the following:

mysql> GRANT …
-> ON *.*
-> TO myusername@”%.mydomainname.com”
-> IDENTIFIED BY ‘mypassword’;
To do the same thing by modifiying the grant tables directly, use the following:

mysql> INSERT INTO user VALUES (‘%.mydomainname.com’, ‘myusername’,
-> PASSWORD(‘mypassword’), …

mysql> FLUSH PRIVILEGES;

AdWords

I just read an article that I found on digg.com that lists the top paying 230 AdWords from google. I thought that it was interesting that almost all of the high paying words had something to do with “Mesothelioma”, “Tax Lawyers”, or “Mortgage Refinancing”. I never would’ve guessed any of these. But I guess thats why I’m not in advertising.

Here are some links to “Mesothelioma”:
http://www.mesotheliomaweb.org/mesothelioma.htm

http://www.mesolink.org/

http://www.mesotheliomaresourceonline.com/

Web 2.0

I’m slowly starting to implement Web 2.0 elements into my development and design. Its kind of hard to do since I spend most of my time doing in-house programming for my company. I have to focus on building practical and functional for internal use. Naturally that doesn’t leave a lot of room for creative experimenting in my development and design.

I was starting to feel like I was going to be left behind in the whole “Web 2.0 revolution.” So I added some AJAX to my last application that I built. It wasn’t anything special, but I was excited about it because it was “AJAX and Web 2.0″, and I see all of the possibilities with this new style of development.

I was just fooling around and decided to try out one of the simple tutorials on drag-n-drop AJAX applications. This is what I came up with. Kind of cool.