关于 Oracle Schema 级别的权限问题

最新推荐文章于 2025-05-26 07:01:23 发布

JiekeXu

最新推荐文章于 2025-05-26 07:01:23 发布

阅读量2.7k

点赞数 1

CC 4.0 BY-SA版权

文章标签： oracle 数据库

本文链接：https://blog.youkuaiyun.com/JiekeXu/article/details/130979741

作者 | JiekeXu

来源 |公众号 JiekeXu DBA之路（ID: JiekeXu_IT）

如需转载请联系授权 | (个人微信 ID：JiekeXu_DBA)

大家好，我是 JiekeXu,很高兴又和大家见面了,今天和大家一起来看看关于 Oracle Schema 级别的权限问题，欢迎点击上方蓝字“JiekeXu DBA之路”关注我的公众号，标星或置顶，更多干货第一时间到达！

众所周知，在 Oracle 中，一般情况下 B 用户如果需要访问 A 用户下的表、视图、索引等对象，必须要加前缀即 A.object_name 才有权限访问。所以在生产环境中，一般也是 A 用户为业务用户，具有增删改查等业务相关的权限，B 用户一般给予查询 A 用户下表等对象的权限，所以我们可以加一层 Role 角色的权限（我们暂定为 R_SELECT_A），给 B 用户一个特定的角色 R_SELECT_A,然后定期定时通过脚本化的方式对这个角色授权。我们可以这么做：

创建业务用户和业务表空间，赋予相应的权限

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.15.0.0.0


CREATE TABLESPACE A_DATA DATAFILE '+DATA' SIZE 30G UNIFORM SIZE 1M;
CREATE USER A  IDENTIFIED BY oracle 
DEFAULT TABLESPACE A_DATA  
QUOTA unlimited ON A_DATA
TEMPORARY TABLESPACE temp;


--授权
GRANT "CONNECT" TO "A";
GRANT "RESOURCE" TO "A";
GRANT "PLUSTRACE" TO "A";
GRANT DEBUG CONNECT SESSION TO "A";
GRANT CREATE SEQUENCE TO "A";
GRANT CREATE VIEW TO "A";
GRANT CREATE SYNONYM TO "A";

创建 B 用户及角色

create user B identified by oracle;
create role R_SELECT_A;
grant connect,R_SELECT_A to B;


--然后我们登录 A 用户，将 A 用户下的表对象只读权限给与角色 R_SELECT_A，
--将执行结果再次执行一遍。


SQL> select 'grant select on '||table_name||' to r_select_A;' 
from user_tables where rownum<=3;


'GRANTSELECTON'||TABLE_NAME||'TOR_SELECT_A;'
---------------------------------------------------------------------
grant select on DEPT to r_select_A;
grant select on EMP to r_select_A;
grant select on BONUS to r_select_A;

那么可能有人会问，对于已经存在的表可以这么做，但是如果有新增业务新增表，那么新增表是没有 R_SELECT_A 这个角色的，对于 B 用户而言也是查不到的。那么我们该怎么做呢？

解决办法

两个办法：第一个，如果业务部门有 A 用户使用权限，在建表时直接补一句 grant 授权，当然如果忘记或者事后则需登录 A 用户，可以直接将新增表的只读权限给角色 R_SELECT_A；

create T_NEWTABLE(id int,name varchar2(20));
grant select on T_NEWTABLE to R_SELECT_A；

对于新业务也有可能建表比较多，或者没有合理的开发规范约束，经常忘记给角色赋权，这样拥有 B 用户权限的人员就查不到了，那么就会来找 DBA，通常我们的做法则是统一赋权，对 A 用户下的所有表批量授权，或者通过定时任务来搞定。

vim grantToRole.sh


#!/bin/bash


if [ $# != 1 ] ; then
        echo "USAGE: $0 user_a|user_b|user_c|all"
        exit 1;
fi


task=$1
source /home/oracle/.bash_profile


#Write Logfile
time=`date +%Y%m%d%H%M%S`
basedir=/home/oracle/tmp
logfile=/home/oracle/tmp/grantToRole_${time}.log


#Write and Execute SQL Scripts
grantToRole()
{
echo "==============Start to execute batch sql at `date`================" >> ${logfile}
username=$1
password=$2
sqlplus -S /nolog >> ${logfile} << EOF
conn ${username}/${password}
spool ${basedir}/grantToRole_${username}_${time}.sql
set head off
set feedback off
set timing off
set verify off
set pagesize 0
set linesize 200
select 'grant select on '||table_name||' to r_select_${username};' from user_tables;
select 'grant select on '||sequence_name||' to r_select_${username};' from user_sequences;
select 'grant insert,delete,update on '||table_name||' to r_update_${username};' from user_tables;
spool off
@${basedir}/grantToRole_${username}_${time}.sql
EOF
echo "==============End up of executing batch sql at `date`===============" >> ${logfile}
}


############################################################
#             Main                                         #
############################################################


case "${task}" in


"user_a" )
grantToRole user_a passwd_oracle
;;


