oracle streams学习一（创建streams管理账户和授权）

oracle streams需要首先创建一个管理账户，并且需要配置DBA角色等权限：

SQL> conn / as sysdba
已连接到空闲例程。

SQL> create user streamadmin identified by streamadmin;

用户已创建。

SQL> alter user streamadmin default tablespace users temporary tablespace temp quota unl
imited on users;

用户已更改。

SQL> grant connect,resource,aq_administrator_role,dba to streamadmin;

授权成功。

SQL> create directory stream_dir as 'e:\oracledir';

目录已创建。

SQL> begin dbms_streams_auth.grant_admin_privilege(grantee=>'streamadmin',grant_privileges=>false,fil
e_name=>'grant_streamadmin.sql',directory_name=>'stream_dir');
   end;
/
PL/SQL 过程已成功完成。

 

这时在 e:\oracledir目录会生成一个grant_streamadmin.sql文件，内容如下：

-- rule privileges section
grant execute on dbms_rule_adm to streamadmin
/
BEGIN 
  dbms_rule_adm.grant_system_privilege(
    privilege => dbms_rule_adm.CREATE_EVALUATION_CONTEXT_OBJ,
    grantee => 'streamadmin', 
    grant_option => true);
END;
/
BEGIN 
  dbms_rule_adm.grant_system_privilege(
    privilege => dbms_rule_adm.CREATE_RULE_SET_OBJ,
    grantee => 'streamadmin', 
    grant_option => true);
END;
/
BEGIN 
  dbms_rule_adm.grant_system_privilege(
    privilege => dbms_rule_adm.CREATE_RULE_OBJ,
    grantee => 'streamadmin', 
    grant_option => true);
END;
/
BEGIN 
  dbms_rule_adm.grant_system_privilege(
    privilege => dbms_rule_adm.CREATE_ANY_RULE_SET,
    grantee => 'streamadmin', 
    grant_option => true);
END;
/
BEGIN 
  dbms_rule_adm.grant_system_privilege(
    privilege => dbms_rule_adm.ALTER_ANY_RULE_SET,
    grantee => 'streamadmin', 
    grant_option => true);
END;
/
BEGIN 
  dbms_rule_adm.grant_system_privilege(
    privilege => dbms_rule_adm.EXECUTE_ANY_RULE_SET,
    grantee => 'streamadmin', 
    grant_option => true);
END;
/
BEGIN 
  dbms_rule_adm.grant_system_privilege(
    privilege => dbms_rule_adm.CREATE_ANY_RULE,
    grantee => 'streamadmin', 
    grant_option => true);
END;
/
BEGIN 
  dbms_rule_adm.grant_system_privilege(
    privilege => dbms_rule_adm.ALTER_ANY_RULE,
    grantee => 'streamadmin', 
    grant_option => true);
END;
/
BEGIN 
  dbms_rule_adm.grant_system_privilege(
    privilege => dbms_rule_adm.EXECUTE_ANY_RULE,
    grantee => 'streamadmin', 
    grant_option => true);
END;
/
-- queue privileges section
grant execute on dbms_aq to streamadmin
/
grant execute on dbms_aqadm to streamadmin
/
BEGIN 
  dbms_aqadm.grant_system_privilege(
    'ENQUEUE_ANY', 
    'streamadmin', 
    admin_option => TRUE); 
END;
/
BEGIN 
  dbms_aqadm.grant_system_privilege(
    'DEQUEUE_ANY', 
    'streamadmin', 
    admin_option => TRUE); 
END;
/
BEGIN 
  dbms_aqadm.grant_system_privilege(
    'MANAGE_ANY', 
    'streamadmin', 
    TRUE); 
END;
/
grant select on dba_queue_tables to streamadmin
/
grant select on dba_queues to streamadmin
/
grant select on dba_queue_schedules to streamadmin
/
grant select on sys.v_$aq to streamadmin
/
grant select on sys.gv_$aq to streamadmin
/
grant select on sys.aq$_propagation_status to streamadmin
/
grant execute on sys.dbms_aqin to streamadmin
/
grant select on sys.aq$internet_users to streamadmin
/
grant execute on sys.dbms_transform to streamadmin
/
grant execute on sys.dbms_aqelm to streamadmin
/
grant select on dba_aq_agents to streamadmin
/
grant select on dba_aq_agent_privs to streamadmin
 
 
 
/
grant select on dba_queue_subscribers to streamadmin
/
-- streams packages section
grant execute on dbms_capture_adm to streamadmin
/
grant execute on dbms_propagation_adm to streamadmin
/
grant execute on dbms_apply_adm to streamadmin
/
grant execute on dbms_streams_adm to streamadmin
/
grant execute on dbms_streams_messaging to streamadmin
 
