SQL create users

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

Create Users

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.

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

Previous: Controlling Transactions
Next: Change passwords