trafodion-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From rmar...@apache.org
Subject [1/4] incubator-trafodion git commit: TRAFODION-2156 Update SQL reference manual for column level privileges
Date Mon, 28 Nov 2016 17:13:08 GMT
Repository: incubator-trafodion
Updated Branches:
  refs/heads/master bfa51f1cc -> 3b3166308


TRAFODION-2156 Update SQL reference manual for column level privileges


Project: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/commit/466048a8
Tree: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/tree/466048a8
Diff: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/diff/466048a8

Branch: refs/heads/master
Commit: 466048a8d52cb1cdc4c7f3546cf2fed448e4ec00
Parents: bbbd26e
Author: Roberta Marton <rmarton@edev07.esgyn.local>
Authored: Mon Nov 21 20:12:34 2016 +0000
Committer: Roberta Marton <rmarton@edev07.esgyn.local>
Committed: Mon Nov 21 20:12:34 2016 +0000

----------------------------------------------------------------------
 .../_chapters/sql_language_elements.adoc        |   3 +-
 .../src/asciidoc/_chapters/sql_statements.adoc  | 217 ++++++++++---------
 2 files changed, 121 insertions(+), 99 deletions(-)
----------------------------------------------------------------------


http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/466048a8/docs/sql_reference/src/asciidoc/_chapters/sql_language_elements.adoc
----------------------------------------------------------------------
diff --git a/docs/sql_reference/src/asciidoc/_chapters/sql_language_elements.adoc b/docs/sql_reference/src/asciidoc/_chapters/sql_language_elements.adoc
index 4bd94e8..535286f 100644
--- a/docs/sql_reference/src/asciidoc/_chapters/sql_language_elements.adoc
+++ b/docs/sql_reference/src/asciidoc/_chapters/sql_language_elements.adoc
@@ -3678,7 +3678,8 @@ privilege granted to the remaining role.
 the role. The only way to revoke any such privilege is to revoke the
 role from the user. For more information, see
 <<roles,Roles>> .
-
+* Privileges granted on an object can be for all the columns of the object or just a subset
of the columns. 
+Only the following subset of privileges is applicable at the column-level: INSERT, REFERENCES,
SELECT, and UPDATE.  
 
 You can manage privileges by using the GRANT and REVOKE statements.
 

http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/466048a8/docs/sql_reference/src/asciidoc/_chapters/sql_statements.adoc
----------------------------------------------------------------------
diff --git a/docs/sql_reference/src/asciidoc/_chapters/sql_statements.adoc b/docs/sql_reference/src/asciidoc/_chapters/sql_statements.adoc
index 2bc2a6d..91ac2a2 100644
--- a/docs/sql_reference/src/asciidoc/_chapters/sql_statements.adoc
+++ b/docs/sql_reference/src/asciidoc/_chapters/sql_statements.adoc
@@ -135,7 +135,7 @@ Use these statements to register users, create roles, and grant and revoke
privi
 | <<alter_user_statement,ALTER USER Statement>>                             
   | Changes attributes associated with a user who is registered in the database.
 | <<create_role_statement,CREATE ROLE Statement>>                           
   | Creates an SQL role.
 | <<drop_role_statement,DROP ROLE Statement>>                               
   | Deletes an SQL role.
-| <<grant_statement,GRANT Statement>>                                       
   | Grants access privileges on an SQL object to specified users or roles.
+| <<grant_statement,GRANT Statement>>                                       
   | Grants access privileges on an SQL object or an SQL objects' columns to specified users
or roles.
 | <<grant_component_privilege_statement,GRANT COMPONENT PRIVILEGE Statement>>
  | Grants one or more component privileges to a user or role.
 | <<grant_role_statement,GRANT ROLE Statement>>                             
   | Grants one or more roles to a user.
 | <<register_user_statement,REGISTER USER Statement>>                       
   | Registers a user in the SQL database, associating the user's login name with a database
user name.
@@ -4775,7 +4775,8 @@ System Version 0.9.1. Expected Version 1.0.0.
 [[grant_statement]]
 == GRANT Statement
 
