Protecting Your Data

Information: Adding | Updating | Retrieving | Removing | PHP and MySQL

Chapter 5 Protecting Your Data - Page 93-110

093 Contolling Access to Your Data
094 Understanding account names and hostnames
096 Finding out about passwords
097 Taking a look at account permissions
098 Setting Up MySQL Accounts
100 Identifying what accounts currently exist
100 Adding accounts
101 Adding and changing passwords
102 Changing permissions
103 Removing accounts and permissions
104 Backing Up Your Data
107 Restoring Your Data
110 Upgrading MySql

093 Contolling Access to Your Data

no one can access the data in your database without an account
each MySQL account has the following atributes

a name
a hostname
a password
a set of permissions

094 to access your data, someone must use a valid account name and know the password to that account

in addition, the user must use the specific database server

the user is granted access to the database limited by the permissions the account has

094 permissions

each account is either allowed or not allowed to perform an operation in your database

such as SELECT, DELETE, INSERT, CREATE or DROP
settings that specify what an account can do are called permissions or privileges
you can set up an account with whatever permissions are needed

ie: for an online product catalog, you want the customer to see the information in the catalog, but not change it

094 when a user attempts to connect to MySQL and execute a query, MySQL controls access to the data in two stages

Connection verification

MySQL checks the validity of the account name and password
checks whether the connection is coming from an allowable host

Request verification

checks that the account has the necessary permissions before exuting a query

094 any query can fail either because the connection is rejected in the first step

or because the query is not permitted to execute

094 Understanding account names and hostnames

the account name and host name together identifies an account
095 when you build the GRANT or REVOKE query, you identify the MySQL account by using both the account name and the hostname in the following format

accountname@hostname (ie: root@localhost)

095 MySQL account names and hostnames are defined as follows

an account name can be up to 16 characters long
an account name can be blank

you can use an account with a blank name to allow anonymous users to connect to your database

the hostname can be a name or an IP address
the hostname can contain wildcards

you use the percent sign (%) as a wildcard

the hostname can be blank

095 an account with a blank name and blank hostname is possible

such an account would allow anyone to connect to the MySQL server by using any account name from any computer
an account with a blank name and a percent sign (%) for the hostname name is the same thing
it is unlikely that you would want such an account
such an account is sometimes installed when MySQL is installed

but it's given no privileges, so it can't do anything.

096 when MySQL is installed, it automatically installs an account with all privileges, root@localhost

depending on your system, this account may be created without a password
anyone using your computer where MySQL is installed

can access MySQL and do anything to it by using the account named root

if your administrating MySQL, add a password immediately

096 on some systems, like windows, an additional account named root@% is installed with full privileges

give this a password or delete the account

096 Finding out about passwords

a password is set up for every account, even if the password is blank, which means no password is required

there is no limit to the size of the password except for the limitations of your machine, pc

up to eight characters are preferred
MySQL encrypts the password so no one can see them
recommendations for choosing a password

use 6 to 8 characters
include one or more of the following

uppercase letter, lowercase letter, number, punctuation mark

do not use the account name as a password
do not use a word from the dictionary
do not include a name
do not use a phone number or a date

097 Taking a look at account permissions

MySQL uses account permissions to specify who can do what
permissions can be granted for particular databases, tables, or columns

ie: an account can be set up to do the following

allows the user to select data from all tables
insert data into only one table
update only a single column in a specific table

permissions can be granted by using the GRANT query
permissions are removed by using the REVOKE query
the GRANT & REVOKE query must be sent from an account that has permission to execute GRANT & REVOKE statements in the database
if you attempt to send a GRANT or REVOKE query using an account without the proper permissions, you get an error message

ie: grant command denied

098 permissions can be granted or removed individually or all at once

some MySQL Account Permissions

Permission	Description
ALL	all permissions
ALTER	can alter the structures of tables
CREATE	can create new databases or tables
DELETE	can delete rows in tables
DROP	can drop databases or tables
FILE	can read and write files on a server
GRANT	can change the permissions on a MySQL account
INSERT	can insert new rows or tables
SELECT	can read data from tables
SHUTDOWN	can shut down the MySQL server
UPDATE	can change data in a table
USAGE	no permissions

098 granting ALL is not a good idea because it includes permissions for administrative operations,

ie: shutting down the MySQL server

098 Setting Up MySQL Accounts

an account is identified by the account name and the name of the computer allowed to access MySQL using this account
when you create an account you specify it as accountname@hostname
you can specify a password when you create the account or add the password later
you can set up permissions when you create the account or add permissions later

098 all the account information is stored in a database named "mysql" that is automatically created when MySQL is installed

to add a new account or change any account information

you must use an account that has the proper permissions on the "mysql" database

099 for more info, read: The MySQL security database
100 Identifying what accounts currently exist

to see what accounts currently exist for your database, you need the necessary permissions
to list all the accounts try the following query

SELECT * FROM user
if you do not have permission you get the error message: "No Database Selected"

100 Adding accounts

100 when you add accounts to MySQL, only give the permissions required for its task
101 use the CREATE USER query added to MySQL version 5.0.2 as follows:

