Authorization is the function of specifying access rights to resources and operations. In Jethro, once a user is properly authenticated and granted an access to the system, defined ROLES and OWNERSHIP will control which content and objects will the user be able to access, and which actions a user, or group of users, would be able to perform on that content.

*While authorization and authentication are closely connected, it is important not to confuse between them, as they are distinct from one another. Authentication verifies the identity of a client, while Authorization determines its operations and access policies. To learn more about Authentication, visit its documentation page.

The following subjects will be covered under this section:

Ownership Permissions Model

As a result:

Roles Permissions Model

Objects Hierarchy

Permissions are managed according to a parent/child hierarchy.

Any permission granted to an object, applies also to its descendant objects.

Objects Hierarchy









Permissions for objects

PermissionObjectsDescription
ALLINSTANCE, SCHEMA, TABLELets you create or modify an object. Required to run DDL statements.
INSERTINSTANCE, SCHEMA, TABLE
Lets you write data to a table.
SELECTINSTANCE, SCHEMA, TABLE
Lets you read data from a table or view.

Permissions for operations

OperationScopeRequired Permission
Schemas
CREATE SCHEMAINSTANCEALL
DROP SCHEMASCHEMAALL
ALTER SCHEMA … RENAME TOINSTANCEALL
ALTER SCHEMA … OWNER TOINSTANCEALL
SHOW SCHEMAS


The output includes only the accessible schemas:

- Of which the user holds SELECT/INSERT/ALL permissions to

- Which contains objects (table/view) he is authorized for (SELECT/INSERT/ALL)
Tables
CREATE TABLESCHEMAALL
DROP TABLETABLEALL
TRUNCATE TABLETABLEALL
DESCRIBE TABLETABLESELECT/INSERT
ALTER TABLE … ADD COLUMNSSCHEMAALL
ALTER TABLE … DROP COLUMNSCHEMAALL
ALTER TABLE … DROP PARTITIONTABLEALL
ALTER TABLE … ADD PRIMARY KEYSCHEMAALL
ALTER TABLE … DROP PRIMARY KEYSCHEMAALL
ALTER TABLE … OWNER TOSCHEMAALL
CREATE EXTERNAL TABLESCHEMAALL
SHOW TABLE COLUMNSTABLESELECT/INSERT
SHOW TABLE PARTITIONSTABLESELECT/INSERT
SHOW TABLES MAINT

The output includes only accessible tables (has SELECT/INSERT/ALL)

SHOW TABLES EXTENDED
The output includes only accessible tables (has SELECT/INSERT/ALL)
SHOW TABLES
The output includes only accessible tables (has SELECT/INSERT/ALL)
SHOW ALL TABLES
The output includes only accessible tables (has SELECT/INSERT/ALL)
SHOW FULL TABLES
The output includes only accessible tables (has SELECT/INSERT/ALL)
SHOW FULL COLUMNS
The output includes only accessible tables (has SELECT/INSERT/ALL)
Views
DROP VIEWVIEWALL
SHOW VIEWS
The output includes only accessible views (has SELECT/INSERT/ALL)
Special Indexes
CREATE JOIN INDEXTABLESALL
DROP JOIN INDEXTABLESALL
SHOW JOIN INDEXES

Join indexes on tables that are accessible (has SELECT/INSERT)

CREATE RANGE INDEXTABLEALL
DROP RANGE INDEXTABLEALL
SHOW RANGE INDEXES

Range indexes on tables that are accessible (has SELECT/INSERT)

Cubes
GENERATE CUBESTABLESALL
SHOW CUBES

Cubes that the tables in their select statement are accessible (has SELECT access to)