-The GRANT statement grants access privileges on an SQL object to specified users or roles.
+The GRANT statement grants access privileges on an SQL object and its columns to specified
users or roles.
+Privileges can be granted on the object, on one or more columns, or both.
 
 IMPORTANT: This statement works only when authentication and
 authorization are enabled in {project-name}. For more information, see
@@ -4784,30 +4785,34 @@ authorization are enabled in {project-name}. For more information,
see
 ```
 GRANT {privilege [,privilege]... |ALL [PRIVILEGES]} 
   ON [object-type] [schema.]object
-  TO {grantee [,grantee ]...} 
-    [WITH GRANT OPTION]
-    [[granted] by grantor]
+  TO {grantee} 
+  [WITH GRANT OPTION]
+  [[GRANTED] BY grantor]
 
 privilege is: 
-    select
-  | delete
-  | insert
-  | references
-  | update
-  | execute
-  | usage
+    DELETE
+  | EXECUTE
+  | INSERT     [column-list]
+  | REFERENCES [column-list]
+  | SELECT     [column-list]
+  | UPDATE     [column-list]
+  | USAGE
 
 object-type is: 
-    table
-  | procedure
-  | library
-  | function
+    FUNCTION
+  | PROCEDURE
+  | LIBRARY
+  | SEQUENCE 
+  | TABLE
 
 grantee is:
     auth-name
 
 grantor is:
-   role-name
+    role-name
+
+column-list is:
+    (column [,colummn] ...)
 
 ```
 
@@ -4816,59 +4821,67 @@ grantor is:
 
 * `_privilege_ [,_privilege_ ] &#8230; | all [privileges]`
 +
-specifies the privileges to grant. you can specify these privileges for an object.
+Specifies the privileges to grant. You can specify these privileges for an object.
 +
 [cols="25%,75%"]
 |===
-| select     | can use the select statement.
-| delete     | can use the delete statement.
-| insert     | can use the insert statement.
-| references | can create constraints that reference the object.
-| update     | can use the update statement on table objects.
-| execute    | can execute a stored procedure using a call statement or can execute a user-defined
function (UDF).
-| usage      | can access a library using the create procedure or create function statement.
this privilege provides you 
-with read access to the library’s underlying library file.
-| all        | all the applicable privileges. when you specify all for a table or view, this
includes the select, delete, 
-insert, references, and update privileges. when the object is a stored procedure or user-defined
function (UDF), only the 
-execute privilege is applied. when the object is a library, only the update and usage privileges
are applied.
+| DELETE                   | Can use the delete statement.
+| EXECUTE                  | Can execute a stored procedure using a call statement or can
execute a user-defined function (UDF).
+| INSERT     [column-list] | Can use the insert statement.
+| REFERENCES [column-list] | Can create constraints that reference the object.
+| SELECT     [column-list] | Can use the select statement.
+| UPDATE     [column-list] | Can use the update statement on table objects.
+| USAGE                    | For libraries, can access a library using the create procedure
or create 
+function statement. This privilege provides you with read access to the library’s underlying
library 
+file. For sequences, can use the sequence in a SQL statement.
+| ALL                      | All the applicable privileges. When you specify all for a table
or view, 
+this includes the select, delete, insert, references, and update privileges. When the object
is a 
+stored procedure or user-defined function (UDF), only the execute privilege is applied. When
the 
+object is a library, only the update and usage privileges are applied. When the object is
a sequence
+generator, only the usage privilege is applied.
 |===
 
-* `on [_object-type_] [_schema_.]_object_`
+* `ON [_object-type_] [_schema_.]_object_`
 +
-specifies an object on which to grant privileges. _object-type_ can be:
+Specifies an object on which to grant privileges. _object-type_ can be:
 
