Roles are used for both users and groups, and are global across all databases in the SQream cluster. For a
ROLE to be used as a user, it requires a password and log-in and connect permissionss to the relevant databases.
The Managing Roles section describes the following role-related operations:
A user role logging in to the database requires
LOGIN permissions and a password.
The following is the syntax for creating a new role:
CREATE ROLE <role_name> ;
GRANT LOGIN to <role_name> ;
GRANT PASSWORD <'new_password'> to <role_name> ;
GRANT CONNECT ON DATABASE <database_name> to <role_name> ;
The following is an example of creating a new role:
CREATE ROLE new_role_name ;
GRANT LOGIN TO new_role_name;
GRANT PASSWORD 'Passw0rd!' to new_role_name;
GRANT CONNECT ON DATABASE master to new_role_name;
A database role may have a number of permissions that define what tasks it can perform, which are assigned using the GRANT command.
The following is the syntax for dropping a user:
DROP ROLE <role_name> ;
The following is an example of dropping a user:
DROP ROLE admin_role ;
The following is the syntax for altering a user name:
ALTER ROLE <role_name> RENAME TO <new_role_name> ;
The following is an example of altering a user name:
ALTER ROLE admin_role RENAME TO copy_role ;
You can change a user role’s password by granting the user a new password.
The following is an example of changing a user password:
GRANT PASSWORD <'new_password'> TO rhendricks;
Granting a new password overrides any previous password. Changing the password while the role has an active running statement does not affect that statement, but will affect subsequent statements.
The database has a predefined
PUBLIC role that cannot be deleted. Each user role is automatically granted membership in the
PUBLIC role public group, and this membership cannot be revoked. However, you have the capability to adjust the permissions associated with this
PUBLIC role has
CREATE permissions on
PUBLIC schema by default, therefore, newly created user roles are granted
CREATE (databases, schemas, roles, functions, views, and tables) on the public schema. Other permissions, such as INSERT, DELETE, SELECT, and UPDATE on objects in the public schema are not automatically granted.
Many database administrators find it useful to group user roles together. By grouping users, permissions can be granted to, or revoked from a group with one command. In SQream DB, this is done by creating a group role, granting permissions to it, and then assigning users to that group role.
To use a role purely as a group, omit granting it
CONNECT permission can be given directly to user roles, and/or to the groups they are part of.
CREATE ROLE my_group;
Once the group role exists, you can add user roles (members) using the
GRANT command. For example:
-- Add my_user to this group
GRANT my_group TO my_user;
To manage object permissions like databases and tables, you would then grant permissions to the group-level role (see the permissions table below.
All member roles then inherit the permissions from the group. For example:
-- Grant all group users connect permissions
GRANT CONNECT ON DATABASE a_database TO my_group;
-- Grant all permissions on tables in public schema
GRANT ALL ON all tables IN schema public TO my_group;
Removing users and permissions can be done with the
-- remove my_other_user from this group
REVOKE my_group FROM my_other_user;