删除和重新创建表时维护表访问的最佳流程策略

Question

是否可以让permissions为Oracle 12c数据库中的所有权限编写脚本，而不同时拥有修改架构中对象或数据的权限？

我需要能够在删除和重新创建表之前为表上的现有权限编写脚本，以便在重新创建表后重新应用权限。我必须将脚本提交给DBA才能运行，并且在删除和重新创建表时需要包含这些权限。如果我看不到现有的权限，我就不能包括它们。DBA不允许我自己做这件事

但他只会运行我100%自己写的脚本。

当我在使用与架构名称不匹配的ID登录时尝试查看表的DDL时，我会得到以下错误：

若要提取审核选项，您必须在DBA_OBJ_AUDIT_OPTS或登录到正在提取的模式中。

授予DBA_OBJ_AUDIT_OPTS的SELECT权限是否可以让我查看表上的所有授予，而不必为我提供修改架构或数据的额外权限？

Answer 1

不要执行DROP TABLE/CREATE TABLE。请改用DBMS_REDEFINITION。以下是我为这个功能保留的示例代码的修改版本：

CREATE TABLE my_preexisting_table 
  ( a number,
    constraint my_preexisting_table_pk primary key (a) );
GRANT SELECT, UPDATE ON my_preexisting_table TO ont;
-- Start the online redefinition process...
-- First, check whether your table is a candidate for the process
BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE('apps','my_preexisting_table',
      DBMS_REDEFINITION.CONS_USE_ROWID);
END;
/
-- Create your new table with a new name.  This will eventually replace the pre-existing one
--DROP TABLE apps.my_preexisting_table_redef;
CREATE TABLE apps.my_preexisting_table_redef
(
  new_column1     NUMBER,
  a               NUMBER,
  new_column2     DATE,
  -- Let's change the primary key while we're at it
  -- Unfortunately, we have to rename our constraints because they share a global namespace 
  constraint my_preexisting_table_pk_r primary key (new_column1, a)  
)
-- Let's partition the table while we're at it...
PARTITION BY RANGE (new_column2)
INTERVAL (NUMTODSINTERVAL (1,'DAY')) ( partition my_preexisting_table_old values less than (to_date('01-JAN-2000','DD-MON-YYYY') ));
;

-- Takes long if your table is big.
BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE('apps', 'my_preexisting_table','my_preexisting_table_redef',
-- Map columns from the existing table to the new table here
       'a new_column1, a a, sysdate new_column2',
        dbms_redefinition.cons_use_rowid);
END;
/
DECLARE
num_errors PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('apps', 'my_preexisting_table','my_preexisting_table_redef',
   DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors);
  DBMS_OUTPUT.PUT_LINE('Copy depenedent objects: num_errors = ' || num_errors);   
END;

-- Make sure there were no problems... or, if there were problems then they were expected.  For example,
-- there will be an error listed because it cannot copy the PK constraint from the original table (because we made a new one already)
-- and that's OK.
select * from         DBA_REDEFINITION_ERRORS where base_table_name = 'MY_PREEXISTING_TABLE';

BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE('apps', 'my_preexisting_table', 'my_preexisting_table_redef');
END;
/

-- Check out the results.
select * from my_preexisting_table;
-- Verify the grants are still in place...
select * from DBA_TAB_PRIVS where table_Name = 'MY_PREEXISTING_TABLE';
-- Drop our redef table when ready...
DROP TABLE apps.my_preexisting_table_redef;

Answer 2

在应用程序架构上创建一个函数，该函数返回该架构拥有的表的对象权限，然后授予自己执行该函数的权限。

这是解决这个问题最简单的方法。从大局来看，有更好的方法，但这些方法可能需要对流程进行重大更改。

• 用户ALTER，而不是DROP和CREATE。有很多依赖对象类型，不可能把它们都想出来。例如，这些表有虚拟专用数据库谓词、基于列使用情况构建的直方图等。在代码"生活"在数据库上的环境中，DROP是敌人
• 将数据库的"一个真实版本"存储在受版本控制的文本文件中。这是您可以安全地删除表并确切地知道如何重建它们的唯一方法。只有在本地数据库上删除并重新创建了几百次模式之后，您的组织才能真正了解事情是如何工作的

---

以下是实现此功能的最简单方法：

示例架构

drop table test1;
create table test1(a number);
grant select on test1 to system;
grant references on test1 to system with grant option;

创建函数以生成脚本

在应用程序架构上创建此函数。

create or replace function get_table_grants(p_table_name in varchar2) return clob is
--Purpose: Return the object grants for a table.
    v_ddl clob;
begin
    --Enable the SQL terminator, ";" or "/".
    dbms_metadata.set_transform_param(
        dbms_metadata.session_transform,
        'SQLTERMINATOR',
        true);
    --Get the DDL.
    select dbms_metadata.get_dependent_ddl(
        object_type => 'OBJECT_GRANT',
        base_object_name => upper(trim(p_table_name)),
        base_object_schema => user)
    into v_ddl
    from dual; 
    --Return the DDL.
    return v_ddl;
end get_table_grants;
/
--Grant access to yourself.
grant execute on get_table_grants to YOUR_USERNAME;

样本输出

select get_table_grants('TEST1') from dual;
  GRANT REFERENCES ON "JHELLER"."TEST1" TO "SYSTEM" WITH GRANT OPTION;
  GRANT SELECT ON "JHELLER"."TEST1" TO "SYSTEM";