GRANT

Syntax

   GRANT <system_privilege>,... TO <grantee> [WITH ADMIN OPTION]
 | GRANT <schema_privilege>,... ON SCHEMA <schema_name> TO <grantee> [WITH GRANT OPTION]
 | GRANT <object_privilege>,... ON <object_name> TO <grantee> [WITH GRANT OPTION]
 | GRANT <role_name>,... TO <grantee> [WITH ADMIN OPTION]
 | GRANT STRUCTURED PRIVILEGE <privilege_name> TO <grantee>

Syntax Elements

 <system_privilege> ::= AUDIT ADMIN                 | BACKUP ADMIN 
                       | CATALOG READ               | CREATE SCENARIO 
                       | CREATE SCHEMA              | CREATE STRUCTURED PRIVILEGE 
                       | DATA ADMIN                 | EXPORT 
                       | IMPORT                     | INIFILE ADMIN 
                       | LICENSE ADMIN              | LOG ADMIN 
                       | MONITOR ADMIN              | OPTIMIZER ADMIN 
                       | RESOURCE ADMIN             | ROLE ADMIN 
                       | SAVEPOINT ADMIN            | SCENARIO ADMIN 
                       | SERVICE ADMIN              | SESSION ADMIN 
                       | STRUCTUREDPRIVILEGE ADMIN  | TRACE ADMIN 
                       | TRUST ADMIN                | USER ADMIN
                       | VERSION ADMIN 
                       | <identifier>.<identifier>

System privileges are used to restrict administrative tasks. The table below describes the supported system privileges.

System Privilege	Privilege Purpose	Comment
AUDIT ADMIN	Controls the execution of the following auditing-related commands: CREATE AUDIT POLICY, DROP AUDIT POLICY and ALTER AUDIT POLICY and the changes of the auditing configuration.
BACKUP ADMIN	Authorizes the BACKUP command to define and initiate a backup process or to perform a recovery process.
CATALOG READ	Authorizes users to have unfiltered read-only access to all system and monitoring views.	Normally, the content of these views is filtered based on the privileges of the accessing user.
CREATE SCENARIO	Controls the creation of calculation scenarios and cubes (calculation database)
CREATE SCHEMA	Authorizes the creation of database schemas using the CREATE SCHEMA command.By default each user owns one schema, with this privilege the user is allowed to create additional schemas.
CREATE STRUCTURED PRIVILEGE	Authorizes the creation of Structured Privileges (Analytical Privileges)	Only the owner of an Analytical Privilege can further grant or revoke that privilege to other users or roles.
DATA ADMIN	Authorizes reading all data in the system and monitoring views. It also enables execution of any Data Definition Language (DDL) commands in the SAP HANA database	A user having this privilege cannot select or change data stored tables for which they do not have access privileges, but they can drop tables or modify table definitions.
EXPORT	Authorizes export activity in the database via the EXPORT TABLE command.Note that beside this privilege the user requires the SELECT privilege on the source tables to be exported.
IMPORT	Authorizes the import activity in the database using the IMPORT commands.	Note that beside this privilege the user requires the INSERT privilege on the target tables to be imported.
INIFILE ADMIN	Authorizes changing of system settings.
LICENSE ADMIN	Authorizes the SET SYSTEM LICENSE command install a new license.
LOG ADMIN	Authorizes the ALTER SYSTEM LOGGING [ON|OFF] commands to enable or disable the log flush mechanism.
MONITOR ADMIN	Authorizes the ALTER SYSTEM commands for EVENTs.
OPTIMIZER ADMIN	Authorizes the ALTER SYSTEM commands concerning SQL PLAN CACHE and ALTER SYSTEM UPDATE STATISTICS commands, which influence the behavior of the query optimizer.
RESOURCE ADMIN	This privilege authorizes commands concerning system resources, for example ALTER SYSTEM RECLAIM DATAVOLUME and ALTER SYSTEM RESET MONITORING VIEW. It also authorizes many of the commands available in the Management Console.
ROLE ADMIN	This privilege authorizes the creation and deletion of roles using the CREATE ROLE and DROP ROLE commands. It also authorizes the granting and revocation of roles using the GRANT and REVOKE commands.	Activated roles, meaning roles whose creator is the pre-defined user _SYS_REPO, can neither be granted to other roles or users nor dropped directly. Not even users having ROLE ADMIN privilege are able to do so. Please check documentation concerning activated objects.
SAVEPOINT ADMIN	Authorizes the execution of a savepoint process using the ALTER SYSTEM SAVEPOINT command.
SCENARIO ADMIN	Authorizes all calculation scenario-related activities (including creation).
SERVICE ADMIN	Authorizes the ALTER SYSTEM [START|CANCEL|RECONFIGURE] commands.	This privilege is for administering system services of the database
SESSION ADMIN	authorizes the ALTER SYSTEM commands concerning sessions to stop or disconnect a user session or to change session variables.
STRUCTUREDPRIVILEGE ADMIN	Authorizes the creation, reactivation, and dropping of structured privileges.
TRACE ADMIN	Authorizes the ALTER SYSTEM [CLEAR|REMOVE] TRACES commands for operations on database trace files and authorizes changing trace system settings.
TRUST ADMIN	Authorizes commands to update the trust store.
USER ADMIN	Authorizes the creation and modification of users using the CREATE USER, ALTER USER, and DROP USER commands.
VERSION ADMIN	Authorizes the ALTER SYSTEM RECLAIM VERSION SPACE command of the multi-version concurrency control (MVCC) mechanism.
<identifier>.<identifier>	Components of the SAP HANA database can create new system privileges. These privileges use the component-name as first identifier of the system privilege and the component-privilege-name as the second identifier.

 <schema_privilege> ::= CREATE ANY | DEBUG
                       | DELETE     | DROP 
                       | EXECUTE    | INDEX 
                       | INSERT     | SELECT 
                       | TRIGGER    | UPDATE