CREATE USER accountname@hostname IDENTIFIED BY 'password', accountname@hostname IDENTIFIED BY 'password', ...

this query creates a new user account with the password you used and no permissions
you do not need to specify a password
if you leave out IDENTIFIED BY 'password', the account is created with no password
you can add or change a password at a later time

101 if your using a version of MySQL before 5.0.2, you must use the GRANT query to create an account

the GRANT query is described in the "Changing permissions" section, page 102

101 Adding and changing passwords

you can add or change a password for an existing account using the SET PASSWORD query

SET PASSWORD FOR username@hostame = PASSWORD('password')

the account is set to "password" for the account username@hostname
if the account has a password, the password is changed
you do not need to specify the FOR clause, if you do not, the password is set in the account you are currently using

you can remove a password by sending the SET PASSWORD query with an empty password as follows

SET PASSWORD FOR username@hostname = PASSWORD('') note: '' are two single quotes

102 Changing permissions

you can see the current permissions for an account with the following query

SHOW GRANTS ON accountname@hostname

the output is a GRANT query that would create the current account

(thats wierd, I thought it would display current permissions for an account, not create it)

it shows all the current permissions

if you do not include the ON clause

you see the current permissions for the account that issued the SHOW GRANTS query

you can change permissions for an account with the following GRANT query

GRANT permission (columns) ON tablename
TO accountname@hostname IDENTIFIED BY 'password'

you can create a new account or change a password with the GRANT query

you need to fill in the following infomation:

permission (columns)

you must list at least one permission
you can limit each permission to one or more columns

by listing the column name in parentheses following the permission

if no column name is listed, the permission is granted on all columns in the table(s)
you can list as many permissions and columns as needed, separated by commas
the possible permissions are listed in that table above (link)
ie: a GRANT query might start with this:

GRANT select (firstName, lastName), update,
insert (birthdate) ...

tablename

this indicates which tables the permission is granted on
at least one table is required
you can list several tables, separated by commas
the possible values for 'tablename' are:

tablename
databasename.tablename

tablename

the entire table named 'tablename' in the current database
you can use an asterisk (*) to mean all tables in the current database
if you use an asterisk and no current database is selected,

the privilege will be granted to all tables on all databases

databasename.tablename

the entire table named 'tablename' in databasename
you can use an asterisk (*) for either the database name or the table name to mean all
Using *.* grants the permission for all tables in all databases

accountname@hostname

if the account already exists, it is given the indicated permissions
it the account doesn't exist, it's added
the account is identified by the 'accountname' and the 'hostname' as a pair
if an account exits with the specified account name but a different hostname

the existing account is not changed, a new one is created

password

this is the password that you're adding or changing
a password is not required
if you don't want to add or change a password for this account

leave out the phrase IDENTIFIED BY 'password'

103 the GRANT query to add a new account for use in the PHP program for the PetCatalog database might be:

GRANT select ON PetCatalog.* TO phpuser@localhost
IDENTIFIED BY 'A41!14a!'

103 Removing accounts and permissions

to remove an account, you can use the DROP USER query

which was added in MySQL 4.1.1

DROP USER accountname@hostname, accountname@hostname, ...
you must have an account that has DELETE privileges on the mysql database to execute the DROP USER query
after MySQL 5.0.2

removes the account and all records related to the account, including records that give it permissions on specific databases or table

before MySQL 5.0.2

DROP USER drops only accounts with no privileges
therefore you must remove all privileges from an account,

including database and table permissions, before you can drop it

to remove permissions, use the REVOKE query, syntax follows as:

REVOKE permission (columns) ON tablename
FROM accountname@hostname
you need to fill in the following information (read the page, similar but different from page 102)

permission (columns)
tablename

tablename
databasename.tablename

accountname@hostname

you can remove all the permissions for an account with the following REVOKE query

REVOKE all on *.* FROM accountname@hostname

104 Backing Up Your Data

104 backup your data and store it off-site
104 you can also back up to a DVD or flash or external drives
105 web hosting companies will backup for you, learn their policies
105 if you are the MySQL administer, use the "mysqldump" program that creates backups

mysqldump creates a text file that contains all the queries needed to recreate your database

105 for the mac, do the following: (read the pages 104 - 105)

go to the bin folder from the mysql folder
type or execute the following (you might have to be in some terminal program to do this):

mysqldump --user=accountname --password=password
databasename > path/backupfile name

106 example of making backups

107 Restoring Your Data

an example of an error message that may indicate a corrupted file

Incorrect key file for table: 'tablename'.

you can restore a backup file to replace the corrupted database
instructions for restoring a backup file on pages 107 - 110

110 Upgrading MySql

MySQL recommends that you do not skip versions
occasionally, incompatible changes are introduced in new versions of MySQL
after upgrading to the newer version, you should run the 'mysql_upgrade' script which checks your files, repairing them if needed
for a Windows machine, run the 'mysql_fix_privileges_tables' script with MySQL versions prior to 5.0.19

version: 20120514 1120A

This site is for the private use of my students. (2008)

Information: Adding | Updating | Retrieving | Removing | PHP and MySQL