-** `[table] [_schema_.]_object_`, where _object_ is a table or view. see <<database_object_names,database
object names>>.
-** `[procedure] [_schema_.]_procedure-name_`, where _procedure-name_ is the name of a stored
procedure in java (SPJ) 
+** `[FUNCTION] [_schema_.]_function-name_`, where _function-name_ is the name of a user-defined
function (UDF) in the database.
+** `[LIBRARY] [_schema_.]_library-name_`, where _library-name_ is the name of a library object
in the database.
+** `[PROCEDURE] [_schema_.]_procedure-name_`, where _procedure-name_ is the name of a stored
procedure in java (SPJ) 
 registered in the database.
-** `[library] [_schema_.]_library-name_`, where _library-name_ is the name of a library object
in the database.
-** `[function] [_schema_.]_function-name_`, where _function-name_ is the name of a user-defined
function (UDF) in the database.
+** `[SEQUENCE] [_schema_.]_sequence-name_`, where _sequence-name_ is the name of a sequence
object in the database.
+** `[TABLE] [_schema_.]_object_`, where _object_ is a table or view. see <<database_object_names,database
object names>>.
 
-* `to {_grantee_ [, _grantee_] &#8230; }`
+* `TO {_grantee_ &#8230; }`
 +
-specifies one or more _auth-names_ to which you grant privileges.
+Specifies one or more _auth-names_ to which you grant privileges.
 
 * `_auth-name_`
 +
-specifies the name of an authorization id to which you grant privileges. see <<authorization_ids,authorization
ids>>. 
-the authorization id must be a registered database username, an existing role name, or public.
the name is a regular 
+Specifies the name of an authorization id to which you grant privileges. see <<authorization_ids,authorization
ids>>. 
+The authorization id must be a registered database username, an existing role name, or public.
the name is a regular 
 or delimited case-insensitive identifier. see <<case_insensitive_delimited_identifiers,case-insensitive
delimited identifiers>>.
-if you grant a privilege to public, the privilege remains available to all users, unless
it is later revoked from public.
+If you grant a privilege to public, the privilege remains available to all users, unless
it is later revoked from public.
 
-* `with grant option`
+* `WITH GRANT OPTION`
 +
-specifies that the _auth-name_ to which a privilege is granted may in turn grant the same
privilege to other users or roles.
+Specifies that the _auth-name_ to which a privilege is granted may in turn grant the same
privilege to other users or roles.
 
-* `[granted] by _grantor_`
+* `[GRANTED] BY _grantor_`
 +
-allows you to grant privileges on behalf of a role. if not specified, the privileges will
be granted on your behalf as 
+Allows you to grant privileges on behalf of a role. If not specified, the privileges will
be granted on your behalf as 
 the current user/grantor.
 
 * `_role-name_`
 +
-specifies a role on whose behalf the grant operation is performed. to grant the privileges
on behalf of a role, you must 
+Specifies a role on whose behalf the grant operation is performed. To grant the privileges
on behalf of a role, you must 
 be a member of the role, and the role must have the authority to grant the privileges; that
is, the role must have been 
 granted the privileges with grant option.
 
+* `_column-list_`
++
+Specifies the list of columns to grant the requested privilege to.
+
 [[grant_considerations]]
 === Considerations for GRANT
 
@@ -4892,11 +4905,11 @@ an error.
 [[grant_examples]]
 === Examples of GRANT
 
-* To grant SELECT and DELETE privileges on a table to two specified users:
+* To grant column level SELECT and object level DELETE privileges on a table:
 +
 ```
-GRANT SELECT, DELETE ON TABLE invent.partloc
-  TO ajones, "MO.Neill@company.com";]
+GRANT SELECT (part_no, part_name), DELETE ON TABLE invent.partloc
+  TO "MO.Neill@company.com";]
 ```
 
 * To grant SELECT privileges on a table to a user:
@@ -4940,33 +4953,40 @@ specifies one or more component privileges to grant. The comma-separated
list ca
 | Component      | Component Privilege    | Description
 | SQL_OPERATIONS | ALTER                  | Privilege to alter database objects
 // |                | ALTER_LIBRARY          | Privilege to alter libraries
