SQL Statements
GRANT Syntax
DROP USER Syntax
DROP USER <user_name> [<drop_option>]
Syntax Elements
<drop_option> ::= CASCADE | RESTRICT
Default = RESTRICT Description
The DROP USER statement deletes a database user. <user_name> must specify an existing database user.
Only database users having the system privilege USER ADMIN are allowed to drop a database user. Each user having this privilege is allowed to drop any user. Only those users which are delivered with the SAP HANA database can not be dropped: SYS, SYSTEM, _SYS_REPO, _SYS_STATISTICS.
If the <drop_option> RESTRICT is specified implicitly or explicitly then the user will not be dropped in case he is the owner of any other object than the schema with his name and other schemas created by him or in case there is an object stored in one of his schemas which was not created by him.
If the <drop_option> CASCADE is specified, the schema with the user's name and the schemas belonging to the user, together with all objects stored in them (even if they are created by other users), are deleted.
Objects owned by the user, even if they are part of another schema, are deleted. Objects that are dependent on deleted objects are deleted. Even public synonyms owned by the deleted user are deleted.
Privileges on deleted objects are revoked. Privileges granted by the deleted user are revoked. Revoke privileges may cause further revokes if they had been granted further.
Users created by the deleted user and roles created by him are not deleted.
Audit policies created by the deleted user are not deleted.
It is possible to delete a user even if an open session of this user exists.
System and Monitoring Views
The deleted user will be deleted in any of these views:
USERS: shows all users, their creator, creation date and some info about their current states.
USER_PARAMETERS: shows the defined user_parameters; currently only CLIENT is available.
INVALID_CONNECT_ATTEMPTS: shows how many invalid connect attempts were made for each user.
LAST_USED_PASSWORDS: shows info about dates of last password-changes per user.
* The deletion of objects may influence all of the system views describing objects, like TABLES, VIEWS, PROCEDURES, ... .
The deletion of objects may influence the view describing privileges like GRANTED_PRIVILEGES and all of the monitoring views like M_RS_TABLES, M_TABLE_LOCATIONS, ...
Example
A user with user_name NEW_USER was created before and will now be dropped, together with all of his objects;
DROP USER new_user CASCADE;
GRANT Syntax
GRANT <system_privilege>,... TO <grantee> [WITH ADMIN OPTION]
| GRANT <schema_privilege>,... ON SCHEMA <schema_name> TO <grantee> [WITH GRANT OPT ION]
| 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>
<schema_privilege> ::=
CREATE ANY
<object_privilege> ::=
ALL PRIVILEGES
<table_name>
| <synonym_name>
| <view_name>
| <procedure_name>
| <sequence_name>
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.
Only users having a privilege and being allowed to grant that privilege any further can grant a privilege. Each user having ROLE ADMIN system privilege is allowed to grant roles to other roles and users.
A user can not grant a privilege to himself.
User SYSTEM has at least all system privileges and the role PUBLIC. All other users have the role PUBLIC.
These privileges and roles can not be revoked from them.
Although user SYSTEM has many privileges, he is not able to select or change data in other user's tables if he is not granted the privilege to do so explicitly.
User SYSTEM and all other users do have the privilege to create objects in their own default schema, which has the same name as the user itself.
For tables created by themselves, users have all privileges and may grant all of them to users and roles.
For other objects which are dependent on objects like views being dependent on tables, it may happen, that even the owner of the dependent object does not have all privileges if he does not have them on the underlying objects. Or it may happen, that he has privileges, but is not allowed to grant them any further.
Then he cannot grant those privileges.
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.
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.
System privileges are used to restrict administrative tasks. The following system privileges are defined:
AUDIT ADMIN
This privilege controls the execution of the following auditing-related commands: CREATE AUDIT POLICY, DROP AUDIT POLICY and ALTER AUDIT POLICY.
BACKUP ADMIN
This privilege authorizes the ALTER SYSTEM BACKUP command to define and initiate a backup process or to perform a recovery process.
CATALOG READ
This privilege authorizes all users to have unfiltered read-only access to all system and monitoring views.
Normally, the content of those views is filtered based on the privileges of the accessing user. The CATALOG READ privilege enables users to have read-only access to the full content of all system and monitoring views.
CREATE SCENARIO
This privilege controls the creation of calculation scenarios and cubes (calculation database).
CREATE SCHEMA
This privilege authorizes the creation of database schemas using the CREATE SCHEMA command. Each user owns one schema. With this privilege he is allowed to create further ones.
CREATE STRUCTURED PRIVILEGE
This privilege authorizes the creation of Structured Privileges (Analytical Privileges). Note that only the owner of an Analytical Privilege can further grant it to other users or roles and revoke it again.
DATA ADMIN
This powerful privilege authorizes to read all data in the system and monitoring views as well as execute all DDL (Data Definition Language) � and only DDL � commands in the SAP HANA database. This means, that a user having this privilege can not select or change data stored in other user's tables, but can modify the table definition or even drop the table.
EXPORT
This privilege authorizes the export activity in the database via the EXPORT TABLE commands. Note that, beside this privilege, the user still needs the SELECT privilege on the source tables to be exported.
IMPORT
This privilege authorizes the import activity in the database using the IMPORT commands. Note that, beside this privilege, the user still needs the INSERT privilege on the target tables to be imported.
INIFILE ADMIN
This privilege authorizes different methods to change system settings.
LICENSE ADMIN
This privilege authorizes the SET SYSTEM LICENSE command install a new license.
LOG ADMIN
This privilege authorizes the ALTER SYSTEM LOGGING [ON|OFF] commands to enable or disable the log flush mechanism.
MONITOR ADMIN
This privilege authorizes monitoring all activities done using the various ALTER SYSTEM MONITOR commands as well as the ALTER SYSTEM SET MONITOR LEVEL <level> command.
OPTIMIZER ADMIN
This privilege 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 different commands concerning resources, like ALTER SYSTEM RECLAIM DATAVOLUME and ALTER SYSTEM RESET MONITORING VIEW and it authorizes many of the commands 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.
SAVEPOINT ADMIN
This privilege authorizes the execution of a savepoint process using the ALTER SYSTEM SAVEPOINT command.
SCENARIO ADMIN
This privilege authorizes all calculation scenario-related activities (including creation).
SERVICE ADMIN
This privilege authorizes the ALTER SYSTEM [START|CANCEL|RECONFIGURE] commands, intended for administering system services of the database.
SESSION ADMIN
This privilege authorizes the ALTER SYSTEM commands concerning sessions to stop or disconnect a user session or to change session variables.
STRUCTUREDPRIVILEGE ADMIN
This privilege authorizes the creation, reactivation, and dropping of structured privileges.
TRACE ADMIN
This privilege authorizes the ALTER SYSTEM [CLEAR|REMOVE] TRACES commands for operations on database trace files.
USER ADMIN
This privilege authorizes the creation and changing of users using the CREATE USER, ALTER USER, and DROP USER commands.
VERSION ADMIN
This privilege authorizes the ALTER SYSTEM RECLAIM VERSION SPACE command of the multi-version concurrency control (MVCC) mechanism.
<identifier>.<identifier>
Components of the SAP HANA database may create privileges for their own needs. Those use the component-name as first identifier of the system privilege and the component-privilege-component-name as the second identifier.
Currently, the repository uses this feature. For description of the privileges named REPO.<identifier>, please see the manual of the repository.
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, SQL Script 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 privileges are used to restrict the access and modifications on database objects like tables, views, sequences, procedures and the like. Not all of these privileges are applicable to all kinds of database objects.
For object types allowing which privilege, see table below.
The following object privileges are defined:
ALL PRIVILEGES
This privilege is a collection of all DDL (Data Definition Language) and DML (Data Manipulation Language) privileges that on the one hand, the grantor currently has and is allowed to grant further and on the other hand, can be granted on this particular object. This collection is dynamically evaluated for the given grantor and object. ALL PRIVILEGES is applicable to a table or view.
ALTER
This DDL privilege authorizes the ALTER command for that object.
DEBUG
This DML privilege authorizes the debug-functionality for that procedure or calculation view or for the procedures or calculation views in that schema.
DELETE
This DML privilege authorizes the DELETE and TRUNCATE commands for that object.
DROP
This DDL privilege authorizes the DROP commands for that object.
EXECUTE
This DML privilege authorizes the execution of a SQL Script function or a database procedure using the CALLS or CALL command, respectively.
INDEX
This DDL privilege authorizes the creation, changing, or dropping of indexes for that object.
INSERT
This DML privilege authorizes the INSERT command for that object. The INSERT and the UPDATE privilege together are needed for one object to allow the REPLACE and UPSERT command for that object.
SELECT
This DML privilege authorizes the SELECT command for that object or the usage of the sequence, respectively.
TRIGGER
This DDL privilege authorizes the CREATE TRIGGER / DROP TRIGGER command for the specified table or the tables in the specified schema.
UPDATE
This DML privilege authorizes the UPDATE command for that object. The INSERT and the UPDATE privilege together are needed for one object to allow the REPLACE and UPSERT command for that object.
<identifier>.<identifier>
Components of the SAP HANA database may create privileges for their own needs. Those use the component-name as first identifier of the privilege component-name and the component-privilege-component-name as the second identifier.
Currently, the repository uses this feature. For description of the privileges named REPO.<identifier>, please see the manual of the repository.
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 ---
---SELECT YES YES YES YES
---TRIGGER YES YES --- ---
---UPDATE YES YES YES ---
---DELETE, INSERT and UPDATE on views are valid for updatable views only, meaning such views complying to some restrictions as there are: no join included, no UNION included, no aggregation and some further restrictions.
DEBUG is only valid for calculation views, not for other kinds of views.
For synonyms those restrictions are valid, which are valid for the object this synonym stands for.
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 can create a role, grant the needed privileges to this role, and grant the role to the different database users.
When granting roles to roles, a tree of roles can be build. When granting one 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.
With GRANT STRUCTURED PRIVILEGE <structured_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.
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.
Example
Assuming a user owning schema myschema is connected. He has the privilege to create roles and users.
He creates one new role and one user. Then he grants the privilege to SELECT on any object in his schema to this new role. Additionally he grants the privilege to INSERT into one specific table to this new role.
Afterwards he grants this role to that new user.
CREATE USER worker PASSWORD His_Password_1;
CREATE ROLE role_for_work_on_my_schema;
CREATE TABLE myschema.work_done (t TIMESTAMP, user NVARCHAR (256), work_done VARCHA R (256);
GRANT SELECT ON SCHEMA myschema TO role_for_work_on_my_schema;
GRANT INSERT ON myschema.work_done TO role_for_work_on_my_schema;
GRANT role_for_work_on_my_schema TO worker;
The currently connected user has the privilege TRACE ADMIN with the possibility to grant further. He grants it to the new user with the option to let him grant it further. At least the user worker is granted the privilege to DELETE in table myschema.work_done with the option to grant this privilege further.
GRANT TRACE ADMIN TO worker WITH ADMIN OPTION;
GRANT DELETE ON myschema.work_done TO worker WITH GRANT OPTION;
REVOKE