CREATE USER

The CREATE USER statement may be used to add a new user to a NuoDB database. At the same time, with this SQL statement, you must either specify an initial password, or associate the user account with an external authentication system.

Syntax

CREATE USER username PASSWORD 'password'
CREATE USER username EXTERNAL

username: This is the name of the user account. Generally, the user name should be a string. If the user name given is not a string, or if it doesn’t have a qualifying prefix, it must be entered between double quotes ("username"), backticks (`username`), or square brackets ([username]). Incidentally, using quotes, backticks, or brackets for the user name does not preserve the case. Regardless of what you enter, all letters will be converted and stored in uppercase.

There are some restrictions on which characters are allowed for user names, as well as what names are not allowed. The rules for specifying NuoDB identifier names in general, also apply to user names (see Rules for Specifying NuoDB Identifier Names).
password: This is the user’s initial password. It must be given within single quotes. All users must be assigned a password, unless you’ve included the EXTERNAL option. In which case, a password will have already been given within an external system (i.e., LDAP).
EXTERNAL: This option allows you to create the NuoDB user from an account defined and authenticated by an external LDAP server. No password is given when using this option with the CREATE USER statement.

Creating a user with the EXTERNAL option will cause an entry to be added to the system tables SYSTEM.USERS and SYSTEM.PASSWORDS. Were you to look at the SYSTEM.PASSWORDS table, you would see that the FLAGS field indicates whether or not a user is authenticated by NuoDB or by an external LDAP server (see USERS System Table Description and PASSWORDS System Table Description).

Description

To protect the databases and data, NuoDB requires each user, each client connection to provide a user name and password. To create NuoDB users, you would use the SQL statement, CREATE USER. When doing so, for the user name, you may give either a name that will be maintained and authenticated internally by NuoDB, or one that will be authenticated using an LDAP server.

Using external authentication would allow you to minimize administrative duties, since users will use their existing accounts to access NuoDB databases. Keep in mind that if a user already exists in NuoDB, you won’t be able to create an external user with the same name. Doing so could cause conflicts, especially if the NuoDB user is someone other than the LDAP user.

With the CREATE USER statement, in addition to creating a user, you also specify the user’s initial password. The password can be changed later by using the ALTER USER statement (see ALTER USER).

Creating a user account merely gives a user access to the database. It doesn’t give them any privileges; they don’t have the ability to insert or delete data, nor permission to even see data. Privileges for an entire database, as well as specific tables, can be granted to a user with the GRANT statement (see GRANT).

Instead of directly granting users privileges, it’s recommended that you create roles and that privileges be granted to those roles—not users. You can then grant those roles to multiple users. You do this—create roles and grant privileges to roles and assign roles to users—with the GRANT statement. This method of using roles, in lieu of granting privileges directly to users, is easier to maintain (e.g., all users in the Sales Department are granted a role you might call, sales_dept, which has privileges that allows access to a table you might call, sales_orders).

To revoke roles or privileges from users, you would use the REVOKE statement (see REVOKE). Should you decide to remove a user, use the DROP USER statement (see DROP USER).

Users must be granted the CREATE USER privilege by the DBA to enable them to create users.

For more information on using database identifiers with special characters, see Rules for Specifying NuoDB Identifier Names.

Examples

Below are some examples of how the CREATE USER statement might be deployed. These examples illustrate some of the possibilities, some of the deviations mentioned above.

CREATE USER winston_colridge
PASSWORD 'Evoke_Poltroon_379_Vitalism';

/* When logged in as winston_colridge */
SELECT CURRENT_USER FROM dual;
  [CURRENT_USER]
 ----------------
  WINSTON_COLRIDGE

In this example, notice that although the user name was entered with all lowercase letters, the results of the SELECT statement using the CURRENT_USER( ) function shows that the user name was converted and stored in all uppercase letters. When logging in, the user may use upper or lowercase letters for the user name.

Notice also that the initial password is long. A password may be up to 128 characters. The password also contains special characters (i.e., three underscores), as well as numbers. Although user names are not case sensitive, passwords are case sensitive. Let’s see how the user name and password for this user account might be given at the command-line for logging into the SQL client:

nuosql test@localhost --user winston_colridge --password evoke_poltroon_379_vitalism

Connection failed: Authentication failed

As you can see here above, the user wasn’t able to log into the SQL client because they entered all lowercase letters for the password, instead of the mix as given with the CREATE USER statement.

In this next example, the database administrator is utilizing an external authentication system (i.e., LDAP), rather than NuoDB’s internal system for authenticating this user account.

CREATE USER pbrady EXTERNAL;

Notice that a password was not given in the example here above. The password is instead maintained externally. If you were to give the password, an error would occur.

Let’s look at another example with an unusual user name, one containing only numbers:

CREATE USER 1138
PASSWORD 'thx_1971';

Error 42000: syntax error on line 1
CREATE USER 1138
            ^ unexpected 1138

CREATE USER "1138"
PASSWORD 'thx_1971';

In the example above, first we tried to create a user with the name 1138, which is fine in general. However, the user name is stored as a string. To be able to use only numbers, in the second attempt, we enclosed the user name within double quotes. That tells NuoDB to treat the numeric value as a string.

Related to this, let’s look at another user name that include numbers:

CREATE USER 1202Admin
PASSWORD 'io89764f';

Error 42000: syntax error on line 1
CREATE USER 1202Admin
            ^ Numeric literal must be followed by space or delimiter character

CREATE USER Admin1202
PASSWORD 'io89764f';

In the first attempt here above, we tried to create a user name that contains numbers and letters (i.e., 1202Admin). The problem isn’t that it contains numbers, but that it starts with a number. As shown in the previous example, enclosing the user name in double quotes will solve the problem. However, in the example here, we were successful by choosing a slightly different user name (i.e., Admin1202), which starts with a letter instead of a number. By doing this, NuoDB understands that the user name is a string and any numbers afterwards will be treated as such, rather than as a numeric. Therefore, quotes are not required around this user name.

For this next example, let’s consider using special characters contained within a user name:

CREATE USER Finance.Manager
PASSWORD 'lira_1861_2002';

Error 42000: syntax error on line 1
CREATE USER Finance.Manager
                   ^ expected EXTERNAL or PASSWORD got .

CREATE USER [Finance.Manager]
PASSWORD 'lira_1861_2002';

In this case, although special characters are allowed, using a dot within the user name confuses NuoDB. This is the format by which schema and table names are specified (e.g., bookstore.books). To include the dot within the user name, we need only enclose it within double quotes, backticks, or square brackets as shown here.