CREATE USER
NAME
CREATE USER: make a user of the database (extension)
SYNOPSIS
<user definition> ::=
CREATE USER <authorization identifier> [ WITH ] PASSWORD <character string literal>
DESCRIPTION
CREATE USER makes a user: an authorization identifier that a client of the server may prove it is, by the password given. The database keeps no password, only a verifier made of it, from which the password cannot be found sooner than by guessing.
A user is a name a client signs in as; what it may do is the privileges granted to its authorization identifier, as to any. A session the host program of an embedded database starts may be of any identifier, a user or not. Every client of a server proves it is a user, unless the server's setting SERVER.TRUST_LOCAL is ON, not its default, which trusts a client of its own machine as whoever it says it is (Users).
The authorization identifier is an identifier: a regular one, such as
carol, is folded to upper case, CAROL; a delimited one, such as
"carol", is taken as written. INFORMATION_SCHEMA.USERS lists the users
to the owner, and to any other user itself alone.
CREATE USER is made at once, apart from any transaction: no ROLLBACK undoes it.
RULES
- The session has no transaction open: COMMIT or ROLLBACK first.
- Its user is the database's owner.
- No user has the identifier, and no role (see CREATE ROLE).
- The database has a credentials plug-in to make a verifier: a server always has one.
PRIVILEGES
The database's owner's alone.
ERRORS
| SQLSTATE | SQLCODE | When |
|---|---|---|
| 25000 | -917 | The session has a transaction open |
| 2A000 | -102 | PASSWORD, or its literal, is not written |
| 2A000 | -103 | A user, or a role, has the identifier |
| 2A000 | -104 | Its user is not the database's owner |
| TB910 | -910 | An embedded database has no credentials plug-in |
EXAMPLES
Run by ANN, the database's owner, of users ANN and BOB and the role CLERK:
CREATE USER CAROL WITH PASSWORD 'correct horse';
SELECT * FROM INFORMATION_SCHEMA.USERS ORDER BY USER_NAME;
COMMIT;
CREATE USER BOB PASSWORD 'x';
CREATE USER DAVE;
CREATE USER CLERK PASSWORD 'x';
OK
USER_NAME | IS_OWNER | CREATE_ROLE
--------------------------------------------------------------+----------+-------------
ANN | YES | YES
BOB | NO | NO
CAROL | NO | NO
(3 rows)
OK
turborel: SQLCODE -103 (SQLSTATE 2A000): a user BOB exists already (at character 12)
turborel: SQLCODE -102 (SQLSTATE 2A000): expected PASSWORD, found end of statement (at character 16)
turborel: SQLCODE -103 (SQLSTATE 2A000): CLERK is a role's name, which no user takes (at character 12)
Run by BOB:
CREATE USER DAVE PASSWORD 'x';
turborel: SQLCODE -104 (SQLSTATE 2A000): only the database's owner runs a user statement, and BOB is not it
STANDARD
An extension. Which identifiers are authorization identifiers is implementation-defined [ISO/IEC 9075:1992 §5.4]; neither SQL-86 nor SQL-92 has a statement that makes one.
SEE ALSO
ALTER USER, DROP USER, SET SESSION AUTHORIZATION, GRANT, CONNECT