Chapter 9: Creating Your Web Database
What are SPECIAL privileges and what are they used for?
-"ALL" (grants all privileges) -"USAGE" (grants no privileges)
What are binary string types?
-"BINARY(M)" (fixed length of M can have 0-255 bytes) _"VARBINARY(M)" (variable length of M can have 1-65,535 bytes)
What are regular string types?
-"CHAR(M)" (fixed length of M can have 0-255 characters) -"CHAR" (same as "CHAR(1)") -"VARCHAR(M)" (variable length of M can have 1-65,535 characters)
What are ADMINISTRATOR privileges and what are they used for?
-"CREATE TABLESPACE" (admins can alter, drop, or create tablespaces) -"CREATE USER" (admins can create users) -"CREATE TEMPORARY TABLES" (admins can create temporary tables) -"FILE" (admins can read data from files into tables) -"LOCK TABLES" (admins can lock tables) -"PROCESS" (admins can view all users processes) -"RELOAD" (admins can reload privileges and eliminate hosts, logs, privileges and tables) -"REPLICATION CLIENT" (admins can use "SHOW STATUS" on replication masters/slaves) -"REPLICATION SLAVE" (admins can connect replication slave servers to master servers) -"SHOW DATABASE" (admins can allow users to see all databases) -"SHUTDOWN" (admins can shut down MySQL server) -"SUPER" (admins can kill user threads)
What are the main commands used for privileges?
-"CREATE USER" (create a user for info) -"GRANT" (gives rights to user) -"REVOKE" (takes rights away from user)
What are date and time data types?
-"DATE" (date displayed as YYYY-MM-DD) -"TIME" (time displayed as HH:MM:SS) -"DATETIME" (date and time displayed as YYYY-MM-DD HH:MM:SS) -"TIMESTAMP[(M)]" (timestamp that reports time based on M) -"YEAR[(2|4)]" (year displayed in 2 or 4 digits)
What are fixed point data types?
-"DECIMAL[(M[,D])]" (digits depend on M) -"NUMERIC[(M,D)]" (same as decimal) -"DEC[(M,D)]" (same as decimal) -"FIXED[(M,D)]" (same as decimal)
What are ENUM and SET types?
-"ENUM" (can hold only one listed value or null, 65,535 values max) -"SET" (can hold a set of specified values or null, 64 values max)
What are floating point data types?
-"FLOAT(precision)" (specifies single or double) -"FLOAT[(M,D)]" (single precision with specified display width and amount of decimal places) -"DOUBLE[(M,D)]" (double precision with specified display width and amount of decimal places) -"DOUBLE PRECISION[(M,D)]" (same as "DOUBLE[(M,D)]") -"REAL[(M,D)]" (same as "DOUBLE[(M,D)]" )
What are the only queries needed for privilege of least principle?
-"SELECT" -"INSERT" -"DELETE" -"UPDATE"
What are USER privileges and what are they for?
-"SELECT" (users can select table rows) -"INSERT" (users can insert new table rows) -"UPDATE" (users can modify table data) -"DELETE" (users can delete table rows) -"INDEX" (users can create/drop table indexes) -"ALTER" (users can alter tables by adding/modifying/renaming columns and tables) -"CREATE" (users can create databases, indexes, and tables) -"DROP" (users can remove a database, table, or view) -"EVENT" (users can alter, create, drop, and view database events) -"TRIGGER" (users can create, drop, or execute table triggers) -"CREATE VIEW" (users can create views) -"SHOW VIEW" (users can view created views) -"PROXY" (users can impersonate other users) -"CREATE ROUTINE" (users can create functions and procedures) -"EXECUTE" (users can run stored functions and procedures) -"ALTER ROUTINE" (users can modify definitions of stored functions and procedures)
What are different TIMESTAMP display types for M?
-"TIMESTAMP" (YYYYMMDDHHMMSS) -"TIMESTAMP(14)" (YYYYMMDDHHMMSS) -"TIMESTAMP(12)" (YYMMDDHHMMSS) -"TIMESTAMP(10)" (YYMMDDHHMM) -"TIMESTAMP(8)" (YYYYMMDD) -"TIMESTAMP(6)" (YYMMDD) -"TIMESTAMP(4)" (YYMD) -"TIMESTAMP(2)" (YY)
What are TEXT and BLOB types?
-"TINYBLOB" (tiny BLOB field, 255 characters max) -"TINYTEXT" (tiny TEXT field, 255 characters max) -"BLOB" (normal BLOB field, 65,535 characters max) -"TEXT" (normal TEXT field, 65,535 characters max) -"MEDIUMBLOB" (medium BLOB field, 16,777,215 characters max) -"MEDIUMTEXT" (medium TEXT field, 16,777,215 characters max) -"LONGBLOB" (long BLOB field, 4,294,967,295 characters max) -"LONGTEXT" (long TEXT field, 4,294,967,295 characters max)
What are integer data types?
-"TINYINT[(M)]" (tiny integers) -"SMALLINT[(M)]" (small integers) -"MEDIUMINT[(M)]" (medium integers) -"INT[(M)]" (regular integers) -"INTEGER[(M)]" (same as "INT") -"BIGINT[(M)]" (big integers)
What are the six tables called that store privileges in the "mysql" database?
-"mysql.user" -"mysql.db" -"mysql.host" -"mysql.tables_priv" -"mysql.columns_priv" -"mysql.procs_priv"
What are the four types of numeric data?
-Integers -Fixed point -Floating point -Bit fields
What are four basic column data types?
-Numeric -Date/Time -String -Spatial
What should appear if the create command works?
-Query OK -Affected rows -Execution time
What are string data types?
-Regular -Binary -TEXT and BLOB -ENUM and SET
What should you do if you do not receive those messages?
-Run: mysql_install_db -Make sure that you correctly set the password for root (if you are using a personal machine) -Make sure that you enter the correct password (for somebody else's machine)
What are the three basic types of privileges in MySQL?
-User -Administrator -Special
What will appear if the password works?
-Welcome message with keywords for End (; or \g) -MySQL connection ID -Server version -Copyright information -Trademark information -Keywords for Help ("help;" or "\h") and Clear ("\c")
What are key aspects of creating a MySQL database for a website?
1. Database creation 2. Users and privileges 3. Privilege System 4. Database tables 5. Indexes 6. Column types
What are the six levels of privilege?
1. Global 2. Database 3. Table 4. Column 5. Stored Routine 6. Proxy User
How do you install MySQL on a web server?
1. Install files 2. Set up a user to run on (if required) 3. Set up path 4. Open up the Command Prompt and run: mysql_install_db 5. Set root password 6. Delete previous user and text database 7. Start server and set it to run automatically
What is a privilege system in MySQL?
A method of giving certain users a right to certain actions on certain database objects (similar to permission)
What is a global privilege?
A privilege granted for all databases by replacing the "item" placeholder with "*.*"
What is the Principal of Least Privilege?
A security measure that states that a user should only have access to privileges required for the particular task.
What must come after each MySQL command in order to run?
A semicolon
What does the "WITH GRANT OPTION" clause do?
Allows specified users to grant privileges to others
What does BLOB stand for?
Binary large object
What does the "CREATE USER" command do?
Creates a user by adding placeholders for user info in the following command: CREATE USER user_info
What does the "GRANT" command do?
Gives a user privileges (can also create a user account with additional syntax): "GRANT privileges [tables]" "ON item" "TO user_info"
What is the first step to creating a database?
In the Command Prompt, enter: mysql> create database dbname; (replace "dbname" with whatever you want to name the database)
What does the mysql command do?
Launches the MySQL server
What happens if a semicolon is forgotten and the Enter key is pressed?
MySQL assumes that the code is to be continued, and then it provides a continuation arrow (->)
How do you log into MySQL?
Open up the Command Prompt and enter: mysql -h hostname -u username -p
What does the -h command do?
Specifies the host/machine to run MySQL on (can be left out along with hostname if the command is being run on the same server as MySQL)
What does the -p command do?
Specifies the password to log in under (It can and should be left out if a root password has not been set upon installation, as the server will automatically request it)
What does the -u command do?
Specifies the username to log in under (otherwise the default server username is used)
What does the "REQUIRE" clause do?
Specifies what the user must do in order for the database to run effectively.
What is the default MySQL username to use for your own machine/server?
root
