Privileges

Kognitio provides the ability to grant and revoke privileges from users or entire groups. Privileges can be used to define the access each user or group has to:

  • Data (to ensure security and integrity).

  • Commands that affect overall performance and system availability for all users.

Privileges are recorded in the system tables, which are set up when Kognitio is installed.

Privileges, for example, can control a user’s ability to:

  • Create, view, drop, update and select from objects (views, tables, schemas etc)

  • Abort sessions and queries

  • Grant privileges to other users

All privileges derive from the user identity SYS (probably your System Administrator). SYS grants certain privileges when registering a new user, and has privileges on all tables in the system - both system tables, and tables created by other users. SYS can change a user’s privileges as the need arises.

Once SYS creates a schema for an ordinary user, the user can automatically create tables and views in that schema. Table owners are granted all privileges to their own tables, and can also grant privileges to another user who is not the owner of their tables.

Privileges can also be granted to a group. Users will inherit privileges from any groups they are a member of. For example, PUBLIC is a group on the system which all users are a member of. If you grant a privilege to PUBLIC, that privilege is granted to all users on the system.

Granting privileges

Single grant

The syntax for granting a specified list of privileges is:

GRANT priv1 [,priv2, priv3, ...]
ON { { [ TABLE | VIEW | SCHEMA | USER | GROUP | MODULE | QUEUE |
         CONNECTOR | SCRIPT ENVIRONMENT | EXTERNAL SCRIPT] name} | SYSTEM}
TO {user | group}
[WITH GRANT OPTION]

Not all of the above combinations will work with each other, as some privileges are only valid within certain domains. For example, you cannot run ‘grant select on system to user1’, as the ‘select’ privilege is not valid in the ‘system’ domain. See Privilege Domains for a list of domains and their valid privileges.

You may optionally specify ‘with grant option’ to allow the user or group to grant the same privileges to other users.

Aggregate grant

Privileges can also be granted on a group of objects with one query, for example a privilege could be granted on every table in a specified schema:

GRANT select ON EVERY TABLE IN SCHEMA myschema TO testuser

In addition, you can grant ALL privileges on an object, for example:

GRANT all ON SCHEMA myschema TO testuser

Specifying ‘all’ will grant every privilege which is valid in the specified domain (the domain is schema in this case) to the specified user or group. See Privilege Domains for a list of domains and the privileges which are valid in them. So in this example, testuser will be granted every privilege that is available on the ‘myschema’ schema.

You can also combine the above, and grant ALL privileges on a group of objects to a user or group, for example:

GRANT all ON EVERY TABLE IN SCHEMA myschema TO testgroup

Grant option

When granting privileges (both single and aggregate), you can optionally specify with grant option to allow the given user or group to grant the same privilige to others. Note that grant option will only allow the SAME privilege to be granted to others. If an aggregate privilege is granted with grant option, that user will NOT be able to grant single privileges to others, only the same aggregate privilege. This is demonstrated in the following example:

GRANT select ON EVERY TABLE IN SYSTEM TO user1 WITH GRANT OPTION
-- user1 can now select from every table. user1 can also run 'GRANT select ON EVERY TABLE IN SYSTEM TO user2'. However user1 can NOT run 'GRANT select ON schema1.table1 TO user2'.

GRANT select ON TABLE schema1.table1 TO user1 WITH GRANT OPTION
-- user1 can now run 'GRANT select ON schema1.table1 TO user2'.

The grant option only allows the SAME privilege to be granted to others. The only exception to this is when ALL privileges are granted on an object. ALL includes the SQL standard ALL priviliges (UPDATE, INSERT, REFERENCES, DELETE, SELECT). When ALL privileges are granted with grant option, the user can then grant any of these individual privileges to others:

GRANT all ON TABLE t1 TO user1 WITH GRANT OPTION
-- user1 now has update, insert, references, delete and select privileges on t1.  User1 can run 'GRANT all ON TABLE t1 TO user2'. User1 can also run, for example, 'GRANT select ON TABLE t1 TO user3'.

Revoking privileges

To revoke (in other words, remove) privileges, simply run the same query that you would run to grant those privileges, but with ‘revoke instead of ‘grant’, and ‘from’ instead of ‘to’.

Single revoke

The syntax is therefore:

