w3resource logo

:

SQL Tutorial

SQL create users

Description

Following the standard SQL syntax for creating how to create users, we have discussed how to create a user in different database platforms like DB2, Oracle, MySQL, PostgreSQL, and Microsoft SQL Server.

SQL Syntax:

GRANT CONNECT TO username IDENTIFIED BY password

Parameters:

Name Description
username A username.
password Password for a username.

DB2 Syntax

GRANT privilege ON table_name TO USER username

Parameters:

Name Description
privilege A privilege (for example SELECT)you want to assign to the user.
table_name Table on which you want to assign permission to the user.
username A username already existing in the underlying Operating System.

Oracle Syntax:

CREATE USER username IDENTIFIED BY password

Parameters:

Name Description
username A username.
password Password for the username.

MySQL Syntax:

CREATE USER username IDENTIFIED BY password 
IDENTIFIED WITH auth_plugin

Parameters:

Name Description
username A username.
password Password for the username.
auth_plugin Authorization plugin.

PostgreSQL Syntax:

CREATE USER username  WITH SYSID uid   | CREATEDB | NOCREATEDB 
| CREATEUSER | NOCREATEUSER  | IN GROUP groupname [, ...] | 
[ ENCRYPTED | UNENCRYPTED ]PASSWORD 'password' | VALID UNTIL 'time' 

Parameters:

Name Description
username A username.
uid The SYSID clause can be used to choose the PostgreSQL user ID of the new user.
CREATEDB NOCREATEDB These clauses define a user's ability to create databases. If CREATEDB is specified, the user being defined will be allowed to create his own databases. Using NOCREATEDB will deny a user the ability to create databases. If not specified, NOCREATEDB is the default.
CREATEUSER NOCREATEUSER These clauses determine whether a user will be permitted to create new users himself. CREATEUSER will also make the user a superuser, who can override all access restrictions. If not specified, NOCREATEUSER is the default.
groupname A name of an existing group into which to insert the user as a new member. Multiple group names may be listed.
password Password for the username.
ENCRYPTED UNENCRYPTED These key words control whether the password is stored encrypted in the system catalogs.
time The VALID UNTIL clause sets an absolute time after which the user's password is no longer valid. If this clause is omitted the password will be valid for all time.

SQL Server Syntax:

CREATE USER username [ { { FOR | FROM } { LOGIN loginname | 
CERTIFICATE cert_name | ASYMMETRIC KEY asym_key_name } | WITHOUT LOGIN ]
[ WITH DEFAULT_SCHEMA =schema_name ]

Parameters:

Name Description
username A username.
loginname Specifies the SQL Server login for which the database user is being created.
cert_name Specifies the certificate for which the database user is being created.
asym_key_name Specifies the asymmetric key for which the database user is being created.
schema_name Specifies the first schema that will be searched by the server when it resolves the names of objects for this database user.
WITHOUT LOGIN Specifies that the user should not be mapped to an existing login.

Here is a new document which is a collection of questions with short and simple answers, useful for learning SQL as well as for interviews.