+|                | ALTER_SCHEMA           | Privilege to alter schemas
+|                | ALTER_SEQUENCE         | Privilege to alter sequence generators
 |                | ALTER_TABLE            | Privilege to alter tables
 |                | ALTER_VIEW             | Privilege to alter views
 |                | CREATE                 | Privilege to create database objects
-|                | CREATE_CATALOG         | Privilege to create catalogs in the database
 |                | CREATE_INDEX           | Privilege to create indexes
 |                | CREATE_LIBRARY         | Privilege to create libraries in the database
-|                | CREATE_ROUTINE         | Privilege to create stored procedures in Java
(SPJs), user-defined functions (UDFs), 
+|                | CREATE_PROCEDURE       | Privilege to create stored procedures in Java
(SPJs)
+|                | CREATE_ROUTINE         | Privilege to create user-defined functions (UDFs),

 table-mapping functions, and other routines in the database
 |                | CREATE_SCHEMA          | Privilege to create schemas in the database
+|                | CREATE_SEQUENCE        | Privilege to create sequence generators in the
database
 |                | CREATE_TABLE           | Privilege to create tables in the database
 |                | CREATE_VIEW            | Privilege to create views in the database
 |                | DROP                   | Privilege to drop database objects
-|                | DROP_CATALOG           | Privilege to drop catalogs
 |                | DROP_INDEX             | Privilege to drop indexes
 |                | DROP_LIBRARY           | Privilege to drop libraries
-|                | DROP_ROUTINE           | Privilege to drop stored procedures in Java (SPJs),
user-defined functions (UDFs), 
+|                | DROP_PROCEDURE         | Privilege to drop stored procedures in Java (SPJs)
+|                | DROP_ROUTINE           | Privilege to drop user-defined functions (UDFs),

 table-mapping functions, and other routines from the database
 |                | DROP_SCHEMA            | Privilege to drop schemas
+|                | DROP_SEQUENCE          | Privilege to drop sequence generators
 |                | DROP_TABLE             | Privilege to drop tables
 |                | DROP_VIEW              | Privilege to drop views
+|                | MANAGE_COMPONENT       | Privilege to perform component commands, such
as register, create, and grant privileges
 |                | MANAGE_LIBRARY         | Privilege to perform library-related commands,
such as creating and dropping libraries
 |                | MANAGE_LOAD            | Privilege to perform LOAD and UNLOAD commands
 |                | MANAGE_ROLES           | Privilege to create, alter, drop, grant, and
revoke roles
 |                | MANAGE_STATISTICS      | Privilege to update and display statistics
 |                | MANAGE_USERS           | Privilege to register or unregister users, alter
users, and grant or revoke 
 component privileges.
-|                | QUERY_CANCEL           | Privilege to cancel an executing query.
+|                | QUERY_ACTIVATE         | Privilege to activate and executing query after
it has been suspended
+|                | QUERY_CANCEL           | Privilege to cancel an executing query
+|                | QUERY_SUSPEND          | Privilege to suspend an executing query
 |                | SHOW                   | Privilege to run EXPLAIN, GET, INVOKE, and SHOW
commands. The SHOW privilege 
 has been granted to PUBLIC by default.
 |===
@@ -5794,6 +5814,7 @@ REGISTER USER "jsmith@company.com";
 == REVOKE Statement
 
 The REVOKE statement revokes access privileges on an SQL object from specified users or roles.
+Privileges can be revoked from the object, from one or more columns, or both.
 
 IMPORTANT: This statement works only when authentication and authorization are enabled in
{project-name}. For more information,
 {docs-url}/provisioning_guide/index.html#enable-security [Enable Secure {project-name}].
@@ -5802,102 +5823,102 @@ IMPORTANT: This statement works only when authentication and authorization
are e
 REVOKE [GRANT OPTION FOR]
    {privilege [,privilege]...| ALL [PRIVILEGES]}
    ON [object-type] [schema.]object
-   FROM {grantee [,grantee]...}
-   [[GRANTED] BY grantor] [RESTRICT | CASCADE]
+   FROM {grantee}
+   [[GRANTED] BY grantor] 
 
 privilege is:
