In the instructions below, substitute your UW Net ID wherever you see "uwnetid".

Last updated: 18 Jan 2007

These instructions presume you are running from an unprivileged account.

Start MySQL

Note that for a mysql installation on a personal computer -- one that is not shared as the lab workstations are -- you don't need to do anything special. Institute lab staff needed to modify the installation and operation of MySQL to work in the labs; the default installation and operation should work fine for personal computers. But remember, since you'll likely be on the network, it is always a good idea to change any default root password.

This step is done outside of the Eclipse environment:

1. In the labs (except SCI113):

   Enter in a CMD shell (DON'T double-click on it via the windowing system -- you won't see any messages that way):

     g:\start_mysql
     

   The first time you ever execute this script, it will:

   • copy fundamental data files into your H:\mysql.data directory (creating it),

   • start the mysql service (or "daemon", called mysqld-nt), and

   • set the initial root password to a value it displays on the console window.

     You can look at the script G:\start_mysql.cmd to see the initial password at any time.

   The script also tells you how to set the root password to something you want, and how to cleanly shutdown the service. Subsequent times that you execute the script, it just starts the mysql service.

2. In SCI113, the ituser account will not have write permissions to the directory where MySQL stores its databases. Therefore, we will masquerade as itadmin and start it from a CMD shell, although it would be better security-wise to change the file permissions on that directory.

   runas /user:itadmin cmd
   start "mysql" "C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt.exe"
     

   If you wanted to change file permissions instead, use the "change access control list" command, called cacls -- but you'll also have to be itadmin to do so. In one command, we will alter the permissions on the data directory to allow the "Users" group to change files there, then start MySQL as ituser:

   runas /user:itadmin "cacls \"C:\Program Files\MySQL\MySQL Server 5.0\data\" /e /t /g Users:C"
   start "mysql" "C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt.exe"
     

The rest of the steps are done within Eclipse.

Configure and Test MySQL using Database Explorer

1. start Eclipse
2. activate the Database Explorer view via the menu items:
   • select Window/Show View/Other/Data/Database Explorer

3. set up connection information:
   1. right-click on Connections
   2. select New Connection
   3. enter for Database:

      mysql
          

   4. enter for JDBC Driver Class:

      com.mysql.jdbc.Driver
          

   5. enter for Class location:

      C:\Program Files\Apache Software Foundation\Tomcat 5.5.\common\lib\mysql-connector-java-3.1.13-bin.jar
          

   6. enter for Connection URL:

      jdbc:mysql://localhost/
          

   7. enter for User id:

      root
          

   8. enter for Password: (leave blank)

4. test connection
   • click on Test Connection

5. connect to the mysql database using your connection
   1. expand the mysql connection
   2. right-click on the mysql database
   3. select Open SQL Scrapbook
   4. click on ClientIP for the project name
   5. enter:

          testdb
          

      as the file name.

6. create a database
   1. if not already open, open that testdb.sqlpage "file"
   2. right-click in "file"
   3. select Use Database Connection...
   4. click on Use and existing connection
   5. select mysql
   6. click on Reconnect or Finish
   7. enter:

      create database newdb;
          

   8. right-click in the window
   9. select Run SQL

7. Secure mysql, then create users
   1. open the testdb.sqlpage file
   2. secure account for root (substitute your own password for "9876root"):

      set password for 'root'@'localhost' = password('9876root');
          

   3. create new accounts (substitute your passwords for "xxxx", "yyyy" and "zzzz"):

      create user newadmin@localhost identified by 'xxxx';
      create user readuser@localhost identified by 'yyyy';
      create user updateuser@localhost identified by 'zzzz';
          

   4. allow newadmin to dole out privileges to the newdb database:

      grant all on newdb.* to newadmin@localhost with grant option;
          

   5. right-click in the window
   6. select Run SQL
   7. close and don't save the results for testdb.sqlpage

8. disconnect from root's mysql database, as it is no longer needed
   1. right-click on the connection name
   2. select Disconnect

9. create a new connection to the new database (newdb) as user id "newadmin"
   1. follow the pattern above, connecting to the "newdb" connection

10. as newadmin, make sure that you can create a couple of simple tables and write to and read from a table
    1. use the testdb.sqlpage file
    2. enter:

       create table newdb.test (name varchar(20), state char(2));
       insert into newdb.test values('joe blow', 'ID');
       insert into newdb.test values('jane doe', 'WA');
       select * from newdb.test;
           

    3. Create another table called "books":

       create table newdb.books (title varchar(40), author varchar(20));
           

11. as newadmin, change the privileges of special accounts to be restricted to only certain operations
    1. allow the "readuser" the ability to read and execute anything in the database:

       grant select,execute on newdb.* to readuser@localhost;
           

    2. Allow the updateuser account to select, insert, delete, and update only one table (e.g., "books") in the database

       grant select,insert,delete,update on newdb.books to updateuser@localhost;
           

12. verify readuser permissions
    1. change user id to "readuser" in connection
    2. open the testdb.sqlpage file
    3. enter (should work):

       select * from newdb.test;
           

    4. enter (should fail -- " INSERT command denied"):

       insert into newdb.test values('mary lamb', 'MD');
           

13. check updateuser permissions:
    1. change user id to "updateuser" in connection
    2. open the testdb.sqlpage file
    3. enter (should fail for table test -- "UPDATE command denied"):

       update newdb.test set state = 'MD' where name='jane doe';
           

    4. enter (should work):

       insert into newdb.books(title,author) values('Gone with the Wine','Margaret Mitchell');
           

    5. enter (should work):

       update newdb.books set title='Gone with the Wind' where title='Gone with the Wine';
           

    6. enter (should work):

       select * from newdb.books;
           

Change Log