ECA 236 Open Source Server Side Scripting Permissions

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

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

format specifiers Specifier Description Example %e day of the month 1 – 31 %d day of the month, 2 digits 01 – 31 %D day with suffix 1 st – 31 st %W weekday name Sunday – Saturday %a abbreviated weekday name month number Sun – Sat month number, 2 digit 01 – 12 %c %m ECA 236 Open Source Server Side Scripting 1 – 12 3

format specifiers Specifier cont … Description Example %M month name January – December %b abbreviated month name Jan – Dec %Y year 2003 %y year 03 %l (lowercase L) %h hour 1 – 12 hour, 2 digit 01 – 12 %k hour, 24 hour clock 0 – 23 ECA 236 Open Source Server Side Scripting 4

format specifiers Specifier Description cont … Example %H hour, 24 hour clock, 2 digit 00 – 23 %i minutes 00 – 59 %S seconds 00 – 59 %r time 3: 13: 03 PM %T time, 24 hour clock 15: 13: 03 %p AM or PM ECA 236 Open Source Server Side Scripting 5

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

ALTER tused to alter the structure of a table after it has been created tchanging data type tchanging size tchange column name tetc ALTER TABLE table_name alteration [, alteration … ]; ECA 236 Open Source Server Side Scripting 7

ALTER tcommon cont … ALTER clauses Clause Meaning ADD COLUMN Add a new column to the end of a table DROP COLUMN Removes a column from a table, including all its data CHANGE COLUMN ADD INDEX Change the data type and properties of a column Adds a new index on a column DROP INDEX Removes an existing index RENAME AS Changes the name of a table ECA 236 Open Source Server Side Scripting 8

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

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

INDEX tused to improve overall performance, especially when searching a particular column or columns tindexes are best used on columns that tare frequently used in a WHERE clause tare frequently used in an ORDER BY clause tare frequently used in joins tcontain unique value tdo not place an INDEX on columns ECA 236 don’t need which them Open Source Server Side Scripting 11

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

INDEX cont … tto 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 ); ECA 236 Open Source Server Side Scripting 13

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

database users troot user tadministrative privileges which should not be shared with any other user tother users twe will create one administrative user for PHP scripts which connect through the web tlimit these other users to what privileges they have on any particular database ECA 236 Open Source Server Side Scripting 15

privileges tprivilege ta right to perform a particular action on a particular database tspecific privileges are associated with individual users tprivileges are granted when a user is created tprinciple of least privilege t. Do not give a user any more privileges than ECA 236 Open Source Server Side Scripting necessary 16

privileges t. My. SQL Privilege cont … Privileges Applies To Allows SELECT tables, columns Read rows from tables INSERT tables, columns Add new rows to tables UPDATE tables, columns Modify existing data in tables DELETE tables Delete existing data in tables INDEX tables Create and drop indexes ALTER tables Modify the structure of tables CREATE database, tables Create new databases or tables ECA 236 Open Source Server Side Scripting 17

privileges t. My. SQL Privilege cont … Privileges Applies To Allows DROP database, tables Drop existing databases or tables RELOAD server Reload the grant tables to enact user changes SHUTDOWN server Shut down the My. SQL Server PROCESS server View and stop My. SQL server processes FILE server Import data into tables from text files GRANT database, tables Create new users REVOKE ECA 236 database, tables Remove the privileges of existing 18 users Open Source Server Side Scripting

privileges cont … tby default, the root user has been granted all privileges tas root user, we can create new users with a limited set of privileges on specific databases t. My. SQL server can contain multiple databases teach user may be limited to a single database, table, or column, as well as limiting 19 ECA 236 of privileges Open Source Side Scripting type on. Server each

privileges cont … twhen a user attempts to do something with the server, My. SQL checks to make sure user has: permission to connect to server, based on username and pw tpermission to connect to specified database tpermission to run specific queries t ECA 236 Open Source Server Side Scripting 20

privileges cont … tto check permissions, My. SQL looks in the following tables of the mysql database: tdb thost tuser ttables_priv tcolumns_priv ECA 236 Open Source Server Side Scripting 21

GRANT tusers can be granted 4 levels of privileges tglobal ( reserve for root ) tdatabase ttable tcolumn t. GRANT is used to create users and grant privileges GRANT privileges ON database. * TO username IDENTIFIED BY ‘password’; ECA 236 Open Source Server Side Scripting 22

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

GRANT cont … GRANT privileges ON database. * TO username IDENTIFIED BY ‘password’; tusername tspecify user name t 16 character limit tno spaces tcase sensitive ECA 236 Open Source Server Side Scripting 24

GRANT cont … GRANT privileges ON database. * TO username IDENTIFIED BY ‘password’; t. IDENTIFIED tdesignated BY ‘password ’ password with which the user logs on tno length limit tautomatically encrypted to 16 characters tcase sensitive tomitting IDENTIFIED BY clause will create a user who requires no password ECA 236 Open Source Server Side Scripting 25

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

GRANT example cont … tcreate a second user with only SELECT privileges GRANT SELECT ON mushrooms. * TO leland IDENTIFIED BY ‘alter. Ego’; ttell My. SQL to enact the changes in the privilege tables FLUSH PRIVILEGES; ECA 236 Open Source Server Side Scripting 27

GRANT example ttest cont … new users and their privileges texit as root user tsign in as the user bob with the password “ Tw. Pk ” tattempt to use mysql database use mysql; tattempt ECA 236 to use mushrooms database use mushrooms; Open Source Server Side Scripting 28

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

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

General Security Guidelines t 4. 3. 1 tdo in the My. SQL Manual not ever give anyone, except the root user, access to the user table in the mysql database tlearn the My. SQL privilege system tdo not keep plain-text passwords in the database tdo not choose passwords from the dictionary tdo not trust any data entered by a user tdo not transmit plain, unencrypted data over 31 ECA 236 the Internet Open Source Server Side Scripting

PHP & My. SQL tto test whether PHP is making a connection to My. SQL, run the following from a server <? php echo $dbc = mysql_connect( ‘localhost’, ’Web_User’, ‘my 1230’ ); ? > tif you connect Resource you id #1 will see ECA 236 Open Source Server Side Scripting 32