User privileges

{question}

What is the way to grant a user to create new users and schemas?

{question}

{answer}

Users must be granted the CREATE USER or CREATE SCHEMA privilege by the DBA to be able to create new users and schema.

For user grants, the DBA needs to set the GRANT CREATE on SYSTEM.USERS view.
For schema grants, the DBA needs to set the GRANT CREATE on the table SYSTEM.SCHEMAS.

  • Example: (NuoDB 4.0.2-4)
    This example shows how to grant a DB user “accounting_manager” to create a schema accounting, and DB accounts for employees.

(connected as dba):

CREATE USER accounting_manager password 'pass1234';
GRANT CREATE on system.users TO accounting_manager;
GRANT CREATE on system.schemas TO accounting_manager;

 

(connected as "accounting_manager" user):

CREATE SCHEMA accounting;
USE accounting;
CREATE USER accountant password 'pass5678'; 

(verify that you are able to connect as "accountant" DB user)

./nuosql test@localhost --user accountant --password pass5678
SQL>

 

Note: In case the grants are not granted on the system tables the following errors would be thrown when a user tries to create a new user or schema:

./nuosql test@localhost --user accountant --password pass5678

CREATE SCHEMA test
Error 58000: user ACCOUNTANT does not have create authority to Table SYSTEM.SCHEMAS

create user accountant2 password 'test1234';
Error 58000: user ACCOUNTANT does not have create authority to View SYSTEM.USERS


{answer}

Have more questions? Submit a request

Comments