Jump to: navigation, search

SRA840 Lab7

1,181 bytes added, 23:43, 16 July 2012
no edit summary
=Gregory Katherine Masseau=
1. Show SQL commands that work on MySQL for:
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,'Gregory 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='Gregory 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?
=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'''
#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.

Navigation menu