REVOKE  [GRANT OPTION FOR] { priv1 [,priv2,priv3,...] }
ON { { [ TABLE | VIEW | SCHEMA | USER | GROUP | MODULE | QUEUE |
         CONNECTOR | SCRIPT ENVIRONMENT | EXTERNAL SCRIPT] name} | SYSTEM}
FROM {user | group}

If you specify ‘grant option for’ , it will revoke the user/group’s ability to grant the specified privileges to other users and groups, but it will not revoke those privileges from the user/group itself. If you do NOT specify ‘grant option for’, it will revoke both the user/group’s privileges AND it’s ability to grant those privileges.

Aggregate revoke

Similar syntax can be used to revoke privileges on a group of objects from a user/group. For example:

REVOKE select ON EVERY TABLE IN myschema FROM testuser

Or to revoke a group of privileges on an object from a user/group, for example:

GRANT all ON SCHEMA myschema TO testuser

Or to revoke a group of privileges on a group of objects from a user/group, for example:

GRANT all ON EVERY TABLE IN SCHEMA myschema TO testgroup

Privilege Domains

The following privilege domains exist in Kognitio:

Domain

Example Query

System

GRANT create user ON SYSTEM TO user1

Schema

GRANT create view ON SCHEMA s1 TO u3

Table

GRANT select ON TABLE s1.t1 TO u3

Column

GRANT select(a) ON s1.t1 TO u3

User

GRANT abort session ON USER u4 TO u3

Group

GRANT drop ON GROUP g1 TO u3

Module

GRANT drop ON MODULE datetime TO u3

Queue

GRANT view ON QUEUE q1 TO u3

Connector

GRANT activate ON CONNECTOR hdfs TO u3

Script environment

GRANT execute ON SCRIPT ENVIRONMENT bash TO u3

Table (system-wide)

GRANT select ON EVERY TABLE IN SYSTEM TO u3

Table (schema-wide)

GRANT select ON EVERY TABLE IN SCHEMA s1 TO u3

External script

GRANT execute ON EXTERNAL SCRIPT es1 TO u3

External script (schema-wide)

GRANT execute ON EVERY EXTERNAL SCRIPT IN schema s1 TO u3

External script (system-wide)

GRANT execute ON EVERY EXTERNAL SCRIPT IN SYSTEM TO u3

User (system-wide)

GRANT abort session ON EVERY USER IN SYSTEM TO u3

Group (system-wide)

GRANT drop ON EVERY GROUP IN SYSTEM TO u3

Module (system-wide)

GRANT drop ON EVERY MODULE IN SYSTEM TO u3

Queue (system-wide)

GRANT view ON EVERY QUEUE IN SYSTEM TO u3

Connector (system-wide)

GRANT activate ON EVERY CONNECTOR IN SYSTEM TO u3

Script environment (system-wide)

GRANT execute ON EVERY SCRIPT ENVIRONMENT IN SYSTEM TO u3

Schema (system-wide)

GRANT create view ON EVERY SCHEMA IN SYSTEM TO u3

External script (script environment-wide)

GRANT execute ON EVERY EXTERNAL SCRIPT IN ENVIRONMENT bash TO u3

Table (connector-wide)

GRANT select ON EVERY TABLE IN connector hdfs TO u3

Within each domain different privileges can be granted See full list

domain

privilege

id

Column

Update

1

Column

Insert

2

Column

Select

8

Column

References

16

Column

All

27

Connector

Drop

1

Connector

Activate

2

Connector

Configure

4

Connector

Connect

8

Connector

View

1024

Connector

Set comment

1048576

Connector

All

1049615

Connector (system-wide)

Drop

1

Connector (system-wide)

Activate

2

Connector (system-wide)

Configure

4

Connector (system-wide)

Connect

8

Connector (system-wide)

View

1024

Connector (system-wide)

Set comment

1048576

Connector (system-wide)

All

1049615

External script

Drop

1

External script

Configure

4

External script

Execute

8

External script

View

1024

External script

Set comment

1048576

External script

All

1049613

External script (schema-wide)

Drop

1

External script (schema-wide)

Configure

4

External script (schema-wide)

Execute

8

External script (schema-wide)

View

1024

External script (schema-wide)

Set comment

