SQL create users
has average rating
8
out of 10.
Total 4 users rated.
Description
Following the standard SQL syntax for creating how to create users, we have discussed how to create 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 username. |
DB2 Syntax
GRANT privilege ON tablename TO USER username
Parameters
| Name | Description |
|---|---|
| privilege | A privilege (for example SELECT)you want to assign to the user. |
| tablename | 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 username. |
MySQL Syntax
CREATE USER username IDENTIFIED BY password IDENTIFIED WITH auth_plugin
Parameters
| Name | Description |
|---|---|
| username | A username. |
| password | Password for 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 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.
