授予

授予 - 定义访问权限。

句法

授予 rolename TO username [[NOT] DEFAULT] [ WITH 授予 OPTION ]

授予 { ALTER | 执行 } ON PROCEDURE procname TO { [用R] user | ROLE rolename } [, ...]

授予 { ALTER | 执行 } ON FUNCTION funcname[/argcount] TO { [用R] user | ROLE rolename } [, ...]

授予 { CREATE | ALTER } ON SCHEMA schemaname TO { [用R] user | ROLE rolename } [, ...]
    [ WITH 授予 OPTION ]

授予 privilege ON object TO { [用R] user | ROLE rolename } [, ...]

  where user is:
      username |PUBLIC

  where privilege is:
      选择||更新|删除|ALTER|授予|TRIGGER|{ALL[PRIVILEGES]}

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

描述

授予 defines access. The 授予 statement gives specific permissions on an object (a table, view or procedure) to one or more roles and/or users. The 授予 statement is also used to assign a role, that has been granted specific permissions, to a user. 如果有的话,这些权限将添加到已授予的那些。 It is recommended that you use the 授予 statement to grant privileges to roles (rather than to users) and then assign roles to users.

Nuodb定义了两个系统角色,可以如下授予用户:

ADMINISTRATOR

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

DBA

A user granted the DBA role can create a stored procedure (see 创建程序)。

您不必向对象的创建者授予权限,因为默认情况下创建者具有所有权限。 创作者可以选择为安全撤销他们自己的一些特权。

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

  • 在该模式中创建新的数据库对象。

  • 下降那个架构。

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

  • Specify 授予 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 关于数据库访问和安全性.

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

    • 它不授予创建角色,架构或用户对象的权限。

    • 它不会影响函数和程序。 Only users assigned the DBA role have permission to create functions and procedures.

    • 它不会影响索引。 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.

    • 它不会影响触发器。 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 授予 ALTER ON SCHEMA to give permission to a user to drop the specified schema.

Use the 撤销 statement (see 撤销)从角色和/或用户撤消访问权限。

要授予仅限几列的权限,必须创建包含感兴趣列的视图,然后授予该视图的权限。

参数

rolename

授予权限的角色的名称。 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 创建角色)。

[NOT] DEFAULT

角色可以是活动或非活动的。

  • 活动角色是授予用户的角色,其权限当前是可访问的,并且可以通过该用户使用。

  • 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 放 ROLE rolename [ ACTIVE | INACTIVE ].

  • 默认情况下,向用户授予角色会导致该用户处于活动的角色。

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

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

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

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

WITH GRANT OPTION

默认情况下,向用户授予角色不会给用户授权将该角色授予他人的权限。

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 授予 privileges apply.

username

授予权限的用户。

schemaname

Schema in which 授予 privileges apply to all tables and views.

For the 授予 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 授予 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.

例子

例1:

创建用户定义的角色。授予权限到一个角色。授予用户的角色。

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

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

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

/* Assign Privileges to roles or users */
授予 ALL ON Salaries TO ROLE HR_Manager;
授予 选择,删除 ON Salaries TO ROLE HR_Clerk;
授予 选择 ON Salaries TO 用R beta;

/* Grant Roles to Users */
授予 HR_Manager TO alpha;
授予 HR_Clerk TO delta;
例2:

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

CREATE 用R admin2 password '1234';
授予 SYSTEM.DBA TO admin2 DEFAULT;
授予 SYSTEM.ADMINISTRATOR TO admin2 DEFAULT;