CREATE ROLE statement allows you to create an SQL role. Only the
database owner can create a role.
CREATE ROLE roleName
The name of an SQL role.
Before you issue a
CREATE ROLE statement, verify that the
derby.database.sqlAuthorization property is set to
derby.database.sqlAuthorization property enables SQL authorization
You cannot create a role name if there is already a user by that name. An attempt to create a role name that conflicts with an existing user name raises the SQLException X0Y68. If user names are not controlled by the database owner (or administrator), it may be a good idea to use a naming convention for roles to reduce the possibility of collision with user names.
Splice Machine tries to avoid name collision between user names and role names, but this is not always possible, because Splice Machine has a pluggable authorization architecture. For example, an externally defined user may exist who has never yet connected to the database, created any schema objects, or been granted any privileges. If Splice Machine knows about a user name, it will forbid creating a role with that name. Correspondingly, a user who has the same name as a role will not be allowed to connect. Splice Machine built-in users are checked for collision when a role is created.
A role name cannot start with the prefix
SYS (after case
normalization). The purpose of this restriction is to reserve a name
space for system-defined roles at a later point. Use of the prefix
raises the SQLException 4293A.
You cannot create a role with the name
PUBLIC (after case
PUBLIC is a reserved authorization identifier. An
attempt to create a role with the name
PUBLIC raises SQLException
Creating a Role
Here’s a simple example of creating a role:
splice> CREATE ROLE statsEditor_role; 0 rows inserted/updated/deleted
Examples of Invalid Role Names
Here are several examples of attempts to create a role using names that are reserved and cannot be used as role names. Each of these generates an error:
splice> CREATE ROLE public; splice> CREATE ROLE "PUBLIC"; splice> CREATE ROLE sysrole;