Skip to content

Instantly share code, notes, and snippets.

@lomkju lomkju/databseCommands.txt
Last active Feb 14, 2020

Embed
What would you like to do?
Database Commands
#MSQL
CREATE DATABASE `DB_NAME`;
CREATE USER `DB_USER`@'%' IDENTIFIED BY 'DB_PASS';
GRANT CREATE,DELETE,INSERT,SELECT,UPDATE ON `DB_NAME`.* TO `DB_USER`@'%';
SELECT User from mysql.user;
SELECT User from mysql.user WHERE USER='DB_USER';
SHOW GRANTS for DB_USER;
FLUSH PRIVILEGES;
REVOKE INSERT ON *.* FROM 'jeffrey'@'localhost';
GRANT PROCESS, SHOW DATABASES, REPLICATION CLIENT ON *.* TO 'DB_USER'@'%'
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE ON `DB_NAME`.* TO `DB_USER`@'%';
#Show user connections.
SELECT IFNULL(usr,'All Users') user,IFNULL(hst,'All Hosts') host,COUNT(1) Connections
FROM
(
SELECT user usr,LEFT(host,LOCATE(':',host) - 1) hst
FROM information_schema.processlist
WHERE user NOT IN ('system user','root')
) A GROUP BY usr,hst WITH ROLLUP;
#RDS
#Show current connection by users:
SHOW PROCESSLIST;
#Kill connection id:
CALL mysql.rds_kill(2417469);
#PSQL
CREATE DATABASE ptest;
REVOKE connect ON DATABASE database_name FROM PUBLIC;
CREATE USER ptest WITH PASSWORD 'ptest';
GRANT connect ON DATABASE ptest TO ptest;
\c ptest
GRANT DELETE,INSERT,SELECT,UPDATE ON ALL TABLES IN SCHEMA public TO ptest;
// This has to be done from the user who created the database. By default admin dopesn't have access to any tables.
GRANT SELECT ON ALL TABLES IN SCHEMA public TO redash;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO redash;
#PSQL OPTIMIZATIONS
alter database DB_NAME set statement_timeout = 60000;
alter database DB_NAME set idle_in_transaction_session_timeout = 10000;
SELECT state,datname, COUNT(*) FROM pg_stat_activity GROUP BY datname, state;
select pg_terminate_backend(pid) from pg_stat_activity where datname='DB_NAME';
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.