CREATE DATABASE ROLE¶
Create a new database role or replace an existing database role in the system.
After creating database roles, you can grant object privileges to the database role and then grant the database role to other database roles or account roles to enable access control security for objects in the system.
- See also:
GRANT <privileges>, GRANT DATABASE ROLE , GRANT OWNERSHIP , DROP DATABASE ROLE , ALTER DATABASE ROLE , SHOW DATABASE ROLES, CREATE <object> … CLONE
Syntax¶
CREATE [ OR REPLACE ] DATABASE ROLE [ IF NOT EXISTS ] <name>
[ COMMENT = '<string_literal>' ]
Required parameters¶
name
Specifies the identifier (i.e. name) for the database role; must be unique in the database in which the role is created.
The identifier must start with an alphabetic character and cannot contain spaces or special characters unless the entire identifier string is enclosed in double quotes (e.g.
"My object"
). Identifiers enclosed in double quotes are also case-sensitive.If the identifier is not fully qualified in the form of
db_name.database_role_name
, the command creates the database role in the current database for the session.For more details, see Identifier requirements.
Optional parameters¶
COMMENT = 'string_literal'
Specifies a comment for the database role.
Default: No value
Access control requirements¶
A role used to execute this SQL command must have the following privileges at a minimum:
Privilege |
Object |
Notes |
---|---|---|
CREATE DATABASE ROLE |
Database |
A role with the OWNERSHIP privilege on the database can grant the CREATE DATABASE ROLE privilege to another account role. |
For instructions on creating a custom role with a specified set of privileges, see Creating custom roles.
For general information about roles and privilege grants for performing SQL actions on securable objects, see Overview of Access Control.
Usage notes¶
When you create a database role, the USAGE privilege on the database that contains the database role is granted to the database role automatically.
Caution
Avoid recreating a database role (using the OR REPLACE keywords). Behind the scenes, recreating an object (using CREATE OR REPLACE <object>) first drops and then creates the object. Recreating a database role drops the database role from any shares that it is granted to. You must grant the database role to these shares again.
If you must recreate a database role, notify any data consumers of a share that includes the database role. They must grant the database role to their own account roles again.
Regarding metadata:
Attention
Customers should ensure that no personal data (other than for a User object), sensitive data, export-controlled data, or other regulated data is entered as metadata when using the Snowflake service. For more information, see Metadata fields in Snowflake.
Examples¶
Create database role dr1
in database d1
:
CREATE DATABASE ROLE d1.dr1;