SRA840 Lab7

Katherine Masseau

1. Show SQL commands that work on MySQL for:

• give and remove user rights:

To give a user 'select' privileges on all databases, you might use:

 "grant select on *.* to user@localhost identified by 'password';"

• create a table

To create a table holding people along with their locations, you might use:

 "create table people.addresses (id INT, name VARCHAR(100), city VARCHAR(100), country VARCHAR(100) );"

• insert a few records into the table you created

To insert some records into the table created above, you might use:

 "insert into people (id,name,city,country) values (0,'Katherine Masseau','Toronto','Canada');
 insert into people (id,name,city,country) values (0,'Mark Fernandes','Toronto','Canada');"

• select some of the records in the table you created

To select records from this table, you might use:

 "select * from people where city = 'Toronto';"

• change some of the records into the table you created

To change some records in this table, you might use:

 "update people SET city='North York' where name='Katherine Masseau';"

• delete records from the table you created

To delete the records in this table, you might use:

 "delete from people where city = 'Toronto';"

2. How will you read records from in a plain text file into MySQL. Give an example.

If you have dumped the records from one database into a text file, and want to import them, you could use:

 "mysql -p -h localhost mydatabase < mydump.sql"

3. What are the exact sequence of steps that you need to take so that Apache logs can be read in from MySQL. Assume you have a default installation of Apache and a default installation of MySQL.

I tried to do this, but installing mod_log_sql failed with the following error sequence, which I was unable to resolve in time to submit the lab:

 '/usr/local/build-1/libtool --silent --mode=compile cc -prefer-pic -O2 -fno-strict-aliasing -pipe -I/usr/include   -I/usr/local/include - 
 I/usr/local/include/apache22  -I/usr/local/include/apr-1   -I/usr/local/include/apr-1 -I/usr/local/include -O2 -Wall -I/usr/local/include/mysql -DEAPI  -c -o 
 mod_log_sql.lo mod_log_sql.c && touch mod_log_sql.slo
 mod_log_sql.c:78: error: expected specifier-qualifier-list before 'array_header'
 ...'

4. Name two security measures that MySQL recommmends

Disable TCP networking: If the database is only accessed from the local machine, disabling TCP networking can mitigate the possibility of the database being exploited remotely. To do this, you must edit the 'mysqld_safe' script. Find the line:

 '--skip-locking >> $err_log 2>&1'

and replace it with:

 '--skip-networking --skip-locking >> $err_log 2>&1'

Then, find the line:

 '--skip-locking "$@" >> $err_log 2>&1'

and replace it with:

 '--skip-networking --skip-locking "$@" >> $err_log 2>&1'

Now, restart your MySQL server so the change takes effect.

Remove default users and tables: By default mysql includes two default users which are able to connect to the database without a password. As this is an obvious security weakness, remove them, along with the default 'test' databse, using:

 "DELETE FROM user WHERE User = ;
  DELETE FROM db WHERE Host = '%';"

5. Which MySQL log file, in your opinion, is important for you to keep track of and why?

Probably the file located at '/var/db/mysql/hostname.err', as it will contain records of errors that you can make use of in debugging your database configuration.

6. As you observe from the tutorials, many users have added their own comments in addition to the MySQL material. Which two comments (one from the using tutorial and one from the administration tutorial) you think were useful to you.

I performed this lab from memory based on my previous experiences with MySQL, and am thus unable to judge the comments to which you refer.

Mohak

• View privileges for a user:

SHOW GRANTS FOR 'root'@'localhost'; 

• Give user rights:

GRANT CREATE, INSERT ON vyas.* TO 'user'@'localhost' IDENTIFIED BY PASSWORD 'password hash';
You can give a plain text password but it's better to use hash if you know your password hash.

• Remove user rights:

REVOKE ALL FROM 'user'@'localhost';

• Create a table:

USE vyas;
CREATE TABLE tmp (name VARCHAR(20), sex CHAR(1), bday DATE);

• Insert a few records into the table you created:

INSERT INTO tmp VALUES ('Abc','M','1900-01-02');
INSERT INTO tmp VALUES ('Xyz','M','NULL');

• Select some of the records in the table you created:

SELECT * FROM tmp;
SELECT name,sex FROM tmp WHERE name = 'Abc';
SELECT name,bday FROM tmp WHERE bday > '1900-01-01';

• Change some of the records into the table you created:

UPDATE tmp SET bday = '1980-01-11' WHERE name = 'Xyz';

• Delete records from the table you created:

DELETE FROM tmp WHERE name = 'Xyz'

• How will you read records from in a plain text file into MySQL.

LOAD DATA LOCAL INFILE '/tmp/vyas.txt' INTO TABLE tmp;
You need to enable this to use it. Start your mysql like this:
mysql --local-infile=1 -u root -p
Value of 1 enables it and 0 disables it.
This should also be enabled from the server side too.

• What are the exact sequence of steps that you need to take so that Apache logs can be read in from MySQL:

Install mod_log_sql module and issue following commands.

mysqladmin CREATE apache_logs
mysql apache_logs < access_logs.sql
mysql alache_logs
GRANT INSERT,CREATE ON apache_logs.* TO 'root'@'localhost' IDENTIFIED BY 'password';

Add following lines to httpd.conf:
<IfModule mod_log_sql.c>
  LogSQLLoginInfo mysql://webserver:password@mysqldb.example.com/apache_logs
  LofSQLCreateTables on
</IfModule>

• Name two security measures that MySQL recommends:

Some general security measures recommended my MySQL are as follows:
In general mysql emphasize on protecting the entire system against all attacks. mysql also emphasizes on not giving any one else except root, access to the tables in the mysql database. 

