关于 oracle schema 级别的权限问题 -m6米乐安卓版下载

原创 jiekexu all china database union 2023-06-01

1272

众所周知，在 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 oracle
;;
"user_b" )
granttorole user_b oracle
;;
"user_c" )
granttorole user_c oracle
;;
"all" )
granttorole user_a oracle
granttorole user_b oracle
granttorole user_c oracle
;;
esac
-- rm -f ${basedir}/granttorole_*.sql
-- crontab
-- 0 14 * * * /home/oracle/tmp/granttorole.sh all

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

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 2023
version 23.2.0.0.0
米乐app官网下载 copyright (c) 1982, 2023, oracle.  all rights reserved.
connected to:
oracle database 23c free, release 23.0.0.0.0 - developer-release
version 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_data
temporary 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 a20
set 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;

图片.png

接下来我们登录到业务用户 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 后面可以跟多个值了，以前只能跟一个。
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

图片.png

授权 “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;

图片.png

新需求

现在又遇到一个新的需求，怎么个情况呢，且听我慢慢道来，新来一业务要求可以仅读取 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
米乐app官网下载 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 的表

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

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

欢迎关注我的公众号【jiekexu dba之路】，第一时间一起学习新知识！
———————————————————————————
公众号：jiekexu dba之路
csdn ：https://blog.csdn.net/jiekexu
墨天轮：https://www.modb.pro/u/4347
腾讯云：https://cloud.tencent.com/developer/user/5645107
———————————————————————————
图片.png