Data Control Language (DCL) Statement Reference

In order to assign privileges to users, administrators can use Data Control Language (DCL) statements. You can write and execute DCL statements like any other SQL statements against the System and databases. DCL statements allow you to quickly and easily grant or revoke privileges from users. DCL statements work off two main concepts: Grants and Revokes. Statements that begin with GRANT give the associated privileges to a user or group. Statements that begin with REVOKE remove those privileges from a user or group. A full list of privileges can be found in the Ocient Privileges Reference. Supported DCL SQL statements are:

GRANT PRIVILEGE
REVOKE PRIVILEGE
GRANT ROLE
REVOKE ROLE

When you create an object in the system, database, or schema, the Ocient System automatically grants you all valid privileges for that type of object. For example, if you create a table in a database, the system grants you all privileges that apply to the created table. For details, see Table Privileges.For non-SSO users, the system assigns creator privileges to the user. For SSO users, the system follows specific criteria to assign the privileges. To understand the criteria the system uses, see User Access Control and Workload Management with SSO.

Grant Privileges

The GRANT statement grants privileges to a user or group. The privileges that you can grant in the GRANT statement map to the tables and differ per object. The WITH GRANT OPTION keywords specify that the grantee can also grant privileges on that object. To grant privileges, you must have:

VIEW privileges for the specified user or group being granted new privileges.
SYSAUTH privileges over the system or database object that the specified user or group is being granted privileges on.

Syntax

SQL

GRANT {
    { CREATE { <systemObjects> }
        | VIEW { SCHEMA | TABLE | VIEW | QUERIES | REDACTED QUERIES }
        | ALTER { SCHEMA | TABLE | VIEW }
        | DROP { SCHEMA | TABLE | VIEW }
        | CANCEL QUERIES
        | SELECT | DELETE | SYSAUTH | USE | ALL }
        ON SYSTEM
    | { CREATE { <databaseObjects> }
        | VIEW { SCHEMA | TABLE | VIEW  | QUERIES | REDACTED QUERIES }
        | ALTER { SCHEMA | TABLE | VIEW }
        | DROP { SCHEMA | TABLE | VIEW }
        | CANCEL QUERIES
        | SELECT | ALTER | DROP | DELETE | SYSAUTH | USE | ALL }
        ON DATABASE object_name
    | { CREATE { <schemaObjects> | MLMODEL } | VIEW { <schemaObjects> }
        | ALTER { <schemaObjects> }
        | DROP { <schemaObjects> }
        | VIEW | SELECT | ALTER | DROP | DELETE | SYSAUTH | INSERT | ALL }
        ON SCHEMA object_name
    | { VIEW | ALTER | DROP | SYSAUTH | ALL }
        ON { GROUP | USER } object_name
    | { SELECT | DROP | SYSAUTH | ALL }
        ON MLMODEL object_name
    | { VIEW | EXECUTE | ALTER | DROP | SYSAUTH | ALL }
        ON PIPELINE object_name
    | { VIEW | DROP | SYSAUTH | ALL }
        ON PIPELINE FUNCTION object_name
    | { VIEW | SELECT | ALTER | DROP | SYSAUTH | ALL }
        ON VIEW object_name
    | { VIEW | SELECT | ALTER | LOAD | DROP | DELETE | SYSAUTH | INSERT | ALL }
        ON TABLE object_name
    | { VIEW | SELECT | ALL }
        ON TABLE sys.system_catalog_table
 }
    TO { { GROUP | USER } object_name | PUBLIC } [ WITH GRANT OPTION ]

<systemObjects> ::=
    DATABASE | TABLE | VIEW | USER | GROUP
<databaseObjects> ::=
    SCHEMA | TABLE | VIEW | MLMODEL | PIPELINE | PIPELINE FUNCTION | USER | GROUP
<schemaObjects> ::=
    TABLE | VIEW

When you grant any privilege to a user on a system or database object other than the VIEW privilege, the database implicitly grants the VIEW privilege on the specified object to the user, as well as associated object types (e.g., DROP VIEW ON DATABASE also grants VIEW VIEW ON DATABASE and VIEW ON DATABASE).

Examples Grant Privilege on a Table This example grants privileges for the SELECT SQL statement on a table to a trusted group.

SQL

GRANT SELECT ON TABLE company_data TO GROUP trusted_employees;

Grant Privilege on a Database This example grants the SELECT privilege for all tables and views on the database_name database to a trusted group.

SQL

GRANT SELECT ON DATABASE database_name TO GROUP trusted_employees;

