Random thoughts from an unusual company

Using Replica IDs in ACLs and Security

Gabriella Davis  17 March 2009 18:32:06
Found in the designer help for ACLs this paragraph

"To allow an agent in one database to use @DbColumn or @DbLookup to retrieve data from another database, enter the replica ID of the database containing the agent in the ACL of the database containing the data to be retrieved. The database containing the agent must have at least Reader access to the database containing the data to be retrieved. Both databases must be on the same server. An example of a replica ID in a database ACL is 85255B42:005A8fA4.
If you do not add the replica ID to the access control list, the other database can still retrieve data if the -Default- access level of your database is Reader or higher.

My first reading of this is that dblookups / dbcolumns don't work unless the replica id of the calling db is in the ACL of the lookup db (when  -Default- is set to no access). Which we all know isn't true.   Now I've never put a replica id in an ACL in my life so I wanted to do some testing as my assumption has been that:
  • If the agent is run from the workstation by a user (say from an action button), it runs with the security of whoever activated it
  • If the agent is scheduled to run on the server it runs with the authority of the agent signer (or 'on behalf of' assignee)

Sure enough, an agent run by a user in the client runs with their authority, if they can't access a lookup database then the @dblookup or @dbcolumn fails.  However when scheduling the agent to run on the server and trying to lookup information in a database to which the agent signer doesn't have access but the replica id of the agent owning database does, the lookup completes successfully.

I don't think the documentation is clear enough on the circumstances under which that works but I'm more concerned about audit trails and security.  It's fairly easy to change the replica id of a db to one that is listed in the ACL of a lookup database you do not usually have access to and grant yourself access to read information (and easy to find that replica id if the catalog isn't locked down).  It also works for scheduled agents so a local replica of a database with a private agent could also lookup information in a database I strictly don't have access to.  Plus replica ids don't tell you who explicitly can access your db, just that anyone who can access another db can in theory access your data.  Lots of things there to make a security paranoiac like me nervous.

I've been thinking about it for about a day now and I can't see a upside to having replica ids in ACLs (someone mentioned to me it was to support R3 dbs with unsigned agents but I think it's about time someone signed those suckers) !

1Nathan T. Freeman  17/03/2009 20:27:28  Using Replica IDs in ACLs and Security

If you don't have access to the database, how would you know what replicaID is in the ACL?

Do the default server settings even allow scheduled executions of unsigned agents anymore?

Not disagreeing with your point. Just wondering if there's even a circumstance where it applies anymore.

2Michelle O’Rorke   17/03/2009 21:04:19  Using Replica IDs in ACLs and Security

I agree that this is strange and should probably be removed. However a scheduled private agent running locally would not be able to access a database on a server - only a local replica (to which you would need at least Reader access).

To answer Nathan's point: you could find out the ACL settings from the catalog

3Gab Davis  17/03/2009 23:05:08  Using Replica IDs in ACLs and Security

@Nathan - finding the replica id of a database isn't that tough. Many companies leave the catalog wide open and the replica id of a database isn't considered a security risk per se.

It does seem to me to be an outdated feature that is poorly documented as to purpose and whose risk exceeds its value

Just my opinion though