Database changes

2018-06-10

Memos of syntax for database changes

PostgreSQL

Reassign objects without super user.

https://sysadmintips.com/services/databases/postgresql-error-permission-denied-to-reassign-objects/

Bash

# dump database
pg_dump -h localhost -U root -W -p 5555 -d 'database_name' > dump.sql

# import database
psql -h localhost -U root -W -p 5555 'database_name' < dump.sql

SQL

-- create user
CREATE USER myuser WITH ENCRYPTED PASSWORD 'mypass';

-- grant database permission to user
GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;

-- grant login
ALTER USER 'myuser' WITH LOGIN;

-- change database owner
ALTER DATABASE name OWNER TO new_owner;

-- change ownership of tables
GRANT myuser TO root;
REASSIGN OWNED BY admin TO new_admin;
REVOKE myuser FROM root;

-- make database not accessible by anyone which is the default setting in postgres
REVOKE connect ON DATABASE db FROM PUBLIC;

MySQL

SQL

-- Create user with password
CREATE USER `newuser`@`%` IDENTIFIED BY 'password';

GRANT ALL PRIVILEGES ON database_name.table_name TO 'username'@'%';