数据库SQL Server 中如何构建受限制的数据库

1. 了解受限制的数据库

一个数据库中可以存储很多种类型的数据，不同的数据可能有不同的敏感级别。为了保护这些数据，SQL Server 可以创建受限制的数据库。通过为不同的用户创建不同的登录名和用户权限，可以限制用户的访问、修改、或者删除数据库中的某些数据。

1.1 用户登录名和用户

在 SQL Server 中，用户登录名和用户是两个不同的概念。用户登录名用于启用 SQL Server 中的登录，而用户则用于授权，用户的权限通过授权其对数据库对象的访问来确定。

为了创建一个新的登录名，可以使用以下 SQL 语句：

CREATE LOGIN login_name
WITH PASSWORD = 'password';

一个登录名可以有多个用户，不同的用户可以拥有不同的权限，也可以属于不同的数据库用户组。可以使用以下 SQL 语句来创建一个新的用户：

USE database_name;
CREATE USER user_name
FOR LOGIN login_name;

1.2 授权和权限

在 SQL Server 中，授权与权限是不同的。授权是为用户设置特定权限，将授权分配给用户时，可以为用户指定访问的数据库和对象。

以下 SQL 语句可以为用户授予对表的 SELECT 权限：

USE database_name;
GRANT SELECT ON table_name TO user_name;

可以撤销授权，使用 REVOKE：

USE database_name;
REVOKE SELECT ON table_name FROM user_name;

2. 创建受限制的数据库

现在，我们将创建一个新的数据库，并创建一些用户和用户权限。

2.1 创建数据库

使用以下 SQL 语句可以创建一个新的数据库：

CREATE DATABASE my_database;

现在，我们可以使用以下 SQL 语句切换到新创建的数据库，以便在其中创建表和用户：

USE my_database;

2.2 创建表

现在，我们将创建一个名为“users”的新表。首先，我们需要定义表的结构，以便可以向其中添加数据。以下 SQL 语句可以定义一个包含“id”、“name”和“email”列的表：

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL
);

现在，我们可以使用 INSERT INTO 语句将数据插入表中：

INSERT INTO users (id, name, email)
VALUES (1, 'John Doe', 'john@example.com'),
       (2, 'Jane Doe', 'jane@example.com'),
       (3, 'Bob Smith', 'bob@example.com');

执行这些 SQL 语句后，我们可以使用 SELECT 语句验证表中是否存在数据：

SELECT * FROM users;

2.3 创建用户和授权

现在，我们将创建一些用户，并授予它们对表的不同访问权限。

首先，我们将创建一个名为“readonly_user”的用户，该用户仅能读取表中的数据：

CREATE LOGIN readonly_user
WITH PASSWORD = 'password';
CREATE USER readonly_user
FOR LOGIN readonly_user;
GRANT SELECT ON users TO readonly_user;

现在，我们将创建一个名为“readwrite_user”的用户，该用户可以读取和写入表的数据：

CREATE LOGIN readwrite_user
WITH PASSWORD = 'password';
CREATE USER readwrite_user
FOR LOGIN readwrite_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON users TO readwrite_user;

现在，我们将创建一个名为“admin_user”的用户，该用户可以读取、写入和删除表的数据：

CREATE LOGIN admin_user
WITH PASSWORD = 'password';
CREATE USER admin_user
FOR LOGIN admin_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON users TO admin_user;

3. 测试受限制的数据库

现在，我们可以使用三个不同的用户登录 SQL Server，以验证我们为它们设置的访问权限是否生效。

3.1 只读用户

我们将使用只读用户登录 SQL Server，并尝试向表中插入一条新的记录：

USE my_database;
INSERT INTO users (id, name, email)
VALUES (4, 'Tom Smith', 'tom@example.com');

但是，当我们执行此语句时，将收到以下错误消息：

Msg 229, Level 14, State 5, Line 1

The INSERT permission was denied on the object 'users', database 'my_database', schema 'dbo'.

这表明，我们的只读用户没有足够的权限向表中添加新记录。

3.2 读写用户

我们将使用读写用户登录 SQL Server，并尝试向表中插入一条新的记录：

USE my_database;
INSERT INTO users (id, name, email)
VALUES (4, 'Tom Smith', 'tom@example.com');

这次，当我们执行此语句时，它将成功执行，因为我们的读写用户拥有足够的权限向表中添加新记录。

我们还可以使用以下语句从表中删除记录：

USE my_database;
DELETE FROM users WHERE id = 4;

3.3 管理员用户

最后，我们将使用管理员用户登录 SQL Server，并尝试向表中插入一条新的记录：

USE my_database;
INSERT INTO users (id, name, email)
VALUES (4, 'Tom Smith', 'tom@example.com');

这次，当我们执行此语句时，它将成功执行，因为我们的管理员用户拥有向表中添加新记录的权限。

我们还可以使用以下语句从表中删除记录：

USE my_database;
DELETE FROM users WHERE id = 4;

4. 总结

通过为不同的用户创建不同的登录名和用户权限，可以限制用户的访问、修改、或者删除数据库中的某些数据。在 SQL Server 中，用户登录名和用户是不同的概念，用户权限通过授权其对数据库对象的访问来确定。要创建受限制的数据库，可以使用 CREATE LOGIN、CREATE USER、GRANT 和 REVOKE 等 SQL 语句。