Repeat the following: Never use the SYS or SYSTEM users as Streams administrators. These users are assigned the SYSTEM and SYSAUX tablespaces as default tablespaces, as well as very powerful database privileges.
As mentioned in Chapter 2, Plot Your Course: Design Considerations, the Stream user(s) can be configured for a trusted or untrusted security environment. In a trusted environment, a single Streams Administrator user is configured at each database site. All capture, propagation, and apply duties and supporting object ownership are in the security context of this user. In an untrusted environment, the duties of capture, propagation, and apply can be separated and assigned to different users with different privilege levels sufficient to perform the duties.
In this book, we use the STRMADMIN
user as our Streams Administrator. The user ID can be whatever makes the most sense to you. The expectation is that this user is only used exclusively for Streams purposes and is not referenced directly by user applications. This user has a high level of privileges, so we recommend that the login information be limited to the DBAs that are responsible for maintaining the Streamed environment.
Make sure that the Streams Administration user is not assigned the SYSTEM tablespace as a default or temporary tablespace. Here, we create our STRMADMIN
user with the STREAMS_TS
tablespace as the default tablespace.
Create the strmadmin account with the appropriate role and privileges: CREATE USER strmadmin IDENTIFIED BY <password> DEFAULT TABLESPACE streams_ts TEMPORARY TABLESPACE TEMP QUOTA UNLIMITED ON streams_ts; GRANT DBA TO strmadmin; BEGIN DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE( grantee => 'strmadmin', grant_privileges => TRUE ); END; /
As mentioned above, this should be performed on each database in the streamed environment.
The configuration of the Streams Administration user can be accomplished as a part of the setup through the Enterprise Manager Streams Setup Wizard if desired. We address it here, as well as in Chapter 4, for clarity of usage.
The following describes how to go about configuring "separation of duties" for an untrusted Streams configuration.
Even though you may intend to have separate capture, propagation, and/or apply users, it is recommended that you have a Streams Administration user to manage the overall Streams environment. You can either create a Streams Administration user using the same method above for a trusted environment, or use an existing DBA user.
If you wish to configure an existing DBA user as a Streams Administrator, ensure that the SYSTEM tablespace is not assigned as the DBA users' default and temporary tablespaces (this is why it is strongly recommended not to use SYS or SYSTEM for your Streams Administrator). If the DBA users' default and/or tablespace is SYSTEM, either ALTER
the user to reassign these, or create a new DBA user.
To configure an existing DBA user as a Stream Administrator, run the following command:
BEGIN DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE( grantee => '<dba_user>', grant_privileges => TRUE ); END; /
The Capture user must have DBA privileges.
It is recommended that you use the DBMS_CAPTURE_ADM.CREATE_CAPTURE
package to create the Capture process. The DBMS_STREAMS_ADM
package makes assumptive associations between the execution user and queue and rule ownership, and captures user assignments that can get a little tricky.
When using DBMS_CAPTURE_ADM.CREATE_CAPTURE
, the CAPTURE_USER
can be specified at the creation of a Capture process. This user is configured as a secure queue user and is granted enqueue privileges on the capture queue.
However, you must also make sure that the user is explicitly granted the following privileges:
EXECUTE
on all rule sets used by the Capture process.This can be done by either:
DBMS_RULE_ADM.GRANT_OBJECT_PRIVILEGE( privilege IN BINARY_INTEGER, object_name IN VARCHAR2, grantee IN VARCHAR2, grant_option IN BOOLEAN DEFAULT FALSE); where privilege => 'SYS.DBMS_RULE_ADM.EXECUTE_ON_RULE_SET' and object_name => <schema>.<rule_set_name>
OR
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE( privilege IN BINARY_INTEGER, grantee IN VARCHAR2, grant_option IN BOOLEAN DEFAULT FALSE); where privilege => 'SYS.DBMS_RULE_ADM.EXECUTE_ANY_RULE_SET'
For more information on granting privileges on rules and rule sets, please refer to the Oracle PL/SQL Packages and Types Reference manual.
EXECUTE
on all rule-based transformation functions used in the positive rule set.EXECUTE
on all packages (Oracle-supplied and user created) invoked by rule-based transformations run by the Capture process.These privileges must be granted explicitly, they cannot be granted through roles.
Messages are propagated by the user who owns the source queue. The owner of the source queue is not necessarily a capture user. The source queue owner must have the following privileges to propagate messages:
EXECUTE
on the rule sets used by the propagation (see capture user for specifics).EXECUTE
on all custom rule-based transformation functions used in the rule sets.Security note: It is possible for the database link to be a public database link. However, this opens up many security issues by having a database link in an untrusted system that any user can use to potentially exploit access to the remote database. Due to the high security risks of public database links, it is adamantly recommended that public database links not be used in either a trusted or untrusted Streams configuration.
The apply user must have DBA privileges.
It is recommended that you use the DBMS_APPLY_ADM.CREATE_APPLY
package to create the APPLY
process for the same reason mentioned in the "Capture user" section.
When using DBMS_APPLY_ADM.CREATE_APPLY
, the apply_user
can be specified at the creation of an Apply process. This user is configured as a secure queue user and granted dequeue privileges on the apply queue. However, you must also make sure that the user is explicitly granted the following privileges:
EXECUTE
on the rule sets used by the Apply process (see capture user for specifics)EXECUTE
on all rule-based transformation functions used in the rule setEXECUTE
on all apply handler proceduresEXECUTE
on all packages, including Oracle-supplied packages, that are invoked in subprograms run by the Apply processAgain, these privileges must be granted explicitly, they cannot be granted through roles.
When creating the database links, we again stress—use private database links, not public, in order to avoid security breaches through the links. Create the database link when connected as the user that will use the database link. This should be the only user that uses this database link. The overheads for maintaining private database links is well worth the security breach potential that a shared database link opens up.
We also recommend using the TNS Description parameter in the database link connection clause rather than a TNS Alias to avoid unintended or malicious misdirection of a database link if the local TNSNAMES.ora
is ever changed, moved, or replaced. However, we have provided examples of TNSNAMES.ora
files above, should you choose to go down that route.
Database link creation with TNS Description:
Create database link STRM2 connect to strm_admin identified by strm_admin using '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = STRM2_HOST) (PORT = 1521) ) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = STRM2) ) );
Database Link creation with TNS Alias:
Create database link STRM2 connect to strm_admin identified by strm_admin using 'STRM2';
After running through the privileges needed for an untrusted configuration, it becomes obvious that the "separation of duties" does not really save us anything from a security perspective. The capture and apply users must have DBA privileges which our trusted STRMADMIN
user has. The source queue owner is the propagator, so if you create the queue as STRMADMIN
, this makes STRMADMIN
the propagator anyway. Then, you add in the best practices of keeping the number of users with DBA or higher privileges to a minimum, the trusted model becomes a better option even in an untrusted environment. The only time untrusted buys you any advantage is if you are in an environment where you are not allowed to create a DBA user specific to Streams, but you do have a non-SYS and non-SYSTEM DBA user that you can work with. By using private database links and secure queues, you can lock down many of the security loopholes with distributed systems.