Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 17 » SQL Anywhere Server - SQL Reference » SQL statements » Alphabetical list of SQL statements

CREATE ROLE statement

Creates or replaces a role, creates a user-extended role, or modifies administrators for a role.

CREATE [ OR REPLACE ] ROLE { role-name | FOR USER userid }
[ WITH ADMIN [ ONLY ] administrator-userid [,...] ]
  • OR REPLACE clause

    Use this clause to create the role if it does not already exist or replace its administrators if it does exist.

  • role-name

    Use this parameter to specify the name of the role. This name must be unique across all users and roles in the database.

  • FOR USER userid clause

    Use this clause to convert the specified user into a user-extended role that can be assigned to others. The user must not already be extended as another role.

  • WITH ADMIN and WITH ADMIN ONLY administrator-userid clause

    Optionally specify administrators for the role. WITH ADMIN means that administrator-userid can exercise the role and administer it. WITH ADMIN ONLY means that administrator-userid can only administer the role. If no clause is specified, then any user with the MANAGE ROLES system privilege can administer the role.

    The min_role_admins database option controls the minimum number of administrators required for each role. If you do not specify enough administrators when creating the role, the statement returns an error.


The name of the new role must not begin and end with 'SYS_' and '_ROLE', respectively. For example SYS_MyBackup_ROLE is not an acceptable name for a user-defined role, whereas MyBackup_ROLE and SYS_MyBackup are acceptable.

If an ADMIN clause is specified, only the specified users can administer the roles. If no ADMIN clause is specified, then by default the role is granted to the MANAGE ROLES system privilege, with administrative rights only. This means that global administrators can administer the role.

To create a user-extended role (that is, to extend a user to be a role), use the CREATE ROLE FOR USER userid syntax.

Use the GRANT statements to grant system privileges to the role.


You must have the MANAGE ROLES system privilege to create a new role.

If the OR REPLACE clause is specified and the role already exists, you must also have administrative rights over the role.

Side effects


  • ANSI/ISO SQL Standard

    Not in the standard.


The following statement creates the Sales role. Any user with the MANAGE ROLES system privilege can administer the role.


The following statement extends user JaneSmith to become a role that can be assigned to others.


The following statement creates the role Finance with MaryJones and JeffTurkott as role administrators with administrative rights (only) for the role.

WITH ADMIN ONLY MaryJones, JeffTurkott;

The following example replaces the existing Finance role created in the previous example, replacing MaryJones and JeffTurkott with EllenChong and DaveLexx as role administrators, this time with exercise rights to the role.

WITH ADMIN EllenChong, DaveLexx;