Database Users

You should always create new users to access your database. Do not use the root user account.

Create an administrative user for PHP scripts to connect through the web

Limit these users to the privileges they need to access any particular database

Priviledges

Privilege- a right to perform a particular action on a particular database

Each user is granted specific privileges when that user is created
Give only the privileges necessary

MySQL Privileges

Privilege	Applies To	Allows
SELECT	tables, columns	Read rows from tables
INSERT	tables, columns	Add new rows to tables
UPDATE	tables, columns	Modify existing data in tables
DELETE	tables	Delete existing data in tables
INDEX	tables	Create and drop indexes
ALTER	tables	Modify the structure of tables
CREATE	database, tables	Create new databases or tables
Privilege	Applies To	Allows
DROP	database, tables	Drop existing databases or tables
RELOAD	server	Reload the grant tables to enact user changes
SHUTDOWN	server	Shut down the MySQL Server
PROCESS	server	View and stop MySQL server processes
FILE	server	Import data into tables from text files
GRANT	database, tables	Create new users
REVOKE	database, tables	Remove the privileges of existing users

The root user has been granted all privileges by default
The root user can create new users with a limited set of privileges on specific databases

MySQL server can contain multiple databases
each user may be limited to a single database, table, or column, as well as limiting type of privileges on each
privilege system insures integrity of databases

When a user attempts to do something with the server, MySQL checks to make sure user has:

permission to connect to server, based on username and password
permission to connect to specified database
permission to run specific queries

To check permissions, MySQL looks in the following tables of the mysql database:

db
host
user
tables_priv
columns_priv

Users can be granted 4 levels of privileges

global ( reserve for root )
database
table
column

Grant

GRANT is used to create users and grant privileges

Syntax:

GRANT privileges ON database.* TO username;

With a password:

Syntax:

GRANT privileges ON database.* TO username IDENTIFIED BY ‘password’;

privileges

comma separated list of privileges to grant to user

database.*

designate the database and table to which the privileges apply
database.* applies to all tables in the database
database.table_name applies only to specified table

username

specify user name
16 character limit
no spaces
case sensitive

IDENTIFIED BY ‘password ’

designated password with which the user logs on
no length limit
automatically encrypted to 16 characters
case sensitive
omitting IDENTIFIED BY clause will create a user who requires no password

Create a new database

CREATE DATABASE survey;

create a user who has administrative privileges on survey

alter tables, insert data, create tables, etc
privileges on every table in survey

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, INDEX ON survey.* TO myuser IDENTIFIED BY ‘mypassword’;

create a second user with only SELECT privileges

GRANT SELECT ON survey.* TO myother IDENTIFIED BY ‘password2’;

tell MySQL to enact the changes in the privilege tables

FLUSH PRIVILEGES;

test new users and their privileges

exit as root user
sign in as the user myother with the password “ password2”
attempt to use mysql database

Test the privileges:

Show grants for myuser;

use mysql;

attempt to use the survey database

use survey;

create a table in the survey database

CREATE TABLE mysurvey ( myname VARCHAR(50), comment VARCHAR(250));

INSERT one record into mysurvey

INSERT INTO mysurvey values( ‘Steve’, ‘Here are my comments’ );

SELECT records

SELECT * FROM mysurvey;

Add another row

create a user NAMED Web_User for survey with the following privileges: SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, INDEX, FILE.
Web_User is identified by the password ‘my1234’

Provide a username for PHP

GRANT all ON *.* TO webuser;

do not ever give anyone, except the root user, access to the user table in the mysql database
learn the MySQL privilege system
do not keep plain-text passwords in the database
do not choose passwords from the dictionary
do not trust any data entered by a user
do not transmit plain, unencrypted data over the Internet

to test whether PHP is making a connection to MySQL, run the following from a server

<?php
echo $dbc = mysql_connect( ‘localhost’, ’Web_User’, ‘webpw’ );
?>

if you connect you will see

Resource id #1

Free Tutorials and Training