---

Defining a database and its users

---

• Data Definition Language Features in SQL.
  • SQL allow users to perform the following data definition functions:

    Create a database Define new relations in a database Define conditions on attributes in the relations Alter the structure of (existing) relations (This operation can be very complex when there is already data in the relations - it is best done when relations are still empty) Delete one or more relations Delete a database

  ---

  ---

  ---

  ---

• Creating a database: the CREATE SCHEMA command.
  • Database schema:

    Database schema = the content of one database         Database schema is used to group together related database tables (= relations) A database schema also contains other constructs (such as indices)

  • Example:

    The Company database (schema) that we have designed in the course (see: click here ) is an example of a database schema.

    ---

    ---

  • Command to create a database (schema):

    The CREAT SCHEMA command is used to define a database schema

  • Syntax of the CREATE SCHEMA command in the standard SQL language:

    create schema schema_name authorization db_user;

    All access authorization of the new database is granted (= given) to the user db_user (i.e., he is the owner of the database)

  ---

  ---

  ---

  ---

• MySQL implementation of CREATE SCHEMA
  • Fact:

    Implementations of standards may (sometimes) deviate from the specified standard

    ---

    ---

  • MySQL syntax of the Create Schema command:

    create database database_name ;

    In MySQL, a database can only be created by the "root" user

    The authorization (= access permission) is granted separately

  ---

  ---

  ---

  ---

• Creating user accounts in MySQL
  • Creating users:

    CREATE USER 'userid'@'hostname' IDENTIFIED BY 'password' ;

    ---

    ---

  • Example 1:

    CREATE USER 'cheung'@'aruba.mathcs.emory.edu' IDENTIFIED BY '12345' ;

    User 'cheung' can only use MySQL from host aruba.mathcs.emory.edu.

    ---

  • Example 2:

    CREATE USER 'cheung'@'%' IDENTIFIED BY '12345' ;

    User 'cheung' can only use MySQL from any (wildcard) host.

    ---

    ---

  • How I created the "cs377" user:"

    CREATE USER 'cs377'@'%' IDENTIFIED BY 'abc123';

  ---

  ---

  ---

  ---

• Granting access to users in MySQL
  • Grant access to database.table to a user@host:

    GRANT permission ON database.table TO 'user'@'host' ;

    ---

  • Example:

    GRANT SELECT ON companyDB.* TO 'cs377'@'%';

    Grants the SELECT permission to all tables in the companyDB database to user cs377 login from any host

    ---

  • Example:

    GRANT ALL ON companyDB.* TO 'cs377'@'%';

    Grants the all permission (select, delete, updata, create table, drop table, ....) to all tables in companyDB database to user cs377 login from any host