• Which MySQL log file, in your opinion, is important for you to keep track of and why ?

In my opinion, the general query log file is important. It keeps a general record of what mysql daemon is doing. It becomes 
useful when there is an error in a client and you want to find out exactly what client sent to mysql daemon. It can enables using 
--general_log and 1 or 0 to enable and disable respectively.

• As you observe from the tutorials, many users have added their own comments in addition to the MySQL material. Which two comments you think were useful to you.

Users comments on how to use LOAD DATA LOCAL INFILE and the security concerns with it were helpful to me.

Milton

1. give and remove user rights

1.1 Give privileges:

mysql> grant all on wordpress.* to wordpress@localhost identified by 'password';

1.2 Remove privileges:

mysql> flush privileges;

2. create a table

mysql> create table table_name (name char(20));

3. insert a few records into the table you created

mysql> insert into table_name values('milton');

4. select some of the records in the table you created

mysql> select name from table_name;

5. change some of the records into the table you created

mysql> update table_name set name='johndoe' where name='milton';

6. delete records from the table you created

mysql> delete from table_name where name='johndoe';

7. How will you read records from in a plain text file into MySQL. Give an example.

Copy records to a plain text file:

mysql> select * into outfile '/tmp/database.txt' fields terminated by '\t' lines terminated by '\n' from table_name;

Import records from a plain text file:

mysql> load data infile '/tmp/database.txt' into table table_name fields terminated by '\t' lines terminated by '\n';

8. What are the exact sequence of steps that you need to take so that Apache logs can be read in from MySQL. Assume you have a default installation of Apache and a default installation of MySQL.

8.1 Create a database

8.2 Create a table with the fields I will need

8.3 Split the apache log file to a temporary file then mysql can recognize this file

8.4 Import this plain text file into the database

8.5 Create a shell script to keep updating the text file

9. Name two security measures that MySQL recommmends

• Encrypt the passwords
• Add an password for the default system users like root and mysql.

10. Which MySQL log file, in your opinion, is important for you to keep track of and why?

--log, --log-error and --log-warnings

Nestor

give and remove user rights

give/grant user right

mysql> grant all on wordpress.* to wordpress@localhost identified by 'dumpling';

remove user right

mysql> flush privileges;

create a table

mysql> create table table_foo (rec1 char(9));

insert a few records into the table you created

mysql> insert into table_foo values('record1');
mysql> insert into table_foo values('record2');
mysql> insert into table_foo values('record3');

select some of the records in the table you created

mysql> select rec1 from table_foo;

change some of the records into the table you created

mysql> update table_foo set name='record0' where rec1='record2';

delete records from the table you created

mysql> delete from table_foo where rec1='record3';

How will you read records from in a plain text file into MySQL. Give an example.

Exporting records

mysql> select * into outfile '/tmp/mysql_test/table_foo.txt' fields terminated by '\t' lines terminated by '\n' from table_fo;

Importing records

mysql> load data infile '/tmp/mysql_test/table_foo.txt' into table table_foo2 fields terminated by '\t' lines terminated by '\n';

What are the exact sequence of steps that you need to take so that Apache logs can be read in from MySQL. Assume you have a default installation of Apache and a default installation of MySQL.

It's just like how we do it for wordpress. First, make sure apache and mysql are all installed and running properbly. Then, create a database and table, also create the fileds that we'll need, e.g: hour, minutes, status, etc. Then it's the tricky part, we have to extra the apache log file's lines into different filed, so that mysql can read it. You can acheive it with 'sed' or some other command or even script. We actually have a python script from Danny's assignment that has simular function. We could adjust the script to acheive the task. then, we can import the text file with mysql command 'load data infile'.

Name two security measures that MySQL recommmends

1.always have set a password
2.use a long password with combination of characters and numbers

Which MySQL log file, in your opinion, is important for you to keep track of and why?

In my opintion, i think The General Query Log is the most important. Assuming the database has already installed and running properly. The most critical things is data leaking. 
It is really important, because you do not want any random people to the record. The database may contain some sensitive records, e.g: VISA numbers, address. 

(ref: http://dev.mysql.com/doc/refman/5.0/en/server-logs.html)

As you observe from the tutorials, many users have added their own comments in addition to the MySQL material. Which two comments (one from the using tutorial and one from the administration tutorial) you think were useful to you.

Tut

It fill in a lot of gap that the tutorial didn't cover.
http://dev.mysql.com/doc/refman/4.1/en/database-use.html

Admin

A lot of experience sharing
http://dev.mysql.com/doc/refman/5.0/en/query-log.html.

Kezhong Liang

In my opinion, I think the General Query Log is important for us to keep track. It records the information of each client creating connection and close with timestamps. It even records the situation of each client query. It contains more detail information than others.

Varinder Singh

Security Measure

1. Use MySQL's internal SSL support to make the connection even more secure.Alternatively, use SSH to get an encrypted TCP/IP connection between a MySQL server and a MySQL client.

To make a MySQL system secure, you should strongly consider the following suggestions:

Require all MySQL accounts to have a password. A client program does not necessarily know the identity of the person running it. It is common for client/server applications that the user can specify any user name to the client program. For example, anyone can use the mysql program to connect as any other person simply by invoking it as mysql -u other_user db_name if other_user has no password. If all accounts have a password, connecting using another user's account becomes much more difficult.

Never run the MySQL server as the Unix root user. This is extremely dangerous, because any user with the FILE privilege is able to cause the server to create files as root (for example, ~root/.bashrc). To prevent this, mysqld refuses to run as root unless that is specified explicitly using the --user=root option.