CREATE ROLE statement

The CREATE ROLE statement creates an SQL role. Roles are useful for administering privileges when a database has many users.

Only the database owner can create a role.

For more information on roles, see "Using SQL roles" in the Derby Security Guide.

Syntax

CREATE ROLE roleName

Before you issue a CREATE ROLE statement, verify that the derby.database.sqlAuthorization property is set to TRUE. The derby.database.sqlAuthorization property enables SQL authorization mode.

You cannot create a role name if there is 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.

Derby tries to avoid name collision between user names and role names, but this is not always possible, because Derby 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 Derby 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. Derby 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 SYS raises the SQLException 4293A.

You cannot create a role with the name PUBLIC (after case normalization). PUBLIC is a reserved authorization identifier. An attempt to create a role with the name PUBLIC raises SQLException 4251B.

Example of creating a role

CREATE ROLE purchases_reader;

Examples of invalid role names

CREATE ROLE public;   -- throws SQLException;
CREATE ROLE "PUBLIC"; -- throws SQLException;
CREATE ROLE sysrole;  -- throws SQLException;

Example of creating a role using a naming convention

The following example uses the convention of giving every role name the suffix _role.

CREATE ROLE purchases_reader_role;