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.