利用oracle系统触发器实现用户权限的集中管理

最新推荐文章于 2023-09-05 15:23:37 发布

andyguan01_2

最新推荐文章于 2023-09-05 15:23:37 发布

阅读量1k

点赞数 2

分类专栏： oracle 文章标签： oracle 系统触发器权限

本文链接：https://blog.csdn.net/andyguan01_2/article/details/86305844

版权

oracle 专栏收录该内容

19 篇文章 1 订阅

订阅专栏

【背景】

有多人要使用oracle数据库，每个人属于不同的小组，都需要建表和存储过程。为便于数据集中管理，给每个人创建了单独的个人账号，每个组有一个公共的账号。权限管理的原则是：

1、每个人只能使用自己的个人账号，组账号由DBA统一管理，不开放给个人使用；
2、个人账号可以建表、视图、存储过程、函数，但只能创建在个人归属的组账号下，例如个人账号为ZHANGSAN，组账号为GROUP1，在ZHANGSAN用户下创建表的语句为：CREATE TABLE GROUP1.TAB_NAME ......
3、个人创建的表、视图、存储过程、函数，创建者具有对象的ALL权限，组内其他用户具有查询或查看权限但不能新增、修改和删除，其他组用户不具有任何权限。

【设计思路】

考虑使用系统触发器来实现（用具有DBA权限的用户开发）。

1、用户创建对象时，调用“before ddl on database”系统触发器，判断对象的所有者是否为登录用户对应的组用户，例如用户ZHANGSAN归属于组GROUP1，创建表的SQL必须为CREATE TABLE GROUP1.TAB_NAME ......，而不能写CREATE TABLE GROUP2.TAB_NAME ......；
2、用户创建对象后，调用“after create on database”系统触发器，生成授权语句写入操作表，同时记录对象创建日志表；
3、写一个job执行授权语句操作表中的SQL，job的状态为DISABLE，每当有数据写入授权语句操作表的时候，调用表级触发器，将此job的状态置为ENABLE；
说明：因为在触发器里面不能执行DDL授权语句，所以考虑先把授权语句放入一张表，然后用job来读取表中语句处理。job状态由DISABLE改为ENABLE后，会执行一次job，执行完后job状态又变回DISABLE。
4、用户修改/删除对象时，也调用“before ddl on database”系统触发器，判断要操作的对象是否为当前登录用户所创建，用户只能修改/删除自己创建的对象，不能对其他对象进行此类操作。

【脚本】

一、用户权限：

个人用户：
create session
create any table
create any view
create any procedure
drop any table
drop any view
drop any procedure

组用户：
connect
resource

二、表结构：

1、用户权限管理-用户与组关系表USER_AUTH_LIST：

2、用户权限管理-对象创建日志表USER_AUTH_CREATETAB_LOG：

3、用户权限管理-授权语句表USER_AUTH_SQL：

三、触发器

1、用户权限管理-DDL操作前判断：
create or replace trigger admin.tri_before_ddl
/**********************************************************************************
* 名称：tri_before_ddl
* 功能：用户权限管理-DDL操作前判断
**********************************************************************************/
before ddl on database
declare
int_l_count pls_integer;
begin
-- 针对表，视图，存储过程，函数的DDL操作
if Dictionary_obj_type in ('TABLE','VIEW','PROCEDURE','FUNCTION') and ORA_SYSEVENT in ('CREATE','DROP','TRUNCATE') then
-- 判断执行DDL操作的登录用户是否为需要进行权限管理的用户
select count(*)
into int_l_count
from user_auth_list t1
where t1.user_name = ora_login_user;

if int_l_count > 0 then
-- 针对CREATE操作的判断
if ORA_SYSEVENT = 'CREATE' then
-- 判断登录用户是否有权限操作对象用户
select count(*)
into int_l_count
from user_auth_list t1
where t1.user_name = ora_login_user
and t1.grant_createtab_username = ora_dict_obj_owner;

-- 对没有权限的情况进行提示
if int_l_count = 0 then
raise_application_error(-20001,'您不能在此用户下创建对象噢，请回想一下，您是否有其他用户下的创建对象权限？');
end if;

-- 限制用户不能修改其他人创建的视图，存储过程，函数
-- 备注：注意，修改视图，存储过程，函数的DDL语句也是CREATE，例如：CREATE OR REPLACE VIEW/PROCEDURE/FUNCTION
if Dictionary_obj_type in ('VIEW','PROCEDURE','FUNCTION') then
-- 判断对象是否已存在
select count(*)
into int_l_count
from dba_objects t
where t.OWNER = ora_dict_obj_owner
and t.OBJECT_NAME = ora_dict_obj_name;

