46 lines
1.5 KiB
Transact-SQL
46 lines
1.5 KiB
Transact-SQL
-- 初始化
|
||
USE Homework250403;
|
||
Go
|
||
CREATE SCHEMA Q6;
|
||
Go
|
||
CREATE TABLE Q6.学生(
|
||
学号 CHAR(10) PRIMARY KEY,
|
||
姓名 NVARCHAR(20) NOT NULL,
|
||
年龄 INT,
|
||
性别 CHAR(1),
|
||
家庭住址 NVARCHAR(100),
|
||
班级号 CHAR(10) NOT NULL
|
||
);
|
||
Go
|
||
CREATE TABLE Q6.班级(
|
||
班级号 CHAR(10) PRIMARY KEY,
|
||
班级名 NVARCHAR(20) NOT NULL,
|
||
班主任 NVARCHAR(20),
|
||
班长 NVARCHAR(20)
|
||
);
|
||
Go
|
||
CREATE LOGIN U1 WITH PASSWORD = 'Password123!';
|
||
CREATE LOGIN U2 WITH PASSWORD = 'Password123!';
|
||
CREATE USER U1 FOR LOGIN U1;
|
||
CREATE USER U2 FOR LOGIN U2;
|
||
CREATE ROLE R1;
|
||
Go
|
||
|
||
-- 1. 授予用户U1对两个表的所有权限，并可给其他用户授权
|
||
GRANT SELECT, INSERT, UPDATE, DELETE ON Q6.学生 TO U1 WITH GRANT OPTION;
|
||
GRANT SELECT, INSERT, UPDATE, DELETE ON Q6.班级 TO U1 WITH GRANT OPTION;
|
||
Go
|
||
-- 2. 授予用户U2对学生表具有查看权限，对家庭住址具有更新权限
|
||
GRANT SELECT ON Q6.学生 TO U2;
|
||
GRANT UPDATE (家庭住址) ON Q6.学生 TO U2;
|
||
Go
|
||
-- 3. 将对班级表查看权限授予所有用户
|
||
GRANT SELECT ON Q6.班级 TO PUBLIC;
|
||
Go
|
||
-- 4. 将对学生表的查询、更新权限授予角色R1
|
||
GRANT SELECT, UPDATE ON Q6.学生 TO R1;
|
||
Go
|
||
-- 5. 将角色R1授予用户U1，并且U1可继续授权给其他角色
|
||
ALTER ROLE R1 ADD MEMBER U1;
|
||
GRANT CONTROL ON ROLE::R1 TO U1;
|
||
Go |