MySQL Frequently Asked Questions
Where are MySQL database files stored on machine with LINUX operating system? Go To Answer
What are the file extensions of the MySQL database table files? Go To Answer
How can I create a database in a "back-door" way? Go To Answer
What are the stages of access control? Go To Answer
How do I set permissions that allow changes to be made to only one field in a table, but allow all of the fields to be viewed? Go To Answer
1. MySQL database files stored on a computer with the LINUX operating system are stored in /var/lib/mysql/(database name).Go to top
2. The tables of a database use the following extensions: tablename.ISD, tablename.ISM, tablename.frm.Go to top
3. If you make a new directory in /var/lib/mysql MySQL will view that as a new database. Keep in mind that there will not be any tables associated with the database at that time. For example: if pwd is /var/lib/mysql and you use the command: mkdir newdatabase, if you view the databases via WEBMIN you will now see a database with the name "newdatabase". Note: there will not be any tables within the database. Go to top
4. Stages of access control (per O'Reilly book MySQL & mSQL, Randy Jay Yarger, George Reese & Tim King, July 1999, pg 56-57):
MySQL divides access control into two stages. The first is connection, because you must connect to the server before being able to do anything else. Connection has two checks: 1) to see that the user name and host under which you are connecting has a corresponding entry in the USER table - no match = no connection. If it finds a match and there is a password entry, if the password does not match there = no connection.
The second is request verification. At this point any request you make is matched against your privileges. MySQL make take these priviliges from any of the user, db, host, tables_priv, or columns_priv tables (They are the actual names of tables, not the exact names shown in the MySQL Database Server configuration page Global Options portion in WEBMIN ). If there is a match with the user table, and the user table has a positive permission, then the operationis immediately allowed. Otherwise MySQL looks for matches in the following tables in the following order:
If the db table has a positive entry, the operation is allowed and no further checking occurs. If the entry is negative, then MySQL checks with all matching tables_priv entries. If, for example, the operation is a SELECT that joins two tables, then the user must have positive entries for both tables in taht database in the tables_priv table. If one or more of the entries is negative or nonexistant, then MySQL will perform the same logic for all the columns in the columns_priv table. Go to top
5. To grant permissions to the table field level requires entries in a number of Permission Tables. The Permission tables used are: USER, DATABASE, TABLE, and FIELDS.
The example database, table, and table fields being used is: Database: mydb, Table Name: AddressBook, AddressBook Fields: fname, lname, address, city, state, zip, phone.
USER Permissions: Any user listed that is granted permissions has those permissions on all databases. It is not a good practice to grant any permissions in this table except to the database administrator. Specify the User Name, Host (that they can connect from), Password (if wanted), and Permissions (none - recommended).
DATABASE Permissions: Used to define which MySQL users will have access to which databases from which hosts. Every user must already exist in the User Permissions table, though they can be granted additional permissions here. Users may not login from any hosts other than those specified in. However, you may grant different permissions for different hosts by creating multiple rows for the same user. Specify the Database name, User name (same as in USER Permissions table), Host (same as in USER Permissions table), and Permissions (none - recommended)
TABLE Permissions: Used to grant additional access to tables by users, beyond what is available in User Permissions or Database Permissions. The permissions granted here apply only to access to one table in one database by one user, thus allowing quite fine-grained access to be granted. Specify the Table name, Database name, Host (same as in USER Permissions table), User name (same as in USER Permissions table), Table Permissions (none), Field Permissions (Select & Update). If Select is not choosen you will be unable to connect to the database via the ODBC used from the windows box (MyODBC).
FIELD Permissions: This field allows you to grant additional access to specific fields in tables by users. The permissions granted cannot be greater than those specified in the Table Permissions under Field permissions for the table the field is in. Specify each Field name, Table name, Database name, Host, and User (same as used in the previous permissions tables). Then, for each Field that you only want to have Read permissions you must set Permissions to be "Select." For each field that you want to be able to grant permission to make changes you set Permissions to both "Select and Update."
NOTE: There is another permissions table called HOST. This table gives you a way of creating basic permissions on a host-by-host basis.Go to top