-- 对于已存在的对象，限制只有对象创建者才能修改
if int_l_count > 0 then
-- 判断对象创建者是否为登录用户
select count(*)
into int_l_count
from user_auth_createtab_log t1
where t1.owner = ora_dict_obj_owner
and t1.tab_name = ora_dict_obj_name
and t1.create_user = ora_login_user
and not exists (select 1
from user_auth_createtab_log t2
where t2.owner = t1.owner
and t2.tab_name = t1.tab_name
and t2.create_time > t1.create_time);

-- 如果对象创建者不是登录用户，进行提示
if int_l_count = 0 then
raise_application_error(-20001,'您不能修改非本人创建的对象噢');
end if;
end if;
end if;
-- 针对DROP，TRUNCATE操作的判断
elsif ORA_SYSEVENT in ('DROP','TRUNCATE') then
-- 对象只能由其创建者DROP，TRUNCATE
select count(*)
into int_l_count
from user_auth_createtab_log t1
where t1.owner = ora_dict_obj_owner
and t1.tab_name = ora_dict_obj_name
and t1.create_user = ora_login_user
and not exists (select 1
from user_auth_createtab_log t2
where t2.owner = t1.owner
and t2.tab_name = t1.tab_name
and t2.create_time > t1.create_time);

-- 如果对象创建者不是登录用户，进行提示
if int_l_count = 0 then
raise_application_error(-20001,'您不能'||ORA_SYSEVENT||'非本人创建的对象噢');
end if;
end if;
end if;
end if;
end tri_before_ddl;

2、用户权限管理-CREATE操作后授权及记录日志：
create or replace trigger admin.tri_after_create_table
/**********************************************************************************
* 名称：tri_after_create_table
* 功能：用户权限管理-CREATE操作后授权及记录日志
**********************************************************************************/
after create on database
declare
int_l_count pls_integer;
str_l_Dictionary_obj_type varchar2(100);
begin
str_l_Dictionary_obj_type := Dictionary_obj_type;

-- 针对表，视图，存储过程，函数的CREATE操作
if Dictionary_obj_type in ('TABLE', 'VIEW', 'PROCEDURE', 'FUNCTION') then
-- 判断执行CREATE操作的登录用户是否为需要进行权限管理的用户
select count(*)
into int_l_count
from user_auth_list t1
where t1.grant_createtab_username = ora_dict_obj_owner;

if int_l_count > 0 then
-- 生成授权SQL（表/视图：登录用户ALL权限，组内其他用户SELECT权限；存储过程/函数：登录用户ALL权限）
insert into admin.user_auth_sql
(sql_text)
select 'grant '|| (case when t1.user_name = ora_login_user then 'all' else 'select' end) || ' on '||ora_dict_obj_owner||'."'||ora_dict_obj_name||'" to '||t1.user_name
from user_auth_list t1
where t1.grant_createtab_username = ora_dict_obj_owner
and not (str_l_Dictionary_obj_type in ('PROCEDURE','FUNCTION') and t1.user_name <> ora_login_user);

-- 记录创建对象日志表
insert into user_auth_createtab_log
(owner, tab_name, create_user)
values (ora_dict_obj_owner, ora_dict_obj_name, ora_login_user);
end if;
end if;
end tri_after_create_table;

3、用户权限管理-执行授权语句：
CREATE OR REPLACE TRIGGER admin.tri_ai_user_auth_sql
/**********************************************************************************
* 名称：tri_ai_user_auth_sql
* 功能：用户权限管理-执行授权语句
**********************************************************************************/
after insert on admin.user_auth_sql
declare
begin
-- 调用job执行授权语句
dbms_scheduler.enable('JOB_USER_AUTH');
end tri_ai_user_auth_sql;

四、JOB

调用存储过程执行授权语句操作表中的SQL：
begin
sys.dbms_scheduler.create_job(job_name => 'JOB_USER_AUTH',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN
P_JOB_USER_AUTH;
END;',
start_date => to_date('01-06-2018 00:00:00', 'dd-mm-yyyy hh24:mi:ss'),
repeat_interval => '',
end_date => to_date(null),
job_class => 'DEFAULT_JOB_CLASS',
enabled => false,
auto_drop => false,
comments => '');
end;

调用的存储过程：
create or replace procedure P_JOB_USER_AUTH
/**********************************************************************************
* 名称：P_JOB_USER_AUTH
* 功能：用户权限管理-执行授权语句
**********************************************************************************/
as
str_l_sqlerrm varchar2(1000);
begin
-- 找出所有未执行过的授权语句
for c in (select *
from user_auth_sql t
where t.exe_status = 'N') loop
begin
-- 执行授权语句
execute immediate c.sql_text;

-- 更新执行状态为Y
update user_auth_sql t
set t.exe_status = 'Y'
where t.id = c.id;
exception
when others then
str_l_sqlerrm := sqlerrm;

-- 记录异常日志
update user_auth_sql t
set t.exe_status = 'E',
t.err_msg = str_l_sqlerrm
where t.id = c.id;
end;
commit;
end loop;
exception
when others then
dbms_output.put_line(sqlerrm);
end P_JOB_USER_AUTH;