testdata/workloads/functional-query/queries/QueryTest/grant_revoke.test

====
---- USER
admin
---- QUERY
create role grant_revoke_test_ALL_SERVER
---- RESULTS
'Role has been created.'
====
---- USER
admin
---- QUERY
create role grant_revoke_test_ALL_TEST_DB
---- RESULTS
'Role has been created.'
====
---- USER
admin
---- QUERY
create role grant_revoke_test_SELECT_INSERT_TEST_TBL
---- RESULTS
'Role has been created.'
====
---- USER
admin
---- QUERY
create role grant_revoke_test_ALL_URI
---- RESULTS
'Role has been created.'
====
---- USER
admin
---- QUERY
refresh authorization;
show roles;
---- RESULTS: VERIFY_IS_SUBSET
'grant_revoke_test_ALL_SERVER'
'grant_revoke_test_ALL_TEST_DB'
'grant_revoke_test_SELECT_INSERT_TEST_TBL'
'grant_revoke_test_ALL_URI'
====
---- QUERY
create database grant_rev_db location '$FILESYSTEM_PREFIX/test-warehouse/grant_rev_db.db'
---- CATCH
does not have privileges to execute 'CREATE' on: grant_rev_db
====
---- USER
admin
---- QUERY
grant all on server to grant_revoke_test_ALL_SERVER
====
---- USER
admin
---- QUERY
# Grant the privilege to the group `$GROUP_NAME`, to which the user 'getuser()' belongs
# so that the user 'getuser()' could perform the some of the following operations on the
# database 'grant_rev_db'.
# Group name will be replaced with the group of 'getuser()' in the test.
# framework.
grant role grant_revoke_test_ALL_SERVER to group `$GROUP_NAME`
====
---- USER
admin
---- QUERY
refresh authorization
====
---- QUERY
show current roles
---- RESULTS: VERIFY_IS_SUBSET
'grant_revoke_test_ALL_SERVER'
---- TYPES
STRING
====
---- USER
does_not_exist
---- QUERY
# Run this query as a different user and verify no roles show up but the
# stmt does not fail with an authorization error.
show current roles
---- RESULTS: VERIFY_IS_SUBSET
---- TYPES
STRING
====
---- QUERY
# We have to specify the resource for SHOW GRANT since SHOW GRANT is not supported
# without a defined resource in Ranger.
show grant role grant_revoke_test_ALL_SERVER on server
---- RESULTS: VERIFY_IS_EQUAL_SORTED
'ROLE','grant_revoke_test_ALL_SERVER','','','','','*','*','','rwstorage',false,regex:.+
'ROLE','grant_revoke_test_ALL_SERVER','','','','*','','','','all',false,regex:.+
'ROLE','grant_revoke_test_ALL_SERVER','*','','','','','','*','all',false,regex:.+
'ROLE','grant_revoke_test_ALL_SERVER','*','*','*','','','','','all',false,regex:.+
---- LABELS
principal_type, principal_name, database, table, column, uri, storage_type, storage_uri, udf, privilege, grant_option, create_time
---- TYPES
STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, BOOLEAN, STRING
====
---- USER
does_not_exist
---- QUERY
# A user that is not a Ranger administrator should not have privileges to execute
# SHOW ROLES
show roles
---- RESULTS: VERIFY_IS_SUBSET
---- TYPES
STRING
---- CATCH
User does_not_exist does not have permission for this operation
====
---- USER
does_not_exist
---- QUERY
# A user that is not a Ranger administrator should not have privilege to execute
# SHOW ROLE GRANT GROUP for a group the user does not belong to.
show role grant group non_owner
---- RESULTS: VERIFY_IS_SUBSET
---- TYPES
STRING
---- CATCH
User does_not_exist does not have permission for this operation
====
---- USER
non_owner
---- QUERY
# The 'non_owner' user doesn't have any roles granted to them, but since it is part of the
# 'non_owner' group, they should have privileges to execute this statement.
# Note that this test case requires that impalad was started with the argument
# '--use_customized_user_groups_mapper_for_ranger' so that a customized user-to-groups
# mapper will be used to retrieve the groups the user 'non_owner' belongs to, which
# consists of exactly one group, the group 'non_owner'.
show role grant group non_owner
---- RESULTS: VERIFY_IS_SUBSET
---- TYPES
STRING
====
---- QUERY
drop database if exists grant_rev_db
====
---- QUERY
create database grant_rev_db location '$FILESYSTEM_PREFIX/test-warehouse/grant_rev_db.db'
====
---- QUERY
show tables in grant_rev_db
---- RESULTS
---- TYPES
STRING
====
---- QUERY
create table grant_rev_db.test_tbl1(i int)
====
---- QUERY
show tables in grant_rev_db
---- RESULTS
'test_tbl1'
---- TYPES
STRING
====
---- QUERY
create function grant_rev_db.fn() RETURNS int
LOCATION '$FILESYSTEM_PREFIX/test-warehouse/libTestUdfs.so' SYMBOL='Fn'
====
---- QUERY
show functions in grant_rev_db
---- RESULTS
'INT','fn()','NATIVE','true'
---- TYPES
STRING, STRING, STRING, STRING
====
---- QUERY
show create function grant_rev_db.fn
---- RESULTS: MULTI_LINE
['CREATE FUNCTION grant_rev_db.fn()
 RETURNS INT
 LOCATION ''$NAMENODE/test-warehouse/libTestUdfs.so''
 SYMBOL=''_Z2FnPN10impala_udf15FunctionContextE''
']
---- TYPES
STRING
====
---- USER
admin
---- QUERY
# To prevent the user 'getuser()' (which belongs to the group '$GROUP_NAME') from
# performing any operation on the database 'grant_rev_db', we also need to alter
# the owner of 'grant_rev_db' since 'getuser()' is the owner of this database, which by
# default is allowed by Ranger to perform any operation on the database.
revoke role grant_revoke_test_ALL_SERVER from group `$GROUP_NAME`;
alter database grant_rev_db set owner user admin;
refresh authorization;
====
---- QUERY
create database grant_rev_db location '$FILESYSTEM_PREFIX/test-warehouse/grant_rev_db.db'
---- CATCH
does not have privileges to execute 'CREATE' on: grant_rev_db
====
---- QUERY
show tables in grant_rev_db
---- CATCH
does not have privileges to access: grant_rev_db.*
====
---- QUERY
show functions in grant_rev_db
---- CATCH
does not have privileges to access: grant_rev_db
====
---- QUERY
show create function grant_rev_db.fn
---- CATCH
does not have privileges to access: grant_rev_db
====
---- QUERY
show create function _impala_builtins.sin
---- RESULTS: MULTI_LINE
['CREATE FUNCTION _impala_builtins.sin(DOUBLE)
 RETURNS DOUBLE
 SYMBOL=''_ZN6impala13MathFunctions3SinEPN10impala_udf15FunctionContextERKNS1_9DoubleValE''
']
---- TYPES
STRING
====
---- USER
admin
---- QUERY
grant role grant_revoke_test_ALL_TEST_DB to group `$GROUP_NAME`
====
---- USER
admin
---- QUERY
# Should now have all privileges on the test db
grant all on database grant_rev_db to grant_revoke_test_ALL_TEST_DB
====
---- QUERY
show tables in grant_rev_db
---- RESULTS
'test_tbl1'
---- TYPES
STRING
====
---- QUERY
# Even though the user has all privileges on the database, they do not have privileges
# on any URIs. The FE tests have additional error message verification.
create table grant_rev_db.test_tbl2(i int) location '$FILESYSTEM_PREFIX/test-warehouse/grant_rev_test_tbl2';
---- CATCH
does not have privileges to access: $NAMENODE/test-warehouse/grant_rev_test_tbl2
====
---- USER
admin
---- QUERY
grant role grant_revoke_test_ALL_URI to group `$GROUP_NAME`
====
---- USER
admin
---- QUERY
grant all on uri '$FILESYSTEM_PREFIX/test-warehouse/grant_rev_test_tbl2' to grant_revoke_test_ALL_URI
====
---- QUERY
# Should now have privileges to create the table.
create table grant_rev_db.test_tbl2(i int) location '$FILESYSTEM_PREFIX/test-warehouse/grant_rev_test_tbl2';
====
---- USER
admin
---- QUERY
# Running grant on a URI with upper case letters
grant all on uri '$FILESYSTEM_PREFIX/test-warehouse/GRANT_REV_TEST_TBL3' to grant_revoke_test_ALL_URI
====
---- QUERY
# Should now have privileges to create the table.
create table grant_rev_db.test_tbl_uppercase(i int) location '$FILESYSTEM_PREFIX/test-warehouse/GRANT_REV_TEST_TBL3';
====
---- QUERY
# Privileges on the uri '$FILESYSTEM_PREFIX/test-warehouse/GRANT_REV_TEST_TBL3' do not
# imply privileges on any location under
# '$FILESYSTEM_PREFIX/test-warehouse/GRANT_REV_TEST_TBL3'.
create table grant_rev_db.test_tbl2_uppercase(i int) location '$FILESYSTEM_PREFIX/test-warehouse/GRANT_REV_TEST_TBL3/test';
---- CATCH
does not have privileges to access: $NAMENODE/test-warehouse/GRANT_REV_TEST_TBL3/test
====
---- QUERY
show tables in grant_rev_db
---- RESULTS
'test_tbl1'
'test_tbl2'
'test_tbl_uppercase'
---- TYPES
STRING
====
---- QUERY
# IMPALA-1670: User does not have privileges to access URI when adding partitions
create table grant_rev_db.test_tbl_partitioned(i int) partitioned by (j int);
alter table grant_rev_db.test_tbl_partitioned add
partition (j=1)
partition (j=2) location '$FILESYSTEM_PREFIX/test-warehouse/grant_rev_test_prt';
---- CATCH
does not have privileges to access: $NAMENODE/test-warehouse/grant_rev_test_prt
====
---- USER
admin
---- QUERY
grant all on uri '$FILESYSTEM_PREFIX/test-warehouse/grant_rev_test_prt'
to grant_revoke_test_ALL_URI;
====
---- QUERY
# Should now have privileges to add partitions
alter table grant_rev_db.test_tbl_partitioned add
partition (j=1)
partition (j=2) location '$FILESYSTEM_PREFIX/test-warehouse/grant_rev_test_prt';
show partitions grant_rev_db.test_tbl_partitioned;
---- RESULTS
'1',-1,0,'0B','NOT CACHED','NOT CACHED','TEXT','false',regex:.*/j=1,'$ERASURECODE_POLICY'
'2',-1,0,'0B','NOT CACHED','NOT CACHED','TEXT','false','$NAMENODE/test-warehouse/grant_rev_test_prt','$ERASURECODE_POLICY'
'Total',-1,0,'0B','0B','','','','',''
---- TYPES
STRING, BIGINT, BIGINT, STRING, STRING, STRING, STRING, STRING, STRING, STRING
====
---- QUERY
# We have to specify the resource for SHOW GRANT since SHOW GRANT is not supported
# without a defined resource in Ranger.
show grant role grant_revoke_test_ALL_URI on uri '$NAMENODE/test-warehouse/grant_rev_test_tbl2'
---- RESULTS
'ROLE','grant_revoke_test_ALL_URI','','','','$NAMENODE/test-warehouse/grant_rev_test_tbl2','','','','all',false,regex:.+
---- LABELS
principal_type, principal_name, database, table, column, uri, storage_type, storage_uri, udf, privilege, grant_option, create_time
---- TYPES
STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, BOOLEAN, STRING
====
---- QUERY
# We have to specify the resource for SHOW GRANT since SHOW GRANT is not supported
# without a defined resource in Ranger.
show grant role grant_revoke_test_ALL_URI on uri '$NAMENODE/test-warehouse/GRANT_REV_TEST_TBL3'
---- RESULTS
'ROLE','grant_revoke_test_ALL_URI','','','','$NAMENODE/test-warehouse/GRANT_REV_TEST_TBL3','','','','all',false,regex:.+
---- LABELS
principal_type, principal_name, database, table, column, uri, storage_type, storage_uri, udf, privilege, grant_option, create_time
---- TYPES
STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, BOOLEAN, STRING
====
---- QUERY
# We have to specify the resource for SHOW GRANT since SHOW GRANT is not supported
# without a defined resource in Ranger.
show grant role grant_revoke_test_ALL_URI on uri '$NAMENODE/test-warehouse/grant_rev_test_prt'
---- RESULTS
'ROLE','grant_revoke_test_ALL_URI','','','','$NAMENODE/test-warehouse/grant_rev_test_prt','','','','all',false,regex:.+
---- LABELS
principal_type, principal_name, database, table, column, uri, storage_type, storage_uri, udf, privilege, grant_option, create_time
---- TYPES
STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, BOOLEAN, STRING
====
---- QUERY
# Does not result in an AuthorizationException since '$GROUP_NAME' is still assigned the
# role 'grant_revoke_test_ALL_TEST_DB', which is granted the 'ALL' privilege on the
# database 'grant_rev_db'
# TODO(IMPALA-10401): Investigate whether the privilege on the provided uri should be
# verified.
create database grant_rev_db location '$FILESYSTEM_PREFIX/test-warehouse/grant_rev_db.db'
---- CATCH
Database already exists: grant_rev_db
====
---- QUERY
# To create a database server-level privileges are required.
create database grant_rev_db2 location '$FILESYSTEM_PREFIX/test-warehouse/grant_rev_db2.db'
---- CATCH
does not have privileges to execute 'CREATE' on: grant_rev_db2
====
---- USER
admin
---- QUERY
# Currently dropping the role does not remove its associated privileges.
drop role grant_revoke_test_ALL_TEST_DB
---- CATCH
Role 'grant_revoke_test_ALL_TEST_DB' can not be deleted as it is referenced in one or more policies
====
---- USER
admin
---- QUERY
# We have to manually remove the privilege associated with the role
# 'grant_revoke_test_ALL_TEST_DB' before removing it.
revoke all on database grant_rev_db from grant_revoke_test_ALL_TEST_DB;
drop role grant_revoke_test_ALL_TEST_DB;
====
---- QUERY
# Recall that the owner of the database 'grant_rev_db' has been changed to 'admin'.
show tables in grant_rev_db
---- CATCH
does not have privileges to access: grant_rev_db.*
====
---- QUERY
# The user 'getuser()' can select the data from the table 'grant_rev_db.test_tbl1'
# because 'getuser()' is still the owner of 'grant_rev_db.test_tbl1'.
select * from grant_rev_db.test_tbl1
---- RESULTS
---- TYPES
INT
====
---- USER
admin
---- QUERY
# Up to this point, the owner of the tables 'grant_rev_db.test_tbl1' and
# 'grant_rev_db.test_tbl1' is the user 'getuser()'. Changing the owner of these tables to
# the user 'admin' prevents 'getuser()' from accessing the data in those tables.
alter table grant_rev_db.test_tbl1 set owner user admin;
alter table grant_rev_db.test_tbl2 set owner user admin;
====
---- QUERY
# After changing the owner of the table 'grant_rev_db.test_tbl1' to the user 'admin',
# the user 'getuser()' can no longer select the data from 'grant_rev_db.test_tbl1'.
select * from grant_rev_db.test_tbl1
---- CATCH
does not have privileges to execute 'SELECT' on: grant_rev_db.test_tbl1
====
---- USER
admin
---- QUERY
grant role grant_revoke_test_SELECT_INSERT_TEST_TBL to group `$GROUP_NAME`
====
---- USER
admin
---- QUERY
GRANT SELECT ON TABLE grant_rev_db.test_tbl1 TO grant_revoke_test_SELECT_INSERT_TEST_TBL
====
---- QUERY
# This query succeeds since the user 'getuser()' has been granted the role
# 'grant_revoke_test_SELECT_INSERT_TEST_TBL', which in turn has been granted the SELECT
# privilege on the table 'grant_rev_db.test_tbl1'.
select * from grant_rev_db.test_tbl1
---- RESULTS
---- TYPES
INT
====
---- QUERY
# The user 'getuser()' should not be able to select the data from the table
# 'grant_rev_db.test_tbl2'.
select * from grant_rev_db.test_tbl2
---- CATCH
does not have privileges to execute 'SELECT' on: grant_rev_db.test_tbl2
====
---- QUERY
insert overwrite grant_rev_db.test_tbl1 select 1
---- CATCH
does not have privileges to execute 'INSERT' on: grant_rev_db.test_tbl1
====
---- USER
admin
---- QUERY
GRANT INSERT ON TABLE grant_rev_db.test_tbl1 TO grant_revoke_test_SELECT_INSERT_TEST_TBL
====
---- QUERY
show grant role grant_revoke_test_SELECT_INSERT_TEST_TBL on table grant_rev_db.test_tbl1
---- RESULTS: VERIFY_IS_EQUAL_SORTED
'ROLE','grant_revoke_test_SELECT_INSERT_TEST_TBL','grant_rev_db','test_tbl1','*','','','','','insert',false,regex:.+
'ROLE','grant_revoke_test_SELECT_INSERT_TEST_TBL','grant_rev_db','test_tbl1','*','','','','','select',false,regex:.+
---- LABELS
principal_type, principal_name, database, table, column, uri, storage_type, storage_uri, udf, privilege, grant_option, create_time
---- TYPES
STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, BOOLEAN, STRING
====
---- QUERY
insert overwrite grant_rev_db.test_tbl1 select 1
---- RUNTIME_PROFILE
NumModifiedRows: 1
====
---- QUERY
select * from grant_rev_db.test_tbl1
---- RESULTS
1
---- TYPES
INT
====
---- USER
test_user
---- QUERY
create role some_test_role
---- CATCH
User test_user does not have permission for this operation
====
---- USER
test_user
---- QUERY
drop role grant_revoke_test_ALL_SERVER
---- CATCH
User test_user does not have permission for this operation
====
---- USER
test_user
---- QUERY
# A non-Ranger administrator should not be able to tell from the result that the role
# does not exist.
drop role grant_revoke_test_NON_EXISTING
---- CATCH
User test_user does not have permission for this operation
====
---- USER
test_user
---- QUERY
grant role grant_revoke_test_ALL_SERVER to group `$GROUP_NAME`
---- CATCH
User doesn't have permissions to grant role grant_revoke_test_ALL_SERVER
====
---- USER
test_user
---- QUERY
revoke role grant_revoke_test_ALL_SERVER from group `$GROUP_NAME`
---- CATCH
User doesn't have permissions to revoke role grant_revoke_test_ALL_SERVER
====
---- USER
non_owner
---- QUERY
# Grantor is required to exist in Ranger and thus we set the user to 'non_owner', which
# is currently a user created in Ranger in Impala's development environment.
grant all on server to grant_revoke_test_ALL_SERVER
---- CATCH
User doesn't have necessary permission to grant access
====
---- USER
non_owner
---- QUERY
# Grantor is required to exist in Ranger and thus we set the user to 'non_owner', which
# is currently a user created in Ranger in Impala's development environment.
revoke all on server from grant_revoke_test_ALL_SERVER
---- CATCH
User doesn't have necessary permission to revoke access
====
---- USER
admin
---- QUERY
# Set up a role to test the WITH GRANT OPTION.
# Note that the group 'non_owner' and the user 'non_owner' are created in Ranger in
# Impala's development environment and that the group 'non_owner' is a group to which the
# user 'non_owner' belongs according to the user-to-groups mapper provided for impalad
# and catalogd via the argument of "--use_customized_user_groups_mapper_for_ranger".
create role grant_revoke_test_NON_OWNER;
grant role grant_revoke_test_NON_OWNER to group non_owner;
grant all on database functional to grant_revoke_test_NON_OWNER WITH GRANT OPTION;
====
---- USER
non_owner
---- QUERY
# There should only be one role that exists for non_owner.
show current roles
---- RESULTS
'grant_revoke_test_NON_OWNER'
---- TYPES
STRING
====
---- USER
non_owner
---- QUERY
# This privilege is actually active.
show databases
---- RESULTS
'default','Default Hive database'
'functional',''
---- TYPES
STRING,STRING
====
---- USER
non_owner
---- QUERY
# The non_owner user should be able to grant/revoke child privileges.
grant all on table functional.alltypes to grant_revoke_test_NON_OWNER
====
---- USER
non_owner
---- QUERY
# We have to specify the resource for SHOW GRANT since SHOW GRANT is not supported
# without a defined resource in Ranger.
show grant role grant_revoke_test_NON_OWNER on database functional
---- RESULTS: VERIFY_IS_EQUAL_SORTED
'ROLE','grant_revoke_test_NON_OWNER','functional','','','','','','*','all',true,regex:.+
'ROLE','grant_revoke_test_NON_OWNER','functional','*','*','','','','','all',true,regex:.+
---- LABELS
principal_type, principal_name, database, table, column, uri, storage_type, storage_uri, udf, privilege, grant_option, create_time
---- TYPES
STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, BOOLEAN, STRING
====
---- USER
non_owner
---- QUERY
# We have to specify the resource for SHOW GRANT since SHOW GRANT is not supported
# without a defined resource in Ranger.
# Note that the column of 'grant_option' is false for this privilege.
show grant role grant_revoke_test_NON_OWNER on table functional.alltypes
---- RESULTS: VERIFY_IS_EQUAL_SORTED
'ROLE','grant_revoke_test_NON_OWNER','functional','alltypes','*','','','','','all',false,regex:.+
---- LABELS
principal_type, principal_name, database, table, column, uri, storage_type, storage_uri, udf, privilege, grant_option, create_time
---- TYPES
STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, BOOLEAN, STRING
====
---- USER
admin
---- QUERY
revoke all on table functional.alltypes from grant_revoke_test_NON_OWNER
====
---- USER
non_owner
---- QUERY
# User should not be able to grant privileges outside of this scope of the database
# 'functional'.
grant all on table functional_seq.alltypes to grant_revoke_test_NON_OWNER
---- CATCH
User doesn't have necessary permission to grant access
====
---- USER
non_owner
---- QUERY
# Also cannot create/drop/grant roles
create role grant_revoke_test_NON_OWNER2
---- CATCH
User non_owner does not have permission for this operation
====
---- USER
non_owner
---- QUERY
# Also cannot create/drop/grant roles
grant role grant_revoke_test_NON_OWNER to group non_owner
---- CATCH
User doesn't have permissions to grant role grant_revoke_test_NON_OWNER
====
---- USER
admin
---- QUERY
# Revoke the GRANT OPTION and verify the user can no longer GRANT or REVOKE
revoke grant option for all on database functional from grant_revoke_test_NON_OWNER;
refresh authorization;
====
---- USER
non_owner
---- QUERY
grant all on table functional.alltypes to grant_revoke_test_NON_OWNER
---- CATCH
User doesn't have necessary permission to grant access
====
---- USER
non_owner
---- QUERY
# This privilege is actually active.
show databases
---- RESULTS
'default','Default Hive database'
'functional',''
---- TYPES
STRING,STRING
====
---- USER
non_owner
---- QUERY
# We have to specify the resource for SHOW GRANT since SHOW GRANT is not supported
# without a defined resource in Ranger.
# Also, privilege still exists, but grant option is set to false.
show grant role grant_revoke_test_NON_OWNER on database functional
---- RESULTS: VERIFY_IS_EQUAL_SORTED
'ROLE','grant_revoke_test_NON_OWNER','functional','','','','','','*','all',false,regex:.+
'ROLE','grant_revoke_test_NON_OWNER','functional','*','*','','','','','all',false,regex:.+
---- LABELS
principal_type, principal_name, database, table, column, uri, storage_type, storage_uri, udf, privilege, grant_option, create_time
---- TYPES
STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, BOOLEAN, STRING
====
---- USER
admin
---- QUERY
# After the following two statements, there is no role assigned to the group
# '$GROUP_NAME' to which the user 'getuser()' belongs.
REVOKE ROLE grant_revoke_test_ALL_URI FROM GROUP `$GROUP_NAME`;
REVOKE ROLE grant_revoke_test_SELECT_INSERT_TEST_TBL FROM GROUP `$GROUP_NAME`;
---- RESULTS
'Role has been revoked.'
====
---- USER
admin
---- QUERY
GRANT ROLE grant_revoke_test_ALL_SERVER TO GROUP `$GROUP_NAME`
---- RESULTS
'Role has been granted.'
====
---- QUERY
show current roles
---- RESULTS: VERIFY_IS_SUBSET
'grant_revoke_test_ALL_SERVER'
---- TYPES
STRING
====
---- QUERY
# Create a table with multiple columns to test column-level security.
create table grant_rev_db.test_tbl3(a int, b int, c int, d int, e int) partitioned by (x int, y int)
---- RESULTS
'Table has been created.'
====
---- USER
admin
---- QUERY
GRANT SELECT (a, b, x) ON TABLE grant_rev_db.test_tbl3 TO grant_revoke_test_ALL_SERVER
---- RESULTS
'Privilege(s) have been granted.'
====
---- QUERY
# We have to specify the resource for SHOW GRANT since SHOW GRANT is not supported
# without a defined resource in Ranger.
# Note that after IMPALA-8587, the related privileges with regard to the specified
# resource will be shown as well.
show grant role grant_revoke_test_ALL_SERVER on column grant_rev_db.test_tbl3.a
---- RESULTS: VERIFY_IS_EQUAL_SORTED
'ROLE','grant_revoke_test_ALL_SERVER','*','*','*','','','','','all',false,regex:.+
'ROLE','grant_revoke_test_ALL_SERVER','grant_rev_db','test_tbl3','a','','','','','select',false,regex:.+
---- LABELS
principal_type, principal_name, database, table, column, uri, storage_type, storage_uri, udf, privilege, grant_option, create_time
---- TYPES
STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, BOOLEAN, STRING
====
---- QUERY
# We have to specify the resource for SHOW GRANT since SHOW GRANT is not supported
# without a defined resource in Ranger.
show grant role grant_revoke_test_ALL_SERVER on column grant_rev_db.test_tbl3.b
---- RESULTS: VERIFY_IS_EQUAL_SORTED
'ROLE','grant_revoke_test_ALL_SERVER','*','*','*','','','','','all',false,regex:.+
'ROLE','grant_revoke_test_ALL_SERVER','grant_rev_db','test_tbl3','b','','','','','select',false,regex:.+
---- LABELS
principal_type, principal_name, database, table, column, uri, storage_type, storage_uri, udf, privilege, grant_option, create_time
---- TYPES
STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, BOOLEAN, STRING
====
---- QUERY
# We have to specify the resource for SHOW GRANT since SHOW GRANT is not supported
# without a defined resource in Ranger.
show grant role grant_revoke_test_ALL_SERVER on column grant_rev_db.test_tbl3.x
---- RESULTS: VERIFY_IS_EQUAL_SORTED
'ROLE','grant_revoke_test_ALL_SERVER','*','*','*','','','','','all',false,regex:.+
'ROLE','grant_revoke_test_ALL_SERVER','grant_rev_db','test_tbl3','x','','','','','select',false,regex:.+
---- LABELS
principal_type, principal_name, database, table, column, uri, storage_type, storage_uri, udf, privilege, grant_option, create_time
---- TYPES
STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, BOOLEAN, STRING
====
---- USER
admin
---- QUERY
# After granting the privileges on (c, d, y), we will check if they are indeed granted in
# the following 3 SHOW GRANT statements.
GRANT SELECT (c, d, y) ON TABLE grant_rev_db.test_tbl3 TO grant_revoke_test_ALL_SERVER
---- RESULTS
'Privilege(s) have been granted.'
====
---- QUERY
# We have to specify the resource for SHOW GRANT since SHOW GRANT is not supported
# without a defined resource in Ranger.
show grant role grant_revoke_test_ALL_SERVER on column grant_rev_db.test_tbl3.c
---- RESULTS: VERIFY_IS_EQUAL_SORTED
'ROLE','grant_revoke_test_ALL_SERVER','*','*','*','','','','','all',false,regex:.+
'ROLE','grant_revoke_test_ALL_SERVER','grant_rev_db','test_tbl3','c','','','','','select',false,regex:.+
---- LABELS
principal_type, principal_name, database, table, column, uri, storage_type, storage_uri, udf, privilege, grant_option, create_time
---- TYPES
STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, BOOLEAN, STRING
====
---- QUERY
# We have to specify the resource for SHOW GRANT since SHOW GRANT is not supported
# without a defined resource in Ranger.
show grant role grant_revoke_test_ALL_SERVER on column grant_rev_db.test_tbl3.d
---- RESULTS: VERIFY_IS_EQUAL_SORTED
'ROLE','grant_revoke_test_ALL_SERVER','*','*','*','','','','','all',false,regex:.+
'ROLE','grant_revoke_test_ALL_SERVER','grant_rev_db','test_tbl3','d','','','','','select',false,regex:.+
---- LABELS
principal_type, principal_name, database, table, column, uri, storage_type, storage_uri, udf, privilege, grant_option, create_time
---- TYPES
STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, BOOLEAN, STRING
====
---- QUERY
# We have to specify the resource for SHOW GRANT since SHOW GRANT is not supported
# without a defined resource in Ranger.
show grant role grant_revoke_test_ALL_SERVER on column grant_rev_db.test_tbl3.y
---- RESULTS: VERIFY_IS_EQUAL_SORTED
'ROLE','grant_revoke_test_ALL_SERVER','*','*','*','','','','','all',false,regex:.+
'ROLE','grant_revoke_test_ALL_SERVER','grant_rev_db','test_tbl3','y','','','','','select',false,regex:.+
---- LABELS
principal_type, principal_name, database, table, column, uri, storage_type, storage_uri, udf, privilege, grant_option, create_time
---- TYPES
STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, BOOLEAN, STRING
====
---- USER
admin
---- QUERY
GRANT SELECT (a, a, e, x) ON TABLE grant_rev_db.test_tbl3 TO grant_revoke_test_ALL_SERVER
---- RESULTS
'Privilege(s) have been granted.'
====
---- QUERY
# We have to specify the resource for SHOW GRANT since SHOW GRANT is not supported
# without a defined resource in Ranger.
# Verify that the privilege on 'grant_rev_db.test_tbl3.e' is indeed granted.
show grant role grant_revoke_test_ALL_SERVER on column grant_rev_db.test_tbl3.e
---- RESULTS: VERIFY_IS_EQUAL_SORTED
'ROLE','grant_revoke_test_ALL_SERVER','*','*','*','','','','','all',false,regex:.+
'ROLE','grant_revoke_test_ALL_SERVER','grant_rev_db','test_tbl3','e','','','','','select',false,regex:.+
---- LABELS
principal_type, principal_name, database, table, column, uri, storage_type, storage_uri, udf, privilege, grant_option, create_time
---- TYPES
STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, BOOLEAN, STRING
====
---- USER
admin
---- QUERY
# Revoke SELECT privileges from columns.
# Verify the privileges on (a, b, y) are indeed revoked in the following 3 queries.
REVOKE SELECT (a, b, b, y) ON TABLE grant_rev_db.test_tbl3 FROM grant_revoke_test_ALL_SERVER
---- RESULTS
'Privilege(s) have been revoked.'
====
---- QUERY
# We have to specify the resource for SHOW GRANT since SHOW GRANT is not supported
# without a defined resource in Ranger.
show grant role grant_revoke_test_ALL_SERVER on column grant_rev_db.test_tbl3.a
---- RESULTS: VERIFY_IS_EQUAL_SORTED
'ROLE','grant_revoke_test_ALL_SERVER','*','*','*','','','','','all',false,regex:.+
---- LABELS
principal_type, principal_name, database, table, column, uri, storage_type, storage_uri, udf, privilege, grant_option, create_time
---- TYPES
STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, BOOLEAN, STRING
====
---- QUERY
# We have to specify the resource for SHOW GRANT since SHOW GRANT is not supported
# without a defined resource in Ranger.
show grant role grant_revoke_test_ALL_SERVER on column grant_rev_db.test_tbl3.b
---- RESULTS: VERIFY_IS_EQUAL_SORTED
'ROLE','grant_revoke_test_ALL_SERVER','*','*','*','','','','','all',false,regex:.+
---- LABELS
principal_type, principal_name, database, table, column, uri, storage_type, storage_uri, udf, privilege, grant_option, create_time
---- TYPES
STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, BOOLEAN, STRING
====
---- QUERY
# We have to specify the resource for SHOW GRANT since SHOW GRANT is not supported
# without a defined resource in Ranger.
show grant role grant_revoke_test_ALL_SERVER on column grant_rev_db.test_tbl3.y
---- RESULTS: VERIFY_IS_EQUAL_SORTED
'ROLE','grant_revoke_test_ALL_SERVER','*','*','*','','','','','all',false,regex:.+
---- LABELS
principal_type, principal_name, database, table, column, uri, storage_type, storage_uri, udf, privilege, grant_option, create_time
---- TYPES
STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, BOOLEAN, STRING
====
---- USER
admin
---- QUERY
# Verify the privileges on (b, c, x) are indeed revoked in the following 3 queries.
# Recall that the privilege on (a) had been revoked previously.
REVOKE SELECT (a, b, c, x) ON TABLE grant_rev_db.test_tbl3 FROM grant_revoke_test_ALL_SERVER
---- RESULTS
'Privilege(s) have been revoked.'
====
---- QUERY
# We have to specify the resource for SHOW GRANT since SHOW GRANT is not supported
# without a defined resource in Ranger.
show grant role grant_revoke_test_ALL_SERVER on column grant_rev_db.test_tbl3.b
---- RESULTS: VERIFY_IS_EQUAL_SORTED
'ROLE','grant_revoke_test_ALL_SERVER','*','*','*','','','','','all',false,regex:.+
---- LABELS
principal_type, principal_name, database, table, column, uri, storage_type, storage_uri, udf, privilege, grant_option, create_time
---- TYPES
STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, BOOLEAN, STRING
====
---- QUERY
# We have to specify the resource for SHOW GRANT since SHOW GRANT is not supported
# without a defined resource in Ranger.
show grant role grant_revoke_test_ALL_SERVER on column grant_rev_db.test_tbl3.c
---- RESULTS: VERIFY_IS_EQUAL_SORTED
'ROLE','grant_revoke_test_ALL_SERVER','*','*','*','','','','','all',false,regex:.+
---- LABELS
principal_type, principal_name, database, table, column, uri, storage_type, storage_uri, udf, privilege, grant_option, create_time
---- TYPES
STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, BOOLEAN, STRING
====
---- QUERY
# We have to specify the resource for SHOW GRANT since SHOW GRANT is not supported
# without a defined resource in Ranger.
show grant role grant_revoke_test_ALL_SERVER on column grant_rev_db.test_tbl3.x
---- RESULTS: VERIFY_IS_EQUAL_SORTED
'ROLE','grant_revoke_test_ALL_SERVER','*','*','*','','','','','all',false,regex:.+
---- LABELS
principal_type, principal_name, database, table, column, uri, storage_type, storage_uri, udf, privilege, grant_option, create_time
---- TYPES
STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, BOOLEAN, STRING
====
---- USER
admin
---- QUERY
# Verify the privileges on (d, e) are indeed revoked in the following 2 queries.
# Recall that the privilege on (a) had been revoked previously.
REVOKE SELECT (a, b, c, d, e) ON TABLE grant_rev_db.test_tbl3 FROM grant_revoke_test_ALL_SERVER;
---- RESULTS
'Privilege(s) have been revoked.'
====
---- QUERY
# We have to specify the resource for SHOW GRANT since SHOW GRANT is not supported
# without a defined resource in Ranger.
show grant role grant_revoke_test_ALL_SERVER on column grant_rev_db.test_tbl3.d
---- RESULTS: VERIFY_IS_EQUAL_SORTED
'ROLE','grant_revoke_test_ALL_SERVER','*','*','*','','','','','all',false,regex:.+
---- LABELS
principal_type, principal_name, database, table, column, uri, storage_type, storage_uri, udf, privilege, grant_option, create_time
---- TYPES
STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, BOOLEAN, STRING
====
---- QUERY
# We have to specify the resource for SHOW GRANT since SHOW GRANT is not supported
# without a defined resource in Ranger.
show grant role grant_revoke_test_ALL_SERVER on column grant_rev_db.test_tbl3.e
---- RESULTS: VERIFY_IS_EQUAL_SORTED
'ROLE','grant_revoke_test_ALL_SERVER','*','*','*','','','','','all',false,regex:.+
---- LABELS
principal_type, principal_name, database, table, column, uri, storage_type, storage_uri, udf, privilege, grant_option, create_time
---- TYPES
STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, BOOLEAN, STRING
====
---- USER
admin
---- QUERY
# Grant SELECT on table to 'non_owner' without 'WITH GRANT' option.
GRANT ROLE grant_revoke_test_NON_OWNER TO GROUP non_owner;
GRANT SELECT ON TABLE grant_rev_db.test_tbl3 TO grant_revoke_test_NON_OWNER;
REVOKE ALL ON DATABASE functional FROM grant_revoke_test_NON_OWNER;
---- RESULTS
'Privilege(s) have been revoked.'
====
---- USER
non_owner
---- QUERY
# We have to specify the resource for SHOW GRANT since SHOW GRANT is not supported
# without a defined resource in Ranger.
# Verify that the SELECT privilege is indeed granted to the role
# 'grant_revoke_test_NON_OWNER'.
show grant role grant_revoke_test_NON_OWNER on table grant_rev_db.test_tbl3
---- RESULTS: VERIFY_IS_EQUAL_SORTED
'ROLE','grant_revoke_test_NON_OWNER','grant_rev_db','test_tbl3','*','','','','','select',false,regex:.+
---- LABELS
principal_type, principal_name, database, table, column, uri, storage_type, storage_uri, udf, privilege, grant_option, create_time
---- TYPES
STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, BOOLEAN, STRING
====
---- USER
non_owner
---- QUERY
GRANT SELECT (a) ON TABLE grant_rev_db.test_tbl3 TO grant_revoke_test_NON_OWNER
---- CATCH
User doesn't have necessary permission to grant access
====
---- USER
admin
---- QUERY
REVOKE SELECT ON TABLE grant_rev_db.test_tbl3 FROM grant_revoke_test_NON_OWNER
---- RESULTS
'Privilege(s) have been revoked.'
====
---- USER
admin
---- QUERY
# Grant SELECT on table to 'non_owner' with 'WITH GRANT' option.
GRANT SELECT ON TABLE grant_rev_db.test_tbl3 TO grant_revoke_test_NON_OWNER WITH GRANT OPTION
---- RESULTS
'Privilege(s) have been granted.'
====
---- USER
non_owner
---- QUERY
GRANT SELECT (a) ON TABLE grant_rev_db.test_tbl3 TO grant_revoke_test_NON_OWNER
---- RESULTS
'Privilege(s) have been granted.'
====
---- USER
non_owner
---- QUERY
# We have to specify the resource for SHOW GRANT since SHOW GRANT is not supported
# without a defined resource in Ranger.
# Verify that the privilege on the column 'grant_rev_db.test_tbl3.a' is indeed granted.
# TODO: Notice that at this point, the role 'grant_revoke_test_NON_OWNER' has also been
# granted the SELECT privilege on the table 'grant_rev_db.test_tbl3'. We need to
# investigate whether this is the expected behavior after IMPALA-8587.
show grant role grant_revoke_test_NON_OWNER on column grant_rev_db.test_tbl3.a
---- RESULTS: VERIFY_IS_EQUAL_SORTED
'ROLE','grant_revoke_test_NON_OWNER','grant_rev_db','test_tbl3','a','','','','','select',false,regex:.+
---- LABELS
principal_type, principal_name, database, table, column, uri, storage_type, storage_uri, udf, privilege, grant_option, create_time
---- TYPES
STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, BOOLEAN, STRING
====
---- USER
admin
---- QUERY
# Grant the SELECT privileges on the columns (a, c, e) and verify in the following 3
# queries that the privileges are granted with the column of 'grant_option' being true.
GRANT SELECT (a, c, e) ON TABLE grant_rev_db.test_tbl3 TO grant_revoke_test_ALL_SERVER WITH GRANT OPTION
---- RESULTS
'Privilege(s) have been granted.'
====
---- QUERY
# We have to specify the resource for SHOW GRANT since SHOW GRANT is not supported
# without a defined resource in Ranger.
show grant role grant_revoke_test_ALL_SERVER on column grant_rev_db.test_tbl3.a
---- RESULTS: VERIFY_IS_EQUAL_SORTED
'ROLE','grant_revoke_test_ALL_SERVER','*','*','*','','','','','all',false,regex:.+
'ROLE','grant_revoke_test_ALL_SERVER','grant_rev_db','test_tbl3','a','','','','','select',true,regex:.+
---- LABELS
principal_type, principal_name, database, table, column, uri, storage_type, storage_uri, udf, privilege, grant_option, create_time
---- TYPES
STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, BOOLEAN, STRING
====
---- QUERY
# We have to specify the resource for SHOW GRANT since SHOW GRANT is not supported
# without a defined resource in Ranger.
show grant role grant_revoke_test_ALL_SERVER on column grant_rev_db.test_tbl3.c
---- RESULTS: VERIFY_IS_EQUAL_SORTED
'ROLE','grant_revoke_test_ALL_SERVER','*','*','*','','','','','all',false,regex:.+
'ROLE','grant_revoke_test_ALL_SERVER','grant_rev_db','test_tbl3','c','','','','','select',true,regex:.+
---- LABELS
principal_type, principal_name, database, table, column, uri, storage_type, storage_uri, udf, privilege, grant_option, create_time
---- TYPES
STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, BOOLEAN, STRING
====
---- QUERY
# We have to specify the resource for SHOW GRANT since SHOW GRANT is not supported
# without a defined resource in Ranger.
show grant role grant_revoke_test_ALL_SERVER on column grant_rev_db.test_tbl3.e
---- RESULTS: VERIFY_IS_EQUAL_SORTED
'ROLE','grant_revoke_test_ALL_SERVER','*','*','*','','','','','all',false,regex:.+
'ROLE','grant_revoke_test_ALL_SERVER','grant_rev_db','test_tbl3','e','','','','','select',true,regex:.+
---- LABELS
principal_type, principal_name, database, table, column, uri, storage_type, storage_uri, udf, privilege, grant_option, create_time
---- TYPES
STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, BOOLEAN, STRING
====
---- USER
admin
---- QUERY
# Revoke the granting privilege on the columns (a, c) and veviry in the following 2
# queries that the column of 'grant_option' is indeed false.
REVOKE GRANT OPTION FOR SELECT (a, c) ON TABLE grant_rev_db.test_tbl3 FROM grant_revoke_test_ALL_SERVER
---- RESULTS
'Privilege(s) have been revoked.'
====
---- QUERY
# We have to specify the resource for SHOW GRANT since SHOW GRANT is not supported
# without a defined resource in Ranger.
show grant role grant_revoke_test_ALL_SERVER on column grant_rev_db.test_tbl3.a
---- RESULTS: VERIFY_IS_EQUAL_SORTED
'ROLE','grant_revoke_test_ALL_SERVER','*','*','*','','','','','all',false,regex:.+
'ROLE','grant_revoke_test_ALL_SERVER','grant_rev_db','test_tbl3','a','','','','','select',false,regex:.+
---- LABELS
principal_type, principal_name, database, table, column, uri, storage_type, storage_uri, udf, privilege, grant_option, create_time
---- TYPES
STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, BOOLEAN, STRING
====
---- QUERY
# We have to specify the resource for SHOW GRANT since SHOW GRANT is not supported
# without a defined resource in Ranger.
show grant role grant_revoke_test_ALL_SERVER on column grant_rev_db.test_tbl3.c
---- RESULTS: VERIFY_IS_EQUAL_SORTED
'ROLE','grant_revoke_test_ALL_SERVER','*','*','*','','','','','all',false,regex:.+
'ROLE','grant_revoke_test_ALL_SERVER','grant_rev_db','test_tbl3','c','','','','','select',false,regex:.+
---- LABELS
principal_type, principal_name, database, table, column, uri, storage_type, storage_uri, udf, privilege, grant_option, create_time
---- TYPES
STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, BOOLEAN, STRING
====
---- USER
admin
---- QUERY
# At this point, the group '$GROUP_NAME' is still assigned the role of
# 'grant_revoke_test_ALL_SERVER'.
revoke role grant_revoke_test_ALL_SERVER from group `$GROUP_NAME`
====
---- USER
admin
---- QUERY
# Test 'grant all on server' with explicit server name specified.
create role grant_revoke_test_ALL_SERVER1
---- RESULTS
'Role has been created.'
====
---- USER
admin
---- QUERY
grant all on server server1 to grant_revoke_test_ALL_SERVER1
====
---- USER
admin
---- QUERY
# Grant role to the group '$GROUP_NAME' to which the user 'getuser()' belongs.
grant role grant_revoke_test_ALL_SERVER1 to group `$GROUP_NAME`
====
---- USER
admin
---- QUERY
# We will be testing whether the user 'getuser()' is able to drop and then create the
# database 'grant_rev_db'. Thus, we have to revoke the privileges on the resources under
# the database 'grant_rev_db' in advance. Otherwise, we won't be able to revoke the
# privileges after dropping the database since revoking the privileges on those
# non-existing resources would result AnalysisException's. If we are not able to remove
# these privileges, then we won't be able to drop those roles they reference after the
# execution of this test file, leaving some roles and privileges that are not well-defined.
REVOKE SELECT ON TABLE grant_rev_db.test_tbl1 FROM grant_revoke_test_SELECT_INSERT_TEST_TBL;
REVOKE INSERT ON TABLE grant_rev_db.test_tbl1 FROM grant_revoke_test_SELECT_INSERT_TEST_TBL;
REVOKE SELECT ON TABLE grant_rev_db.test_tbl3 FROM grant_revoke_test_NON_OWNER;
REVOKE SELECT (a) ON TABLE grant_rev_db.test_tbl3 FROM grant_revoke_test_NON_OWNER;
REVOKE SELECT (a, c, e) ON TABLE grant_rev_db.test_tbl3 FROM grant_revoke_test_ALL_SERVER;
====
---- QUERY
# Verify that the user 'getuser()' is able to drop the database.
drop database grant_rev_db cascade
====
---- QUERY
create database grant_rev_db location '$FILESYSTEM_PREFIX/test-warehouse/grant_rev_db.db'
====
---- USER
admin
---- QUERY
# To prevent the user 'getuser()' (which belongs to the group '$GROUP_NAME') from
# performing any operation on the database 'grant_rev_db', we also need to alter
# the owner of 'grant_rev_db' since 'getuser()' is the owner of this database, which by
# default is allowed by Ranger to perform any operation on the database.
revoke role grant_revoke_test_ALL_SERVER1 from group `$GROUP_NAME`;
alter database grant_rev_db set owner user admin;
====
---- QUERY
create database grant_rev_db location '$FILESYSTEM_PREFIX/test-warehouse/grant_rev_db.db'
---- CATCH
does not have privileges to execute 'CREATE' on: grant_rev_db
====
---- QUERY
# We have to specify the resource for SHOW GRANT since SHOW GRANT is not supported
# without a defined resource in Ranger.
show grant role grant_revoke_test_ALL_SERVER1 on server
---- RESULTS: VERIFY_IS_EQUAL_SORTED
'ROLE','grant_revoke_test_ALL_SERVER1','','','','','*','*','','rwstorage',false,regex:.+
'ROLE','grant_revoke_test_ALL_SERVER1','','','','*','','','','all',false,regex:.+
'ROLE','grant_revoke_test_ALL_SERVER1','*','','','','','','*','all',false,regex:.+
'ROLE','grant_revoke_test_ALL_SERVER1','*','*','*','','','','','all',false,regex:.+
---- LABELS
principal_type, principal_name, database, table, column, uri, storage_type, storage_uri, udf, privilege, grant_option, create_time
---- TYPES
STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, BOOLEAN, STRING
====
---- USER
admin
---- QUERY
revoke all on server server1 from grant_revoke_test_ALL_SERVER1
====
---- QUERY
# We have to specify the resource for SHOW GRANT since SHOW GRANT is not supported
# without a defined resource in Ranger.
# Verify that the privilege on server is indeed revoked.
show grant role grant_revoke_test_ALL_SERVER1 on server
---- RESULTS: VERIFY_IS_EQUAL_SORTED
---- LABELS
principal_type, principal_name, database, table, column, uri, storage_type, storage_uri, udf, privilege, grant_option, create_time
---- TYPES
STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, BOOLEAN, STRING
====
---- USER
admin
---- QUERY
# IMPALA-4951: make sure database is visible to a user having only column level access
# to a table in the database
grant role grant_revoke_test_ALL_SERVER to group non_owner
---- RESULTS
'Role has been granted.'
====
---- USER
admin
---- QUERY
create role grant_revoke_test_COLUMN_PRIV
====
---- USER
admin
---- QUERY
grant role grant_revoke_test_COLUMN_PRIV to group non_owner
====
---- USER
non_owner
---- QUERY
create database if not exists grant_rev_db;
====
---- USER
non_owner
---- QUERY
create table grant_rev_db.test_tbl4 (col1 int, col2 int)
====
---- USER
admin
---- QUERY
revoke role grant_revoke_test_ALL_SERVER from group non_owner
====
---- USER
non_owner
---- QUERY
# We have to specify the resource for SHOW GRANT since SHOW GRANT is not supported
# without a defined resource in Ranger.
show grant role grant_revoke_test_COLUMN_PRIV on table grant_rev_db.test_tbl4
---- RESULTS
---- LABELS
principal_type, principal_name, database, table, column, uri, storage_type, storage_uri, udf, privilege, grant_option, create_time
---- TYPES
STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, BOOLEAN, STRING
====
---- USER
non_owner
---- QUERY
# grant_rev_db is not visible as user does not have any level of access to it
show databases
---- RESULTS
'default','Default Hive database'
---- TYPES
STRING,STRING
====
---- USER
admin
---- QUERY
grant select(col1) on table grant_rev_db.test_tbl4 to role grant_revoke_test_COLUMN_PRIV
====
---- USER
non_owner
---- QUERY
show grant role grant_revoke_test_COLUMN_PRIV on column grant_rev_db.test_tbl4.col1
---- RESULTS
'ROLE','grant_revoke_test_COLUMN_PRIV','grant_rev_db','test_tbl4','col1','','','','','select',false,regex:.+
---- LABELS
principal_type, principal_name, database, table, column, uri, storage_type, storage_uri, udf, privilege, grant_option, create_time
---- TYPES
STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, BOOLEAN, STRING
====
---- USER
non_owner
---- QUERY
# Verify that the database 'grant_rev_db' is visible to the user 'non_owner'.
show databases
---- RESULTS
'default','Default Hive database'
'grant_rev_db',''
---- TYPES
STRING,STRING
====
---- USER
admin
---- QUERY
# We will be testing whether the user 'getuser()' is able to drop a database if the group
# 'getuser()' belongs to is assigned the role 'grant_revoke_test_ALL_SERVER', which is
# granted the ALL privilege on the server. Before dropping a database, the user also has
# to revoke every privilege granted on the resources of the database and thus we grant
# the ALL privilege with the grant option.
grant all on server to role grant_revoke_test_ALL_SERVER with grant option;
grant role grant_revoke_test_ALL_SERVER to group `$GROUP_NAME`;
---- RESULTS
'Role has been granted.'
====
---- QUERY
# The user 'getuser()' has to revoke the privileges on the resources under the database
# 'grant_rev_db' before dropping the database. Otherwise, the revocation would fail due
# to an AnalysisException thrown because 'grant_rev_db' does not exist.
revoke select(col1) on table grant_rev_db.test_tbl4 from role grant_revoke_test_COLUMN_PRIV;
drop database if exists grant_rev_db cascade;
====
---- USER
admin
---- QUERY
revoke role grant_revoke_test_ALL_SERVER from group `$GROUP_NAME`
---- RESULTS
'Role has been revoked.'
====
---- USER
admin
---- QUERY
revoke role grant_revoke_test_COLUMN_PRIV from group `$GROUP_NAME`
====
---- QUERY
# Verify that the user 'getuser()', which belongs to the group '$GROUP_NAME' is not
# assigned any role afterwards.
show current roles
---- RESULTS
====