DATABASE ADMINISTRATION
AND SECURITY
Chapter 4
Bandarlampung, Oktober 2020
Jupriyadi, S.Kom. M.T.
jupriyadi@teknokrat.ac.id https://spada.teknokrat.ac.id
Chapter Objective
The student will be able to:
1. Create, delete and alter user in MySQL
2. Set database permissions to users
User Management in MySQL
MySQL Database is a popular database and one of the
feature in the MySQL database is user management
User management is used to protect the database from
unauthorized users
Protecting the database is one of the tasks of a database
administrator
Database administrators (DBAs) use specialized software to
store and organize data. The role may include capacity planning,
installation, configuration, database design, migration,
performance monitoring, security, troubleshooting, as well as
backup and data recovery
User Management in MySQL
In the MySQL database, a database administrators using a
‘mysql’ database to manage user
Contents of
mysql database
There are five tables that can be used to manage users and to set
user privileges (grant tables), namely: user, db, host, tables_priv
and columns_priv
User Management in MySQL
GRANT TABLES :
user table
This table contains the user data, the connection and the user access
privileges. Access level is Global.
structure of user table
User Management in MySQL
GRANT TABLES :
db table
Organizing the database access privileges by the user.
Access level is database.
structure of db table
User Management in MySQL
GRANT TABLES :
host table
Organizing the allowable host for the user to access MySQL, if more than
one host. Access level is database.
tables_priv table
Organizing the tables that can be accessed by the user and the type of
access privileges. Access level is table.
columns_priv table
Organizing the attributes (fields) that can be accessed by the user and
the type of access privileges. Access level is attribute (field).
User Management in MySQL
User Privileges type :
Normal user access levels
Includes access privileges at database or column level.
1. ALTER
2. CRETATE
3. DELETE
Administrator access level
4. DROP
5. INDEX Used only by root or administrator level users.
6. INSERT 1. FILE
7. SELECT 2. PROCESS
8. UPDATE 3. RELOAD
9. REFERENCE 4. SHUTDOWN
S 5. CREATE TEMPORARY TABLE
6. EXCUTE
7. LOCK TABLES
8. REPLICATION CLIENT
9. REPLICATION SLAVE
10. SHOW Special privileges level
DATABASES
11. SUPER Can be applied to each user
1. ALL
2. USAGE
User Management in MySQL
Anonymous and Root User
Anonymous user is user without identity and password. with an anonymous user,
mysql is accessible to anyone without a user name and password. therefore, this
user should be removed to keep the database secure (delete from user where
user=‘ ‘;). host consists of
Localhost : MySQL database can only be
accessed by local server computer
127.0.0.1 : by default, MySQL database will only
open its access to this IP
% : MySQL database can be accessed
Root User from any computer
Anonymous User
The root user (superuser) is the highest user level that can run any command in
MySQL. This user can run a query that serves to control, create a MySQL user,
restrict and administer user access privileges in the MySQL database.
This root user is automatically created during the first MySQL Server installation
with a blank password.
User Management in MySQL
Change the root password
The root password must be replaced in order for the database to be
secure.
Command to update the root user password is :
update user set password=password(‘......’)
where user=‘root’;
Continue with the command : Example :
flush privileges;
The flush privileges command will instruct the server to reread the
access privileges table
User Management in MySQL
Add a new user
Adding a new user can be done by adding a record to the user table.
Insert into tbname values (........); user with name john does not have access privileges yet
Example :
login to MySQL server with user 'john'
no database can be accessed by 'john',
because this user does not have access
privileges
User Management in MySQL
Granting the access privileges
Syntax :
GRANT Access_type [(column_name)],.....
ON db_name.tb_name TO
User_name [IDENTIFIED BY ”password”] [WITH GRANT
access_choise]
example
change of access priviliges
access type : insert, select, update are given to user 'john'.
The access type applies to all databases and tables (global).
Therefore, changes in access privileges are seen in the user
table
login to MySQL server using user 'john'
all databases and tables in the MySQL
server can be accessed by 'john' with
access privileges : insert, select, update
User Management in MySQL
Granting the access privileges
‘Grant’ can also be used to create new users directly
(without the record insertion command)
example1
example2
by adding this option, access privileges
'grant_priv' become 'y'
User Management in MySQL
Granting the access privileges
example3
User ‘adam' can login to MySQL and access all
databases but do not have access privileges, so this
user only as dummy user or blank user
login to MySQL server using user ‘adam'
login to MySQL server from the localhost user
can be done in various ways :
mysql –u username –h localhost –p password
or
mysql –u username –h 127.0.0.1 –p password
or
mysql –u username –p password
User Management in MySQL
Granting the access privileges
Create a new user with host type ‘%’ (remote host)
example4
Remote host
login to MySQL server from
remote host (%) must include IP
address of MySQL server
User Management in MySQL
Remove the access privileges
Syntax :
REVOKE access_type ON dbname.tbname FROM user_name
example
change of access priviliges
removing the access type : insert, select and
update from user 'john‘ (host type : localhost)
User Management in MySQL
access privileges of database level
Granting the access privileges only to certain database
example
access type : select, insert only applies to database
'academic‘.
To check the change of access privileges can be seen
table 'db‘ (not table 'user')
login to MySQL server using user 'john'
Only the database 'academic' can be
accessed by user 'john'
User Management in MySQL
access privileges of database level
login to MySQL server using user 'john'
All table in the database
‘academic’ can be
accessed by user 'john'
this operation can be
accessed by user 'john'
this operation cannot be
accessed by user 'john'
User Management in MySQL
access privileges of table level
Granting the access privileges only to certain table
example
access type : select, insert only applies to table ‘department’
in database 'academic‘.
To check the change of access privileges can be seen table
‘tables_priv‘ (not table 'user‘ or ‘db’)
login to MySQL server using user 'john'
Only the database 'academic' can be
accessed by user 'john'
User Management in MySQL
access privileges of column level
Granting the access privileges only to column table
example
access type : insert only applies to column ‘budget’
in table ‘department‘, select only applies to column ‘dept_name’
and ‘building’ in table ‘department‘.
To check the change of access privileges can be seen table
‘columns_priv‘ (not table 'user‘ or ‘db’ or ‘user’)
login to MySQL server using user 'john'
this operation can be
accessed by user 'john'
Question & Answer
