Users can successfully execute operations only if they have the authority to perform the specified function. For example, to create a table, a user must be authorized to create tables; to alter a table, a user must be authorized to alter the table. solidDB offers several methods for managing user authorization.
Privileges and roles
A privilege is a permission to perform an action or a task. Authorized users can create objects, have access to objects they own, and can pass on privileges on their own objects to other users by using the GRANT statement. Privileges can be granted to individual users or roles (groups).
You can grant any combination (or all) of the following user privileges to a user:
▪ SELECT: User can query a specified table.
▪ INSERT: User can add rows to a specified table.
▪ UPDATE: User can update rows in a specified table.
▪ DELETE: User can delete rows from a specified table.
▪ REFERENCES: User can create a table that has a foreign key reference to the specified table.
▪ EXECUTE ON: User can execute a specified procedure or function.
A user that has no privileges to an object cannot use the object at all.
After user privileges are granted, they take effect when the user that is granted the privileges logs on to the database. If the user is already logged on to the database when the privileges are granted, they take effect only for objects that have not already been accessed during the user session:
A role is a group of privileges that can be granted to users as one unit. You can create roles and assign users to certain roles. A single user can have more than one role assigned, and a single role can have more than one user assigned.
Note:
▪ The same string cannot be used both as a user name and a role name.
▪ After a user role is granted, it takes effect when the user who is granted the role logs on to the database. If the user is already logged on to the database when the role is granted, the role takes effect when the user disconnects and then reconnects to the database.
There are both system roles and user-defined roles. User-defined roles are created by using the CREATE ROLE statement, see CREATE ROLE. All roles are assigned to users by using the GRANT statement, see GRANT.
System roles
solidDB offers the following system roles. The system role names are reserved user names.
Reserved Names
Description
PUBLIC
This role grants privileges to all users. When user privileges to a certain table are granted to the role PUBLIC, all current and future users have the specified user privileges to this table. This role is granted automatically to all users.
SYS_ADMIN_ROLE
This is the default role for the database administrator. This role has administration privileges to all tables, indexes and users, as well as the right to use solidDB Remote Control. This is also the database creator role.
SYS_CONSOLE_ROLE
This role has the right to use solidDB Remote Control, but does not have other administration privileges.
SYS_MONITOR_ROLE
This role has the right to query pmon values and the server state and health.
SYS_REPLICATION_ROLE
This role is for CREP replication. It enables access for SYS_LOG table for online replication, reading tables involved in replication for initial load and starting and stopping of replication.