8.2 Granting Object Privileges with SQL
In this section, we’ll review the different ways to grant object privileges to users or roles using SQL statements.
8.2.1 Securing Schemas with SQL
When you secure catalog objects, you’re essentially granting SQL privileges to users, roles, or both. In Section 8.1, we outlined many of the privileges that you grant based on the type of catalog objects you’re working with. In this section, we’ll demonstrate the actual process of granting those privileges to users or roles using SQL statements.
In Chapter 3, we outlined the process for creating catalog schemas. Once a schema is created, the owner of the schema will likely need to access the SAP HANA cockpit and grant other administrator users or security administration roles full privileges that are grantable to others on the schema. Once this process is complete, the security administrators will be able to establish a security model based on the schema and its objects. Keep in mind that the schema might also be associated with a service account and not an actual human user. For example, if you set up an SAP HANA service account to manage table replication between SAP ERP and SAP HANA using SAP Landscape Transformation Replication Server, then additional security settings in SAP HANA will need to be maintained. This approach assumes that users will interact with the replicated data. Once an SAP Landscape Transformation Replication Server mass data transfer configuration is established, you’ll need to log on to SAP HANA using the service account credentials and grant others access to the schema used by SAP Landscape Transformation Replication Server. Remember, only the owner of the schema can grant this access.
One way a user can grant or revoke privileges to its schema is by issuing SQL statements, which can be executed within the SQL console. The basic syntax of the SQL script that grants privileges is as follows:
GRANT <PRIVILEGE1, PRIVILEGE2,…> ON SCHEMA <SCHEMA> TO <SOME_USER or
SOME_ROLE> WITH GRANT OPTION;
In this syntax, the items inside the angle brackets (<>) are variables that you can adjust based on the privilege, schema, user, or role. The GRANT command means that you’re allowing the action. A comma-separated array of privileges can also be specified after the GRANT statement. If only one privilege is required, you don’t need an array; simply specify a single privilege by name without commas. Include ON SCHEMA followed by the schema name to identify the schema catalog object. Then, include TO followed by a user or role to identify who is receiving the granted privileges.
For example, to grant the ADMIN_SEC user account the SELECT privilege on a user’s schema, log on as the user that owns the schema, access the SQL console, and execute the following statement (assuming that the account that owns the schema is named TEST_USER and that the schema name is the same):
GRANT SELECT on SCHEMA TEST_USER to ADMIN_SEC WITH GRANT OPTION;
Adding the WITH GRANT OPTION syntax is critical in this case because you need to grant the security administrator full access to a user’s schema. The user must include this statement to grant the security administrator the ability to grant the same privilege to other users or roles.
As an alternative, the schema owner can also grant privileges to existing roles. We typically recommend that the schema owner grant the privileges to existing roles because roles can be assigned to multiple security administrator user accounts. To grant the SEC_ADMINS role SELECT rights for a user’s schema, log on as the user that owns the schema, access the SQL console, and execute the following statement. In our example, we are assuming that the account that owns the schema is named TEST_USER and that the SEC_ADMINS role is assigned to all security administration accounts; you can assume that this schema is a user schema because the user name and schema name are the same:
GRANT SELECT on SCHEMA TEST_USER to SEC_ADMINS WITH GRANT OPTION;
Now, let’s say that you need to give the SEC_ADMIN role full privileges for a user schema named TEST_USER. Issuing the statement shown in Listing 8.1 will grant all privileges to that role.
GRANT
ALTER, SELECT, CREATE ANY, DEBUG,
DELETE, DROP, EXECUTE, INDEX,
INSERT, UPDATE, REFERENCES, TRIGGER,
SELECT CDS METADATA, SELECT METADATA,
CREATE TEMPORARY TABLE, CREATE VIRTUAL FUNCTION PACKAGE
ON SCHEMA TEST_USER TO ADMIN_SEC WITH GRANT OPTION;
Listing 8.1 SQL Code to Grant Multiple Schema Privileges
Notice how you can specify multiple SQL privileges separated by commas in a single statement. To remove privileges, you’ll need to issue a REVOKE statement, as follows:
REVOKE <PRIVILEGE1, PRIVILEGE2,…> ON SCHEMA <SCHEMA> FROM <SOME_USER or
SOME_ROLE>
In this syntax, the items inside the angle brackets (<>) are variables that you can adjust based on the privilege, schema, user, or role. The REVOKE command means that you’re removing the action. A comma-separated array of privileges can also be specified after the REVOKE statement. Include ON SCHEMA followed by the schema name to identify the schema catalog object. Then, include FROM followed by a user or role to identify from whom we’re removing privileges.
To remove all privileges assigned to a role for a given schema, execute the SQL statement shown in Listing 8.2 as the schema owner.
REVOKE
ALTER, SELECT, CREATE ANY, DEBUG,
DELETE, DROP, EXECUTE, INDEX,
INSERT, UPDATE, REFERENCES, TRIGGER,
SELECT CDS METADATA, SELECT METADATA,
CREATE TEMPORARY TABLE, CREATE VIRTUAL FUNCTION PACKAGE
ON SCHEMA TEST_USER FROM ADMIN_SEC;
Listing 8.2 SQL Code to Revoke Multiple Schema Privileges
Note that the GRANT and REVOKE SQL statements can be issued against a user or role even if the listed privileges are already granted or not already revoked. This option is helpful when you’re programmatically granting or revoking privileges because you won’t need to first identify which privileges are missing or which privileges are absent before executing commands; they’ll execute regardless.
8.2.2 Securing Individual Catalog Objects with SQL
Tables and other catalog objects within a schema can be secured individually. We identified these other catalog objects in Section 8.1.1. In these cases, privileges are granted not at the schema level but at the individual object level. For example, let’s assume that user account JH2345 has been granted the SELECT privilege on schema CORPORATE. Another user account, GR4555, has the CREATE ANY privilege on schema COPORATE. GR4555 creates a table named CUSTOMERS in schema CORPORATE. At this point, GR4555 has all privileges on table CUSTOMERS because the account owns the table. However, JH2345 also has SELECT privileges on the table because he was granted that privilege on schema CORPORATE.
Always consider this relationship between schema privileges and individual object privileges. Granting too many privileges on a schema can expose security risks for objects created within that schema. Granting too many privileges on individual objects can lead to difficulties in maintaining the security model. Therefore, security administers often must carefully balance their use of schema privileges and individual object privileges.
Now, let’s walk through the process of granting and revoking privileges for a catalog table, one of many different types of catalog objects that can be secured. However, the SQL statements and processes for securing each item is the same as the code used to secure tables. The syntax for granting privileges to a table is as follows:
GRANT <PRIVILEGE1, PRIVILEGE2,…> ON <CATALOG OBJECT> TO <SOME_USER or SOME_ROLE> WITH GRANT OPTION;
As with the syntax used for schemas, you can specify one or more privileges in a comma-separated array. The privileges that can be granted are specific to the object listed after the ON clause. Section 8.1.1 contains a list of the privileges that can be granted based on the object type. After the ON clause, you’ll then specify the catalog object. After the TO clause, you’ll list the users or roles to which you want to grant privileges.
For example, to grant SELECT on table CUSTOMERS, created in the CORP schema, to the role named QUERY_USERS, you’d issue the following SQL statement:
GRANT SELECT ON CORP.CUSTOMERS TO QUERY_USERS;
Notice that we prefixed the catalog table with the schema name. This prefix is often required so that the system knows the exact table you’re referencing.
Now, let’s look at how an object owner would delegate all privileges to the security administrator group. Keep in mind that they’ll likely need to delegate these privileges to other users in the future. To grant all privileges to the security administrator role named SEC_ADMIN, you’d use the following SQL statement:
GRANT ALTER, DROP, SELECT, INSERT, UPDATE, DELETE, INDEX, TRIGGER, REFERENCES ON CORP.CUSTOMERS TO SEC_ADMIN WITH GRANT OPTION;
Note that we included WITH GRANT OPTION, which is required to allow the grantee to grant these privileges to other users or roles. In most cases, you’ll only specify this option if the grantee will be responsible for establishing security within the SAP HANA system.
In some instances, you’ll also need to revoke privileges, that is, remove privileges granted to a user or role. Use the following syntax to revoke catalog object privileges from a user or role:
REVOKE <PRIVILEGE1, PRIVILEGE2,…> ON <Catalog Object> FROM <SOME_USER or
SOME_ROLE>
As with schemas, the statement starts with REVOKE followed by a list of privileges. After the ON clause, you’ll specify the catalog object. After the FROM clause, you’ll include the user or role to remove the privilege from.
Now, let’s look at how an object owner would remove all privileges from the security administrator group, in this case named SEC_ADMIN. Use the following SQL statement:
REVOKE ALTER, DROP, SELECT, INSERT, UPDATE, DELETE, INDEX, TRIGGER, REFERENCES ON CORP.CUSTOMERS FROM SEC_ADMIN;
Except for schemas, the syntax you use to either GRANT or REVOKE privileges for a given catalog object applies to all individual catalog object types. You can grant or revoke privileges for tables, views, procedures, functions, and sequences. Now, let’s look at how to use the SAP HANA cockpit to grant or revoke privileges for catalog objects.