当前位置：文江博客话题详情

将 Oracle 中的 DDL 授予特定用户

发布于 2024-09-18 03:23:42 字数 156 浏览 3 评论 0原文

如何在oracle中授予DDL权限？

在数据库上，我有用户 SCHEMA_1、SCHEMA_2 和 SCHEMA_3

，现在我希望从 schema_1 只能在 SCHEMA_2 上执行 DDL

是否可以从 SCHEMA_2 级别或仅从系统授予授权？

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

滿滿的愛 2024-09-25 03:23:42

Oracle 不是这样工作的。您必须向该用户授予CREATE ANY [OBJECT_TYPE]权限，并拥有一个系统事件触发器来限制他们在您不希望他们使用的架构中工作。

警告：使用了 DBMS_STANDARD 的未记录/记录不足的功能。

CREATE OR REPLACE TRIGGER schema_1_on_schema_2
  before DDL on DATABASE
as
  has_dba_priv number;
  n            number;
  stmt         ora_name_list_t;
BEGIN
  -- exit if user is object owner
  if ora_dict_obj_owner = ora_login_user then
    return
  end if;

  -- exit if user has dba directly
  select count(*)
    into has_dba_priv
    from dba_role_privs 
   where granted_role = 'DBA'
     and grantee = ora_login_user;

  if has_dba_priv <> 0 then
    return;
  end if;

  -- exit if action is an automatic recompile
  stmt := null;
  n := ora_sql_txt(sql_text);
  FOR i IN 1..n LOOP
    stmt := stmt || sql_text(i);
  END LOOP;

  if stmt like 'ALTER % COMPILE REUSE SETTINGS%' then
    return;
  end if;

  -- you should probably organize this into a database table of permitted
  -- schema_x can affect schema_y, but this is a "basic" example
  if     (ora_dict_obj_owner = 'SCHEMA_2')
     and (ora_login_user = 'SCHEMA_1') then 
    null;
  else
    raise_application_error (-20000, 'User ' || ora_login_user || 
         ' is not permitted to execute DDL against ' || ora_dict_obj_owner);
  end if;
end;

Oracle doesn't work that way. You'd have to grant CREATE ANY [OBJECT_TYPE] to that user and have a system event trigger which restricts them from working in the schemas you don't want them to.

Warning: Undocumented / underdocumented features of DBMS_STANDARD are used.

CREATE OR REPLACE TRIGGER schema_1_on_schema_2
  before DDL on DATABASE
as
  has_dba_priv number;
  n            number;
  stmt         ora_name_list_t;
BEGIN
  -- exit if user is object owner
  if ora_dict_obj_owner = ora_login_user then
    return
  end if;

  -- exit if user has dba directly
  select count(*)
    into has_dba_priv
    from dba_role_privs 
   where granted_role = 'DBA'
     and grantee = ora_login_user;

  if has_dba_priv <> 0 then
    return;
  end if;

  -- exit if action is an automatic recompile
  stmt := null;
  n := ora_sql_txt(sql_text);
  FOR i IN 1..n LOOP
    stmt := stmt || sql_text(i);
  END LOOP;

  if stmt like 'ALTER % COMPILE REUSE SETTINGS%' then
    return;
  end if;

  -- you should probably organize this into a database table of permitted
  -- schema_x can affect schema_y, but this is a "basic" example
  if     (ora_dict_obj_owner = 'SCHEMA_2')
     and (ora_login_user = 'SCHEMA_1') then 
    null;
  else
    raise_application_error (-20000, 'User ' || ora_login_user || 
         ' is not permitted to execute DDL against ' || ora_dict_obj_owner);
  end if;
end;

回复收藏 0 原文