CREATE ROLE statement
Derby Reference Manual
36
Example
CREATE PROCEDURE SALES.TOTAL_REVENUE(IN S_MONTH INTEGER,
IN S_YEAR INTEGER, OUT TOTAL DECIMAL(10,2))
PARAMETER STYLE JAVA READS SQL DATA LANGUAGE JAVA EXTERNAL NAME
'com.example.sales.calculateRevenueByMonth'
CREATE ROLE statement
The CREATE ROLE statement allows you to create an SQL role.
For more information on roles, see "Using SQL roles" in the Derby Developer's Guide.
Syntax
Before you issue a CREATE ROLE statement, verify that the
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;