DROP CUBESINSTANCEALL
Select
SELECTTABLE/VIEWSELECT
Parameters
SETAny
SET/UNSET GLOBALINSTANCEALL
SHOW PARAMAny
SHOW PARAM EXTENDEDINSTANCEALL
Admin
SHOW ADAPTIVE CACHEINSTANCEALL
DROP ADAPTIVE CACHEINSTANCEALL
DROP ADAPTIVE STORAGEINSTANCEALL
SHOW LOCAL CACHE EXTENDEDINSTANCEALL
SHOW ACTIVE QUERIESINSTANCEALL
CREATE SCHEDULED LOADTABLEINSERT
SHOW SCHEDULED LOADSINSTANCEALL
DROP SCHEDULED LOADSTABLEINSERT
DROP ALL SCHEDULED LOADSINSTANCEALL
External Data Source
CREATE EXTERNAL DATA SOURCEINSTANCEALL
CREATE EXTERNAL TABLESCHEMAALL
Authorizations
CREATE ROLEINSTANCEALL
DROP ROLEINSTANCEALL
GRANT ROLEINSTANCEALL
REVOKE ROLEINSTANCEALL
GRANT PERMISSION .. TO ROLEINSTANCEALL
REVOKE PERMISSION .. TO ROLEINSTANCEALL
SHOW GRANT ON [ALL | TABLE ...| VIEW ...| SCHEMA...]INSTANCEALL
SHOW ROLESINSTANCEALL
SHOW CURRENT ROLEAny
SHOW ROLES GRANT GROUP

INSTANCE or group members

ALL
SHOW ROLES GRANT GROUPSINSTANCEALL
SHOW GRANT ROLE

INSTANCE + users that had been granted this role

ALL
SHOW LDAP GROUPSINSTANCEALL

Commands Syntax

Roles

CREATE ROLE

Create a role to which permissions are granted

CREATE ROLE [role-name]

DROP ROLE

Remove an existing role. Once role is drop the role will be revoked from all users to whom it was previously assigned

DROP ROLE [role-name]

GRANT ROLE

Grant role to group(s).

GRANT ROLE role-name [, role-name] TO GROUP group-name [, GROUP group-name]

REVOKE ROLE

Revoke role from group

REVOKE ROLE role-name [, role-name] FROM GROUP group-name [, GROUP group-name]

SET ROLE

Enable specific role for the current session. Only granted roles can be enabled. When setting specific roles, any other roles that not set at session level are disabled. By default all roles for user are enabled (SET ROLE ALL).

Enable a specific role:

SET ROLE <role-name>

Enable all roles:

SET ROLE ALL

Disable all roles:

SET ROLE NONE

SHOW ROLES

List all the roles in the system:

SHOW ROLES

List all roles in effect for the current user session:

SHOW CURRENT ROLES

List all roles assigned to a given group:

SHOW ROLES GRANT GROUP <group-name>

List all roles granted to all groups.

SHOW ROLES GRANT GROUPS

SHOW GRANT ROLES

List all permissions granted to a role:

SHOW GRANT ROLE <role-name>

List all permissions granted to a role on a give object

SHOW GRANT ROLE <role-name> on object <object-name>

PERMISSIONS

GRANT <PERMISSION>

Grant a permission on an object to a role.

GRANT <PERMISSION> [, <PERMISSION> ] ON <OBJECT_TYPE> <object-name> TO ROLE <role-name> [, ROLE <role-name>]

<OBJECT_TYPE> - type of object INSTANCE, SCHEMA, TABLE or VIEW

<object-name> - instance name, schema name, table name or view name.

REVOKE <PERMISSION>

Revoke a permission on an object from a role

REVOKE <PERMISSION> [, <PERMISSION> ] ON <OBJECT_TYPE> <object-name> FROM ROLE <role-name> [, ROLE <role-name>]

SHOW GRANT

Show granted permissions, roles and ownership on objects

SHOW GRANT ON (ALL | (SCHEMA schema-name | TABLE table-name | VIEW view-name)

ALTER

ALTER SCHEMA … OWNER TO

Change the owner of a schema. The new owner must be an existing user (if the user is an LDAP user, it must be found in the LDAP directory)

ALTER SCHEMA <schema-name> OWNER TO <new_owner>

ALTER TABLE … OWNER TO

Change the owner of a table. The new owner must be an existing user (if the user is an LDAP user, it must be found in the LDAP directory)

ALTER TABLE <table-name> OWNER TO <new_owner>

SHOW 

SHOW LDAP GROUPS

List all LDAP groups relevant for the instance found using ldap config/filter rules

SHOW LDAP GROUP

SHOW SCHEMAS

List all schemas for which the current user has schema or table level access.

SHOW SCHEMAS

SHOW TABLES

List all tables for which the current user has table level access:

SHOW TABLES [EXTENDED]

List all columns for all tables for which the current user has table level access:

SHOW ALL TABLES [...]

Command Line Operations

The following Jethro operations are executed via the command line:

The security for those operations is based on a Jethro Linux user (by default it is user 'jethro').


See also

Authentication

Auditing

Show