# connect to database
mysql -u user -p -h host database

# check connections and processes
show variables like '%conn%';
show status like '%conn%';
show variables like '%ssl%';
show processlist;
status;

# check character-set and collation
show variables like '%character%';
show variables like '%collat%';
show table status where name = 'MY_TABLE';
show full columns from 'MY_TABLE';

# database and character sets
alter database MY_DATABASE character set utf8 collate utf8_unicode_ci;
alter table MY_TABLE character set utf8 collate utf8_unicode_ci;
create database MY_DATABASE default character set = 'utf8' default collate = 'utf8_unicode_ci';

# create new table
create table if not exists MY_TABLE (
    ID bigint not null auto_increment,
    NAME varchar(100) not null,
    ORG_ID bigint not null,
    COMMENT longtext,
    CREATED_ON datetime,
    primary key (ID),
    unique (NAME, ORG_ID)
) engine = MyISAM|InnoDB , default character set = 'utf8' default collate = 'utf8_general_ci';

# create new user
create user 'dummy'@'localhost' identified by 'dummy';
grant all privileges on MY_DATABASE.* to 'dummy'@'localhost' with grant option;
create user 'dummy'@'%' identified by 'dummy';
grant all privileges on MY_DATABASE.* to 'dummy'@'%' with grant option;
select Host,User,ssl_type,ssl_cipher,x509_issuer,x509_subject,plugin,authentication_string from mysql.user;

# require user to use ssl
alter user 'dummy'@'localhost' require ssl;

# require user to use client-cert
alter user 'dummy'@'localhost' require x509;

# require user to use a specific client-cert with given CN, O, C, ST, L
alter user 'dummy'@'localhost' require subject '/CN=client.mariadb.com/O=MariaDB Inc./C=US/ST=California/L=RWC';

# check blob rows & table sizes
select id, length(body)/1024 as `len_body (KB)` \
        from tableA order by `len_body (KB)` desc;
select table_schema as `database`, table_name as `table`, \
        round((data_length + index_length) / 1024 ) as `size (KB)` \
        from information_schema.tables where table_schema in ('dapp', 'dauth') \
        order by (data_length + index_length) desc;

# backup and restore
mysqldump -u userA -p db_xyz > db_xyz.sql
mysql -u userA -p db_xyz < db_xyz.sql

# create CA certificate
openssl genrsa 2048 > ca-key.pem
openssl req -new -x509 -nodes -days 3600 \
        -key ca-key.pem -out ca-cert.pem

# create server certificate, remove passphrase, and sign it
openssl req -newkey rsa:2048 -days 3600 \
        -nodes -keyout server-key.pem -out server-req.pem
openssl rsa -in server-key.pem -out server-key.pem
openssl x509 -req -in server-req.pem -days 3600 -CA ca-cert.pem \
        -CAkey ca-key.pem -set_serial 01 -out server-cert.pem

# create client certificate, remove passphrase, and sign it
openssl req -newkey rsa:2048 -days 3600 -nodes \
        -keyout client-key.pem -out client-req.pem
openssl rsa -in client-key.pem -out client-key.pem
openssl x509 -req -in client-req.pem -days 3600 -CA ca-cert.pem \
        -CAkey ca-key.pem -set_serial 01 -out client-cert.pem

# enable ssl for mariadb in /etc/my.cnf.d/mariadb-server.cnf
[mariadb]
...
ssl_cert = /etc/my.cnf.d/certificates/server-cert.pem
ssl_key = /etc/my.cnf.d/certificates/server-key.pem
ssl_ca = /etc/my.cnf.d/certificates/ca-cert.pem
#require_secure_transport=ON

# enable ssl for mysql client in /etc/my.cnf.d/mysql-clients.cnf
[mysql]
...
ssl_cert = /etc/my.cnf.d/certificates/client-cert.pem
ssl_key = /etc/my.cnf.d/certificates/client-key.pem
ssl_ca = /etc/my.cnf.d/certificates/ca-cert.pem
#ssl-verify-server-cert

# connect to database
psql postgres
psql -U userA databaseB

# list databases, schemas, tables, columns
\list
\dn
\dt
\d tableC

# common pg commands
\du
\q
\connect databaseB
\password userA

# create new user
create user userA with superuser createrole createdb password 'userA-password';
alter role userA createdb;
create user userB with encrypted password 'userB-password';
grant all privileges on database databaseB to userB;

# other commands
select current_database();
sudo su -s /bin/bash postgres

# change schema order in search path
show search_path;
set search_path = schema1, public;

