Streams users and privileges

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.

Trusted Streams Administrator user configuration

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.

Note

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.

Untrusted Streams capture, propagation, and apply user configuration

The following describes how to go about configuring "separation of duties" for an untrusted Streams configuration.

Streams Administration user

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;
/

Capture user

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'

    Note

    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.

Propagation user

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.
  • Enqueue privilege on the destination queue if the destination (apply) queue is in the same database.
  • Own the database link used by the propagation if destination (apply) queue is on a remote database. The user to which the database link connects at the remote database must have enqueue privileges on the destination (apply) queue.

Note

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.

Apply user

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:

  • DML and DDL privileges on the apply objects
  • 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 set
  • EXECUTE on all apply handler procedures
  • EXECUTE on all packages, including Oracle-supplied packages, that are invoked in subprograms run by the Apply process

Again, these privileges must be granted explicitly, they cannot be granted through roles.

Database links

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';

Trusted versus untrusted configurations

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.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset