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 compatibility role. However, the SYS_AUTH_DBA_ROLE compatibility role can be revoked from the SYS_RUN_REPLICATION_ROLE system role. The SYS_AUTH_DBA_ROLE compatibility role is the only authority that can be removed from the SYS_RUN_REPLICATION_ROLE system role.

The SYS_AUTH_DBA_ROLE compatibility 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.

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;

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;