对比中理解PG、Oracle、MySQL的角色和权限

Whoami：5年+金融、政府、医疗领域工作经验的DBA
Certificate：OCP、PCP
Skill：Oracle、Mysql、PostgreSQL
Platform：CSDN、墨天伦、公众号（呆呆的私房菜）

-- 创建一个名为 ECommerceDataHub 的新数据库
create database ecommercedatahub;


-- 创建销售部门用户
create role sales_user with login password 'sales_pwd';
-- 创建库存部门用户
create role inventory_user with login password 'inventory_pwd';
-- 创建市场部门用户
create role marketing_user with login password 'marketing_pwd';
-- 创建合作伙伴用户
create role partner_user with login password 'partner_pwd';


-- 授权上述用户拥有业务数据库访问和创建权限
grant connect,create on database ecommercedatahub to sales_user, inventory_user, marketing_user;
grant connect on database ecommercedatahub to partner_user;


-- 创建销售部门schema并授权所有表权限，创建表并插入数据
psql -Usales_user -decommercedatahub
create schema sales authorization sales_user;
CREATE TABLE sales.sales_orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    total_amount NUMERIC(10, 2)
);
insert into sales.sales_orders values (1, 101, current_date, 10000);


-- 创建库存部门schema并授权所有表权限，创建表并插入数据
psql -Uinventory_user -decommercedatahub
create schema inventory authorization inventory_user;
CREATE TABLE inventory.product_stock (
    product_id SERIAL PRIMARY KEY,
    product_name VARCHAR(255),
    stock_quantity INT
);
insert into inventory.product_stock values (1, 'PROD1', 12345);


-- 创建市场部门schema并授权所有表权限，创建表并插入数据
psql -Umarketing_user -decommercedatahub
create schema marketing authorization marketing_user;
CREATE TABLE marketing.campaigns (
    campaign_id SERIAL PRIMARY KEY,
    campaign_name VARCHAR(255),
    start_date TIMESTAMP,
    end_date TIMESTAMP
);
insert into marketing.campaigns values (1, 'CN1', current_date, current_date);


-- 授权合作伙伴查看所有表的权限
psql -Upostgre -decommercedatahub
grant usage on schema sales, inventory, marketing to partner_user;
grant select on all tables in schema sales, inventory, marketing to partner_user;

-- 创建销售部门用户
CREATE USER sales_user IDENTIFIED BY sales_pwd default tablespace quota unlimited on users;
-- 创建库存部门用户
CREATE USER inventory_user IDENTIFIED BY inventory_pwd default tablespace quota unlimited on users;
-- 创建市场部门用户
CREATE USER marketing_user IDENTIFIED BY marketing_pwd default tablespace quota unlimited on users;
-- 创建合作用户
CREATE USER partner_user IDENTIFIED BY partner_pwd;


-- 授权用户系统权限
grant connect, resource to sales_user, inventory_user, marketing_user;
grant connect to partner_user;


-- Oracle的schema与用户是相关联的，所以创建用户时已经定义了schema


-- 创建销售部门表并插入数据
sqlplus sales_user/sales_pwd
CREATE TABLE sales_user.sales_orders (
    order_id number(10) PRIMARY KEY,
    customer_id INT,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    total_amount NUMERIC(10, 2)
);
insert into sales_user.sales_orders values (1, 101, current_date, 10000);
commit;


-- 创建库存部门表并插入数据
sqlplus inventory_user/inventory_pwd
CREATE TABLE inventory_user.product_stock (
    product_id number(10) PRIMARY KEY,
    product_name VARCHAR(255),
    stock_quantity INT
);
insert into inventory_user.product_stock values (1, 'PROD1', 12345);
commit;


-- 创建市场部门表并插入数据
sqlplus marketing_user/marketing_pwd
CREATE TABLE marketing_user.campaigns (
    campaign_id number(10) PRIMARY KEY,
    campaign_name VARCHAR(255),
    start_date TIMESTAMP,
    end_date TIMESTAMP
);
insert into marketing_user.campaigns values (1, 'CN1', current_date, current_date);
commit;


-- 授权合作伙伴查看所有表的权限
grant select on sales_user.sales_orders to partner_user;
grant select on inventory_user.product_stock  to partner_user;
grant select on marketing_user.campaigns to partner_user;

-- 创建销售部门用户
create user 'sales_user'@'%' identified by 'sales_pwd';
-- 创建库存部门用户
create user 'inventory_user'@'%' identified by 'inventory_pwd';
-- 创建市场部门用户
create user 'marketing_user'@'%' identified by 'marketing_pwd';
-- 创建合作伙伴用户
create user 'partner_user'@'%' identified by 'partner_pwd';


-- 创建数据库（等同于schema）
create database sales;
create database inventory;
create database marketing;


-- 授权用户拥有业务数据库访问和创建权限
grant all privileges on sales.* to 'sales_user'@'%';
grant all privileges on inventory.* to 'inventory_user'@'%';
grant all privileges on marketing .* to 'marketing_user'@'%';


-- 创建销售部门表并插入数据
mysql -usales_user -psales_pwd -Dsales
CREATE TABLE sales.sales_orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    total_amount DECIMAL(10, 2)
);
insert into sales.sales_orders values (1, 101, now(), 10000);


-- 创建库存部门表并插入数据
mysql -uinventory_user -pinventory_pwd -Dinventory
CREATE TABLE inventory.product_stock (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(255),
    stock_quantity INT
);
insert into inventory.product_stock values (1, 'PROD1', 12345);


-- 创建市场部门表并插入数据
mysql -umarketing_user -pmarketing_pwd -Dmarketing
CREATE TABLE marketing.campaigns (
    campaign_id INT AUTO_INCREMENT PRIMARY KEY,
    campaign_name VARCHAR(255),
    start_date TIMESTAMP,
    end_date TIMESTAMP
);
insert into marketing.campaigns values (1, 'CN1', current_date, current_date);


-- 授权合作伙伴查看所有表的权限
grant select on sales.* to 'partner_user'@'%';
grant select on inventory.* to 'partner_user'@'%';
grant select on marketing.* to 'partner_user'@'%';