Oracle如何创建只读用户

2021-06-23 程序猿集锦

现在有一个用户A，想把这个用户A下面的所有的表查询权限都赋予用户X，该怎么做？同样的问题，如果有4个用户A、B、C、D，需要把这4个用户下面所有的表的只读权限赋予用户X、Y、Z三个用户，我们该怎么做？

在MySQL5.7中，可以创建用户X，然后使用grant select on A.* to X这样的方式分别把每一个库下面所有的表都给用户X授权就可以实现。在MySQL8.0版本中，有了角色role的概念，我们只要把ABCD四个库下面所有表的只读权限都赋予一个只读角色，然后把这个只读的角色再赋予XYZ三个用户就可以了。

但是在Oracle中，没有基于类似于MySQL中的schema级别的授权，只能针对每一个用户下面每一个表分别授权。下面有2种方式可以实现Oracle只读用户授权。

直接授予查询权限

直接授权的方式就是把某一个用户下面的所有的数据库对象的对应权限，一一授权给指定的另外一个用户。下面我们演示一下具体的实现过程，把用户mydb下面的所有表的只读权限授权给my_ro_user用户。

以下操作都是使用system用户来执行的。

创建一个只读的用my_ro_user

create user my_ro_user identified by "my_ro_user";

给只读的用户授予连接权限

grant create session to my_ro_user;

把mydb下面的userinfo表的只读权限授权给my_ro_user用户

grant select on mydb.userinfo to my_ro_user;

上面只是把mydb下面的一个表授权给了my_ro_user用户，如果我们需要把mydb用户下面所有的表都授权给my_ro_user该如何做呢？在Oracle中没有grant select on mydb.* to my_ro_user这样功能，只能指定每一个数据库对象的名称。可以参考如下的方式，可以循环地把mydb下面的每一张表的查询权限都授权给my_ro_user用户。

BEGIN
    -- FOR t IN (SELECT * FROM user_tables) 
    FOR t in (SELECT owner, object_name, object_type FROM all_objects WHERE owner = UPPER('mydb') AND object_type IN ('TABLE','VIEW','PROCEDURE','FUNCTION','PACKAGE'))
    LOOP
        -- EXECUTE IMMEDIATE 'GRANT SELECT ON ' || t.table_name || ' TO my_ro_user';
        IF t.object_type IN ('TABLE','VIEW') THEN
          EXECUTE IMMEDIATE 'GRANT SELECT ON '||t.owner||'.'||t.object_name||' TO my_ro_user';
        -- ELSIF t.object_type IN ('PROCEDURE','FUNCTION','PACKAGE') THEN
          -- EXECUTE IMMEDIATE 'GRANT DEBUG ON mydb.'||t.object_name||' TO my_ro_user';
        END IF;
    END LOOP;
END;

完成上面的授权操作之后，我们使用新的用户my_ro_user登录Oracle数据库之后，就可以查询到mydb用户下面的表中的数据了。

**注意：**此时在my_ro_user用户下面执行查询语句的时候，需要指定mydb这个用户名称，在所有的表前面都需要增加上mydb.这个字符串才可以查询到对应的表，不能直接执行select * from userinfo;，需要执行select * from mydb.userinfo;才可以。如果想写mydb.这个字符串就可以查询到对应的表，我们需要为my_ro_user赋予创建同义词的权限，然后再my_ro_user下面创建同义词才可以省略掉用户名称。

用system用户给my_ro_user用户授予创建同义词的权限。

grant create synonym to my_ro_user;

用my_ro_user登录Oracle然后执行如下语句，将SQL语句的结果复制出来执行即可批量创建出所有的同义词。

select 
	'create or replace synonym my_ro_user.' || object_name|| ' for ' || owner || '.' || object_name|| ';' ,
	'drop synonym my_ro_user.' || object_name||  ';' 
from dba_objects
where owner in upper('mydb')
and object_type='TABLE';

执行完上述SQL的结果集之后，my_ro_user在查询mydb用户下面的表时，就不用在指定mydb.字符串了。

通过角色来授予查询权限

创建一个角色，然后把对某个数据库下面的表的权限授予这个角色，然后再把这个角色赋予某一个用户。这样用户就可以以某种角色去访问某一个数据库下面的表了。

具体实现方式参考如下：

创建只读角色

create role my_readonly_role;

将数据库下面所有的表授权给角色

/*mydb是Oracle的数据库名称，也是用户名称，userinfo是mydb下面的表，my_readonly_role是一个角色名称*/
grant select on mydb.userinfo to my_readonly_role;

上面的方式比较麻烦，因为要对每一个表单独对角色进行授权。下面使用一个比较快的方式来解决这个授权的过程。

BEGIN
    -- FOR t IN (SELECT * FROM user_tables) 
    FOR t in (SELECT owner, object_name, object_type FROM all_objects WHERE owner = UPPER('sunny_cmd') AND object_type IN ('TABLE','VIEW','PROCEDURE','FUNCTION','PACKAGE'))
    LOOP
        -- EXECUTE IMMEDIATE 'GRANT SELECT ON ' || t.table_name || ' TO my_readonly_role';
        IF t.object_type IN ('TABLE','VIEW') THEN
          EXECUTE IMMEDIATE 'GRANT SELECT ON '||t.owner||'.'||t.object_name||' TO my_readonly_role';
        -- ELSIF t.object_type IN ('PROCEDURE','FUNCTION','PACKAGE') THEN
          -- EXECUTE IMMEDIATE 'GRANT DEBUG ON mydb.'||t.object_name||' TO my_readonly_role';
        END IF;
    END LOOP;
END;

上面的SQL语句块中，使用到了查询某一个用户下面所有的数据库对象的查询方式，如果要查询某一个用户下面所有的数据库对象，可以通过如下的SQL语句：

/*查询mydb用户下面所有的数据库对象，包括表、视图、存储过程、函数、包。*/
SELECT * FROM all_objects WHERE owner = UPPER('mydb') AND object_type IN ('TABLE','VIEW','PROCEDURE','FUNCTION','PACKAGE')

创建只读用户

create user my_ro_user identified by "my_ro_user";

给新创建的用户授权登录的权限

grant create session to my_ro_user;

把只读角色授予新创建的只读用户

grant my_readonly_role to my_ro_user;

同义词的使用

当我们在只读用户下面，访问其他用户下面的只读权限的表时，如果不想在SQL语句中指定其他用户名称，那么就需要在当前只读的用户下面创建一个个表的同义词，让这个同义词自动指向其他用户下面对应的表，这样在只读用户下面就可以直接使用表名称，不用在每一个表名称前面增加用户名称了。

前提是当前只读的用户，对另外一个用户下的表，是有只读的权限的，然后在当前只读用户下创建同义词才可用，否则只要同义词没有对应的访问权限，通过同义词是不能访问到对应的数据表的。