Schema privileges are used to restrict the access and modifications on a schema and the objects stored in this schema. The following schema privileges are defined:

CREATE ANY
This privilege allows the creation of all kinds of objects, in particular, tables, views, sequences, synonyms, SQLScript functions, or database procedures in a schema.

DELETE, DROP, EXECUTE, INDEX, INSERT, SELECT, UPDATE
The specified privilege is granted on every object stored in the specified schema currently and in future. For detailed description of the privileges please see the part describing object privileges below and please check below, which privileges are applicable for which kind of object.

 <object_privilege> ::= ALL PRIVILEGES | ALTER 
                       | DEBUG          | DELETE 
                       | DROP           | EXECUTE 
                       | INDEX          | INSERT 
                       | SELECT         | REFERENCES
                       | TRIGGER        | UPDATE
                       | <identifier>.<identifier>

Object privileges are used to restrict the access and modifications on database objects. Database objects are tables, views, sequences, procedures, etc. The table below describes the supported object privileges.

Object Privilege	Command Types	Privilege Description	Comment
ALL PRIVILEGES	DDL & DML	This privilege is a collection of all Data Definition Language(DDL) and Data Manipulation Language(DML) privileges that the grantor currently possesses and is allowed to grant further. The privilege it grants is specific to the particular object being acted upon.	This privilege collection is dynamically evaluated for the given grantor and object. ALL PRIVILEGES is applicable to a table or view
ALTER	DDL	Authorizes the ALTER command for the object
DEBUG	DML	Authorizes debug-functionality for the procedure or calculation view or for the procedures and calculation views of a schema.
DELETE	DML	Authorizes the DELETE and TRUNCATE commands for the object
DROP	DDL	Authorizes the DROP commands for the object
EXECUTE	DML	Authorizes the execution of an SQLScript function or a database procedure using the CALLS or CALL command respectively.
INDEX	DDL	Authorizes the creation, modification or dropping of indexes for the object
INSERT	DML	Authorizes the INSERT command for the object.	The INSERT and UPDATE privilege are both required on the object to allow the REPLACE and UPSERT commands to be used.
REFERENCES	DDL	Authorizes the usage of all tables in this schema or this table in a foreign key definition.
SELECT	DML	Authorizes the SELECT command for this object or the usage of a sequence.
TRIGGER	DDL	Authorizes the CREATE TRIGGER / DROP TRIGGER command for the specified table or the tables in the specified schema.
UPDATE	DML	Authorizes the UPDATE command for that object.	he INSERT and UPDATE privilege are both required on the object to allow the REPLACE and UPSERT commands to be used. The UPDATE privilege is also required to perform delta merges of column store tables using the MERGE DELTA command.
<identifier>.<identifier>	DDL	Components of the SAP HANA database can create new system privileges. These privileges use the component-name as first identifier of the system privilege and the component-privilege-name as the second identifier.

Not all object privileges are applicable to all kinds of database objects. For details of which object types allow which privilege to be used please see the table below.

Privilege	Schema	Table	View	Sequence	Function/Procedure
ALL PRIVILEGES	---	YES	YES	---	---
ALTER	YES	YES	---	---	YES
CREATE ANY	YES	---	---	---	---
DEBUG	YES	---	YES	---	YES
DELETE	YES	YES	YES	---	---
DROP	YES	YES	YES	YES	YES
EXECUTE	YES	---	---	---	YES
INDEX	YES	YES	---	---	---
INSERT	YES	YES	YES	---	---
REFERENCES	YES	YES	---	---	---
SELECT	YES	YES	YES	YES	---
TRIGGER	YES	YES	---	---	---
UPDATE	YES	YES	YES	---	---

DELETE, INSERT and UPDATE on views are valid for updatable views only. Updatable views have the following characteristics:

• Does not use a join
• Does not contain a UNION
• Does not use aggregation
  
  

DEBUG is only valid for calculation views.
For synonyms, the same restrictions apply to the synonym as they would do for the object the synonym represents.

 <object_name> ::= <table_name>     | <view_name>
                  | <sequence_name> | <procedure_name>
                  | <synonym_name>

