GRANT

GRANT — define access privileges.

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 | 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

GRANT defines access. The GRANT statement gives specific permissions on an object (a table, view or procedure) to one or more roles and/or users. The GRANT statement is also used to assign a role, that has been granted specific permissions, to a user. These permissions are added to those already granted, if any. It is recommended that you use the GRANT statement to grant privileges to roles (rather than to users) and then assign roles to users.

NuoDB defines two system roles that can be granted to users as follows:

ADMINISTRATOR

A user granted the ADMINISTRATOR role has superuser privileges, which means the user has all privileges for all objects in the database except INSERT, UPDATE and ALTER on SYSTEM tables.

DBA

A user granted the DBA role can create a stored procedure (see CREATE PROCEDURE).

You do not have to grant privileges to the creator of an object, because the creator has all privileges by default. Creators can choose to revoke some of their own privileges for safety.

By default, only the creator of a schema and a user who has been assigned the DBA role can:

  • Create new database objects in that schema.

  • Drop that schema.

To give these privileges to another user, you must use the GRANT statement:

  • Specify GRANT CREATE ON SCHEMA to give permission to a user to create table, view, domain and sequence objects in the specified schema.

  • For an example of a query that lets you know which users have CREATE privileges on a schema, see About Database Access and Security.

  • The GRANT CREATE ON SCHEMA statement does not do the following:

    • It does not grant permission to create 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.

  • Specify GRANT ALTER ON SCHEMA to give permission to a user to drop the specified schema.

Use the REVOKE statement (see REVOKE) to revoke access privileges from roles and/or users.

To grant privileges to only a few columns, you must create a view that contains the columns of interest and then grant privileges to that view.

Parameters

rolename

Name of the role to which privileges are being granted. The rolename can be a user defined role (see Example 1) or one of the the NuoDB system roles (DBA or ADMINISTRATOR, see Example 2). The role must already exist (see CREATE ROLE).

[NOT] DEFAULT

A role can be active or inactive.

  • An active role is a role granted to the user, whose privileges are currently accessible and usable by that user.

  • An inactive role is a role granted to the user, but whose privileges are not currently usable by that user, but may be used in the future by the user calling SET ROLE rolename [ ACTIVE | INACTIVE ].

  • By default, granting a role to a user causes the role to be active for that user.

  • The same is true if the optional DEFAULT is specified in the GRANT statement.

  • If NOT DEFAULT is specified in the GRANT statement, the role is not active by default.

  • For the role to be active, the user must specify, SET ROLE rolename ACTIVE.

  • Each time the user starts a new session, the role will be inactive and the user must use SET ROLE to make it active again.

WITH GRANT OPTION

By default, granting a role to a user does not give that user the privilege of then granting that role to others.

Specifying WITH GRANT OPTION at the time of the grant, means that the grantee user has the privilege of granting that role to other users.

Similarly, granting CREATE or ALTER privileges on a schema does not give the specified user the ability to grant that privilege to others. Add WITH GRANT OPTION to give the additional permission.

procname

Stored procedure to which GRANT privileges apply.

username

User to whom privileges are granted.

schemaname

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

For the GRANT ALTER|CREATE ON SCHEMA schemaname statement, privileges are granted on the specified schema itself as opposed to the tables/views in the schema.

tablename

Table to which GRANT privileges apply. This can also specify a view.

PUBLIC

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

Examples

Example 1:

Create a user defined role. Grant privileges to a role. Grant 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;