# enable ssl for postgresql in /var/lib/pgsql/data/postgresql.conf
...
ssl = on
ssl_ca_file = '/var/lib/pgsql/data/ca-cert.pem'
ssl_cert_file = '/var/lib/pgsql/data/server-cert.pem'
ssl_key_file = '/var/lib/pgsql/data/server-key.pem'

# enforece ssl for users in /var/lib/pgsql/data/pg_hba.conf
...
hostnossl all   all   123.12.12.12/32   reject
hostssl   all   all   123.12.12.12/32   cert
host      all   all   0.0.0.0/0         md5
host      all   all   ::/0              md5
hostssl   all   all   0.0.0.0/0         md5
hostssl   all   all   ::/0              md5

# connect to database
mssql -u userA -p
sp_databases
sp_tables
sp_tables @table_owner = 'dbo'
use databaseC
select db_name();

# create database/schema
create database databaseC;
create schema schemaD;
select * from sys.schemas;

# list users
select name, type_desc, default_database_name from sys.sql_logins;
select name, type_desc, default_database_name from sys.server_principals where type in ('S','R');
select name, type_desc, authentication_type_desc from sys.database_principals;

# create new login/user
create login loginA with password = 'my-pasword', check_policy = off, check_expiration = off;
create user userB for login loginA;
alter server role dbcreator add member loginA;
alter login userA with default_database = databaseC;
alter login sa with password = 'weak-password', check_policy = off, check_expiration = off;
use databaseC;
grant select, insert, update, delete to userB;
use databaseC;
exec sp_addrolemember 'db_datawriter', 'userB';
use databaseC;
exec sp_addrolemember 'db_datareader', 'userB';

# connect using sqlcl (username/password)
sql system/oracle
sql test/test
show user;
select ora_database_name from dual; --SID, service name
select table_name from user_tables;

# create new user
select tablespace_name, status, contents from user_tablespaces;
select username, account_status, default_tablespace from dba_users;
create user userB identified by my_password default tablespace users;
alter user userB quota unlimited on users;
grant resource, connect, create session, create table to userB;

# grant user access to another schema
alter session set current_schema=userC;
grant select any table to userB;
grant insert any table to userB;
grant update any table to userB;
grant delete any table to userB;

# configure mongodb to use auth
vi /opt/homebrew/etc/mongod.conf
brew services stop/start mongodb-community
mongod --auth --port 27017 --dbpath /opt/homebrew/var/mongodb

# connect to mongod
mongosh -u test -p
show dbs
use admin
show collections
db.collectionName.countDocuments();
db.collectionName.find().pretty();
db.collectionName.insertOne({name: "foobar", age: 21});

# create new user
use admin
db.createUser(
  {
    user: "test",
    pwd: passwordPrompt(),
    roles: [
      { role: "userAdminAnyDatabase", db: "admin" },
      { role: "readWriteAnyDatabase", db: "admin" }
    ]
  }
);
db.getUsers();

# /opt/homebrew/etc/mongod.conf
systemLog:
  destination: file
  path: /opt/homebrew/var/log/mongodb/mongo.log
  logAppend: true
storage:
  dbPath: /opt/homebrew/var/mongodb
net:
  bindIp: 127.0.0.1, ::1
  ipv6: true
security:
  authorization: enabled
setParameter:
  enableLocalhostAuthBypass: false

# install colima, docker, sqlserver
brew install colima
brew install docker
colima start --memory 4
colima start --profile x86 --arch x86_64 --memory 4
docker context ls
docker ps -a
docker images
docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=strongpasword" -p 1433:1433 \
           --name sqlserver --hostname sqlserver -d mcr.microsoft.com/mssql/server:2022-latest
docker images
docker ps -a
docker start sqlserver
docker logs sqlserver

# install oracle (x86_64), setup colima profile named x86
docker run --name oracle -p 1521:1521 -e ORACLE_PASSWORD=oracle \
           -d gvenzl/oracle-xe:18-slim
docker images
docker ps -a
docker start oracle
docker logs oracle

# install sqlcl for oracle
brew serach openjdk
brew ls openjdk
brew install openjdk@21
brew install sqlcl
sql system/oracle
sql test/test

# common colima, docker cmds
colima stop|status default|x86
colima start
colima start -p x86
colima delete x86
docker ps -a
docker start|stop sqlserver
docker rm sqlserver
docker images
docker image rm 6880f23540e0
docker context ls
docker context use colima
docker context use colima-x86
docker image prune -a
docker container prune
docker volume prune
docker network prune