|
|
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:
- 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:
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.
- 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.
- start Eclipse
- activate the Database Explorer view via the menu items:
- select Window/Show View/Other/Data/Database Explorer
- set up connection information:
- right-click on Connections
- select New Connection
- enter for Database:
mysql
- enter for JDBC Driver Class:
com.mysql.jdbc.Driver
- enter for Class location:
C:\Program Files\Apache Software Foundation\Tomcat 5.5.\common\lib\mysql-connector-java-3.1.13-bin.jar
- enter for Connection URL:
jdbc:mysql://localhost/
- enter for User id:
root
- enter for Password: (leave blank)
- test connection
- connect to the mysql database using your connection
- expand the mysql connection
- right-click on the mysql database
- select Open SQL Scrapbook
- click on ClientIP for the project name
- enter:
testdb
as the file name.
- create a database
- if not already open, open that testdb.sqlpage "file"
- right-click in "file"
- select Use Database Connection...
- click on Use and existing connection
- select mysql
- click on Reconnect or Finish
- enter:
create database newdb;
- right-click in the window
- select Run SQL
- Secure mysql, then create users
- open the testdb.sqlpage file
- secure account for root (substitute your own
password for "9876root"):
set password for 'root'@'localhost' = password('9876root');
- 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';
- allow newadmin to dole out privileges to the newdb database:
grant all on newdb.* to newadmin@localhost with grant option;
- right-click in the window
- select Run SQL
- close and don't save the results for testdb.sqlpage
- disconnect from root's mysql database, as it is no longer needed
- right-click on the connection name
- select Disconnect
- create a new connection to the new database (newdb) as user id "newadmin"
- follow the pattern above, connecting to the "newdb" connection
- as newadmin, make sure that you can create a couple of simple tables and
write to and read from a table
- use the testdb.sqlpage file
- 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;
- Create another table called "books":
create table newdb.books (title varchar(40), author varchar(20));
- as newadmin, change the privileges of special accounts to
be restricted to only certain operations
- allow the "readuser" the ability to read and execute anything in the database:
grant select,execute on newdb.* to readuser@localhost;
- 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;
- verify readuser permissions
- change user id to "readuser" in connection
- open the testdb.sqlpage file
- enter (should work):
select * from newdb.test;
- enter (should fail -- " INSERT command denied"):
insert into newdb.test values('mary lamb', 'MD');
- check updateuser permissions:
- change user id to "updateuser" in connection
- open the testdb.sqlpage file
- enter (should fail for table test -- "UPDATE command denied"):
update newdb.test set state = 'MD' where name='jane doe';
- enter (should work):
insert into newdb.books(title,author) values('Gone with the Wine','Margaret Mitchell');
- enter (should work):
update newdb.books set title='Gone with the Wind' where title='Gone with the Wine';
- enter (should work):
select * from newdb.books;
Change Log
22 Jan 2007 |
Made corrections to SQL statements to fully qualify the table name. |
18 Jan 2007 |
Made corrections based on the first ITW201 workshop. |
16 Jan 2007 |
Original document |
Hours
|
Support Information
|
News
|
Policies
|
Emergencies
|