Grant Privilege on the System This example grants the SELECT privilege for all tables and views on all databases in the system to a trusted group.

SQL

GRANT SELECT ON SYSTEM TO GROUP trusted_employees;

For examples of granting privileges for individual users, see Grant Role Membership.

Revoke Privileges

The REVOKE statement revokes privileges from a user or group. To revoke privileges, you must have:

VIEW privileges for the specified user or group having their privileges revoked.
SYSAUTH privileges over the system or database object from which the specified user or group has their privileges revoked.

SQL

REVOKE [ GRANT OPTION FOR ] {
    { CREATE { <systemObjects> }
        | VIEW { SCHEMA | TABLE | VIEW | QUERIES | REDACTED QUERIES }
        | ALTER { SCHEMA | TABLE | VIEW }
        | DROP { SCHEMA | TABLE | VIEW }
        | CANCEL QUERIES
        | SELECT | DELETE | SYSAUTH | USE | ALL }
        ON SYSTEM
    | { CREATE { <databaseObjects> }
        | VIEW { SCHEMA | TABLE | VIEW | QUERIES | REDACTED QUERIES }
        | ALTER { SCHEMA | TABLE | VIEW }
        | DROP { SCHEMA | TABLE | VIEW }
        | CANCEL QUERIES
        | SELECT | ALTER | DROP | DELETE | SYSAUTH | USE | ALL }
        ON DATABASE object_name
    | { CREATE { <schemaObjects> | MLMODEL } | VIEW { <schemaObjects> }
        | ALTER { <schemaObjects> }
        | DROP { <schemaObjects> }
        | VIEW | SELECT | ALTER | DROP | DELETE | SYSAUTH | INSERT | ALL }
        ON SCHEMA object_name
    | { VIEW | ALTER | DROP | SYSAUTH | ALL }
        ON { GROUP | USER } object_name
    | { SELECT | DROP | SYSAUTH | ALL }
        ON MLMODEL object_name
    | { VIEW | EXECUTE | ALTER | DROP | SYSAUTH | ALL }
        ON PIPELINE object_name
    | { VIEW | DROP | SYSAUTH | ALL }
        ON PIPELINE FUNCTION object_name
    | { VIEW | SELECT | ALTER | DROP | SYSAUTH | ALL }
        ON VIEW object_name
    | { VIEW | SELECT | ALTER | LOAD | DROP | DELETE
        | SYSAUTH | INSERT | ALL }
        ON TABLE object_name
    | { VIEW | SELECT | ALL }
        ON TABLE sys.system_catalog_table
  }
    FROM { [ USER | GROUP ] object_name | PUBLIC }

<systemObjects> ::=
    DATABASE | TABLE | VIEW | USER | GROUP
<databaseObjects> ::=
    SCHEMA | TABLE | VIEW | MLMODEL | PIPELINE | PIPELINE FUNCTION | USER | GROUP
<schemaObjects> ::=
    TABLE | VIEW

When you revoke the VIEW privilege on a system or database object, the database implicitly revokes all privileges on the specified object from the user.If you revoke the VIEW privilege on an associated object type, the system revokes the privileges for that object type. For example, grant the DROP TABLE privilege to the user by using the GRANT DROP TABLE ON DATABASE SQL statement. Due to implicit granting by the system, this action causes the user to have DROP TABLE, VIEW TABLE, and VIEW privileges. If you revoke the VIEW TABLE privilege using the REVOKE VIEW TABLE ON DATABASE SQL statement, then the system revokes the privileges for the associated type. In this case, the user only retains the VIEW privilege on the database.

Example This example revokes the SELECT privilege on a table from an untrusted group.

SQL

REVOKE SELECT ON TABLE company_data FROM GROUP untrusted;

For examples of revoking privileges for individual users, see Revoking Role Membership.

Ocient Privileges Reference

These tables describe the privilege options on each object in Ocient and the allowed privileges.

As of version 23.0, Ocient DCL has replaced the TRUNCATE privilege with the DELETE privilege.Any TRUNCATE privileges, whether manually granted or assigned as part of a user role, should automatically convert to DELETE privileges following a system upgrade to version 23.0 or later.

Ocient System Privileges

