SQL create role statement

Last update on August 19 2022 21:51:36 (UTC/GMT +8 hours)

SQL Role

CREATE ROLE creates a set of privileges which may be assigned to users of a database. Once a role is assigned to a user, (s)he gets all the Privileges of that role. By creating and granting roles, best means of database security can be practiced.

SQL Syntax:

CREATE ROLE role_name [WITH ADMIN {CURRENT_USER | CURRENT_ROLE}]

Parameters:

Name	Description
role_name	A name to identify the role.

Explanation:

With the above syntax, a role with role_name is created and immediately assigned to the current user or the currently active role is passed on to other users. The default usage is WITH ADMIN CURRENT_USER.

Platform specific support

The above syntax is not supported in DB2, MySQL, PostgreSQL and SQL Server. It is supported in Oracle but with variations.

Oracle Syntax:

{CREATE | ALTER} ROLE role_name [NOT IDENTIFIED | 
IDENTIFIED {BY password | EXTERNALLY | GLOBALLY |        
USING package_name}]

Parameters:

Name	Description
role_name	A name to identify the role.
password	Creates a local role authenticated by the string value of the password. Only single-byte characters are allowed in the password even when using a multibyte character set.
package_name	Creates an application role that enables a role only through an application that uses a PL/SQL package of package_name. If you omit the schema, Oracle assumes that the package is in your schema.

Outputs of the said SQL statement shown here is taken by using Oracle Database 10g Express Edition.

See our Model Database

Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.

Previous: Change passwords
Next: Putting text in query output