DEFAULT_ROLE Operator

The DEFAULT_ROLE operator evaluates to a string that contains the name of the default role that has been granted to the user who is running the session. This default role need not be currently enabled, but it must not have been revoked since the most recent GRANT DEFAULT ROLE statement that referenced the user or PUBLIC in the TO clause.

If no default role is explicitly defined for the current user, but PUBLIC has a default role, DEFAULT_ROLE returns the default role of PUBLIC.

If the user has no default role, or if the default role that was most recently granted to the user explicitly, or as PUBLIC, was subsequently revoked by the REVOKE DEFAULT ROLE statement, DEFAULT_ROLE returns a NULL value. If the user has been granted no default role individually, but a default role has been granted to PUBLIC, the DEFAULT_ROLE operator returns the name of this default role. If no default role is currently defined for the user nor for PUBLIC, however, DEFAULT_ROLE returns NULL.

The SET ROLE statement has no effect on the DEFAULT_ROLE operator, but any access privileges of the default role are not necessarily available to the user if SET ROLE has activated some other role, or if SET ROLE specified NULL or NONE as the current role of the user.

The next statements show how you might use the DEFAULT_ROLE operator:

select DEFAULT_ROLE from systables where tabid = 1;

DEFAULT_ROLE does not change the lettercase of the identifier of a role.

If you specify DEFAULT_ROLE as the default value for a column, the column must have a CHAR, VARCHAR, LVARCHAR, NCHAR, or NVARCHAR data type. Because the name of a role is an authorization identifier, truncation might occur if the column width is less than 32 bytes. (See Owner name for the syntax of authorization identifiers.)