Make MySQL Your Own - Page 2

Continued From Page 1

Adding Users
Now it's time to populate the users table with some actual users:

mysql> insert into users values (
  -> '500', '500', 'cschroder', encrypt('secretword'), 'carla', 'schroder');
Query OK, 1 row affected (0.00 sec)
mysql>insert into users values (
  ->', '501', 'thanson', encrypt('bigsecret'), 'terry', 'hanson');
Query OK, 1 row affected (0.00 sec)

And so forth, until you have a nice batch of users in your new table. View the contents by entering select * from users; at the MySQL prompt, which should provide you with a text table that reflects the user and group i.d. of each user, as well as their passwords, logins, and names.

Creating Administrative Users
You can give your lackeys limited privileges to maintain specific databases like this, preferably using something a bit more secure than "password" for the password:

mysql> grant all privileges on samba_auth.* to sqllackey01@"localhost" identified by 'password';
Query OK, 0 rows affected (0.08 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

Then your new user logs in to work like this:

$ mysql -u sqllackey01 -p samba_auth
Enter password:

Adding, Deleting, and Changing
MySQL lets you make all kinds of changes without giving you a lot of backtalk, though you must exercise care and not make a hash of your hard work, which is easy to do. You can delete a table or an entire database:

mysql> drop table users; mysql> drop database samba_auth;

Delete a single entry in a table. Using the field containing the primary key is safest because that is a unique identifier:

mysql> delete from users where uid=500;

Delete a field from a table:

mysql> alter table users drop gid;

Add a field to a table, specifying that it either comes first, or follows another field:

mysql> alter table users add location varchar(64) first;
mysql> alter table users add date_of_hire date after location;

For most sysadmin-type jobs, a simple flat database is all you need, so the basic commands covered here will serve you well. Next week we'll learn how to backup MySQL, connect to remote MySQL servers, and hook into common applications like Apache and Samba.

Resources

• MySQL in a Nutshell by Russell Dyer is a great MySQL reference.
  
• MySQL.com
  
• PhpMyAdmin
  
• Webmin module for MySQL