Query to find Grant time/data/ on object from Hive SQL

[#1]

I can able verify the owner, granter and which role,user is assinged.

Is it possible to list time/data of grant given ?

hive> show grant on table database.table_name;
OK
database    table_name                      user USER    DELETE  true    1447912318000   user
database    table_name                      user USER    INSERT  true    1447912318000   user
database    table_name                      user USER    SELECT  true    1447912318000   user
database    table_name                      user USER    UPDATE  true    1447912318000   user
database    table_name                      user ROLE    DELETE  true    1447913961000   root
database    table_name                      user ROLE    INSERT  true    1447913961000   root
database    table_name                      user ROLE    SELECT  true    1447913961000   root
database    table_name                      user ROLE    UPDATE  true    1447913961000   root

[#2 · Accepted solution]

hi @zkfs

the int field you see is the grantttime (in unix timestamp) when the grant was assigned.

[#3]

I would like to see time of grant given on table or role ?

[#4]

Hi @zkfs

I haven't seen a query to do that yet in Hive. Instead, you can query the hive metastore for the information, though be mindful that queries run directly against the metastore could impact your hive performance and are not recommended for production systems. Look at the TBL_PRIVS and TBLS within the hive DB in the metastore, joining these on the TBL_ID may give you the table view you are looking for. You can probably construct a similar metastore query to look at it from a PRINCIPAL_TYPE (role) as well.