====
---- QUERY
create role grant_revoke_test_ALL_SERVER
---- RESULTS
'Role has been created.'
====
---- QUERY
create role grant_revoke_test_ALL_TEST_DB
---- RESULTS
'Role has been created.'
====
---- QUERY
create role grant_revoke_test_SELECT_INSERT_TEST_TBL
---- RESULTS
'Role has been created.'
====
---- QUERY
create role grant_revoke_test_ALL_URI
---- RESULTS
'Role has been created.'
====
---- QUERY
# Shows all roles in the system
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'
---- TYPES
STRING
====
---- 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
grant all on server to grant_revoke_test_ALL_SERVER
====
---- QUERY
# Group name will be replaced with the actual user's group in the test
# framework.
grant role grant_revoke_test_ALL_SERVER to group `$GROUP_NAME`
====
---- 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
show grant role grant_revoke_test_ALL_SERVER
---- RESULTS
'server','','','','','all',false,regex:.+
---- LABELS
scope, database, table, column, uri, privilege, grant_option, create_time
---- TYPES
STRING, STRING, STRING, STRING, STRING, STRING, BOOLEAN, STRING
====
---- QUERY
show grant role grant_revoke_test_ALL_SERVER on server
---- RESULTS
'server','','','','','all',false,regex:.+
---- LABELS
scope, database, table, column, uri, privilege, grant_option, create_time
---- TYPES
STRING, STRING, STRING, STRING, STRING, STRING, BOOLEAN, STRING
====
---- USER
does_not_exist
---- QUERY
# User should not have privileges to execute SHOW ROLES
show roles
---- RESULTS: VERIFY_IS_SUBSET
---- TYPES
STRING
---- CATCH
User 'does_not_exist' does not have privileges to access the requested policy metadata
====
---- USER
does_not_exist
---- QUERY
# User should not have privileges to execute SHOW ROLE GRANT GROUP for a group they do not
# belong to.
show role grant group root
---- RESULTS: VERIFY_IS_SUBSET
---- TYPES
STRING
---- CATCH
User 'does_not_exist' does not have privileges to access the requested policy metadata
====
---- USER
root
---- QUERY
# The 'root' user doesn't have any roles granted to them, but since they are part of the
# 'root' group, they should have privileges to execute this statement.
show role grant group root
---- 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
====
---- QUERY
revoke role grant_revoke_test_ALL_SERVER from group `$GROUP_NAME`
====
---- 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
====
---- QUERY
grant role grant_revoke_test_ALL_TEST_DB to group `$GROUP_NAME`
====
---- 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
====
---- QUERY
grant role grant_revoke_test_ALL_URI to group `$GROUP_NAME`
====
---- 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';
====
---- 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/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
====
---- 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
'2',-1,0,'0B','NOT CACHED','NOT CACHED','TEXT','false','$NAMENODE/test-warehouse/grant_rev_test_prt'
'Total',-1,0,'0B','0B','','','',''
---- TYPES
STRING, BIGINT, BIGINT, STRING, STRING, STRING, STRING, STRING, STRING
====
---- QUERY
show grant role grant_revoke_test_ALL_URI
---- RESULTS
'uri','','','','$NAMENODE/test-warehouse/grant_rev_test_tbl2','all',false,regex:.+
'uri','','','','$NAMENODE/test-warehouse/GRANT_REV_TEST_TBL3','all',false,regex:.+
'uri','','','','$NAMENODE/test-warehouse/grant_rev_test_prt','all',false,regex:.+
---- LABELS
scope, database, table, column, uri, privilege, grant_option, create_time
---- TYPES
STRING, STRING, STRING, STRING, STRING, STRING, BOOLEAN, STRING
====
---- QUERY
# To create a database server-level privileges are required.
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
# Dropping the role should remove the privileges
drop role grant_revoke_test_ALL_TEST_DB
====
---- QUERY
show tables in grant_rev_db
---- CATCH
does not have privileges to access: grant_rev_db.*
====
---- QUERY
grant role grant_revoke_test_SELECT_INSERT_TEST_TBL to group `$GROUP_NAME`
====
---- QUERY
GRANT SELECT ON TABLE grant_rev_db.test_tbl1 TO grant_revoke_test_SELECT_INSERT_TEST_TBL
====
---- QUERY
select * from grant_rev_db.test_tbl1
---- RESULTS
---- TYPES
INT
====
---- QUERY
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
====
---- 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
'table','grant_rev_db','test_tbl1','','','select',false,regex:.+
'table','grant_rev_db','test_tbl1','','','insert',false,regex:.+
---- LABELS
scope, database, table, column, uri, privilege, grant_option, create_time
---- TYPES
STRING, STRING, STRING, STRING, STRING, STRING, BOOLEAN, STRING
====
---- QUERY
insert overwrite grant_rev_db.test_tbl1 select 1
---- RESULTS
: 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 privileges to execute: CREATE_ROLE
====
---- USER
test_user
---- QUERY
drop role grant_revoke_test_ALL_SERVER
---- CATCH
User 'test_user' does not have privileges to execute: DROP_ROLE
====
---- USER
test_user
---- QUERY
grant role grant_revoke_test_ALL_SERVER to group `$GROUP_NAME`
---- CATCH
User 'test_user' does not have privileges to execute: GRANT_ROLE
====
---- USER
test_user
---- QUERY
revoke role grant_revoke_test_ALL_SERVER from group `$GROUP_NAME`
---- CATCH
User 'test_user' does not have privileges to execute: REVOKE_ROLE
====
---- USER
test_user
---- QUERY
grant all on server to grant_revoke_test_ALL_SERVER
---- CATCH
User 'test_user' does not have privileges to execute: GRANT_PRIVILEGE
====
---- USER
test_user
---- QUERY
revoke all on server from grant_revoke_test_ALL_SERVER
---- CATCH
User 'test_user' does not have privileges to execute: REVOKE_PRIVILEGE
====
---- QUERY
# Set up a role to test the WITH GRANT OPTION. Assumes that tests are not running as
# 'root' and that 'root' exists on all machines.
create role grant_revoke_test_ROOT;
grant role grant_revoke_test_ROOT to group root;
grant all on database functional to grant_revoke_test_ROOT WITH GRANT OPTION;
====
---- USER
root
---- QUERY
# There should only be one role that exists for root
show current roles
---- RESULTS
'grant_revoke_test_ROOT'
---- TYPES
STRING
====
---- USER
root
---- QUERY
# This privilege actually active
show databases
---- RESULTS
'default','Default Hive database'
'functional',''
---- TYPES
STRING,STRING
====
---- USER
root
---- QUERY
# The root user should be able to grant/revoke child privileges.
# Due to SENTRY-445 they cannot grant SELECT/INSERT even though they have been granted
# ALL.
grant all on table functional.alltypes to grant_revoke_test_ROOT
====
---- USER
root
---- QUERY
show grant role grant_revoke_test_ROOT
---- RESULTS
'database','functional','','','','all',true,regex:.+
'table','functional','alltypes','','','all',false,regex:.+
---- LABELS
scope, database, table, column, uri, privilege, grant_option, create_time
---- TYPES
STRING, STRING, STRING, STRING, STRING, STRING, BOOLEAN, STRING
---- USER
root
====
---- QUERY
revoke all on table functional.alltypes from grant_revoke_test_ROOT
====
---- USER
root
---- QUERY
# User should not be able to grant privileges outside of this scope.
grant all on table functional_seq.alltypes to grant_revoke_test_ROOT
---- CATCH
User 'root' does not have privileges to execute: GRANT_PRIVILEGE
====
---- USER
root
---- QUERY
# Also cannot create/drop/grant roles
create role grant_revoke_test_ROOT2
---- CATCH
User 'root' does not have privileges to execute: CREATE_ROLE
====
---- USER
root
---- QUERY
# Also cannot create/drop/grant roles
grant role grant_revoke_test_ROOT to group root
---- CATCH
User 'root' does not have privileges to execute: GRANT_ROLE
====
---- 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_ROOT
====
---- USER
root
---- QUERY
grant all on table functional.alltypes to grant_revoke_test_ROOT
---- CATCH
User 'root' does not have privileges to execute: GRANT_PRIVILEGE
====
---- USER
root
---- QUERY
# The privilege is still active
show databases
---- RESULTS
'default','Default Hive database'
'functional',''
---- TYPES
STRING,STRING
====
---- QUERY
# Privilege still exists, but grant option is set to false
show grant role grant_revoke_test_ROOT
---- RESULTS
'database','functional','','','','all',false,regex:.+
---- LABELS
scope, database, table, column, uri, privilege, grant_option, create_time
---- TYPES
STRING, STRING, STRING, STRING, STRING, STRING, BOOLEAN, STRING
---- USER
root
====
---- QUERY
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.'
====
---- 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.'
====
---- 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
show grant role grant_revoke_test_ALL_SERVER
---- RESULTS: VERIFY_IS_EQUAL_SORTED
'server','','','','','all',false,regex:.+
'column','grant_rev_db','test_tbl3','a','','select',false,regex:.+
'column','grant_rev_db','test_tbl3','b','','select',false,regex:.+
'column','grant_rev_db','test_tbl3','x','','select',false,regex:.+
---- LABELS
scope, database, table, column, uri, privilege, grant_option, create_time
---- TYPES
STRING, STRING, STRING, STRING, STRING, STRING, BOOLEAN, STRING
====
---- QUERY
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
show grant role grant_revoke_test_ALL_SERVER
---- RESULTS: VERIFY_IS_EQUAL_SORTED
'server','','','','','all',false,regex:.+
'column','grant_rev_db','test_tbl3','a','','select',false,regex:.+
'column','grant_rev_db','test_tbl3','b','','select',false,regex:.+
'column','grant_rev_db','test_tbl3','c','','select',false,regex:.+
'column','grant_rev_db','test_tbl3','d','','select',false,regex:.+
'column','grant_rev_db','test_tbl3','x','','select',false,regex:.+
'column','grant_rev_db','test_tbl3','y','','select',false,regex:.+
---- LABELS
scope, database, table, column, uri, privilege, grant_option, create_time
---- TYPES
STRING, STRING, STRING, STRING, STRING, STRING, BOOLEAN, STRING
====
---- 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
show grant role grant_revoke_test_ALL_SERVER
---- RESULTS: VERIFY_IS_EQUAL_SORTED
'server','','','','','all',false,regex:.+
'column','grant_rev_db','test_tbl3','a','','select',false,regex:.+
'column','grant_rev_db','test_tbl3','b','','select',false,regex:.+
'column','grant_rev_db','test_tbl3','c','','select',false,regex:.+
'column','grant_rev_db','test_tbl3','d','','select',false,regex:.+
'column','grant_rev_db','test_tbl3','e','','select',false,regex:.+
'column','grant_rev_db','test_tbl3','x','','select',false,regex:.+
'column','grant_rev_db','test_tbl3','y','','select',false,regex:.+
---- LABELS
scope, database, table, column, uri, privilege, grant_option, create_time
---- TYPES
STRING, STRING, STRING, STRING, STRING, STRING, BOOLEAN, STRING
====
---- QUERY
# Revoke SELECT privileges from columns
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
show grant role grant_revoke_test_ALL_SERVER
---- RESULTS: VERIFY_IS_EQUAL_SORTED
'server','','','','','all',false,regex:.+
'column','grant_rev_db','test_tbl3','c','','select',false,regex:.+
'column','grant_rev_db','test_tbl3','d','','select',false,regex:.+
'column','grant_rev_db','test_tbl3','e','','select',false,regex:.+
'column','grant_rev_db','test_tbl3','x','','select',false,regex:.+
---- LABELS
scope, database, table, column, uri, privilege, grant_option, create_time
---- TYPES
STRING, STRING, STRING, STRING, STRING, STRING, BOOLEAN, STRING
====
---- QUERY
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
show grant role grant_revoke_test_ALL_SERVER
---- RESULTS: VERIFY_IS_EQUAL_SORTED
'server','','','','','all',false,regex:.+
'column','grant_rev_db','test_tbl3','d','','select',false,regex:.+
'column','grant_rev_db','test_tbl3','e','','select',false,regex:.+
---- LABELS
scope, database, table, column, uri, privilege, grant_option, create_time
---- TYPES
STRING, STRING, STRING, STRING, STRING, STRING, BOOLEAN, STRING
====
---- QUERY
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
show grant role grant_revoke_test_ALL_SERVER
---- RESULTS: VERIFY_IS_EQUAL_SORTED
'server','','','','','all',false,regex:.+
---- LABELS
scope, database, table, column, uri, privilege, grant_option, create_time
---- TYPES
STRING, STRING, STRING, STRING, STRING, STRING, BOOLEAN, STRING
====
---- QUERY
# Grant SELECT on table to 'root' without 'WITH GRANT' option.
GRANT ROLE grant_revoke_test_ROOT TO GROUP root;
GRANT SELECT ON TABLE grant_rev_db.test_tbl3 TO grant_revoke_test_ROOT;
REVOKE ALL ON DATABASE functional FROM grant_revoke_test_ROOT;
---- RESULTS
'Privilege(s) have been revoked.'
====
---- USER
root
---- QUERY
show grant role grant_revoke_test_ROOT
---- RESULTS: VERIFY_IS_EQUAL_SORTED
'table','grant_rev_db','test_tbl3','','','select',false,regex:.+
---- LABELS
scope, database, table, column, uri, privilege, grant_option, create_time
---- TYPES
STRING, STRING, STRING, STRING, STRING, STRING, BOOLEAN, STRING
====
---- USER
root
---- QUERY
GRANT SELECT (a) ON TABLE grant_rev_db.test_tbl3 TO grant_revoke_test_ROOT
---- CATCH
User 'root' does not have privileges to execute: GRANT_PRIVILEGE
====
---- QUERY
REVOKE SELECT ON TABLE grant_rev_db.test_tbl3 FROM grant_revoke_test_ROOT
---- RESULTS
'Privilege(s) have been revoked.'
====
---- QUERY
# Grant SELECT on table to 'root' with 'WITH GRANT' option.
GRANT SELECT ON TABLE grant_rev_db.test_tbl3 TO grant_revoke_test_ROOT WITH GRANT OPTION
---- RESULTS
'Privilege(s) have been granted.'
====
---- USER
root
---- QUERY
GRANT SELECT (a) ON TABLE grant_rev_db.test_tbl3 TO grant_revoke_test_ROOT
---- RESULTS
'Privilege(s) have been granted.'
====
---- USER
root
---- QUERY
show grant role grant_revoke_test_ROOT
---- RESULTS: VERIFY_IS_EQUAL_SORTED
'table','grant_rev_db','test_tbl3','','','select',true,regex:.+
'column','grant_rev_db','test_tbl3','a','','select',false,regex:.+
---- LABELS
scope, database, table, column, uri, privilege, grant_option, create_time
---- TYPES
STRING, STRING, STRING, STRING, STRING, STRING, BOOLEAN, STRING
====
---- QUERY
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
show grant role grant_revoke_test_ALL_SERVER
---- RESULTS: VERIFY_IS_EQUAL_SORTED
'server','','','','','all',false,regex:.+
'column','grant_rev_db','test_tbl3','a','','select',true,regex:.+
'column','grant_rev_db','test_tbl3','c','','select',true,regex:.+
'column','grant_rev_db','test_tbl3','e','','select',true,regex:.+
---- LABELS
scope, database, table, column, uri, privilege, grant_option, create_time
---- TYPES
STRING, STRING, STRING, STRING, STRING, STRING, BOOLEAN, STRING
====
---- QUERY
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
# TODO: Add a test case that exercises the cascading effect of REVOKE ALL.
show grant role grant_revoke_test_ALL_SERVER
---- RESULTS: VERIFY_IS_EQUAL_SORTED
'server','','','','','all',false,regex:.+
'column','grant_rev_db','test_tbl3','a','','select',false,regex:.+
'column','grant_rev_db','test_tbl3','c','','select',false,regex:.+
'column','grant_rev_db','test_tbl3','e','','select',true,regex:.+
---- LABELS
scope, database, table, column, uri, privilege, grant_option, create_time
---- TYPES
STRING, STRING, STRING, STRING, STRING, STRING, BOOLEAN, STRING
====
---- QUERY
revoke role grant_revoke_test_ALL_SERVER from group `$GROUP_NAME`
====
---- QUERY
# Test 'grant all on server' with explicit server name specified.
create role grant_revoke_test_ALL_SERVER1
---- RESULTS
'Role has been created.'
====
---- QUERY
grant all on server server1 to grant_revoke_test_ALL_SERVER1
====
---- QUERY
grant role grant_revoke_test_ALL_SERVER1 to group `$GROUP_NAME`
====
---- QUERY
drop database grant_rev_db cascade
====
---- QUERY
create database grant_rev_db location '$FILESYSTEM_PREFIX/test-warehouse/grant_rev_db.db'
====
---- QUERY
revoke role grant_revoke_test_ALL_SERVER1 from group `$GROUP_NAME`
====
---- 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 grant role grant_revoke_test_ALL_SERVER1
---- RESULTS: VERIFY_IS_EQUAL_SORTED
'server','','','','','all',false,regex:.+
---- LABELS
scope, database, table, column, uri, privilege, grant_option, create_time
---- TYPES
STRING, STRING, STRING, STRING, STRING, STRING, BOOLEAN, STRING
====
---- QUERY
revoke all on server server1 from grant_revoke_test_ALL_SERVER1
====
---- QUERY
show grant role grant_revoke_test_ALL_SERVER1
---- RESULTS: VERIFY_IS_EQUAL_SORTED
---- LABELS
scope, database, table, column, uri, privilege, grant_option, create_time
---- TYPES
STRING, STRING, STRING, STRING, STRING, STRING, BOOLEAN, STRING
====
---- 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 `$GROUP_NAME`
---- RESULTS
'Role has been granted.'
====
---- QUERY
create role grant_revoke_test_COLUMN_PRIV
====
---- QUERY
grant role grant_revoke_test_COLUMN_PRIV to group `$GROUP_NAME`;
====
---- QUERY
create database if not exists grant_rev_db;
====
---- QUERY
create table grant_rev_db.test_tbl4 (col1 int, col2 int);
====
---- QUERY
revoke role grant_revoke_test_ALL_SERVER from group `$GROUP_NAME`
====
---- QUERY
show grant role grant_revoke_test_COLUMN_PRIV
---- RESULTS
---- LABELS
scope, database, table, column, uri, privilege, grant_option, create_time
---- TYPES
STRING, STRING, STRING, STRING, STRING, STRING, BOOLEAN, STRING
====
---- 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
====
---- QUERY
grant select(col1) on table grant_rev_db.test_tbl4 to role grant_revoke_test_COLUMN_PRIV
====
---- QUERY
show grant role grant_revoke_test_COLUMN_PRIV
---- RESULTS: VERIFY_IS_EQUAL_SORTED
'column','grant_rev_db','test_tbl4','col1','','select',false,regex:.+
---- LABELS
scope, database, table, column, uri, privilege, grant_option, create_time
---- TYPES
STRING, STRING, STRING, STRING, STRING, STRING, BOOLEAN, STRING
====
---- QUERY
show databases
---- RESULTS
'default','Default Hive database'
'grant_rev_db',''
---- TYPES
STRING,STRING
====
---- QUERY
grant role grant_revoke_test_ALL_SERVER to group `$GROUP_NAME`
---- RESULTS
'Role has been granted.'
====
---- QUERY
drop database if exists grant_rev_db cascade
====
---- QUERY
revoke role grant_revoke_test_ALL_SERVER from group `$GROUP_NAME`
---- RESULTS
'Role has been revoked.'
====
---- QUERY
revoke role grant_revoke_test_COLUMN_PRIV from group `$GROUP_NAME`
====
---- QUERY
# Cleanup test roles
drop role grant_revoke_test_ALL_SERVER;
drop role grant_revoke_test_SELECT_INSERT_TEST_TBL;
drop role grant_revoke_test_ALL_URI;
drop role grant_revoke_test_ROOT;
drop role grant_revoke_test_COLUMN_PRIV;
---- RESULTS
'Role has been dropped.'
====