GRANT

GRANT — Assign roles and access privileges to users.

Syntax

GRANT rolename TO username [[NOT] DEFAULT] [ WITH GRANT OPTION ]

GRANT { ALTER | EXECUTE } ON PROCEDURE procname TO { [USER] user | ROLE rolename } [, ...]

GRANT { ALTER | EXECUTE } ON FUNCTION funcname[/argcount] TO { [USER] user | ROLE rolename } [, ...]

GRANT CREATE on SYSTEM.SCHEMAS TO user

GRANT { CREATE | ALTER } ON SCHEMA schemaname TO { [USER] user | ROLE rolename } [, ...]
    [ WITH GRANT OPTION ]

GRANT privilege ON object TO { [USER] user | ROLE rolename } [, ...]

  where user is:
      username |PUBLIC

  where privilege is:
      SELECT|INSERT|UPDATE|DELETE|ALTER|GRANT|TRIGGER|{ALL[PRIVILEGES]}

  and object is:
      ALL TABLES IN SCHEMA schemaname|
      [TABLE]tablename|
      VIEW viewname|
      ALL SEQUENCES IN SCHEMA schemaname|
      SEQUENCE sequencename
      USER username

Description

The GRANT statement is used to perform the following:

Assign an existing role to a user.
Define the privilege granted to a user or role. These permissions are added to those already granted.
Give specific access permissions to users or roles on an object (table, view, or procedure).

ADMINISTRATOR and DBA are the two system roles defined in NuoDB. A user assigned with the DBA role or the creator of the schema has the privilege to create new database objects in the schema or drop the schema. All other users require the CREATE SCHEMA privilege on the SCHEMAS table to create new schemas and the CREATE USER privilege on the USERS table to create a new user.

Only a user granted the DBA role can give these privileges to other users using the GRANT statement.

Use GRANT CREATE ON SCHEMA to give permission to a user to create tables, views, or sequence objects in the specified schema.

The characteristics of the GRANT CREATE ON SCHEMA statement are the following:
- It does not grant permission to create a role, schema, or user objects.
- It does not affect functions and procedures. Only users assigned the DBA role have permission to create functions and procedures.
- It does not affect indexes. A user can create an index on a table for which the user has ALTER privileges. The user does not need CREATE privileges on a schema to create indexes on tables in that schema.
- It does not affect triggers. A user can create a trigger on a table for which the user has TRIGGER privileges. The user does not need CREATE privileges on a schema to create triggers on tables in that schema.
Use GRANT ALTER ON SCHEMA to give permission to a user to drop the specified schema.
To create a new schema, a user must have the required permission. For example, to allow user1 to create new schemas:
```
GRANT CREATE on System.SCHEMAS TO user1;
```
The creator of a schema has permissions to create, modify, or delete objects in that schema. Other users must be granted permission to access or modify objects in a schema.

Access can be granted only to the SYSTEM.SCHEMAS system table.

To revoke access privileges from roles or users, use the REVOKE statement.
Instead of directly granting privileges to the users, create a role and grant privileges to the role using the GRANT privilege statement. Then assign the role to the users using the GRANT rolename statement. This method of granting privileges to users by first granting privileges to roles and then assigning the roles to the users, is the best practice for managing user privileges.
For example, all users in the Sales Department are granted the sales_dept role. The sales_dept role has privileges to access a table called sales_orders.
For more information on using database identifiers with special characters, see Rules for Specifying NuoDB Identifier Names.

Parameters

rolename

Name of the role to which privileges are being granted. rolename can be a user-defined role or one of the NuoDB system roles such as ADMINISTRATOR or DBA. For more information, see Privileges of the Database Administrator.

The role must already exist before assigning it to a user. For more information on how to create a role, see the CREATE ROLE statement.

[NOT] DEFAULT

A role is either active or inactive.

An active role is a role granted to the user whose privileges are accessible and usable by the user immediately. By default, when a role is granted to a user, the role is active. The role is also active when the DEFAULT option is specified in the GRANT statement.

An inactive role is a role granted to the user whose privileges are not currently usable by the user. By default, if NOT DEFAULT is specified in the GRANT statement, the role is inactive.

To change role settings, use SET ROLE <role_name> [ ACTIVE | INACTIVE ]. For more information, see SET ROLE. Each time the user starts a new session, any role granted to the user with the NOT DEFAULT option, is started as inactive.

WITH GRANT OPTION

A user assigned to a role or granted with the CREATE or ALTER privileges on a schema cannot grant the privilege to other users.

WITH GRANT OPTION with the GRANT statement permits the user to grant these privileges to other users.

procname

Stored procedure to which the GRANT privileges apply.

funcname

The function to which the grant privileges apply.

If a function is overloaded (multiple function versions exist with the same name, each with a different number of arguments), then privileges must be granted to each overloaded function explicitly using the /argcount syntax.

For example, if two echo functions are defined with one and two string arguments respectively, then to grant access, use GRANT EXECUTE ON echo/1 TO USER1 and GRANT EXECUTE ON echo/2 TO USER1 to allow USER1 to invoke both functions.

username

User to whom privileges are granted.

schemaname

Schema in which the GRANT privileges apply to all tables and views.

For the GRANT ALTER|CREATE ON SCHEMA <schema_name> statement, privileges are granted on the specified schema and not to the tables or views in the schema.

tablename

Table to which the GRANT privileges apply.

Privileges can also be granted to specific columns. To grant privileges to only a few columns, create a view that contains the columns of interest and then grant privileges to that view. For more details, see the CREATE VIEW statement. For an example of a query to view the users with the CREATE privileges on a schema, see Example 5.

PUBLIC

The PUBLIC user. Granting a privilege or role to the PUBLIC user instead of a named user allows all users to inherit the privilege or role.

Examples

Example 1: Create a user-defined role, grant privileges to the role, and grant the role to a user.

/* create table to grant access */
CREATE TABLE Salaries (Name STRING, Hourly_Rate NUMBER);

/* Create two new users */
CREATE USER alpha password '1922';
CREATE USER delta password '6767';
CREATE USER beta password '3426';

/* Create two new roles */
CREATE ROLE HR_Manager;
CREATE ROLE HR_Clerk;

/* Assign privileges to roles or users */
GRANT ALL ON Salaries TO ROLE HR_Manager;
GRANT SELECT,DELETE ON Salaries TO ROLE HR_Clerk;
GRANT SELECT ON Salaries TO USER beta;

/* Grant roles to users */
GRANT HR_Manager TO alpha;
GRANT HR_Clerk TO delta;

Example 2: Grant both the system DBA and ADMINISTRATOR roles to a user.

CREATE USER admin2 password '1234';
GRANT SYSTEM.DBA TO admin2 DEFAULT;
GRANT SYSTEM.ADMINISTRATOR TO admin2 DEFAULT;