Grant direct privileges

User privileges are classified into direct and indirect privileges. Direct privileges are the system or object privileges that are granted to a user. Indirect privileges are those that are granted to roles of a user. Most operations can be performed with direct or indirect privileges. This topic describes how to grant direct privileges to users.

Direct privileges are required for the following scenarios:

• When creating a view, the privileges to access the objects in the view are required.
• When executing statements in a named PL block with the definers’ rights, the privileges for the statements are required.

Prerequisites

• When granting an object privilege, you must have the GRANT OPTION or GRANT ANY OBJECT PRIVILEGE privilege, and you must be the owner of the object or have the privileges to be granted. For example, if the test1 user wants to grant the SELECT privilege on table t1 to the test2 user, the test1 user must have the GRANT OPTION or GRANT ANY OBJECT PRIVILEGE privilege, and must be the owner of the object or have the SELECT privilege on table t1.

• When granting a system privilege or a role, you must have the privilege or role to be granted and have the GRANT OPTION, GRANT ANY PRIVILEGE, or GRANT ANY ROLE privilege.

For information about how to view your privileges, see View user privileges. If you do not have the required privileges, contact the administrator to obtain the privileges. For information about how to view your roles, see View roles.

Considerations

When you grant privileges, note the following:

• When granting multiple privileges to a user at the same time, separate the privileges with commas (,).

• If a user has been granted a privilege, the privilege takes effect only after the user is reconnected to OceanBase Database.

Examples

• Grant system privileges

  To grant the CREATE SEQUENCE privilege to the test user, execute the following statement:

  obclient> GRANT CREATE SEQUENCE TO test;
  

• Grant object privileges

  To grant the SELECT and UPDATE privileges on the emp_view view to the test user, execute the following statement:

  obclient> GRANT SELECT, UPDATE ON emp_view TO test;
  

For more information about the GRANT statement, see GRANT.