Data Base Privilege and
User Management
IMPLEMENTATION OF
DATABASE USING
MYSQL.
Chapter 1
Administering mysql database.
Introduction
This is to put mysql more secure in terms of misuse
and attack
Mysql uses Access Control List (ACLs) for all queries,
ACLs
Connection and operations that a user can do in the
database.
It also uses SSL security to connect between the client
and the database.
Mysql Access Privilege System
What the privileges system does.
I. Authenticate user connect from a given host
II. Associate the user with the privileges on the database
PRIVILEDGES CATEGORIES:
I. Select
II. Drop
III. Insert
IV. Update
V. Delete.
VI. Load data
VII. Grant privilege to user
VIII. Administration operations
How the privilege works.
Allows only the allowed operations to be granted to a
given user.
User is identified by the HOST to which he/she
connect via his/her Identification
When connecting into the database, the database will
allow you to do only granted access to be done by a
specific user.
Stages that involves user connecting to
the database/server.
I. Server verify the user through his/her psw
II. After connecting, server look every issued
statement if you have privilege to perform it
Table used by mysql in access control
It uses three tables in managing the privileges' of the
user who logged in to the database.
Table used:
I. User table
II. Db table
III. Host table
Database used in managing the user privileges.
The database used is mysql database,
Inside mysql db, all the tables user, db, host are
found.
STAGE 1
Verifying user connection
When connecting, server identifies the user based
on:
I. Identification
II. Password
If any of the two is not correct the server will not allow
the user to connect into the database (denies
connection)
If all are true then connection is allowed.
User identification
Identifying the user is based on:
I. Client host from which you are connecting
II. Username provided.
In identifying the user all 3 tables (host , user and
db) are used.
Cont…
User accepted if HOST and USER column in any
user table row matches the client hostname and
username
The password specified must be exactly the one
specified in that row for the given user.
STAGE 2
Request verification
When the user access is verified then, request that are
sent into the database are verified.
The server looks:
I. What operation do you want to perform?
II. Check if you have sufficient privilege to perform
that operation
Cont…
At this stage, privilege column in the user table
is used.
Tables that privileges are located:
I. User
II. Db
III. Host
IV. Tables_priv
V. Column_priv
VI. Procs_priv
Cont…
All the tables are coming from the mysql database.
Privileges in mysql:
Host table:
Db table:
When privileges changes take effects
As it starts ,MYSQL recalls all grant table contents
into the memory.
Tables that are in the memory become effective for
access control at that time.
When the server reloads the grant table, privileges
for existing client connection is affected.
The privileges assigned to user takes effects next
time the user connect into the database.
Cont…
Issuing the privileges by GRANT, REVOKE, SET
PASSWORD, server will load them into grant tables
immediately as they are submitted.
The granted access will not take effect in privilege
checking until the server is restarted.