Object privileges are used to restrict the access and modifications on database objects like tables, views, sequences, procedures and synonyms.

 <grantee> :: = <user_name> | <role_name>

The grantee can be a user or a role. In case a privilege or role is granted to a role, then all user being granted that role, will have the specified privilege or role.

A role is a named collection of privileges and can be granted to either a user or a role.
If you want to allow several database users to perform the same actions, you should create a role, grant the needed privileges to this role, and finally grant the role to the different database users.
When granting roles to roles, a tree of roles can be build. When granting a role (R) to a role or user (G), G will have all privileges directly granted to R and all privileges granted to roles which had been granted to R.

 <user_name>        ::= <simple_identifier> 

The grantee username.

 <role_name>        ::= <identifier>

The grantee role name.

 <schema_name>      ::= <identifier>

The schema containing the database object to be acted upon.

 <table_name>       ::= [<schema_name>.]<identifier>

The database table object name.

 <view_name>        ::= [<schema_name>.]<identifier>

The database view object name.

 <sequence_name>    ::= [<schema_name>.]<identifier>

The sequence object name.

 <procedure_name>   ::= [<schema_name>.]<identifier>

The procedure object name.

 <synonym_name>     ::= [<schema_name>.]<identifier>

The synonym name.

 <privilege_name>   ::= <identifier>

The name of the privilege to be granted.

WITH ADMIN OPTION and WITH GRANT OPTION
Specifies that the granted privileges can be granted further by the specified user or by those user having the specified role.

With GRANT STRUCTURED PRIVILEGE <privilege_name>
A previously defined analytical privilege based on a generic structured privilege is granted to a user or role. This analytical privileges is used to restrict the access for read operations to certain data in Analytic, Attribute, and Calculation Views by filtering the attribute values.

Description

GRANT is used to grant privileges and structured privileges to users and roles. GRANT is also used to grant roles to users and other roles.

The specified users, roles, objects, and structured privileges have to exist before they can be used in the GRANT command.
In order to use the GRANT command to grant privileges to other users and roles, a user must have the privilege and also the permissions required to grant that privilege.

A user can not grant a privilege to themselves.
User SYSTEM has at all system privileges and the role PUBLIC. All other users have the role PUBLIC. These privileges and roles cannot be revoked.

Although the SYSTEM user has many privileges, it cannot select or change data in other user's tables unless this privilege has been explicitly granted.

All users have the privilege to create objects in their own default schema. The default schema for a user always has the same schema name as the user.

For tables created by users, users have all privileges and may grant all privilges further to other users and roles.

For objects which are dependent on other objects, like views being dependent on tables, it can occur the owner of the dependent object does not have a complete set of privileges. This can occur if the user do not have the privileges on the underlying objects on which their object depends.

Users can have privileges on an object, but may not have sufficient priviliges to grant them to other users and roles.

System and Monitoring Views

USERS: shows all users, their creator, creation date and some info about their current states.
ROLES: shows all roles, their creator and creation date.
GRANTED_ROLES: shows which roles are granted to which user or role.
GRANTED_PRIVILEGES: shows which privileges are granted to which user or role.

Examples

You create a schema called my_schema.

 CREATE SCHEMA my_schema;

You create a table named work_done in the my_schema schema.

 CREATE TABLE my_schema.work_done (t TIMESTAMP, user NVARCHAR (256), work_done VARCHAR (256));

You create a new user named worker with password His_Password_1.

 CREATE USER worker PASSWORD His_Password_1;

You create role called role_for_work_on_my_schema.

 CREATE ROLE role_for_work_on_my_schema;

You grant the SELECT on any object privilege in my_schema to the role role_for_work_on_my_schema.

 GRANT SELECT ON SCHEMA my_schema TO role_for_work_on_my_schema;

You grant the INSERT privilege for the work_done table to the role role_for_work_on_my_schema.

 GRANT INSERT ON my_schema.work_done TO role_for_work_on_my_schema;

You grant the role_for_work_on_my_schema role to the worker user.

 GRANT role_for_work_on_my_schema TO worker WITH ADMIN OPTION;

You grant DELETE privilige for this table to the worker user.

 GRANT DELETE ON my_schema.work_done TO worker;

You grant the worker user the privilege to create any kind of object in the my_schema schema.

 GRANT CREATE ANY ON SCHEMA my_schema TO worker;

The result of the above commands is that the worker user has the privilege to SELECT all tables and views in schema my_schema, to INSERT into and DELETE from table my_schema.work_done and to create objects in schema my_schema. Additionally the worker user is allowed to grant DELETE on the table myschema.work_done to other users and roles.

You grant the privileges INIFILE ADMIN and TRACE ADMIN to the user worker. You grant these privileges along with the permission for the worker user to grant them further.

 GRANT INIFILE ADMIN, TRACE ADMIN TO worker WITH ADMIN OPTION;