/
-- streams views section
grant select on dba_streams_global_rules to streamadmin
/
grant select on dba_streams_schema_rules to streamadmin
/
grant select on dba_streams_table_rules to streamadmin
/
grant select on dba_streams_transform_function to streamadmin
/
grant select on dba_streams_administrator to streamadmin
/
grant select on dba_streams_message_rules to streamadmin
/
grant select on dba_streams_message_consumers to streamadmin
/
grant select on dba_apply to streamadmin
/
grant select on dba_apply_parameters to streamadmin
/
grant select on dba_apply_instantiated_objects to streamadmin
/
grant select on dba_apply_instantiated_schemas to streamadmin
/
grant select on dba_apply_instantiated_global to streamadmin
/
grant select on dba_apply_key_columns to streamadmin
/
grant select on dba_apply_conflict_columns to streamadmin
/
grant select on dba_apply_dml_handlers to streamadmin
/
grant select on dba_apply_progress to streamadmin
/
grant select on dba_apply_error to streamadmin
/
grant select on dba_apply_enqueue to streamadmin
/
grant select on dba_apply_execute to streamadmin
/
grant select on sys.gv_$streams_apply_coordinator to streamadmin
/
grant select on sys.v_$streams_apply_coordinator to streamadmin
/
grant select on sys.gv_$streams_apply_server to streamadmin
/
grant select on sys.v_$streams_apply_server to streamadmin
/
grant select on sys.gv_$streams_apply_reader to streamadmin
/
grant select on sys.v_$streams_apply_reader to streamadmin
/
grant select on dba_capture to streamadmin
/
grant select on dba_capture_parameters to streamadmin
/
grant select on dba_capture_prepared_database to streamadmin
/
grant select on dba_capture_prepared_schemas to streamadmin
/
grant select on dba_capture_prepared_tables to streamadmin
/
grant select on dba_capture_extra_attributes to streamadmin
/
grant select on dba_registered_archived_log to streamadmin
/
grant select on sys.gv_$streams_capture to streamadmin
/
grant select on sys.v_$streams_capture to streamadmin
/
grant select on dba_rule_sets to streamadmin
/
grant select on dba_rulesets to streamadmin
/
grant select on dba_rules to streamadmin
/
grant select on  dba_rule_set_rules to streamadmin
/
grant select on dba_evaluation_contexts to streamadmin
/
grant select on dba_evaluation_context_tables to streamadmin
/
grant select on dba_evaluation_context_vars to streamadmin
/
grant select on dba_queue_publishers to streamadmin
/
grant select on sys.gv_$buffered_queues to streamadmin
/
grant select on sys.v_$buffered_queues to streamadmin
/
grant select on sys.gv_$buffered_subscribers to streamadmin
/
grant select on sys.v_$buffered_subscribers to streamadmin
/
grant select on sys.gv_$buffered_publishers to streamadmin
/
grant select on sys.v_$buffered_publishers to streamadmin
/
grant select on dba_propagation to streamadmin
 
/
-- streams views section
grant select on dba_streams_unsupported to streamadmin
/
-- streams views section
grant select on dba_streams_newly_supported to streamadmin
/
grant select on dba_streams_rules to streamadmin
/
grant select on dba_apply_table_columns to streamadmin
/
grant select on dba_streams_add_column to streamadmin
/
grant select on dba_streams_delete_column to streamadmin
/
grant select on dba_streams_rename_column to streamadmin
/
grant select on dba_streams_rename_schema to streamadmin
/
grant select on dba_streams_rename_table to streamadmin
/
grant select on dba_streams_transformations to streamadmin
/
grant select on dba_apply_spill_txn to streamadmin
/
grant select on sys.gv_$streams_transaction to streamadmin
/
grant select on sys.v_$streams_transaction to streamadmin
/
-- miscellaneous privileges section
grant restricted session to streamadmin
/
grant execute on dbms_flashback to streamadmin
/
BEGIN 
  dbms_streams_auth.grant_remote_admin_access('streamadmin'); 
END;
/

 然后执行这个脚本，就会给streamadmin用户相应权限了

 

SQL> @"E:\oracledir\grant_streamadmin.sql"
......
授权成功。

 

    或者另一种简单的方式：直接调用DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE过程

SQL> desc dbms_streams_auth;
PROCEDURE GRANT_ADMIN_PRIVILEGE
参数名称                       类型                    输入/输出默认值?
------------------------------ ----------------------- ------ --------
 GRANTEE                        VARCHAR2                IN
 GRANT_PRIVILEGES               BOOLEAN                 IN     DEFAULT
 FILE_NAME                      VARCHAR2                IN     DEFAULT
 DIRECTORY_NAME                 VARCHAR2                IN     DEFAULT
PROCEDURE GRANT_REMOTE_ADMIN_ACCESS
参数名称                       类型                    输入/输出默认值?
------------------------------ ----------------------- ------ --------
 GRANTEE                        VARCHAR2                IN
PROCEDURE REVOKE_ADMIN_PRIVILEGE
参数名称                       类型                    输入/输出默认值?
------------------------------ ----------------------- ------ --------
 GRANTEE                        VARCHAR2                IN
 REVOKE_PRIVILEGES              BOOLEAN                 IN     DEFAULT
 FILE_NAME                      VARCHAR2                IN     DEFAULT
 DIRECTORY_NAME                 VARCHAR2                IN     DEFAULT
PROCEDURE REVOKE_REMOTE_ADMIN_ACCESS
参数名称                       类型                    输入/输出默认值?
------------------------------ ----------------------- ------ --------
 GRANTEE                        VARCHAR2                IN

SQL> begin
  2  dbms_streams_auth.grant_admin_privilege(grantee=>'streamadmin',grant_privileges=>true);
  3  end;
  4  /

PL/SQL 过程已成功完成。

SQL> 

  这样就会给streamadmin相应权限了。

  撤销权限的方式也很简单，只要调用 DBMS_STREAMS_AUTH.REVOKE_ADMIN_PRIVILEGE过程就可以了

SQL> begin
  2  dbms_streams_auth.revoke_admin_privilege(grantee=>'streamadmin',revoke_privileges=>true);
  3  end;
  4  /

PL/SQL 过程已成功完成。

SQL>