Question:
Why do we need to run the Sample_Grants script at all, and why do we need elevated rights to do so? Will this give extra rights to someone outside of the database? Can we use a limited account to do this instead?
Answer:
Overall Purpose of the Script
The purpose of this script is to allow a limited user account the minimum access necessary to process our data on a specific database, rather than requiring a user with some level of elevated permissions.
In net, the script does 3 things, all outlined in the script itself (the 18.4 version is listed below)
- Section 1: Grant the user Data Reader and Data Writer access to the DB
- Section 2: Grant access to the limited account to run our stored procedures. (See Below)
- Section 3: Grant Receive rights on the queues.
There are, essentially, two ways to accomplish the same effect. 1) grant the service account DBO rights on the database, or 2) use an account with full rights on the database to GRANT the service account the necessary permissions, indicated above.
What rights are required to run this script:
As the script reads, it should be run as SA. In many ways, this is for ease of use. First, the DB must be created manually, which requires rights similar to SA. Second, one must add the tables and structure, and finally, special permissions must be granted that are not by default exposed during account creation.
The easiest way to do this is as SA.
From testing, there have been times when a full DBO user on that specific database has been successful in adding the DB structure and granting rights once the DB was created and a user granted full rights to it. However, this is not currently supported.
What do the Scripts Do, which we are giving rights to?
These stored procedures are all for internal use in the TPP database only and have no resultant rights OUTSIDE of our database. They do a variety of things necessary for processing our data, including our nightly tasks, among other things.
They are subject to change as our schema changes, or optimization changes, or processes change.
Sample of the "Sample_Grants" script.
As you review the script below, please read the top section, where you'll see that this is specifically for a "user with limited permissions". As it reads, it should be run as SA, but technically, not quite that many rights are required.
-- Sample script that shows the permissions necessary for the Trust Protection Platform
-- to work with a Microsoft SQL Server user with limited permissions.
-- This script needs to be run as the system administrator.
-- please read the script carefully before running.
--
-- Please consult with your DBA to determine the best way to set this up in your
-- environment.
--
-- In the below script, replace DIRECTOR_RW with the username of the account that
-- will be used in the Trust Protection Platform.
-- This script should be run during new install or upgrade.
-- ============ To grant stored procedure and type access a non-owner DB user ==========
-- As sa or DBA user
-- =====================================================================================
-- The user will also need data_reader and data_writer
EXEC sp_addrolemember db_datareader, DIRECTOR_RW
EXEC sp_addrolemember db_datawriter, DIRECTOR_RW
GRANT EXECUTE ON TYPE::dbo.ConfigObjectRels TO [DIRECTOR_RW]
GRANT EXECUTE ON TYPE::dbo.ConfigObjectRelsKeyed TO [DIRECTOR_RW]
GRANT EXECUTE ON TYPE::dbo.ConfigObjectChildRightsRels TO [DIRECTOR_RW]
GRANT EXECUTE ON TYPE::dbo.ConfigObjectChildRightsPrincipals TO [DIRECTOR_RW]
GRANT EXECUTE ON TYPE::dbo.DAL_V_LOG_TYPE TO [DIRECTOR_RW]
GRANT EXECUTE ON TYPE::dbo.DAL_LOG_T_QUEUE_MESSAGES TO [DIRECTOR_RW]
GRANT EXECUTE ON TYPE::dbo.LargeIntegers TO [DIRECTOR_RW]
GRANT EXECUTE ON TYPE::dbo.SmallIntegers TO [DIRECTOR_RW]
GRANT EXECUTE ON TYPE::dbo.ToDo_Request_Messages TO [DIRECTOR_RW]
GRANT EXECUTE ON TYPE::dbo.ToDo_Requests TO [DIRECTOR_RW]
GRANT EXECUTE ON TYPE::dbo.ToDo_Requests_Defer TO [DIRECTOR_RW]
GRANT EXECUTE ON OBJECT::dbo.RefreshConfigObjectRels TO [DIRECTOR_RW]
GRANT EXECUTE ON OBJECT::dbo.RefreshConfigObjectChildRels TO [DIRECTOR_RW]
GRANT EXECUTE ON OBJECT::dbo.DAL_LOG_SP_BULK_ENQUEUE TO [DIRECTOR_RW]
GRANT EXECUTE ON OBJECT::dbo.DAL_LOG_SP_BULK_DEQUEUE TO [DIRECTOR_RW]
GRANT EXECUTE ON OBJECT::dbo.DAL_SP_V_LOG_LOG_BI TO [DIRECTOR_RW]
GRANT EXECUTE ON OBJECT::dbo.DAL_LOG_SP_LOG_LOG_EXPIRE_COMP TO [DIRECTOR_RW]
GRANT EXECUTE ON OBJECT::dbo.DAL_LOG_SP_LOG_LOG_EXPIRE_DATA TO [DIRECTOR_RW]
GRANT EXECUTE ON OBJECT::dbo.DAL_LOG_SP_LOG_LOG_EXPIRE_PARAMS TO [DIRECTOR_RW]
GRANT EXECUTE ON OBJECT::dbo.DAL_LOG_SP_LOG_LOG_EXPIRE_SOURCE_IP TO [DIRECTOR_RW]
GRANT EXECUTE ON OBJECT::dbo.DAL_LOG_SP_LOG_LOG_EXPIRY TO [DIRECTOR_RW]
GRANT EXECUTE ON OBJECT::dbo.ven_request_applock TO [DIRECTOR_RW]
GRANT EXECUTE ON OBJECT::dbo.ToDo_Request_Dequeue TO [DIRECTOR_RW]
GRANT EXECUTE ON OBJECT::dbo.ToDo_Request_Enqueue TO [DIRECTOR_RW]
-- Must have receive service on FROM queue to initiate dialogs
GRANT RECEIVE ON LogClientQueue TO [DIRECTOR_RW]
-- Must have receive service on FROM queue to retrieve from queue.
GRANT RECEIVE ON LogServerQueue TO [DIRECTOR_RW]
|
Comments