w3resource logo


>SQL CREATE USERS

SQL create users

Secondary Nav

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.



Join our Question Answer community to learn and share your programming knowledge.

Solve these problems:

Java: How to convert a string to an integer in Java?

C#: Loops in c#

SQL: JOIN using more than 5 tables