1048576

External script (schema-wide)

All

1049613

External script (script environment-wide)

Drop

1

External script (script environment-wide)

Configure

4

External script (script environment-wide)

Execute

8

External script (script environment-wide)

View

1024

External script (script environment-wide)

Set comment

1048576

External script (script environment-wide)

All

1049613

External script (system-wide)

Drop

1

External script (system-wide)

Configure

4

External script (system-wide)

Execute

8

External script (system-wide)

View

1024

External script (system-wide)

Set comment

1048576

External script (system-wide)

All

1049613

Group

Set members

128

Group

Drop

256

Group

View

1024

Group

Rename

16384

Group

All

17792

Group (system-wide)

Set members

128

Group (system-wide)

Drop

256

Group (system-wide)

View

1024

Group (system-wide)

Rename

16384

Group (system-wide)

All

17792

Plugin

Drop

1

Plugin

Activate

2

Plugin

Activate safe

4

Plugin

Deactivate

8

Plugin

Set parameter

16

Plugin

Set resource

32

Plugin

View

1024

Plugin

All

1087

Plugin (system-wide)

Drop

1

Plugin (system-wide)

Activate

2

Plugin (system-wide)

Activate safe

4

Plugin (system-wide)

Deactivate

8

Plugin (system-wide)

Set parameter

16

Plugin (system-wide)

Set resource

32

Plugin (system-wide)

View

1024

Plugin (system-wide)

All

1087

Queue

Alter

1

Queue

View

1024

Queue

All

1025

Queue (system-wide)

Alter

1

Queue (system-wide)

View

1024

Queue (system-wide)

All

1025

Schema

Create permanent table

1

Schema

Create view

2

Schema

Drop schema

4

Schema

View

1024

Schema

Create external script

131072

Schema

Set comment

1048576

Schema

Create temporary table

2097152

Schema

Create external table

4194304

Schema

Create table

6291457

Schema

All

7472135

Schema

Create table

50331649

Schema (system-wide)

Create permanent table

1

Schema (system-wide)

Create view

2

Schema (system-wide)

Drop schema

4

Schema (system-wide)

View

1024

Schema (system-wide)

Create external script

131072

Schema (system-wide)

Set comment

1048576

Schema (system-wide)

Create temporary table

2097152

Schema (system-wide)

Create external table

4194304

Schema (system-wide)

Create table

6291457

Schema (system-wide)

All

7472135

Schema (system-wide)

Create table

50331649

Script environment

Drop

1

Script environment

Configure

4

Script environment

Execute

8

Script environment

View

1024

Script environment

Set comment

1048576

Script environment

All

1049613

Script environment (system-wide)

Drop

1

Script environment (system-wide)

Configure

4

Script environment (system-wide)

Execute

8

Script environment (system-wide)

View

1024

Script environment (system-wide)

Set comment

1048576

Script environment (system-wide)

All

1049613

System

Create schema

1

System

Reclaim

2

System

Spin

4

System

Recreate

8

System

Create system image

32

System

Create group

512

System

Create user

1024

System

Create module

2048

System

Create queue

4096

System

Assign slabs

8192

System

Create connector

32768

System

Create script environment

65536

System

Alter user sec_class

2097152

System

Configure system

4194304

System

Drop all invalidated views

8388608

System

All

14794287

Table

Update

1

Table

Insert

2

Table

Delete

4

Table

Select

8

Table

References

16

Table

Create compressed index

32

Table

Drop compressed index

64

Table

Create image

128

Table

Drop image

256

Table

Drop table/view

512

Table

View

1024

Table

Update statistics

2048

Table

Insert statistics

4096

Table

Rename

16384

Table

Set comment

1048576

Table

Configure

2097152

Table

All

3170303

Table (connector-wide)

Update

1

Table (connector-wide)

Insert

2

Table (connector-wide)

Delete

4

Table (connector-wide)

Select

8

Table (connector-wide)

References

16

Table (connector-wide)

Create compressed index

32

Table (connector-wide)

Drop compressed index

64

Table (connector-wide)

Create image

128

Table (connector-wide)

Drop image

256

Table (connector-wide)

Drop table/view

512

Table (connector-wide)

View

1024

Table (connector-wide)

Update statistics

2048