"user_b" )
grantToRole user_b passwd_oracle
;;


"user_c" )
grantToRole user_c passwd_oracle
;;


"all" )
grantToRole user_a passwd_oracle
grantToRole user_b passwd_oracle
grantToRole user_c passwd_oracle
;;
esac


-- rm -f ${basedir}/grantToRole_*.sql
-- crontab
-- 0 14 * * * /home/oracle/tmp/grantToRole.sh all

通过此脚本可以对单个用户或者所有用户赋权，注意我这里包括查询表及序列以及增删改的权限都有，可按照个人实际情况赋权。

在 Oracle 23c 中的解决办法

当然这样还是稍微有点麻烦，对于新增的表要再次单独授权,不是很方便，于是乎在上个月 Oracle 发布的 23c 中，有一个新特性就是 schema 级别授权，什么意思呢？就是你可以给 B 用户授予查询 A 用户 schema 级别的权限，不再需要单个表、单个对象得授权，对于 23c 的环境你只需三步快速体验 Oracle 23c 开发版。

grant select any table on schema USER_A to USER_B;

示例如下：

让我们一起来看看。

SQL*Plus: Release 23.0.0.0.0 - Developer-Release on Wed May 31 15:55:43 2023Version 23.2.0.0.0Copyright (c) 1982, 2023, Oracle. All rights reserved.Connected to:Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-ReleaseVersion 23.2.0.0.0--创建业务用户并授权CREATE TABLESPACE JIEKEXU_DATA DATAFILE '/opt/oracle/oradata/FREE/FREEPDB1/jiekexu_data01.dbf' SIZE 5G UNIFORM SIZE 1M;CREATE USER jiekexu IDENTIFIED BY Oracle_21c DEFAULT TABLESPACE JIEKEXU_DATA QUOTA unlimited ON JIEKEXU_DATATEMPORARY TABLESPACE TEMP;GRANT "CONNECT","RESOURCE" to jiekexu;--创建查询用户，给予登录权限create user jiekexu_sel IDENTIFIED BY jiekexu_sel; grant create session to jiekexu_sel; set line 240 col profile for a20set pages 999 col username for a25 col ACCOUNT_STATUS for a18 select USERNAME,ACCOUNT_STATUS,CREATED,PROFILE,PASSWORD_VERSIONS,DEFAULT_TABLESPACE from dba_users where account_status='OPEN' order by CREATED asc;

接下来我们登录到业务用户 JIEKEXU，但由于自从 21c 开始强制创建容器数据库，则是 PDB 环境，我们登录 PDB 下需要配置 tns 别名进行登录。

cd $ORACLE_HOME/network/admin


[oracle@jiekexu admin]$ more tnsnames.ora 
# tnsnames.ora Network Configuration File: /opt/oracle/product/23c/dbhomeFree/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.


FREE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = jiekexu)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = FREE)
    )
  )


LISTENER_FREE =
  (ADDRESS = (PROTOCOL = TCP)(HOST = jiekexu)(PORT = 1521))


--写入如下信息
[oracle@jiekexu admin]$ vim  tnsnames.ora
FREEPDB1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = jiekexu)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = FREEPDB1)
    )
  )

然后我们登录进入业务用户创建表并插入数据。

conn jiekexu/Oracle_21c@FREEPDB1
create table test(id int,name varchar2(20));
insert into test values(1,'jiekexu'),(2,'freepdb');  
--注意：如上的新特性，在 values 后面可以跟多个值了，以前 21c 之前只能跟一个。
commit;

接下来我们用 SYS 授权 JIEKEXU_SEL 用户 schema 级别的查询权限。然后我们连接查询用户则是可以查到业务用户下的表的数据。

SQL> alter session set container=FREEPDB1;


Session altered.


SQL> grant select any table on schema jiekexu to jiekexu_sel;


Grant succeeded.


SQL> conn jiekexu_sel/jiekexu_sel@FREEPDB1
Connected.
SQL> select * from jiekexu.test;


        ID NAME
---------- --------------------
         1 jiekexu
         2 freepdb

接下来我们模拟业务新增情况，在业务用户下新建表，再去查询用户下看是否可以查询到数据。

SQL> create table t_new(id number);
SQL> insert into t_new values(1),(2),(3),(4);
4 rows created.
SQL> commit;


--切到查询用户，则可以查询到新建的表 t_new 表的数据。
SQL> conn jiekexu_sel/jiekexu_sel@FREEPDB1
Connected.
SQL> select * from jiekexu.test;


        ID NAME
---------- --------------------
         1 jiekexu
         2 freepdb


SQL> select * from jiekexu.t_new;


        ID
----------
         1
         2
         3
         4


