Security considerations with role-based access control and synchronization

A user must be granted the SYS_RUN_REPLICATION_ROLE to run synchronization. The SYS_RUN_REPLICATION_ROLE grants a user system privileges, but these privileges can only be used when the user is logged in through an authenticated tool like dbmlsync or SQL Remote. This is similar to the way REMOTE DBA worked.

By default, the SYS_RUN_REPLICATION_ROLE system role includes the SYS_AUTH_DBA_ROLE system role. However, the SYS_AUTH_DBA_ROLE system role can be revoked from the SYS_RUN_REPLICATION_ROLE system role. The SYS_AUTH_DBA_ROLE system role is the only authority that can be removed from the SYS_RUN_REPLICATION_ROLE system role.

The SYS_AUTH_DBA_ROLE system role usually has more authority than is needed to synchronize. To set up a more secure synchronization environment, use one of the following approaches:

Revoke SYS_AUTH_DBA_ROLE from the SYS_RUN_REPLICATION_ROLE system role and grant the following system privileges:
- INSERT ANY TABLE
- UPDATE ANY TABLE
- DELETE ANY TABLE
- ALTER ANY TABLE
- EXECUTE ANY PROCEDURE
- Any system privileges required by statements contained in hooks.
- Any system privileges required by statements contained in stored procedures used to define scripted uploads.
The advantages of this approach are simplicity and the fact these system-level privileges can only be used when the user is connected through an authenticated tool (like dbmlsync or SQL REMOTE). The disadvantage of this approach is that the SYS_RUN_REPLICATION_ROLE system role is granted more privileges than are strictly needed for synchronization. It is given INSERT, UPDATE, DELETE and ALTER privileges on all tables and EXECUTE on all procedures when it only needs these privileges on a few tables and procedures.
Revoke SYS_AUTH_DBA_ROLE from the SYS_RUN_REPLICATION_ROLE system role, create a user-extended role that has the SYS_RUN_REPLICATION_ROLE system role, and grant the user-extended role to any user you want to allow to synchronize the database. Grant the following object-level privileges to the user-extended role:
- INSERT, UPDATE, DELETE and ALTER on all tables that are to be synchronized.
- EXECUTE on all hook procedures and on stored procedures used to define scripted uploads.
- SELECT, INSERT, UPDATE and DELETE on the tables dbo.synchronize_results and dbo.synchronize_parameters. This is only required if the SQL SYNCHRONIZE statement is being used.
- Any privileges required by statements contained in hooks.
- Any privileges required by statements contained in stored procedures used to define scripted uploads.
The advantage of this approach is that you have very fine control over the privileges granted to the user. However, the privileges granted are available to the users regardless of how they are logged in. Users are not just limited to connections made by dbmlsync and SQL Remote.

Examples

The following examples shows how to revoke the SYS_AUTH_DBA_ROLE system role from the SYS_RUN_REPLICATION_ROLE system role and grant the necessary privileges for synchronization.

REVOKE ROLE SYS_AUTH_DBA_ROLE FROM SYS_RUN_REPLICATION_ROLE;
GRANT INSERT ANY TABLE TO SYS_RUN_REPLICATION_ROLE;
GRANT UPDATE ANY TABLE TO SYS_RUN_REPLICATION_ROLE;
GRANT DELETE ANY TABLE TO SYS_RUN_REPLICATION_ROLE;
GRANT ALTER ANY TABLE TO SYS_RUN_REPLICATION_ROLE;
GRANT EXECUTE ANY PROCEDURE TO SYS_RUN_REPLICATION_ROLE;

The following example shows you how to revoke the SYS_AUTH_DBA_ROLE system role from the SYS_RUN_REPLICATION_ROLE system role and create a user-extended role called SYNC_USER with sufficient privileges to synchronize the tables T1 and T2 and execute the sp_hook_dbmlsync_begin hook. It then grants the SYNC_USER role to the user user1.

REVOKE ROLE SYS_AUTH_DBA_ROLE FROM SYS_RUN_REPLICATION_ROLE;

// Create user-extended role SYNC_USER
CREATE USER SYNC_USER IDENTIFIED BY 'sql';
GRANT ROLE SYS_RUN_REPLICATION_ROLE TO SYNC_USER;
CREATE ROLE FOR USER SYNC_USER;

// Grant privileges on table T1 to SYNC_USER
GRANT INSERT ON T1 TO SYNC_USER;
GRANT UPDATE ON T1 TO SYNC_USER;
GRANT DELETE ON T1 TO SYNC_USER;
GRANT ALTER ON T1 TO SYNC_USER;

// Grant privileges on table T2 to SYNC_USER
GRANT INSERT ON T2 TO SYNC_USER;
GRANT UPDATE ON T2 TO SYNC_USER;
GRANT DELETE ON T2 TO SYNC_USER;
GRANT ALTER ON T2 TO SYNC_USER;

// Grant privileges on any hooks to SYNC_USER
GRANT EXECUTE ON dba.sp_hook_dbmlsync_begin TO SYNC_USER;

// Grant privileges on the synchronize_results and synchronize_parameters tables
// to SYNC_USER so that it can use the SYNCHRONIZE statement
GRANT SELECT ON dbo.synchronize_results TO SYNC_USER;
GRANT INSERT ON dbo.synchronize_results TO SYNC_USER;
GRANT UPDATE ON dbo.synchronize_results TO SYNC_USER;
GRANT DELETE ON dbo.synchronize_results TO SYNC_USER;

GRANT SELECT ON dbo.synchronize_parameters TO SYNC_USER;
GRANT INSERT ON dbo.synchronize_parameters TO SYNC_USER;
GRANT UPDATE ON dbo.synchronize_parameters TO SYNC_USER;
GRANT DELETE ON dbo.synchronize_parameters TO SYNC_USER;

// Grant SYNC_USER to user1 so that user1 can synchronize the database

GRANT ROLE SYNC_USER to user1;

Discuss this page in DocCommentXchange.

Security considerations with role-based access control and synchronization

See also

Examples