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

Only the database owner can create a role.

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

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.

tries to avoid name collision between user names and role names, but this is not always possible, because 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 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. 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;