SQL
Database Administration
Introduction
Jerzy Letkowski, Ph.D.
Outline
1. INSTALLATION
Click here.
2. START / STOP MYSQL IN WINDOWS
• Start:
net start mysql
• Stop:
net stop mysql
• Start and Stop can also be done via Control Panel > Administrative Tools > Services.
3. SYSTEM TABLES (Selected Topics)
Database sys:
• Switch to database sys:
USE sys;
• Find out more about
table Session:
DESCRIBE Session;
• Show some info about
the users (try this statement in MySQL Workbench and in MySQL
terminal:
SELECT
user, db, state, program_name from Session;
Database MYSQL:
• Switch to database MYSQL:
USE MySQL;
• USER (User info and
global privileges)
DESCRIBE
User;
SELECT host, user, grant_priv FROM User;
• DB (Users'
databases)
SHOW
DATABASES;
Database Information_Schema:
• Switch to database Information_Schema:
USE Information_Schema;
• Table in this
database:
SHOW Tables;
DESCRIBE Tables;
DESCRIBE Views;
• Learn more about the
schema set:
SELECT *
FROM Schemata;
• User tables:
SELECT
table_schema, table_name
FROM Tables
WHERE table_schema <> 'sys' AND table_schema <>
'performance_schema';
•
User
views:
SELECT
table_schema, table_name
FROM Views
WHERE table_schema <> 'sys';
•
User
routines (stored ops):
Select
specific_name, routine_definition FROM Routines;
Select specific_name, routine_definition FROM Routines LIMIT 1\G
• Schema priviledges:
SELECT
grantee, table_schema FROM Schema_Privileges;
4. SECURITY
•
Change Root
Password:
ALTER USER 'root'@'localhost' IDENTIFIED BY 'newpassword';
•
Remove the Anonymous
User:
DELETE FROM Mysql.User WHERE user=''; -- use two consecutive
single-quote characters (')
• Generally, act as root only if it is absolutely necessary.
• Give users only those privileges that they really need.
5. MANAGING USERS
•
Create
CREATE
USER 'name'@'host' [IDENTIFIED BY 'password'];
•
Drop
DROP USER 'name'@'host';
•
Rename User
RENAME
USER 'name'@'host' to 'new_name'@'host';
6. MANAGING PERMISSIONS
•
Grant
GRANT
privileges
ON 'target'
TO 'name'@'host'
[IDENTIFID BY 'password'];
•
Revoke
REVOKE
PRIVILEGES
ON 'target'
FROM 'name'@'host';
•
Set Password
ALTER USER 'name'@'host' IDENTIFIED BY 'password';
•
Show Grants
SHOW GRANTS FOR 'name'@'host';
• Specific privileges:
o Table:
'Select',
'Insert', 'Update', 'Delete', 'Create', 'Drop', 'Grant', 'References', 'Index',
'Alter',
'Create View', 'Show view', 'Trigger'
o Column:
'Select',
'Insert', 'Update', 'References'
o Process:
'Execute',
'Create Routine', 'Alter Routine', 'Grant'
o General:
'All
[Privileges]','Super'
• Privilege Targets
o
Global
privileges:
ON *.*
o
Database:
ON db_name.*
o
Table:
ON db_name.table_name
o
Column:
GRANT privileges(col_name) [,...] ON
db_name.table_name ...
7. DATABASE
The examples shown below (in section 8. EXAMPLES) require the Furniture database. Use the db.sql script to recreate the database if necessary. In class, run the cdb.bat batch command in the 11-Admin/Data folder.
8. EXAMPLES
•
Super User:
CREATE USER 'boss'@'localhost' IDENTIFIED BY 'wne14boss';
GRANT ALL PRIVILEGES ON *.* TO 'boss'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;
Open a new MySQL
terminal as boss:
mysql
–u boss –p
wne14boss
Try to create a new user, using a similar CREATE USER statement.
•
Read Only User
(for database Furniture):
CREATE USER 'readonly'@'localhost' IDENTIFIED BY 'only1234';
GRANT SELECT ON Furniture.* TO 'readonly'@'localhost';
FLUSH PRIVILEGES;
Open a new MySQL
terminal as reader:
mysql
–u readonly –p
only1234
Try to create to add a new record to the Product table, for example:
INSERT
INTO Product VALUES(13,'Fluffy Chair','Wool',200,10);
MySQL replies:
ERROR
1142 (42000): INSERT command denied to user 'readonly'@'localhost' for table
'product'
•
Read Only User
(for table Product in database Furniture):
Create User 'seeprod'@'localhost' Identified by 'prod1234';
Grant Select ON Furniture.Product TO 'seeprod'@'localhost';
Create a few
scenarios for this user.
•
Read Only User (for
columns
pid, onHand in table Product in database Furniture):
CREATE USER 'seeprodqoh'@'localhost' IDENTIFIED BY 'onhand1234';
GRANT SELECT (pid, onhand) ON Furniture.Product TO 'seeprodqoh'@'localhost';
Create a few
scenarios for this user too.
TII
REFERENCES
[1] MySQL Server Administration
[2] MySQL Workbench: Administration
[3] MySQL : Administration (at YouTube)
[4] GRANT Syntax
[5] Privileges Provided by MySQL
[6] 15 Practical Usages of MysqlAdmin Commands For Administering MySQL Server