form as MySQL outputs it (YYYY-MM-DD for dates and HH:MM:SS for

times) if data in these formats is available.

Exercises

1. Write a SELECT statement to output the current time in the format "hour:minute
   am/pm weekday nth month year."


2. Write a SELECT statement to find which weekday the 4th of July will fall on in 2010.


3. Write a SELECT statement to add 1000 days to 1 April 2000 and return the date. On
   what weekday will the subsequent day fall?

Day 17: MySQL Database Security

Overview

Database security is an essential component to any database system. It is the security that protects your
data, safeguarding it from would-be crackers. Security also protects your data from users. Sometimes users,
in their ignorance, will delete records they don’t want to delete. To help guard against these types of
accidents, you can enforce a level of security to prevent users from deleting, updating, or adding records to
your database.

Security plays a major role in any application that many users can access at once. MySQL handles

security very well and is one of the most secure databases on the market today. Today, you will learn-

ƒ How security in MySQL is implemented

ƒ Stages of control and control points

ƒ Adding and editing users and their privileges

How Security Is Implemented in MySQL

The MySQL security system is very flexible. With it, you can give potential users various levels of access
ranging from the ability to log in from a specific machine as a specific user to full administrator access from
anywhere. It is up to you to decide how tight you want your security to be. Hopefully, this chapter will provide
you with some guidelines and ideas about how to run your database security.
MySQL holds all permissions and privileges in the mysql database. This database is one of two
databases that are created automatically when you install MySQL. (The other one is the test
database). The only people who should have access to this table are the database administrators. This
database is just like any other MySQL database. The data files are stored in data a directory under the
mysql parent directory, where all other data files are stored. The tables in this database are
ƒ user
ƒ db
ƒ host
ƒ func
ƒ columns_priv
ƒ tables_priv
You can run queries on these tables, just as you can on any other table. These tables are collectively
referred to as the Grant tables. Each column in these Grant tables reflect what permissions a person
has by either a Y (meaning they can perform the operation) or an N (meaning they cannot). For
instance, a person who has DELETE privileges in the user table would have a Y in the Delete_priv
column.

The user Table

The user table contains the permission data for all the users that have access to MySQL. You can set all
the permissions for users here. This table consists of the following columns:
ƒ Host This is the name of the user’s computer. With MySQL, you can limit a person's
access based on the location from which he or she is connecting.
ƒ User This is the user’s name that he or she will use to access MySQL.
ƒ password The user’s password.
ƒ Select_priv Grants the user the ability to perform SQL SELECT queries.
ƒ Insert_priv Allows the user to add data to databases by using the SQL INSERT
statement.