Privilege	Description
CREATE [DATABASE, USER, GROUP]	The privilege to create the specified object type. On creation, the creating user inherits all privileges on the object. In the case of the DATABASE object, this is enforced by granting the creating user the newly created database administrator role as described in the Roles section.
CREATE [SCHEMA, TABLE, VIEW]	The privilege to create the specified object type on any database where the user can connect. On creation, the creating user inherits all privileges on the object. In order to apply the privilege, the user has to be connected to a user-defined database.
DROP [SCHEMA, TABLE, VIEW]	The privilege to drop the specified object type on any database where the user can connect. In order to apply the privilege, the user has to be connected to a user-defined database.
ALTER [SCHEMA, TABLE, VIEW]	The privilege to modify the specified object type on any database where the user can connect. In order to apply the privilege, the user has to be connected to a user-defined database.
VIEW [SCHEMA, TABLE, VIEW]	The privilege to see and read information of the specified object type within the system.
VIEW QUERIES	The privilege to see the full SQL statement and metadata for all queries within the system.
VIEW REDACTED QUERIES	The privilege to see the metadata for all queries with a fully redacted SQL statement within the system.
CANCEL QUERIES	The privilege to cancel or kill running queries within the system.
SELECT	The privilege to read data on any table or view where the user has the VIEW privilege.
SYSAUTH	The privilege to grant and revoke all privileges on the system.
USE	The ability to connect to the system.
DELETE	The privilege to delete data from and truncate tables within the system.

Database Privileges

Privilege	Description
CREATE [SCHEMA, TABLE, VIEW, MLMODEL, PIPELINE, PIPELINE FUNCTION, USER, GROUP]	The privilege to create the specified object type. The user who creates the object inherits all privileges on the object.
DROP [SCHEMA, TABLE, VIEW]	The privilege to drop the specified object type on this database.
ALTER [SCHEMA, TABLE, VIEW]	The privilege to modify the specified object type on this database.
VIEW [SCHEMA, TABLE, VIEW]	The privilege to see and read information of the specified object type within the database.
VIEW QUERIES	The privilege to see the full SQL statement and metadata for all queries in this database.
VIEW REDACTED QUERIES	The privilege to see the metadata for all queries with a fully redacted SQL statement in this database.
CANCEL QUERIES	The privilege to cancel or kill running queries in this database.
SELECT	The privilege to read data on any table or view, where the user has the VIEW privilege, in the database.
USE	Ability to connect to the database. Users created in this database are implicitly granted this privilege.
ALTER	The ability to issue ALTER DATABASE statements on this database.
DROP	The ability to drop this database.
SYSAUTH	The ability to grant and revoke all privileges on the database.
DELETE	The privilege to delete data from and truncate tables in the database.

Schema Privileges

Privilege	Description
CREATE [ TABLE \| VIEW \| MLMODEL ]	The privilege to create the specified object type.
DROP [ TABLE \| VIEW ]	The privilege to drop the specified object type within the schema.
ALTER [ TABLE \| VIEW ]	The privilege to modify the specified object type within the schema.
VIEW [ TABLE \| VIEW ]	The privilege to see and read information of the specified object type within the schema.
SELECT	The privilege to read data on any table or view, where the user has the VIEW privilege, within the schema.
ALTER	The ability to modify the schema.
DROP	The privilege to drop the schema and its contents.
VIEW	The ability to see and read information about the schema.
SYSAUTH	The ability to grant and revoke all privileges on the schema.
INSERT	The privilege to insert data into tables in the schema.
DELETE	The privilege to delete data from and truncate tables in the schema.

Granting privileges using DCL statements promotes an implicit schema to an explicit schema. For details, see Schemas.

View Privileges

Privilege	Description
VIEW	Ability to see and read schema information about the view.
SELECT	Ability to read data from the view.
ALTER	Ability to modify the view.
DROP	Ability to drop the view.
SYSAUTH	Ability to grant privileges on the view.

Table Privileges

Privilege	Description
VIEW	Ability to see and read schema information about the table.
SELECT	Ability to read data from the table.
ALTER	Ability to modify the table.
LOAD	Ability to load data into the table.
DROP	Ability to drop the table.
DELETE	Ability to delete and truncate the table.
SYSAUTH	Ability to grant and revoke privileges on the table.
INSERT	Ability to insert data into the table.

Machine Learning Model Privileges

Privilege	Description
ALTER	Ability to rename the model.
SELECT	Ability to read data and descriptive data from the model.
DROP	Ability to drop the model.
SYSAUTH	Ability to grant and revoke privileges on the model.

Data Pipeline Privileges

Privilege	Description
VIEW	See and read information schema about a pipeline.
EXECUTE	Start and stop the execution of a pipeline.
ALTER	Rename and replace a pipeline.
DROP	Drop a pipeline.
SYSAUTH	Grant and revoke privileges on a pipeline.

