You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
42 lines
1.7 KiB
42 lines
1.7 KiB
-- 创建用户
|
|
CREATE USER 'admin_user'@'localhost' IDENTIFIED BY 'password123';
|
|
CREATE USER 'salesperson_user'@'localhost' IDENTIFIED BY 'password123';
|
|
CREATE USER 'customer_user'@'localhost' IDENTIFIED BY 'password123';
|
|
|
|
-- 授予管理员权限
|
|
GRANT ALL PRIVILEGES ON *.* TO 'admin_user'@'localhost' WITH GRANT OPTION;
|
|
FLUSH PRIVILEGES;
|
|
|
|
-- 授予销售员权限
|
|
-- 查看所有产品信息
|
|
GRANT SELECT ON my_company_db.products TO 'salesperson_user'@'localhost';
|
|
|
|
-- 创建新的销售订单
|
|
GRANT INSERT ON my_company_db.sales_orders TO 'salesperson_user'@'localhost';
|
|
GRANT INSERT ON my_company_db.order_details TO 'salesperson_user'@'localhost';
|
|
|
|
-- 查看自己的销售记录
|
|
GRANT SELECT ON my_company_db.sales_orders TO 'salesperson_user'@'localhost';
|
|
GRANT SELECT ON my_company_db.order_details TO 'salesperson_user'@'localhost';
|
|
|
|
-- 查看客户的订单信息
|
|
GRANT SELECT ON my_company_db.customers TO 'salesperson_user'@'localhost';
|
|
GRANT SELECT ON my_company_db.sales_orders TO 'salesperson_user'@'localhost';
|
|
GRANT SELECT ON my_company_db.order_details TO 'salesperson_user'@'localhost';
|
|
|
|
FLUSH PRIVILEGES;
|
|
|
|
-- 授予客户权限
|
|
-- 查看自己的订单信息
|
|
GRANT SELECT ON my_company_db.customers TO 'customer_user'@'localhost';
|
|
GRANT SELECT ON my_company_db.sales_orders TO 'customer_user'@'localhost';
|
|
GRANT SELECT ON my_company_db.order_details TO 'customer_user'@'localhost';
|
|
|
|
-- 创建新的订单
|
|
GRANT INSERT ON my_company_db.sales_orders TO 'customer_user'@'localhost';
|
|
GRANT INSERT ON my_company_db.order_details TO 'customer_user'@'localhost';
|
|
|
|
-- 查看产品的基本信息（名称、价格）
|
|
GRANT SELECT (product_name, price) ON my_company_db.products TO 'customer_user'@'localhost';
|
|
|
|
FLUSH PRIVILEGES; |