HANA 権限関連のSQLまとめ


# 権限付与/削除

### ロール
<作成>
`CREATE ROLE <role_name>;`
<権限付与/削除>
`GRANT <role_name> TO <user or role>;`
`REVOKE <role_name> FROM <user or role>;`

ロールの複数指定も可能。
`GRANT TESTROLE1, TESTROLE2 to <user or role>;`

<BR>

### スキーマ権限

`GRANT SELECT,EXECUTE ON SCHEMA <schema_name> TO <user or role>;`
`REVOKE SELECT,EXECUTE ON SCHEMA <schema_name> FROM <user or role>;`

> 例）
> GRANT SELECT,EXECUTE ON SCHEMA _SYS_BI TO TEST_ROLE;

<BR>

### オブジェクト権限
`GRANT SELECT ON <schema_name>.<object_name> TO <user or role>;`
`REVOKE SELECT ON <schema_name>.<object_name> FROM <user or role>;`

オブジェクト名にスラッシュ等の記号が含まれる場合はダブルクウォートをつける必要がある。
> GRANT SELECT ON _SYS_BIC."TESTPKG/TEST_ANALYTIC_VIEW" TO TEST_ROLE;

https://help.sap.com/viewer/b3ee5778bc2e4a089d3299b82ec762a7/2.0.03/en-US/f1b28c0904cd4b70bebcfa187831b30f.html

<BR>

### Analytic Privilege
`CALL GRANT_ACTIVATED_ANALYTICAL_PRIVILEGE ('<analytic_privilege>','<user or role>');`
`CALL REVOKE_ACTIVATED_ANALYTICAL_PRIVILEGE ('<analytic_privilege>','<user or role>');`

オブジェクト名にスラッシュ等の記号が含まれる場合はシングルクウォートの中にさらにダブルクウォートをつける必要がある。
> CALL GRANT_ACTIVATED_ANALYTICAL_PRIVILEGE ('_SYS_BI_CP_ALL','TEST_ROLE');
> CALL GRANT_ACTIVATED_ANALYTICAL_PRIVILEGE ('"TESTPKG/TEST_ANALYTIC_PRIV"','TEST_ROLE');


# 権限の確認

### ユーザ/ロールに付与されたロールの一覧
```
select * from SYS.GRANTED_ROLES 
-- WHERE GRANTEE = '<user or role>'
```

### ユーザに付与された権限の一覧（ロール経由のものも含む）
```
SELECT 
    P.GRANTEE,
    ROLE_NAME,
    P.GRANTOR,
    X.GRANTOR,
    OBJECT_TYPE,
    SCHEMA_NAME,
    OBJECT_NAME,
    PRIVILEGE,
    P.IS_GRANTABLE,
    IS_VALID
FROM      SYS.GRANTED_ROLES AS X
LEFT JOIN SYS.GRANTED_PRIVILEGES AS P
ON X.GRANTEE = P.GRANTEE
-- where P.GRANTEE = '<user>'
-- and   ROLE_NAME = '<role>'
order by P.GRANTEE, ROLE_NAME
;
```
https://answers.sap.com/questions/738946/hana-security---user-roles-and-privileges-sql-scri.html

### ロール/ユーザに付与されたオブジェクト権限
```
SELECT
    GRANTEE,
    GRANTEE_TYPE,
    OBJECT_TYPE,
    SCHEMA_NAME,
    OBJECT_NAME,
    PRIVILEGE
FROM SYS.GRANTED_PRIVILEGES
--WHERE GRANTEE = '<user or role>'
order by GRANTEE, OBJECT_TYPE, SCHEMA_NAME, OBJECT_NAME, PRIVILEGE
```
https://www.hanatutorials.com/p/list-users-with-hana-schema-privileges.html

### ついでにユーザ情報
```
select
  USER_NAME                             --Displays the name of the user
  ,USER_MODE                            --Displays the mode of the user: LOCAL/EXTERNAL
  ,CREATOR                              --Displays the creator of the user, SYSTEM, or the ID of a user with USER ADMIN privilege
  ,CREATE_TIME                          --Displays the creation time
  ,VALID_UNTIL                          --Displays the end time of the user's validity
  ,LAST_SUCCESSFUL_CONNECT              --Displays the time of the last successful connection of the user.
  ,LAST_INVALID_CONNECT_ATTEMPT         --Displays the time of the last invalid connection attempt.
  ,ADMIN_GIVEN_PASSWORD                 --Displays whether the password was provided by the administrator or by the user: TRUE/FALSE
  ,LAST_PASSWORD_CHANGE_TIME            --Displays the time of the last password change
  ,PASSWORD_CHANGE_NEEDED               --Displays whether the user is forced to change their own password: TRUE/FALSE
  ,IS_PASSWORD_LIFETIME_CHECK_ENABLED   --Displays whether the password-lifetime will be checked for the user: TRUE/FALSE
  ,USER_DEACTIVATED                     --Displays whether the user is deactivated: TRUE/FALSE
  ,DEACTIVATION_TIME                    --Displays the time given with an explicit deactivation command for the specified user
  ,IS_CLIENT_CONNECT_ENABLED            --Displays whether the user is allowed to connect outside of applications: TRUE/FALSE
from
  USERS
order by
  USER_NAME
```


# 参考
- https://www.sapjp.com/blog/archives/4512
- http://www.bestsaphanatraining.com/how-to-manage-sap-hana-roles-and-privileges.html
- [SAP HANA SQL and System Views Reference | GRANT Statement (Access Control)](https://help.sap.com/viewer/4fe29514fd584807ac9f2a04f6754767/2.0.03/en-US/20f674e1751910148a8b990d33efbdc5.html)
- [SAP HANA SQL and System Views Reference | REVOKE Statement (Access Control)](https://help.sap.com/viewer/4fe29514fd584807ac9f2a04f6754767/2.0.03/en-US/20fc91cb75191014ac15eb4d6f2d7dde.html)
- [SAP HANA SQL and System Views Reference | USERS System View](https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/21026099751910148e0cdbddc75652b8.html)
- [SAP HANA Security Guide | Stored Procedures Used to Grant/Revoke Privileges on Activated Repository Objects](https://help.sap.com/viewer/b3ee5778bc2e4a089d3299b82ec762a7/2.0.03/en-US/f1b28c0904cd4b70bebcfa187831b30f.html)