w3resource
SQL Tutorial

SQL create role statement

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

Practice SQL Exercises

Want to improve the above article? Contribute your Notes/Comments/Examples through Disqus.

Previous: Change passwords
Next: Putting text in query output