ECA 236

1. ECA 236 Open Source Server Side Scripting Permissions & Users Open Source Server Side Scripting

2. 2 additional date functions • DATE_FORMAT( ) • used to format both the date and time • used if values are DATE or DATETIME data types • ( YYYY-MM-DD HH:MM:SS ) • TIME_FORMAT( ) • used to format time • used if values are TIME data type • ( HH:MM:SS ) SELECT DATE_FORMAT( date_column, ‘format_string’ ) FROM table_name; Open Source Server Side Scripting

3. format specifiers Open Source Server Side Scripting

4. format specifiers cont … Open Source Server Side Scripting

5. format specifiers cont … Open Source Server Side Scripting

6. format examples • display the current date and time in the format:Month DD, YYYY – HH:MM • display the registration date in users in the format:Registered on the DDth of Month. SELECT DATE_FORMAT( NOW( ), ‘%M %e, %Y - %l:%i' ); SELECT DATE_FORMAT( registration_date, ‘Registered on the %D of %M.’ ) FROM users; Open Source Server Side Scripting

7. ALTER • used to alter the structure of a table after it has been created • changing data type • changing size • change column name • etc ALTER TABLE table_name alteration [, alteration … ]; Open Source Server Side Scripting

8. ALTER cont … • common ALTER clauses Open Source Server Side Scripting

9. ALTER cont … • to add an additional column to users • AFTER • adds new column after designated column • FIRST • adds new column as first column in table • default is to add column to end ALTER TABLE users ADD COLUMN username VARCHAR(20) AFTER user_id; Open Source Server Side Scripting

10. ALTER cont … • to drop a column • to change the properties of a column, such as changing size of last_name from 30 to 45 ALTER TABLE users DROP COLUMN username; ALTER TABLE users CHANGE COLUMN last_name last_name VARCHAR(45); Open Source Server Side Scripting

11. INDEX • used to improve overall performance, especially when searching a particular column or columns • indexes are best used on columns that • are frequently used in a WHERE clause • are frequently used in an ORDER BY clause • are frequently used in joins • contain unique value • do not place an INDEX on columns which don’t need them Open Source Server Side Scripting

12. INDEX cont … • 3 types of indexes • INDEX • UNIQUE ( each row must have a unique value ) • PRIMARY KEY ( automatically indexed ) • Syntax ALTER TABLE table_name ADD INDEX index_name ( column_name ); Open Source Server Side Scripting

13. INDEX cont … • to add an INDEX on the last_name, first_name, and password columns, and a UNIQUE index on the username column, of the table users ALTER TABLE users ADD INDEX ( last_name ), ADD INDEX ( first_name ), ADD INDEX ( password ), ADD UNIQUE ( username ); Open Source Server Side Scripting

14. INDEX cont … • to drop an index • to rename a table ALTER TABLE users DROP INDEX first_name; ALTER TABLE table_name RENAME AS new_table_name; Open Source Server Side Scripting

15. database users • root user • administrative privileges which should not be shared with any other user • other users • we will create one administrative user for PHP scripts which connect through the web • limit these other users to what privileges they have on any particular database Open Source Server Side Scripting

16. privileges • privilege • a right to perform a particular action on a particular database • specific privileges are associated with individual users • privileges are granted when a user is created • principle of least privilege • Do not give a user any more privileges than necessary Open Source Server Side Scripting

17. privileges cont … • MySQL Privileges Open Source Server Side Scripting

18. privileges cont … • MySQL Privileges Open Source Server Side Scripting

19. privileges cont … • by default, the root user has been granted all privileges • as root user, we can create new users with a limited set of privileges on specific databases • MySQL server can contain multiple databases • each user may be limited to a single database, table, or column, as well as limiting type of privileges on each • privilege system insures integrity of databases Open Source Server Side Scripting

20. privileges cont … • when a user attempts to do something with the server, MySQL checks to make sure user has: • permission to connect to server, based on username and pw • permission to connect to specified database • permission to run specific queries Open Source Server Side Scripting

21. privileges cont … • to check permissions, MySQL looks in the following tables of the mysql database: • db • host • user • tables_priv • columns_priv Open Source Server Side Scripting

22. GRANT • users can be granted 4 levels of privileges • global ( reserve for root ) • database • table • column • GRANT is used to create users and grant privileges GRANT privileges ON database.* TO username IDENTIFIED BY ‘password’; Open Source Server Side Scripting

23. GRANT cont … GRANT privileges ON database.* TO username IDENTIFIED BY ‘password’; • privileges • comma separated list of privileges to grant to user • database.* • designate the database and table to which the privileges apply • database.* applies to all tables in the database • database.table_name applies only to specified table Open Source Server Side Scripting

24. GRANT cont … GRANT privileges ON database.* TO username IDENTIFIED BY ‘password’; • username • specify user name • 16 character limit • no spaces • case sensitive Open Source Server Side Scripting

25. GRANT cont … GRANT privileges ON database.* TO username IDENTIFIED BY ‘password’; • IDENTIFIED BY ‘password ’ • designated password with which the user logs on • no length limit • automatically encrypted to 16 characters • case sensitive • omitting IDENTIFIED BY clause will create a user who requires no password Open Source Server Side Scripting

26. GRANT example • create a new database • create a user who has administrative privileges on mushrooms • alter tables, insert data, create tables, etc • privileges on every table in mushrooms CREATE DATABASE mushrooms; GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, INDEX ON mushrooms.* TO bob IDENTIFIED BY ‘TwPk’; Open Source Server Side Scripting

27. GRANT example cont … • create a second user with only SELECT privileges • tell MySQL to enact the changes in the privilege tables GRANT SELECT ON mushrooms.* TO leland IDENTIFIED BY ‘alterEgo’; FLUSH PRIVILEGES; Open Source Server Side Scripting

28. GRANT example cont … • test new users and their privileges • exit as root user • sign in as the user bob with the password “ TwPk ” • attempt to use mysql database • attempt to use mushrooms database use mysql; use mushrooms; Open Source Server Side Scripting

29. GRANT example cont … • create a table in the mushrooms database • INSERT one record into morel • exit mysql monitor, sign in as leland • SELECT records CREATE TABLE morel ( location VARCHAR(50), find_date DATE ); INSERT INTO morel VALUES ( ‘Bolivar’, ‘2003-05-13’ ); CREATE TABLE morel ( location VARCHAR(50), find_date DATE ); SELECT * FROM morel; Open Source Server Side Scripting

30. sitename • create a user NAMED Web_User for sitename with the following privileges: SELECT, INSERT, UPDATE, DELETE. • Web_User is identified by the password ‘my1230’ GRANT SELECT, INSERT, UPDATE, DELETE ON sitename.* TO Web_User IDENTIFIED BY ‘my1230’; FLUSH PRIVILEGES; Open Source Server Side Scripting

31. General Security Guidelines • 4.3.1 in the MySQL Manual • do not ever give anyone, except the root user, access to the user table in the mysql database • learn the MySQL privilege system • do not keep plain-text passwords in the database • do not choose passwords from the dictionary • do not trust any data entered by a user • do not transmit plain, unencrypted data over the Internet Open Source Server Side Scripting

32. PHP & MySQL • to test whether PHP is making a connection to MySQL, run the following from a server • if you connect you will see <?php echo $dbc = mysql_connect( ‘localhost’, ’Web_User’, ‘my1230’ ); ?> Resource id #1 Open Source Server Side Scripting