 |
|
Creating the Streams Administrator
Oracle Tips by Burleson |
Creating the Streams Administrator
A user will have to be created to act as the
Streams administrator. Any name can be given to the user, as there
is no restriction on naming conventions. However, it is recommended
that a new tablespace be created for the use of the Streams
administrator account. By using a separate tablespace, a place is
created for all of the related objects in this tablespace. This
separate tablespace makes it easy to manage and monitor the growth
of the tablespace.
The following SQL statements show the steps for
the creation of the tablespace, creation of the Streams
administrator account, and the granting of necessary privileges to
that administrator account.
setup_streams.sql
--
*************************************************
spool setup_strmadm.log
PROMPT (1) ** Creating tablespace strm_tbs
CREATE TABLESPACE strm_tbs DATAFILE
'/app/oracle/DNYTST10/data/strmtbs_01.dbf' SIZE 500M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 4M
/
PROMPT (2) ** Creating User strmadm
CREATE USER strmadm IDENTIFIED BY strmadm
DEFAULT TABLESPACE strm_tbs QUOTA UNLIMITED
ON strm_tbs
/
PROMPT 3) ** Grant the privilege
GRANT CONNECT, RESOURCE, DBA TO strmadm
/
PROMPT 4) ** Use the AUTH package function
to grant privilege
BEGIN
DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(
grantee => 'strmadm',
grant_privileges => true);
END;
/
SPOOL OFF
With the above code, the STRMADM user has
been created. By using the
dbms_streams_auth.grant_admin_privilege procedure, in a
single operation, all of the necessary privileges were granted
to the STRMADM user.
Alternately, a series of SQL statements can
be generated that grant various privileges. Using this method
gives the DBA better control over the kinds of privileges that
are granted. For example, the main Streams administrator account
may be created with all of the privileges of database
administrators. However, for another application group or user,
it may be desirable to create additional Streams administrator
accounts with selected privileges only.
The next example shows how to generate the
script file containing the SQL statements that grants various
privileges to the Streams administrator account. By careful
selection of required privileges, the DBA is able create
multiple users with different degree of privileges.
-- First ensure we have a Directory Object
CREATE DIRECTORY admin_dir AS
'/app/oracle/DNYTST10/data'
/
-- generate the Script File
BEGIN
DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(
grantee =>
'strmadm',
grant_privileges => false,
file_name =>
'grant_strms_privs.sql',
directory_name =>
'admin_dir');
END;
/
The above text is
an excerpt from:
Oracle Streams
High Speed Replication and Data
Sharing
ISBN 0-9745993-5-2
by Madhu Tumma
|
Download your Oracle scripts now:
www.oracle-script.com
The
definitive Oracle Script collection for every Oracle professional DBA
|
|
