DBMS

Review

Terms

• DBMS
• Database
• Table, row, column, field, record

Authentication v. Authorization

• Users
  • Where have we ‘used’ users
  • Are these same as system users?
  • Are they same as web app users?
• Hosts

Statements

• Create database/delete/select
  • create database vehicles;
  • use vehicles
• Create table/deletion
  • create table trucks (name varchar(10));

Statements

• Row creation/deletion/access
  • insert into trucks values ('dodge');
  • insert into trucks values ('toyota');
  • insert into trucks values ('ford');
  • delete from trucks where name= 'ford';
  • select * from trucks;

Create user

• create user 'tom'@'localhost' identified with mysql_native_password by 'somepassword';
• create user ‘tom’@‘144.38.199.2’ identified with mysql_native_password by ‘somepassword’;

Grant statements

• grant all privileges on vehicles.* to ‘tom’@‘localhost’;
• grant all privileges on vehicles.* to carlos@‘144.38.199.161’;
• grant all privileges on vehicles.* to joe@‘%’;

Privileges

• What does the mysql.db table have in it that is useful?
  • database-level privileges
• What about mysql.user?
  • User accounts, global privileges, other…
• More

mysql command

• mysql -u <username> -p -h <host>
  • mysql -u carlos -p -h db.thegummibear.com

Web apps

Generally speaking, when you create a new web app you will:

• create a new db
• create a user
• give user privileges to db

What information will web app need in order to connect successfully to db?

Random

• flush privileges
  • If you modify the grant tables directly using statements such as INSERT, UPDATE, or DELETE, your changes have no effect on privilege checking until you either restart the server or tell it to reload the tables. If you change the grant tables directly but forget to reload them, your changes have no effect until you restart the server. This may leave you wondering why your changes seem to make no difference!
  • Grant statements don’t necessarily need a flush, but it doesn’t hurt.
• can we recover a mysql user password?

Last Updated 05/08/2023