Table (connector-wide)

Insert statistics

4096

Table (connector-wide)

Rename

16384

Table (connector-wide)

Set comment

1048576

Table (connector-wide)

Configure

2097152

Table (connector-wide)

All

3170303

Table (schema-wide)

Update

1

Table (schema-wide)

Insert

2

Table (schema-wide)

Delete

4

Table (schema-wide)

Select

8

Table (schema-wide)

References

16

Table (schema-wide)

Create compressed index

32

Table (schema-wide)

Drop compressed index

64

Table (schema-wide)

Create image

128

Table (schema-wide)

Drop image

256

Table (schema-wide)

Drop table/view

512

Table (schema-wide)

View

1024

Table (schema-wide)

Update statistics

2048

Table (schema-wide)

Insert statistics

4096

Table (schema-wide)

Rename

16384

Table (schema-wide)

Set comment

1048576

Table (schema-wide)

Configure

2097152

Table (schema-wide)

All

3170303

Table (system-wide)

Update

1

Table (system-wide)

Insert

2

Table (system-wide)

Delete

4

Table (system-wide)

Select

8

Table (system-wide)

References

16

Table (system-wide)

Create compressed index

32

Table (system-wide)

Drop compressed index

64

Table (system-wide)

Create image

128

Table (system-wide)

Drop image

256

Table (system-wide)

Drop table/view

512

Table (system-wide)

View

1024

Table (system-wide)

Update statistics

2048

Table (system-wide)

Insert statistics

4096

Table (system-wide)

Rename

16384

Table (system-wide)

Set comment

1048576

Table (system-wide)

Configure

2097152

Table (system-wide)

All

3170303

User

Drop

256

User

View

1024

User

Abort query

2048

User

Abort session

4096

User

View queries

8192

User

Rename

16384

User

Add authentication key

65536

User

Drop authentication key

131072

User

Assign queue

262144

User

Revoke user

524288

User

Configure

1048576

User

Set password

2097152

User

Add principal

4194304

User

Drop principal

8388608

User

Template

16777216

User

All

33520896

User (system-wide)

Drop

256

User (system-wide)

View

1024

User (system-wide)

Abort query

2048

User (system-wide)

Abort session

4096

User (system-wide)

View queries

8192

User (system-wide)

Rename

16384

User (system-wide)

Add authentication key

65536

User (system-wide)

Drop authentication key

131072

User (system-wide)

Assign queue

262144

User (system-wide)

Revoke user

524288

User (system-wide)

Configure

1048576

User (system-wide)

Set password

2097152

User (system-wide)

Add principal

4194304

User (system-wide)

Drop principal

8388608

User (system-wide)

Template

16777216

User (system-wide)

All

33520896

Notes

  • The COLUMN domain uses different syntax for specifying columns. The syntax is:

    GRANT priv1[,priv2, ...](column1[,column2,...]) ON TABLE name TO {user | group}
    

    So for example, to give user fred the ability to select columns c1 and c2 from table t1:

    GRANT select(c1, c2) ON TABLE t1 TO fred
    
  • Columns can be specified for UPDATE and REFERENCES but not SELECT.

  • In general, it is the System Administrator’s job to allocate privileges to users. The only exception to this occurs when a privilege is granted in a “grantable” form.

  • The privilege to grant privileges is itself a grantable privilege. Privileges can be allocated using WITH GRANT OPTION to make them grantable; that is, they can be “passed on” to other users on the system.

  • Existing privileges must be revoked before being re-assigned as grantable.

  • Granting ALL on a table will not fail if you cannot grant ALL, the subset of ALL that can be granted will be granted.

  • Granting ALL privileges on a table will attempt to grant ALL POSSIBLE (including Kognitio specific) privileges upon a table.

  • Possession of an aggregate privilege with grant option does not allow individual privileges to be granted; if you run ‘GRANT select ON EVERY TABLE IN SCHEMA s1 TO user1’, then user1 cannot grant SELECT on a specific table in S1, only the aggregate privilege.

  • UPDATE privilege does not imply SELECT privilege. To successfully perform a command of the form “UPDATE T1 SET C1 = X WHERE C1 < 0”, both UPDATE and SELECT privileges must be granted to the user on T1. A similar condition also applies to column-wide privileges.