REVOKE
Purpose
Use the REVOKE statement to withdraw system privileges, object privileges, roles or the access to connections.
Prerequisites
- For system privileges, the revoker requires the
GRANT ANY PRIVILEGE
system privilege, or the user must have received this system privilege with theWITH ADMIN OPTION
. - For object privileges, the revoker requires the
GRANT ANY OBJECT PRIVILEGE
system privilege, or the user must be the owner of the object. - For roles, the revoker requires the
GRANT ANY ROLE
system privilege, or the user must have received the role with theWITH ADMIN OPTION
. - Revoking impersonation privileges are restricted to users with role DBA.
- For connections, the revoker requires the
GRANT ANY CONNECTION
system privilege, or the user must have received the connection with theWITH ADMIN OPTION
.
Syntax
revoke_system_privileges::=
revoke_object_privileges::=
revoke_roles::=
revoke_impersonation::=
revoke_connection_restricted::=
revoke_connection::=
Usage Notes
- If a user has received the same privilege or the same role from several users, then a corresponding
REVOKE
will delete all of these. - If an object privilege was granted to a single schema object and its schema (implicitly to all contained objects), and the privilege of the schema was revoked, then the object privilege for the single schema object is still retained.
- The object privilege
REFERENCES
can only be revoked if the corresponding user has not yet created foreign keys on that table. In this case, you can automatically drop those foreign keys by specifying the optionCASCADE CONSTRAINTS
. - Unlike in Oracle,
REVOKE ALL [PRIVILEGES]
will delete all the system or object privileges, even if the user was not granted these privileges beforehand by usingGRANT ALL
. - For information on impersonation, refer to the description of IMPERSONATE statement.
-
For information about system and object privileges, see the lists below. For more details about privileges and rights management, refer to Details on Rights Management under the Privileges section.
List of System and Object Privileges
The following tables describe the system privileges in Exasol.
Miscellaneous
System Privilege | Permissions |
---|---|
GRANT ANY OBJECT PRIVILEGE
|
Grant or withdraw any object rights |
GRANT ANY PRIVILEGE
|
Grant or withdraw any system rights (this is a powerful privilege and should only be granted to a small number of users) |
SET ANY CONSUMER GROUP
|
Set consumer group for users or roles |
MANAGE CONSUMER GROUPS
|
Create, alter and drop consumer groups |
CREATE SESSION
|
Connect to database |
KILL ANY SESSION
|
Kill session or query |
ALTER SYSTEM
|
Alter system-wide settings (for example, |
IMPORT
|
Import data into tables using the |
EXPORT
|
Export data from tables using the |
Users
System Privilege | Permissions |
---|---|
CREATE USER
|
Create user |
ALTER USER
|
Alter the password of any user (this is a powerful privilege and should only be granted to a small number of users) |
DROP USER
|
Delete user |
IMPERSONATE ANY USER
|
Impersonate other users |
Roles
System Privilege | Permissions |
---|---|
CREATE ROLE
|
Create roles |
DROP ANY ROLE
|
Delete roles |
GRANT ANY ROLE
|
Grant any role (this is a powerful privilege and should only be granted to a small number of users) |
Connections
System Privilege | Permissions |
---|---|
CREATE CONNECTION
|
Create external connections |
ALTER ANY CONNECTION
|
Change connection data of a connection |
DROP ANY CONNECTION
|
Delete connections |
GRANT ANY CONNECTION
|
Grant any connection to users/roles |
USE ANY CONNECTION
|
Using any connection in statements |
ACCESS ANY CONNECTION
|
Access any connection details from scripts |
Schema
System Privilege | Permissions |
---|---|
CREATE SCHEMA
|
Create a schema |
ALTER ANY SCHEMA
|
Allocate a schema to another user or role |
DROP ANY SCHEMA
|
Delete any schema |
CREATE VIRTUAL SCHEMA
|
Create of a virtual schema |
ALTER ANY VIRTUAL SCHEMA
|
Update parameters of any virtual schema |
ALTER ANY VIRTUAL SCHEMA REFRESH
|
Update the metadata of any virtual schema |
DROP ANY VIRTUAL SCHEMA
|
Delete any virtual schema |
USE ANY SCHEMA
|
See and use any schema. This privilege is granted to PUBLIC by default when a new database is setup. |
Tables
System Privilege | Permissions |
---|---|
CREATE TABLE
|
Create table in one's own schema or that of an allocated role ( |
CREATE ANY TABLE
|
Create a table in any schema |
ALTER ANY TABLE
|
Alter a table in any schema |
DELETE ANY TABLE
|
Delete rows in a table in any schema |
DROP ANY TABLE
|
Delete a table in any schema |
INSERT ANY TABLE
|
Insert data into a table from any schema |
SELECT ANY TABLE
|
Access the contents of a table or view from any schema (does not include system tables) |
SELECT ANY DICTIONARY
|
Access the contents of any system table |
UPDATE ANY TABLE
|
Alter rows in a table from any schema |
Views
System Privilege | Permissions |
---|---|
CREATE VIEW
|
Create views in one's own schema or in that of an allocated role (DROP is implicit as the owner of a view can always delete these) |
CREATE ANY VIEW
|
Create a view in any schema |
DROP ANY VIEW
|
Delete a view in any schema |
Functions
System Privilege | Permissions |
---|---|
CREATE FUNCTION
|
Create functions in one's own schema or in those of an allocated role ( |
CREATE ANY FUNCTION
|
Create functions in any schema |
DROP ANY FUNCTION
|
Delete functions from any schema |
EXECUTE ANY FUNCTION
|
Execute functions from any schema |
Scripts
System Privilege | Permissions |
---|---|
CREATE SCRIPT
|
Create scripts in one's own schema or in those of an allocated role ( |
CREATE ANY SCRIPT
|
Create scripts in any schema |
DROP ANY SCRIPT
|
Delete scripts from any schema |
EXECUTE ANY SCRIPT
|
Execute scripts from any schema |
The following table describes the object privileges in Exasol.
Object Privilege | Schema Objects | Permissions |
---|---|---|
ALTER
|
Schema, table, virtual schema |
Run the |
SELECT
|
Schema, table, view, virtual schema, virtual table |
Access to the table contents |
INSERT
|
Schema, table |
Insert rows in a table |
UPDATE
|
Schema, table |
Change the contents of a row in a table |
DELETE
|
Schema, table |
Deletion of rows |
REFERENCES
|
Table |
Creation of foreign keys referencing this table |
EXECUTE
|
Schema, function, script |
Run functions or scripts |
IMPERSONATION
|
User |
Impersonating another user identity |
ACCESS
|
Connection |
Access details of a connection from scripts |
REFRESH
|
Virtual schema |
Update the metadata of a virtual schema |
USAGE
|
Schema | View and use a schema |