GRANT

Description

The GRANT statement assigns privileges and roles to MatrixOne users and roles.

GRANT Overview

System permissions are those of the initial System account administrator ( The corresponding user is the root). The System account administrator can create and delete other accounts, and manage accounts. A System account administrator cannot manage other resources of other accounts.

To use GRANT to GRANT permissions to other users or roles, you must first have the WITH GRANT OPTION permissions and the permissions you are granting. Use the’ SHOW GRANTS’ statement to find out the grant status of your current or another role. For more information, see SHOW GRANTS.

The REVOKE statement is related to GRANT and enables administrators to remove account privileges. For more information on REVOKE, see REVOKE.

Normally, a cluster has one root by default, the root first uses CREATE ACCOUNT to create a new account and define its nonprivilege characteristics such as its password, then account uses CREATE USER to create an user and uses GRANT to define its privileges. ALTER ACCOUNT may be used to change the nonprivilege characteristics of existing accounts. ALTER USER is used to change the privilege characteristics of existing users. For more information on privileges supported by MatrixOne, see Privilege Control Types.

GRANT responds with Query OK, 0 rows affected when executed successfully. To determine what privileges result from the operation, use SHOW GRANTS

Syntax

  1. > GRANT
  2.     priv_type [(column_list)]
  3.       [, priv_type [(column_list)]] ...
  4.     ON [object_type] priv_level
  5.     TO user_or_role [, user_or_role] ...
  7. GRANT role [, role] ...
  8.     TO user_or_role [, user_or_role] ...
  9.     [WITH ADMIN OPTION]
  11. object_type: {
  12.     TABLE
  13.   | FUNCTION
  14.   | PROCEDURE
  15. }
  17. priv_level: {
  18.     *
  19.   | *.*
  20.   | db_name.*
  21.   | db_name.tbl_name
  22.   | tbl_name
  23.   | db_name.routine_name
  24. }

Explanations

The GRANT statement enables account to grant privileges and roles, which can be granted to users and roles. These syntax restrictions apply:

  • GRANT cannot mix granting both privileges and roles in the same statement. A given GRANT statement must grant either privileges or roles.

  • The ON clause distinguishes whether the statement grants privileges or roles:

    • With ON, the statement grants privileges.

    • Without ON, the statement grants roles.

    • It is permitted to assign both privileges and roles to an account, but you must use separate GRANT statements, each with syntax appropriate to what is to be granted.

To grant a privilege with GRANT, you must have the GRANT OPTION privilege, and you must have the privileges that you are granting.

Database Privileges

Database privileges apply to all objects in a given database. To assign database-level privileges, use ON db_name * syntax:

  1. grant all on database * to role1;

Table Privileges

Table privileges apply to all columns in a given table. To assign table-level privileges, use ON db_name.tbl_name syntax:

  1. grant all on table *.* to role1;

Granting Roles

GRANT syntax without an ON clause grants roles rather than individual privileges. A role is a named collection of privileges. For example:

  1. grant role3 to role_user;

Each role to be granted must exist, as well as each user account or role to which it is to be granted.

These privileges are required to grant roles:

  • You have the privilege to grant or revoke any role to users or roles.

Examples

  1. > drop user if exists user_prepare_01;
  2. > drop role if exists role_prepare_1;
  3. > create user user_prepare_01 identified by '123456';
  4. > create role role_prepare_1;
  5. > create database if not exists p_db;
  6. > grant create table ,drop table on database *.*  to role_prepare_1;
  7. Query OK, 0 rows affected (0.01 sec)
  9. > grant connect on account * to role_prepare_1;
  10. Query OK, 0 rows affected (0.01 sec)
  12. > grant insert,select on table *.* to role_prepare_1;
  13. Query OK, 0 rows affected (0.01 sec)
  15. > grant role_prepare_1 to user_prepare_01;
  16. Query OK, 0 rows affected (0.01 sec)