Home arrow MySQL arrow Page 4 - Access Granted

Born Privileged - MySQL

mySQL comes with a pretty powerful security system, the grant tables, which allows database administrators to precisely control access to databases, tables and even specific rows and columns. In this article, find out how the five grant tables combine to offer power users a tremedous amounts of flexibility and control over database access and operations.

TABLE OF CONTENTS:
  1. Access Granted
  2. Meet Joe User
  3. Beeping Turkeys
  4. Born Privileged
  5. The Perfect Host
  6. Cream Of The Crop
  7. The Mechanics
By: icarus, (c) Melonfire
Rating: starstarstarstarstar / 9
April 24, 2001

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement
Once the users, passwords and hosts are specified, it becomes necessary to specify the privileges each user has - which is where the other fourteen columns (or "privilege fields") come in. Here's what each of those fields represents:

Select_priv - execute a SELECT query

Insert_priv - execute an INSERT query

Update_priv - execute an UPDATE query

Delete_priv - execute a DELETE query

Create_priv - CREATE databases and tables

Drop_priv - DROP databases and tables

Reload_priv - Reload/refresh the mySQL server

Shutdown_priv - Shut down a running mySQL server

Process_priv - track activity on a mySQL server

File_priv - read and write files on the server

Grant_priv - GRANT other users privileges

Index_priv - Create, edit and delete indices

Alter_priv - execute an ALTER query

It is important to note at this point that the security privileges assigned to each user in the "user" table are globally valid - they apply to each and every database on the system. The following record

+-----------------+---------------+ | Field | Value | +-----------------+---------------+ | Host | apple.pie.com | | User | joe | | Password | gf64us | | Select_priv | Y | | Insert_priv | N | | Update_priv | N | | Delete_priv | Y | | Create_priv | N | | Drop_priv | N | | Reload_priv | N | | Shutdown_priv | N | | Process_priv | N | | File_priv | N | | Grant_priv | N | | References_priv | N | | Index_priv | N | | Alter_priv | N | +-----------------+---------------+

would imply that user "joe" has the ability to DELETE records from any table in any database on the server - not a Good Thing if Joe happens to be in a bad mood. It is for this reason that most administrators (and the mySQL manual) recommends leaving all privileges in this table to "N" (the default value) for every user, and using the "host" and "db" tables to assign more focused levels of access.

Similarly, the following record would create a super-user named "god", with complete access to all mySQL privileges.

+-----------------+---------------+ | Field | Value | +-----------------+---------------+ | Host | apple.pie.com | | User | god | | Password | hjgj4j34 | | Select_priv | Y | | Insert_priv | Y | | Update_priv | Y | | Delete_priv | Y | | Create_priv | Y | | Drop_priv | Y | | Reload_priv | Y | | Shutdown_priv | Y | | Process_priv | Y | | File_priv | Y | | Grant_priv | Y | | References_priv | Y | | Index_priv | Y | | Alter_priv | Y | +-----------------+---------------+

It is instructive at this point to look at the default "user" table that ships with mySQL, in order to better understand the implications of running an out-of-the-box mySQL setup.

+-----------+------+----------+----------------+ | Host | User | Password | all_privileges | +-----------+------+----------+----------------+ | localhost | root | | Y | | % | | | N | +-----------+------+----------+----------------+

In other words, the user connecting as "root" from "localhost" has complete access, while any other user, connecting from any other host, would not be able to perform any actions at all.{mospagebreak title=The Belly Of The Beast} The "host" and "db" tables are used together - they control which databases are available to which users, and the operations possible on those databases. Take a look at the fields in a typical "db" table:

+-----------------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------+---------------+------+-----+---------+-------+ | Host | char(60) | | PRI | | | | Db | char(32) | | PRI | | | | User | char(16) | | PRI | | | | Select_priv | enum('N','Y') | | | N | | | Insert_priv | enum('N','Y') | | | N | | | Update_priv | enum('N','Y') | | | N | | | Delete_priv | enum('N','Y') | | | N | | | Create_priv | enum('N','Y') | | | N | | | Drop_priv | enum('N','Y') | | | N | | | Grant_priv | enum('N','Y') | | | N | | | References_priv | enum('N','Y') | | | N | | | Index_priv | enum('N','Y') | | | N | | | Alter_priv | enum('N','Y') | | | N | | +-----------------+---------------+------+-----+---------+-------+

Again, the first three fields are scope fields, which link a specific user and host to one or more databases. The remaining fields are used to specify the type of operations the user can perform on the named database.

A record like this would imply that the user "bill", connecting from host "cranberry.domain.com", would be able to use database "darkbeast"

+-----------------------+------+-----------+----------------+ | Host | User | Db | all_privileges | +-----------------------+------+-----------+----------------+ | cranberry.domain.com | bill | darkbeast | Y | +-----------------------+------+-----------+----------------+

while this would imply that any user, connecting from any host, would have complete access to the "test" database.

+------+------+---------+----------------+ | Host | User | Db | all_privileges | +------+------+---------+----------------+ | % | | test | Y | +------+------+---------+----------------+


 
 
>>> More MySQL Articles          >>> More By icarus, (c) Melonfire
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

MYSQL ARTICLES

- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: