Security and backup

• Database backup
  • mysqldump
    • Reference
    • And the man page.
    • To save, user needs
      • Select privileges for dumped tables.
      • Show Views for views
      • Trigger for triggers
      • And possibly permission to lock tables.
    • Reloading user needs
      • CREATE privileges on all types of objects created
      • Alter privileges might be required as well.
    • Choices
      • mysqldump db_name [tables]
      • mysqldump -p dbennett SKU_DATA ORDER_ITEM
      • mysqldump --databases dbname [dbname]
      • mysqldump --all-databases
    • Only dumps INFORMATION_SCHEMA if specified, and only in later versions of MySQL (5.1.38 and later, we use 5.1.73)
    • Output
      • is to standard out
      • Is in sql
    • There are many, many many command line arguments.
      • To control connection (-p, -pipe, ...)
      • To control format of output (--add-drop-table, adds DROP_TABLE before a CREATE TABLE in the file)
      • To provide a master set of flags (--opt is the same as --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset)
• Permissions
  • From Widom chapter 10
  • And here
  • Everything is keyed off of the username.
    • In MySQL users are created with Create User
    • creates a row in mysql.user
    • 'user_name'@'host_name'
    • localhost
    • 'username'@'%' appears to be a wildcard for everything.
    • And is equivalent to 'username'
    • localhost = 127.0.0.1
    • Ip addresses or _ and % in hostname (LIKE),
      • 147.64.242.% matches as you would expect.
      • My sql does not allow "1.2.my.com", if it starts with a digit, it is an ip address.
      • But do be careful with host names.
      • And you may specify submasks CREATE USER 'david'@'192.58.197.0/255.255.255.0';
  • Similar to file system permissions
  • However they are more complex.
  • Privileges are given with GRANT
    • GRANT priv_type [column_list] on object_type privilege_level To user_spec
    • Objects are TABLE, FUNCTION, PROCEDURE
    • privilege_level are *, *.*, db_name.* db_name.table_name table_name and db_name.routine_name
    • priv_type to come
  • And removed with >REVOKE
    • fairly close to grant.
  • privileges
  • Widom says that there are 9 basic SQL privilege types
    • SELECT
    • INSERT
    • DELETE
    • UPDATE
    • REFERENCES
    • USAGE
    • TRIGGER
    • EXECUTE
    • UNDER
  • A corresponding sql statement can not be executed without that privilege (the first four)
  • REFERENCES : right to use referential integrity constraints
  • USAGE : other schema element
  • TRIGGER, EXECUTE
  • UNDER : create subtypes
  • In MySQL
    • Quite a list
    • Administrative are global as they are to administer the database
    • Database are limited to a database
    • Sub-parts of a database can be limited as well (tables, indexes, views, stored routines)
    • ALL: all privileges
    • Some are strange :"The TRIGGER privilege enables trigger operations. You must have this privilege for a table to create, drop, or execute triggers for that table. ", but you still may not be able to use the trigger.
  • WITH GRANT OPTION
  • Widom suggests a Grand Diagram as part of the database.
    • Page 432
    • Keeps track of who give privileges to whom.