SQL> show user;
USER is "JIEKEXU_SEL"
SQL> col OWNER for a15
SQL> col TABLE_NAME for a10
SQL> select owner,table_name from all_tables where owner not in ('SYS','MDSYS','SYSTEM','XDB','CTXSYS');


OWNER           TABLE_NAME
--------------- ----------
JIEKEXU         TEST
JIEKEXU         T_NEW

授权 “select any table on schema” 后，JIEKEXU_SEL 用户除了原先授予的 “CREAT SESSION” 的系统权限外，没有被授予任何其他系统权限和系统角色。

根据上月初 2023 嘉年华上杨长老的演讲介绍，SCHEMA 级授权是通过对象级的继承权限获得，此时，被授权的用户可以看到原始授权用户下所有的表以及数据。授权用户通过 ora_check_sys_privilege 函数获取当前是否具备访问权限，即若返回判断结果为 1，则用户有查看权限，返回结果为 0 则无权限。

SQL> select *  from dba_role_privs where grantee='JIEKEXU_SEL';


no rows selected


SQL> select  *  from dba_sys_privs where  grantee='JIEKEXU_SEL';


GRANTEE     PRIVILEGE                                ADM COM INH
----------- ---------------------------------------- --- --- ---
JIEKEXU_SEL CREATE SESSION                           NO  NO  NO


SQL> conn / as sysdba
SQL> set long 99999 pages 9999 LONGCHUNKSIZE 99999
SQL> select dbms_metadata.get_ddl('VIEW','ALL_TABLES','SYS') from dual;

来了一个新需求

现在又遇到一个新的需求，怎么个情况呢，且听我慢慢道来，新来一业务要求可以仅读取 PROD 业务用户下的表，但是所有程序 JDBC 里配置的都是 READONLY 用户，但是他的程序代码里 SQL 写的全都没有加前缀，类似于“select count(*) from test" 这样访问数据库。这样就 100% 报错了，因为 READONLY 用户是没有 test 表的，业务方又不想改代码中涉及到的表加前缀，那么只能通过创建一个触发器，当登录到 READONLY 用户时则将会立马切到业务用户 PROD，然后再给 READONLY 用户查询业务用户 PROD 的角色 R_SELECT_PROD，则就可以实现登录到 READONLY 用户也不用写前缀便可以查询到 PROD 业务用户数据了。

$ sqlplus / as sysdba 


SQL*Plus: Release 19.0.0.0.0 - Production on Wed May 31 16:13:59 2023
Version 19.15.0.0.0


Copyright (c) 1982, 2022, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.15.0.0.0


SQL> create user READONLY identified by READONLY;
SQL> grant connect,R_SELECT_PROD to READONLY;


SQL> create or replace trigger tri_default_schema_prod
after logon on READONLY.schema
begin
execute immediate 'alter session set current_schema=PROD';
end;
/


--登录只读用户查询业务用户下的表数据
CONN READONLY/READONLY
SELECT COUNT(*) FROM PROD.TEST;
SELECT COUNT(*) FROM TEST;       --均是访问 PROD.TEST 的表

我的临时解决方案是这样的，不知小伙伴们还有其他的方法吗？欢迎添加我微信一起交流讨论。另外:近期新建一个微信交流群，现 150 多人了，最终需要 200 人左右，也算比较活跃，会在群中不定期举行抽奖、红包福利，限时免费开放名额，如有需要的可添加我个人微信【JiekeXu_DBA】，备注：加群。

全文完，希望可以帮到正在阅读的你，如果觉得此文对你有帮助，可以分享给你身边的朋友，同事，你关心谁就分享给谁，一起学习共同进步~~~

欢迎关注我的公众号【JiekeXu DBA之路】，第一时间一起学习新知识！

———————————————————————————
公众号：JiekeXu DBA之路
优快云：https://blog.youkuaiyun.com/JiekeXu
墨天轮：https://www.modb.pro/u/4347
腾讯云：https://cloud.tencent.com/developer/user/5645107
———————————————————————————

分享几个数据库备份脚本

Oracle 表碎片检查及整理方案

OGG|Oracle GoldenGate 基础2022 年公众号历史文章合集整理

Oracle 19c RAC 遇到的几个问题

OGG|Oracle 数据迁移后比对一致性

关于 Oracle Schema 级别的权限问题

创建业务用户和业务表空间，赋予相应的权限

解决办法

在 Oracle 23c 中的解决办法

示例如下：

来了一个新需求

分享几个数据库备份脚本

Oracle 表碎片检查及整理方案

Oracle 19c RAC 遇到的几个问题

OGG|Oracle GoldenGate 微服务架构

Oracle 查询表空间使用率超慢问题一则

Oracle 11g升级到19c需要关注的几个问题

国产数据库|TiDB 5.4 单机快速安装初体验

Oracle ADG 备库停启维护流程及增量恢复