Data Pipeline Function Privileges

Privilege	Description
VIEW	See and read information schema about a data pipeline function in the system catalog tables.
DROP	Drop a data pipeline function.
SYSAUTH	Grant and revoke privileges on a data pipeline function.

User Privileges

Privilege	Description
VIEW	Ability to read information about the user.
ALTER	Ability to modify the user.
DROP	Ability to drop the user.
SYSAUTH	Ability to grant rights and revoke privileges on the user.

Group Privileges

Privilege	Description
VIEW	Ability to read information about the group.
ALTER	Ability to alter the group.
DROP	Ability to drop the group.
SYSAUTH	Ability to grant privileges on the group.

System Catalog and Object or View Visibility

The sys.privileges table in the system catalog exposes the privileges in the system. This table displays this information:

Timestamp of the grant
Grantor
Grantee
Privilege granted
Object type
Object id
Grantable

You can grant and revoke System Catalog VIEW and SELECT privileges just like ordinary tables. By default, everyone has VIEW and SELECT privileges on all system catalog tables. You can see the objects within the system catalog tables only if you have sufficient privileges on those objects. These objects require a VIEW or SELECT privilege or membership in a group or role to provide visibility. When you execute queries against a system catalog table, you do not see or know the existence of objects to which you do not have access. Views offer a similar functionality because the database does not check privileges to the underlying tables and views after you create a view. You can create a table with sensitive information and restrict visibility by creating a view on top of the table with only certain rows or columns. Someone else with the SELECT privilege to the view can query the view, even without any privileges to the underlying table.

Roles

Similar to groups, users can inherit privileges by being granted one of the predefined roles in Ocient. The names of and privileges assigned to roles in Ocient are predefined by the system. Roles can be applicable to the Ocient System or one of the user-defined databases. The roles in Ocient are as follows: System Roles

Security Administrator — Can read, create, and modify all users and groups.
System Administrator — Can read, create, and modify system objects such as tables, clusters, databases, etc. Can see and delete any queries system-wide.
System Analyst — Read-only access to the entire system. Can see all queries in the system.

Database Roles

Database Administrator — Can read, create, and modify database objects such as tables, clusters, databases, etc. This role can also create users for the database. Can see and delete all queries in the database.
Database Analyst — Read-only rights to database objects and data within the database. Can see all queries in the database.
Public — Every user who has access to the database has the Public role by default. You can grant other privileges to this role. This role allows the creation of a schema using the CREATE SCHEMA SQL statement and allows the viewing of other roles by default.

For specifics on each Ocient role, see Default Role Privileges.

Grant Role Membership

Grants a role to a user or group. Syntax

SQL

GRANT ROLE role_name TO { USER | GROUP } user_or_group_name

Parameter	Data Type	Description
`role_name`	string	The name of a role to be granted to the specified user or group. Enclose this string in quotes.
`user_or_group_name`	string	The name of a user or group to be granted the specified role.

Example This example grants user1 the system administrator role.

SQL

GRANT ROLE "System Administrator" TO USER user1;

Revoke Role Membership

Revokes a role from a user or group. Syntax

SQL

REVOKE ROLE role_name FROM { USER | GROUP } user_or_group_name

Parameter	Data Type	Description
`role_name`	string	The name of a role to be revoked for the specified user or group. Enclose this string in quotes.
`user_or_group_name`	string	The name of a user or group to have the specified role revoked.

Example This example revokes the system administrator role from user1.

SQL

REVOKE ROLE "System Administrator" FROM USER user1;

Default Role Privileges

Security Administrator Privileges

Target	Privileges
System	SYSAUTH, CREATE [ USER ]
Schema	SYSAUTH
Database	SYSAUTH, CREATE [ USER, GROUP ]
View	SYSAUTH
Table	SYSAUTH
Machine Learning Model	SYSAUTH
Data Pipeline	SYSAUTH
Data Pipeline Function	SYSAUTH
User	VIEW, ALTER, DROP, SYSAUTH
Group	VIEW, ALTER, DROP, SYSAUTH

System Administrator Privileges

