Enabling hive Authorization in Qubole

Once the Hive authorization is enabled in qubole we need to manage the users and permission by Hive authentication,  following are the some of the commands which will be used for the same.

1. Listing the Current Roles

Set role admin;

show roles

2. Create the roles

CREATE ROLE <role_name>;
Creates a new role. Only the admin role has privilege for this.

Eg:

Set role admin;
Create role sysadmin;

3. Grant Role to users

GRANT ROLE <role_name> TO USER <user_name>
 

Eg:

Set role admin;

Grant Role sysadmin to user rahul ;

4. Revoke a role from user

REVOKE ROLE <role_name> FROM USER <user_name>;

Eg:

Set role admin;
REVOKE Role sysadmin from user rahul;

5. List  Roles attached to a user

SHOW ROLE GRANT USER <user_name>;

Eg.

Set role admin;

show role grant user `rahul`;

6. List Users under a role

SHOW PRINCIPALS <Role_name>

Eg

Set role admin;

SHOW PRINCIPALS sysadmin

7. Assign Role access to tables

Sample Permission

SELECT privilege: It provides read access to an object (table).
INSERT privilege: It provides ability for adding data to an object (table).
UPDATE privilege: It provides ability for running UPDATE queries on an object (table).
DELETE privilege: It provides ability for deleting data in an object (table).
ALL privilege: It provides all privileges. In other words, this privilege gets translated into all the above privileges.

GRANT <Permission> ON <table_name> TO ROLE <role_name>;

Eg:

Grant all on default.testtable to role sysadmin

8. View Role/user Permissions on tables

Check all users who have been granted with a specific role

SHOW GRANT USER <user_name> ON <table_name|All>;
SHOW GRANT ROLE <user_name> ON <table_name|All>;

Eg:

SHOW GRANT user analytics on all

Parsing Value from a Json Field in Qubole.

Description of how to extract a value from a JSON field in Hive using the get_json_object function. When the data in one of the fields in the Hive environment is in JSON format, and we need to extract a value out of the JSON, we can use the get_json_object function. For example, if we have a column named jdata containing the following JSON:

get_json_object(column_name, '$.keyvalue')

The column name is : jdata and json the Column is as followes.

{
    "Foo": "ABC",
    "Bar": "20090101100000",
    "Quux": {
        "QuuxId": 1234,
        "QuuxName": "Sam"
    }
}

if we have to extract ABC : get_json_object(jdata, '$.Foo') 

Azure VPN Gateway with Cisco ASA using Routing

The Azure VPN Gateway and Cisco ASA can encounter routing-type issues when configured together. To resolve this, the UsePolicyBasedTrafficSelectors must be enabled in the Azure Connection. The provided code is a PowerShell script that retrieves the specified Azure virtual network gateway connection and creates a new IPsec policy with specific parameters. The script then sets the IPsec policies for the connection to the new policy and enables UsePolicyBasedTrafficSelectors to solve the routing issue.

$RG1 = "****************"

This line declares a variable $RG1 and sets its value to a string of asterisks. This is likely just a placeholder for the actual resource group name.

$Connection16 = "****************"

Similar to the first line, this line declares a variable $Connection16 and sets its value to a string of asterisks. This is likely just a placeholder for the actual connection name.

$connection6 = Get-AzureRmVirtualNetworkGatewayConnection -Name $Connection16 -ResourceGroupName $RG1

This line retrieves the virtual network gateway connection object for a connection with the specified name ($Connection16) in the specified resource group ($RG1). The connection object is assigned to the variable $connection6.

$newpolicy6 = New-AzureRmIpsecPolicy -IkeEncryption AES256 -IkeIntegrity SHA384 -DhGroup DHGroup24 -IpsecEncryption AES256 -IpsecIntegrity SHA1 -PfsGroup PFS24 -SALifeTimeSeconds 28800 -SADataSizeKilobytes 4608000

This line creates a new IPsec policy object ($newpolicy6) with the specified settings for IKE encryption, integrity, DH group, IPsec encryption, integrity, Perfect Forward Secrecy (PFS) group, Security Association (SA) lifetime, and SA data size.

Set-AzureRmVirtualNetworkGatewayConnection -VirtualNetworkGatewayConnection $connection6 -IpsecPolicies $newpolicy6

This line updates the virtual network gateway connection object ($connection6) with the new IPsec policy ($newpolicy6) created in the previous step.

Set-AzureRmVirtualNetworkGatewayConnection -VirtualNetworkGatewayConnection $connection6 -IpsecPolicies $newpolicy6 -UsePolicyBasedTrafficSelectors $True

This line updates the virtual network gateway connection object ($connection6) again, this time enabling policy-based traffic selectors by setting the -UsePolicyBasedTrafficSelectors parameter to $True. This is necessary to resolve routing issues that can occur when configuring the Azure VPN Gateway with a Cisco ASA.

PS Azure:\> $connection6.UsePolicyBasedTrafficSelectors

True

Azure:\

PS Azure:\> $connection6.IpsecPolicies