Posted 2006 | Updated 23.05.2006 | Contributed by Andy Mallett
The following is temporarily in note form, subject to revision in the near future
The MySQL database holds ALL permissions and privileges on a mysql system.
Standard MySQL Tables
The usual line up includes..
user
db
host
columns_priv
tables_priv
func
|
|
user (grant) Table
Contains all permissions for users that have access to MySQL. By giving permissions here you are setting global access
to the db., i.e. if users have delete privileges here, they can delete any records in any databases on the system.
So BE CAREFUL IN SETTING PRIVILEGES IN USER!! ONLY FOR ADMIN USERS.
Set permissions in the db table, not the user table.
Usually just a username, password and hostname in user table, don't set privileges here.
First level of access. Hostname can be localhost in class, but proper computername/ip in real world.
db Table
Contains permissions for all databases contained in the MySQL server. Permissions granted here are given ONLY for the
named database.
hosts Table
Controls access by limiting the hosts that can connect to the DB. Connect from certain hosts only, for instance. This
host can connect to this database. Fine tuning of permissions in database. i.e. user can view all db but only update 2
columns.
columns_priv & tables_priv
These tables govern the permissions for all databases columns & tables
Other Notes
mysql and test
test is for testing
mysql must exists for other databases to work
all permissions held in myswl table
mysql has many layers of security
top layer giving permissions is users table
Example
-------
Bob - TL (Accounts) - full access to ACCOUNTS db only
JANE - TL (Marketing) - full access to MARKETING db only
---------------------------------------------------------------------------
HEIDE - Admin Assistant
- view access to MARKETING db, update specific tables in MARKETING db ->
- Give Heide entry in user (username/passwd/hostname)
- GRANT SELECT PRIVILEGES ON mkt.* TO ange@localhost IDENTIFIED BY "passsword";
- Set privileges into tables_priv & columns
You set username/pw/hostname in user table, give permissions (privileges) in
db table
GRANT ALL PRIVILEGES ON *.* TO ange@localhost IDENTIFIED BY "passsword"; -->
GRANT ALL PRIVILEGES ON ange.* TO ange@% IDENTIFIED BY "passsword";
GRANT ALL PRIVILEGES ON acc.* TO bob@% IDENTIFIED BY "passsword";
% = any hostname (except localhost!) ange@% AND ange@localhost
-> have to include 2 entries if you use % first for localhost access
Syntax: username.tablename
i.e. ange.table1
MySQL is not case sensitive but you will often see commands in capitals
(easier to read)
-------------------------------------------------------------------------
Ange tip -> create a username who has root privileges and then kill the
root user!
make user Ange
grant ALL on user to Ange
Revoke alluser root
-------------------------------------------------------------------------
|