Target	Privileges
System	USE, CREATE [ DATABASE, USER ], VIEW QUERIES, CANCEL QUERIES
Schema	VIEW, ALTER, DROP
Database	- USE - ALTER - DROP - CREATE [ SCHEMA, TABLE, VIEW, MLMODEL, USER, GROUP, PIPELINE, PIPELINE FUNCTION ]
View	VIEW, SELECT, ALTER, DROP
Table	VIEW, SELECT, ALTER, LOAD, DROP, DELETE, INSERT
Machine Learning Model	SELECT, DROP
Data Pipeline	VIEW, EXECUTE, ALTER, DROP
Data Pipeline Function	VIEW, DROP
User	VIEW, ALTER, DROP
Group	VIEW, ALTER, DROP

System Analyst Privileges

Target	Privileges
System	USE, VIEW QUERIES
Schema	VIEW
Database	USE
View	VIEW, SELECT
Table	VIEW, SELECT
Machine Learning Model	SELECT
Data Pipeline	VIEW
Data Pipeline Function	VIEW
User	VIEW
Group	VIEW

Database Administrator Privileges

Target	Privileges
Schema	VIEW, ALTER, DROP, SYSAUTH
Database	- USE - ALTER - DROP - CREATE [ SCHEMA, TABLE, VIEW, MLMODEL, USER, GROUP, PIPELINE, PIPELINE FUNCTION ] - VIEW QUERIES, CANCEL QUERIES - SYSAUTH
View	VIEW, SELECT, ALTER, DROP, SYSAUTH
Table	VIEW, SELECT, ALTER, LOAD, DROP, DELETE, INSERT, SYSAUTH
Machine Learning Model	SELECT, DROP, SYSAUTH
Data Pipeline	VIEW, EXECUTE, ALTER, DROP, SYSAUTH
Data Pipeline Function	VIEW, DROP, SYSAUTH
User	VIEW, ALTER, DROP, SYSAUTH
Group	VIEW, ALTER, DROP, SYSAUTH

Database Analyst Privileges

Target	Privileges
Schema	VIEW
Database	USE, VIEW QUERIES
View	VIEW, SELECT
Table	VIEW, SELECT
Machine Learning Model	SELECT
Data Pipeline	VIEW
Data Pipeline Function	VIEW
User	VIEW
Group	VIEW

Public Privileges

Target	Privileges
Database	CREATE SCHEMA

Query Visibility

The visibility of queries depends on which database you are logged into and your assigned privileges. Users with no additional VIEW QUERIES or VIEW REDACTED QUERIES privileges can view only queries that they submit themselves. The VIEW QUERIES privilege allows access to queries for all users in these system catalog tables:

sys.queries
sys.completed_queries
sys.plans
sys.active_query_scheduler_info
sys.all_operator_instances
sys.completed_operator_instances
sys.op_inst_debug_info

The VIEW REDACTED QUERIES privilege allows access to queries for all users in these system catalog tables, but with a redacted SQL statement for queries from other users:

sys.queries
sys.completed_queries

For the initial grant of query privileges, the grantor must have the Database Administrator or System Administrator role. Use the WITH GRANT OPTION keywords to allow a user to further grant this privilege to other users and groups. Along with having role privileges, users also must be logged into the appropriate database to view queries:

If the user logs in to the SYSTEM database, the user can see all queries from all databases.
If the user logs in to a database other than SYSTEM, the user can see queries in only that database.

Database Administration Manage Users, Groups, and Roles Object-Type Level Privileges Management

​Grant Privileges

​Revoke Privileges

​Ocient Privileges Reference

​Ocient System Privileges

​Database Privileges

​Schema Privileges

​View Privileges

​Table Privileges

​Machine Learning Model Privileges

​Data Pipeline Privileges

​Data Pipeline Function Privileges

​User Privileges

​Group Privileges

​System Catalog and Object or View Visibility

​Roles

​Grant Role Membership

​Revoke Role Membership

​Default Role Privileges

​Security Administrator Privileges

​System Administrator Privileges

​System Analyst Privileges

​Database Administrator Privileges

​Database Analyst Privileges

​Public Privileges

​Query Visibility

​Login Impacts on Query Visibility

​Related Links

Grant Privileges

Revoke Privileges

Ocient Privileges Reference

Ocient System Privileges

Database Privileges

Schema Privileges

View Privileges

Table Privileges

Machine Learning Model Privileges

Data Pipeline Privileges

Data Pipeline Function Privileges

User Privileges

Group Privileges

System Catalog and Object or View Visibility

Roles

Grant Role Membership

Revoke Role Membership

Default Role Privileges

Security Administrator Privileges

System Administrator Privileges

System Analyst Privileges

Database Administrator Privileges

Database Analyst Privileges

Public Privileges

Query Visibility

Login Impacts on Query Visibility

Related Links