More than 1 year has passed since last update.

HANA 権限関連のSQLまとめ

Last updated at 2022-08-24Posted at 2020-05-14

権限付与/削除

ロール

<作成>
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>;

スキーマ権限

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;

オブジェクト権限

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;

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
;

ロール/ユーザに付与されたオブジェクト権限

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

ついでにユーザ情報

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

参考

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up