如何创建数据库私有用户和私有表？

场景介绍

在业务场景中，普通用户创建的表，系统管理员dbadmin默认是有访问权限，并未完全私有。而在三权分立开启下的业务场景中，管理员dbadmin对普通用户的表没有访问权限，同时也没有控制权限（DROP、ALTER、TRUNCATE）。

若当前业务中需要创建一个私有用户和私有表（即私有用户创建的表），私有表只有私有用户本身可以访问，系统管理员dbadmin和其他普通用户均无权访问（进行INSERT、DELETE、UPDATE、SELECT、COPY操作），但同时需要满足：未经私有用户授权的情况下，可允许系统管理员dbadmin执行DROP/ALTER/TRUNCATE操作。那么可以通过创建INDEPENDENT属性的用户（私有用户）实现。

图1 私有用户

实现原理

通过创建INDEPENDENT属性的用户来实现。

INDEPENDENT | NOINDEPENDENT 定义私有、独立的角色。具有INDEPENDENT属性的角色，管理员对其进行的控制、访问的权限被分离，具体规则如下：

• 未经INDEPENDENT角色授权，管理员无权对其表对象进行增、删、查、改、拷贝、授权操作。
• 未经INDEPENDENT角色授权，管理员无权修改INDEPENDENT角色的继承关系。
• 管理员无权修改INDEPENDENT角色的表对象的属主。
• 管理员无权修改INDEPENDENT角色的数据库口令，INDEPENDENT角色需管理好自身口令，口令丢失无法重置。
• 管理员属性用户不允许定义修改为INDEPENDENT属性。

操作步骤

1. 使用系统管理员dbadmin连接DWS数据库。
2. 执行以下SQL语句创建私有用户u1。

   1	CREATE USER u1 WITH INDEPENDENT IDENTIFIED BY 'password';

3. 切换到u1用户，创建测试表test，并插入数据。

   1 2 3

   CREATE TABLE test (id INT, name VARCHAR(20)); INSERT INTO test VALUES (1, 'joe'); INSERT INTO test VALUES (2, 'jim');

4. 切换到dbadmin用户，并执行以下SQL语句验证dbadmin用户是否可以访问私有用户u1创建的私有表test。

   1	SELECT * FROM u1.test;

   查询结果提示dbadmin无权访问，即私有用户和私有表创建成功。

   

5. dbadmin用户执行DROP语句，可成功删除test表。

   1	DROP TABLE u1.test;