-    SELECT
-  | DELETE
-  | INSERT
-  | REFERENCES
-  | UPDATE
-  | EXECUTE
+    DELETE
+  | EXECUTE 
+  | INSERT      [column-list]
+  | REFERENCES  [column-list]
+  | SELECT      [column-list]
+  | UPDATE      [column-list]
   | USAGE
 
 object-type is:
-    TABLE
-  | PROCEDURE
+    FUNCTION
   | LIBRARY
-  | FUNCTION
+  | PROCEDURE
+  | SEQUENCE
+  | TABLE
 
 grantee is:
     auth-name
 
 grantor is:
     role-name
+
+column-list is:
+    (column [,column]...)
+
 ```
 
 === Syntax Description of REVOKE
 
 * `GRANT OPTION FOR`
 +
-specifies that the grantee’s authority to grant the specified privileges to other users
or roles (that is, WITH GRANT
+Specifies that the grantee’s authority to grant the specified privileges to other users
or roles (that is, WITH GRANT
 OPTION) be revoked. This is an optional clause. When this clause is specified, only the ability
to grant the privilege
 to another user is revoked.
 
 * `_privilege_ [, _privilege_ ] &#8230; | ALL [ PRIVILEGES ]`
 +
-specifies the privileges to revoke. You can specify these privileges for an object:
+Specifies the privileges to revoke. You can specify these privileges for an object:
 +
 [cols="20%,80%"]
 |===
-| SELECT       | Revokes the ability to use the SELECT statement.
-| DELETE       | Revokes the ability to use the DELETE statement.
-| INSERT       | Revokes the ability to use the INSERT statement.
-| REFERENCES   | Revokes the ability to create constraints that reference the object.
-| UPDATE       | Revokes the ability to use the UPDATE statement.
-| EXECUTE      | Revokes the ability to execute a stored procedure using a CALL statement
or revokes the ability
+| DELETE                   | Revokes the ability to use the DELETE statement.
+| EXECUTE                  | Revokes the ability to execute a stored procedure using a CALL
statement or revokes the ability
+| INSERT     [columm-list] | Revokes the ability to use the INSERT statement.
+| REFERENCES [column-list] | Revokes the ability to create constraints that reference the
object.
+| SELECT     [column-list] | Revokes the ability to use the SELECT statement.
+| UPDATE     [column-list] | Revokes the ability to use the UPDATE statement.
 to execute a user-defined function (UDF).
-| USAGE        | Revokes the ability to access a library using the CREATE PROCEDURE or CREATE
FUNCTION statement.
+| USAGE                    | Revokes the ability to access a library using the CREATE PROCEDURE
or CREATE FUNCTION statement.
 Revokes read access to the library’s underlying library file.
-| ALL          | Revokes the ability to use all privileges that apply to the object type.
-When you specify ALL for a table or view, this includes the SELECT, DELETE, INSERT, REFERENCES,
and UPDATE
-privileges. When the object is a stored procedure or user-defined function (UDF), this includes
the EXECUTE privilege.
-When the object is a library, this includes the UPDATE and USAGE privileges.
+| ALL                      | Revokes the ability to use all privileges that apply to the
object type.
+When you specify ALL for a table or view, this includes the SELECT, DELETE, INSERT, REFERENCES,
and 
+UPDATE privileges. When the object is a stored procedure or user-defined function (UDF),
this includes 
+the EXECUTE privilege.  When the object is a library, this includes the UPDATE and USAGE
privileges. 
+When the object is a sequence generator, only the usage privilege is applied.
 |===
 
 * `ON [_object-type_] [_schema_.]_object_`
 +
-specifies an object on which to grant privileges. _object-type_ can be:
+Specifies an object on which to grant privileges. See <<database_object_names,"Database
Object Names>>. _object-type_ can be:
 
