Open main menu

CDOT Wiki β

Changes

SRA840 Lab7

41 bytes added, 16:03, 12 April 2009
no edit summary
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 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 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?