In the old fashioned way, when you want to give data to a statistician, you give them a copy. This usually comes from a database, whether your own it or not.
This category of copying includes most micro and small businesses. When data becomes too much, fortunately you can’t send them by email or chat, due to limitations, such as the size of the attachment. And usually in these cases the data already comes from a database, not from a temporary solution like spreadsheets.
In these cases, you give the access credentials to the database. However, you have to be very careful about which credentials. Because if you give also those of administrator, the statistician or whoever does the analysis for them, can also cause accidental damage, such as eliminating columns and/or rows. This is why we proceed step by step: first we give access to the data ALON, read only, since it is not a copy of the entire database, then, if the need arises, also in WRITING, possibly proceeding step by step.
In some cases the company does not own the database, for example when using certain CRMs, such as Hubspot. In that case the form of access changes, API keys are needed. But the substance does not change: these keys can have different types of permissions and obviously the issue of read and write comes back.
A technical example of creating read-only rights for a database:
— 1) Let’s create a new read-only account for the consultant
CREATE USER reading_consultant
WITH PASSWORD ‘consultant_name_strong_password’;
— 2) We allow the consultant to connect to the company database
GRANT CONNECT ON DATABASE database_aziendale
TO reading_consultant;
— 3) We allow the consultant to “see” the data within the main schema
GRANT USAGE ON SCHEMA public
TO reading_consultant;
a good practice, which however presupposes the presence of IT experts in the company, is to share a subset of the database rather than the entire database, through a view:
— 1) We create a view that contains only the specific data to be shown to the consultant
— In this example we filter orders from the last year, but you can customize
— columns and conditions as desired.
CREATE OR REPLACE VIEW view_consultant_orders AS
SELECT
order_id,
order_date,
client,
amount
FROM public.orders
WHERE order_date >= (CURRENT_DATE – INTERVAL ‘1 year’);
— 2) Create (if it doesn’t already exist) the read-only user
CREATE USER reading_consultant
WITH PASSWORD ‘consultant_name_strong_password’;
— 3) We allow the consultant to connect to the company database
GRANT CONNECT ON DATABASE company_database
TO reading_consultant;
— 4) We allow the consultant to “see” the container (scheme) where the view is
GRANT USAGE ON SCHEMA public
TO reading_consultant;
— 5) We grant the consultant permission to read only the view, NOT the underlying tables
GRANT SELECT ON view_consultant_orders
TO reading_consultant;