-** [TABLE] [_schema_.]_object_, where _object_ is a table or view. See <<database_object_names,Database
Object Names>>.
-** [PROCEDURE] [_schema_.]_procedure-name_, where _procedure-name_ is the name of a stored
procedure in Java (SPJ)
-registered in the database. See <<database_object_names,"Database Object Names>>.
-** [LIBRARY] [_schema_.]_library-name_, where _library-name_ is the name of a library object
in the database. See
-<<database_object_names,"Database Object Names>>,
 ** [FUNCTION] [_schema_.]_function-name_, where _function-name_ is the name of a user-defined
function in the database.
-See <<database_object_names,"Database Object Names>>
+** [LIBRARY] [_schema_.]_library-name_, where _library-name_ is the name of a library object
in the database. 
+** [PROCEDURE] [_schema_.]_procedure-name_, where _procedure-name_ is the name of a stored
procedure in Java (SPJ)
+registered in the database. 
+** [SEQUENCE] [_schema_.]_sequence-name_, where _sequence-name_ is the name of a sequence
generator.
+** [TABLE] [_schema_.]_object-name_, where _object-name_ is the name of a table or view.

 
 * `FROM {_grantee_ [,_grantee_] &#8230; }`
 +
-specifies an _auth-name_ from which you revoke privileges.
+Specifies an _auth-name_ from which you revoke privileges.
 
 * `_auth-name_`
 +
-specifies the name of an authorization ID from which you revoke privileges. See <<authorization_ids,Authorization
IDs>>.
+Specifies the name of an authorization ID from which you revoke privileges. See <<authorization_ids,Authorization
IDs>>.
 The authorization ID must be a registered database username, existing role name, or PUBLIC.
The name is
 a regular or delimited case-insensitive identifier. See
 <<case_insensitive_delimited_identifiers,Case-Insensitive Delimited Identifiers>>.
 
 * `[GRANTED] BY _grantor_`
 +
-allows you to revoke privileges on behalf of a role. If not specified, the privileges will
be revoked
+Allows you to revoke privileges on behalf of a role. If not specified, the privileges will
be revoked
 on your behalf as the current user/grantor.
 
 * `_role-name_`
 +
-specifies a role on whose behalf the GRANT operation was performed. To revoke the privileges
on behalf of a role,
+Specifies a role on whose behalf the GRANT operation was performed. To revoke the privileges
on behalf of a role,
 you must be a member of the role, and the role must have the authority to revoke the privileges;
 that is, the role must have been granted the privileges WITH GRANT OPTION.
 
-* `[RESTRICT | CASCADE]`
+* `_column-list_`
 +
-If you specify RESTRICT, the REVOKE operation fails if any privileges were granted or any
objects were created
-based upon the specified privileges.
-
-If you specify CASCADE, any such dependent privileges and objects are removed as part of
the REVOKE operation.
-
-The default value is RESTRICT.
+Specifies the list of columns to revoke the requested privilege from.
 
 [[revoke_considerations]]
 === Considerations for REVOKE
@@ -5916,16 +5937,16 @@ When you specify the CASCADE option, all objects that were created
based upon th
 [[revoke_examples]]
 === Examples of REVOKE
 
-* To revoke the privilege to grant SELECT and DELETE privileges on a table from a user:
+* To revoke GRANT OPTION for column level SELECT and table level DELETE privileges on a table
from a user:
 +
 ```
-REVOKE GRANT OPTION FOR SELECT, DELETE ON TABLE invent.partloc FROM jsmith;
+REVOKE GRANT OPTION FOR SELECT (part_no, part_name), DELETE ON TABLE invent.partloc FROM
jsmith;
 ```
 
 * To revoke the privilege to grant SELECT and DELETE privileges on a table from a user and
a role:
 +
 ```
-REVOKE GRANT OPTION FOR SELECT, DELETE ON TABLE invent.partloc FROM jsmith, clerks;
+REVOKE SELECT (part_no, part_name), DELETE ON TABLE invent.partloc FROM jsmith, clerks;
 ```
 
 * To revoke a user’s SELECT privileges on a table:


Mime
View raw message