By default, KORE provides a single Redshift account for organizations to use with DWA. That account is typically shared by various users within your organization. For organizations that desire more granular control, we can now provide per-user Redshift accounts.
Contact your KORE Success Manager and, for each user to provision, provide:
- user's name
- for individuals, the user's first (given) and last (family) name
- for a vendor or service, the entity name (such as Acme Partners Inc.)
- list of permissions and access to grant
- IP address(es) they will be allowed to connect from
- for individuals, you can omit this if they do not have a static IP address
We strongly recommend that users change their passwords upon first sign in. They can do this by running the following SQL command, replacing the values in lowercase:
ALTER USER my_user_name PASSWORD 'new_password';
- 8 to 64 character length
- at least one upper case letter, one lower case letter, and one number
- special characters (English-based) allowed, except:
' " \ / @
Access privileges (permissions)
Because you are no longer using a single account shared by all users, appropriate access privileges must be set for the database objects your users create in the
custom schema. Amazon uses a restrictive default, so that only a superuser or the user that creates an object may access it. KORE tightly restricts superuser access to Redshift databases.
When we first provision individual Redshift users for you, we will change the default privileges so that all users will have all privileges for new objects within the
custom schema. We will also grant all privileges to all users for existing objects within the
custom schema. For other schemas, we will grant read-only privileges to each user.
Upon request, KORE can create additional schemas for your organization that you can manage. This allows you to set more granular restrictions on Redshift users than using the
custom schema alone. For example, you might work with a vendor that writes data directly into your data warehouse but who shouldn't be allowed to read any other data.
When a user creates a database object, they may optionally override the default privileges set by KORE. For example, a user could create a table named
private_information within the
custom schema and revoke access for anyone else. They could then grant access to specific users or groups.
When we first provision individual Redshift users for you, KORE configures three groups. "
orgname" is replaced with the name of your organization as used in the hostname and database name:
orgnamedwausers—standard users in your organization with read-write access to the
korebischemas, and read-only access to other schemas.
orgnamekoreusers—KORE employees with read-write access to the
korebischemas, access to admin schemas and views, and read-only access to other schemas.
orgnameadminusers—KORE accounts with extensive access, primarily used for automated management of the data pipeline.
KORE can also configure additional groups as needed. To view the list of groups, run:
SELECT * FROM pg_catalog.pg_group;
Suppose you have a schema set up for a vendor to write data into. To grant them write access to the full schema:
--set default for future database objects ALTER DEFAULT PRIVILEGES IN SCHEMA vendorname_schema GRANT ALL TO USER vendorname; --change access to existing database objects GRANT ALL IN SCHEMA vendorname_schema TO USER vendorname;
To give all users in your organization read-only access to that schema, provide the
SELECT privilege for its tables:
--set default for future database objects ALTER DEFAULT PRIVILEGES IN SCHEMA vendorname_schema GRANT SELECT ON ALL TABLES TO GROUP orgnamedwausers; --change access to existing database objects GRANT SELECT ON ALL TABLES IN SCHEMA vendorname_schema TO GROUP orgnamedwausers;
Or suppose that a particular user should be able to alter a specific table within
vendorname_schema, but no others:
GRANT ALL ON TABLE vendorname_schema.table_name TO USER username;
If you use stored procedures, you can allow all members of a group to run any of them:
GRANT EXECUTE ON ALL PROCEDURES IN SCHEMA custom TO GROUP groupname;
Or alternatively, allow a specific user to execute a specific stored procedure:
GRANT EXECUTE ON PROCEDURE procedure_name IN SCHEMA custom TO USER username;
To reset privileges on existing objects a user created, they can run these commands:
GRANT ALL ON ALL TABLES IN SCHEMA schema_name TO GROUP clientdwausers; GRANT ALL ON ALL TABLES IN SCHEMA schema_name TO GROUP clientkoreusers; GRANT ALL ON ALL TABLES IN SCHEMA schema_name TO GROUP clientadminusers;
To reset the default privileges for objects a user will create later, they can run these commands:
ALTER DEFAULT PRIVILEGES IN SCHEMA schema_name GRANT ALL ON TABLES TO GROUP clientdwausers; ALTER DEFAULT PRIVILEGES IN SCHEMA schema_name GRANT ALL ON TABLES TO GROUP clientkoreusers; ALTER DEFAULT PRIVILEGES IN SCHEMA schema_name GRANT ALL ON TABLES TO GROUP clientadminusers;
A user can only grant privileges or change the default privileges for their own objects, not for any other user's. If you need to change the privileges for an existing object without using the owner's Redshift account (for example, after an employee leaves your organization), open a support ticket.