https://www.metasploit.com/
MysqlTuner-perl //config mysql auto
/*
Lab2 - Database Authorization
*/
-- Create database
/*
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
[create_option] ...
create_option: [DEFAULT] {
CHARACTER SET [=] charset_name
| COLLATE [=] collation_name
| ENCRYPTION [=] {'Y' | 'N'}
}
*/
CREATE DATABASE IF NOT EXISTS DBHIS;
CREATE DATABASE IF NOT EXISTS DBAPP_A;
CREATE DATABASE IF NOT EXISTS DBAPP_B;
CREATE DATABASE IF NOT EXISTS DBAPP_C;
-- Create Table
USE DBHIS;
DROP TABLE IF EXISTS person;
CREATE TABLE person (
id INT NOT NULL AUTO_INCREMENT,
FirstName VARCHAR(255),
LastName VARCHAR(255),
Address VARCHAR(255),
City VARCHAR(255),
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `person`(`id`, `FirstName`, `LastName`, `Address`, `City`) VALUES (NULL, 'John', 'Doe', '123 Address', 'Chicago'), (NULL, 'Peter', 'Parker', '456 Address', 'Dallas');
DROP TABLE IF EXISTS users;
CREATE TABLE `users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`Name` VARCHAR(45) NOT NULL,
`Email` VARCHAR(45) NOT NULL,
`Password` VARCHAR(45) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `users` (`id`, `Name`, `Email`, `Password`) VALUES (NULL, 'john', 'john@gmail.com', MD5('123456')), (NULL, 'peter', 'peter@gmail.com', '123456');
SELECT * FROM users;
-- Create Users
/*
-- https://dev.mysql.com/doc/refman/8.0/en/create-user.html
CREATE USER [IF NOT EXISTS]
user [auth_option] [, user [auth_option]] ...
DEFAULT ROLE role [, role ] ...
[REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
[WITH resource_option [resource_option] ...]
[password_option | lock_option] ...
[COMMENT 'comment_string' | ATTRIBUTE 'json_object']
user:
(see Section 6.2.4, “Specifying Account Names”)
auth_option: {
IDENTIFIED BY 'auth_string'
| IDENTIFIED BY RANDOM PASSWORD
| IDENTIFIED WITH auth_plugin
| IDENTIFIED WITH auth_plugin BY 'auth_string'
| IDENTIFIED WITH auth_plugin BY RANDOM PASSWORD
| IDENTIFIED WITH auth_plugin AS 'auth_string'
}
tls_option: {
SSL
| X509
| CIPHER 'cipher'
| ISSUER 'issuer'
| SUBJECT 'subject'
}
resource_option: {
MAX_QUERIES_PER_HOUR count
| MAX_UPDATES_PER_HOUR count
| MAX_CONNECTIONS_PER_HOUR count
| MAX_USER_CONNECTIONS count
}
password_option: {
PASSWORD EXPIRE [DEFAULT | NEVER | INTERVAL N DAY]
| PASSWORD HISTORY {DEFAULT | N}
| PASSWORD REUSE INTERVAL {DEFAULT | N DAY}
| PASSWORD REQUIRE CURRENT [DEFAULT | OPTIONAL]
| FAILED_LOGIN_ATTEMPTS N
| PASSWORD_LOCK_TIME {N | UNBOUNDED}
}
lock_option: {
ACCOUNT LOCK
| ACCOUNT UNLOCK
}
*/
///-- Uninstall
###CREATE USER 'hisuser'@'192.168.200.%' IDENTIFIED WITH mysql_native_password BY '12345678';
CREATE USER 'hisuser'@'localhost' IDENTIFIED WITH mysql_native_password BY '12345678';
CREATE USER 'hisreport'@'%' IDENTIFIED WITH mysql_native_password BY '12345678' PASSWORD EXPIRE;
CREATE USER 'user_a'@'%' IDENTIFIED BY '123456' PASSWORD EXPIRE INTERVAL 90 DAY;
CREATE USER 'user_b'@'%' IDENTIFIED WITH mysql_native_password BY '12345678' PASSWORD EXPIRE NEVER;
CREATE USER 'user_c'@'%' IDENTIFIED WITH mysql_native_password BY '12345678' PASSWORD EXPIRE INTERVAL 90 DAY;
SELECT * FROM mysql.user;
////select user, host from mysql.user;######################
ใช้ tool pwgen #####apt install pwgen;
pwgen 8; enter
-- Grant privileges
/*
https://dev.mysql.com/doc/refman/8.0/en/grant.html
GRANT
priv_type [(column_list)]
[, priv_type [(column_list)]] ...
ON [object_type] priv_level
TO user_or_role [, user_or_role] ...
[WITH GRANT OPTION]
[AS user
[WITH ROLE
DEFAULT
| NONE
| ALL
| ALL EXCEPT role [, role ] ...
| role [, role ] ...
]
]
}
GRANT PROXY ON user_or_role
TO user_or_role [, user_or_role] ...
[WITH GRANT OPTION]
GRANT role [, role] ...
TO user_or_role [, user_or_role] ...
[WITH ADMIN OPTION]
object_type: {
TABLE
| FUNCTION
| PROCEDURE
}
priv_level: {
*
| *.*
| db_name.*
| db_name.tbl_name
| tbl_name
| db_name.routine_name
}
user_or_role: {
user (see Section 6.2.4, “Specifying Account Names”)
| role (see Section 6.2.5, “Specifying Role Names”)
}
*/
SHOW PRIVILEGES;
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON DBHIS.* TO 'hisuser'@'localhost';
GRANT ALL PRIVILEGES ON DBHIS.* TO 'hisreport'@'%';
GRANT ALL PRIVILEGES ON DBHIS.* TO 'hisreport'@'%' WITH MAX_QUERIES_PER_HOUR 100 MAX_UPDATES_PER_HOUR 10 MAX_CONNECTIONS_PER_HOUR 20 MAX_USER_CONNECTIONS 5;
GRANT ALL PRIVILEGES ON DBAPP_A.* TO 'user_a'@'%';
GRANT SELECT ON DBHIS.person TO 'user_a'@'%';
GRANT ALL PRIVILEGES ON DBAPP_B.* TO 'user_b'@'%';
GRANT SELECT ON DBHIS.person TO 'user_b'@'%';
GRANT ALL PRIVILEGES ON DBAPP_C.* TO 'user_c'@'%';
GRANT ALL PRIVILEGES ON DBHIS.* TO 'user_c'@'%';
SHOW GRANTS FOR 'user_c'@'%';
-- Revoke privileges
/*
REVOKE <privileges> ON <database>.<object> FROM '<user>'@'<host>';
*/แก้ไขสิทธิ์
REVOKE ALL PRIVILEGES ON DBHIS.* FROM 'user_c'@'%';
SHOW GRANTS FOR 'user_c'@'%';
FLUSH PRIVILEGES;
ไม่มีความคิดเห็น:
แสดงความคิดเห็น