SQL Reference

Data Control Language (DCL) Command 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 Commands are:
GRANT PRIVILEGE
REVOKE PRIVILEGE
GRANT ROLE
REVOKE ROLE
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 keyword specifies 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> } | SYSAUTH | USE | ALL } 
    ON SYSTEM
  | { CREATE { <databaseObjects> } | USE | ALTER | DROP | SYSAUTH | ALL }   
    ON DATABASE database_name
  | { VIEW | DROP | SYSAUTH | ALL }                                       
    ON CONNECTION object_name
  | { VIEW | ALTER | DROP | SYSAUTH | ALL }                               
    ON { GROUP | USER } object_name
  | { SELECT | DROP | SYSAUTH | ALL }                                     
    ON MLMODEL 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> ::=
   CONNECTION | DATABASE | USER
<databaseObjects> ::=
   TABLE | VIEW | MLMODEL | USER | GROUP
GRANT { 
  { CREATE { <systemObjects> } | SYSAUTH | USE | ALL } 
    ON SYSTEM
  | { CREATE { <databaseObjects> } | USE | ALTER | DROP | SYSAUTH | ALL }   
    ON DATABASE database_name
  | { VIEW | DROP | SYSAUTH | ALL }                                       
    ON CONNECTION object_name
  | { VIEW | ALTER | DROP | SYSAUTH | ALL }                               
    ON { GROUP | USER } object_name
  | { SELECT | DROP | SYSAUTH | ALL }                                     
    ON MLMODEL 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> ::=
   CONNECTION | DATABASE | USER
<databaseObjects> ::=
   TABLE | VIEW | MLMODEL | USER | GROUP
﻿
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.
Example
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 SELECT ON TABLE company_data 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 that the specified user or group is having their privileges revoked from.
SQL
REVOKE [ GRANT OPTION FOR ] {  
  { CREATE { <systemObjects> } | SYSAUTH | ALL }                        
    ON SYSTEM
  | { CREATE { <databaseObjects> } | USE | ALTER | DROP | SYSAUTH | ALL } 
    ON DATABASE database_name
  | { VIEW | DROP | SYSAUTH | ALL }                                       
    ON CONNECTION object_name
  | { VIEW | ALTER | DROP | SYSAUTH | ALL }                               
    ON { GROUP | USER } object_name
  | { SELECT | DROP | SYSAUTH | ALL }                                     
    ON MLMODEL 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> ::=
   CONNECTION | DATABASE | USER
<databaseObjects> ::=
   TABLE | VIEW | MLMODEL | USER | GROUP
REVOKE [ GRANT OPTION FOR ] {  
  { CREATE { <systemObjects> } | SYSAUTH | ALL }                        
    ON SYSTEM
  | { CREATE { <databaseObjects> } | USE | ALTER | DROP | SYSAUTH | ALL } 
    ON DATABASE database_name
  | { VIEW | DROP | SYSAUTH | ALL }                                       
    ON CONNECTION object_name
  | { VIEW | ALTER | DROP | SYSAUTH | ALL }                               
    ON { GROUP | USER } object_name
  | { SELECT | DROP | SYSAUTH | ALL }                                     
    ON MLMODEL 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> ::=
   CONNECTION | DATABASE | USER
<databaseObjects> ::=
   TABLE | VIEW | MLMODEL | USER | GROUP
﻿
Example
This example revokes the SELECT privilege on a table from an untrusted group.
SQL
REVOKE SELECT ON TABLE company_data FROM GROUP untrusted;
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 [CONNECTION, DATABASE, USER]
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.
SYSAUTH
The privilege to grant and revoke all privileges on the system.
USE
The ability to connect to the system.
Database Privileges
Privilege
Description
CREATE [ TABLE, VIEW, MLMODEL, USER, GROUP]
The privilege to create the specified object type. The user that creates the object inherits all privileges on the object.
USE
Ability to connect to the database. Users created in this database are implicitly granted this privilege.
ALTER
The ability to issue ALTER DATABASE commands on this database.
DROP
The ability to drop this database.
SYSAUTH
The ability to grant and revoke all privileges on the database.
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.
Translation Privileges 
Privilege
Description
VIEW
Ability to read translation parameters.
DROP
Ability to drop the translation.
SYSAUTH
Ability to grant and revoke privileges on the translation.
Connection Privileges
Privilege
Description
VIEW
Ability to read connection parameters.
DROP
Ability to drop a connection.
SYSAUTH
Ability to grant and revoke privileges on the model.
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.
The System Catalog and Object or View Visibility
The sys.privileges table in the system catalog exposes the privileges in the system. This table displays the following 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 nor know the existence of the objects where 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 also kill running queries system-wide.
System Analyst: Read-only access to the entire system.
Database Roles
Administrator: Can read, create, and modify database objects such as tables, clusters, databases, etc. Can also create users for the database and kill running queries.
Analyst: Read-only rights to database objects and data within the database.
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
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;
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
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;
REVOKE ROLE "System Administrator" FROM USER user1;
﻿
Default Role Privileges
Security Administrator Privileges 
Target 
Privileges 
System 
SYSAUTH, CREATE [ USER ] 
Database 
SYSAUTH, CREATE [ USER, GROUP ] 
View 
SYSAUTH 
Table 
SYSAUTH 
Machine Learning Model 
SYSAUTH 
Translation 
VIEW, SYSAUTH 
Connection 
SYSAUTH 
User 
VIEW, ALTER, DROP, SYSAUTH 
Group 
VIEW, ALTER, DROP, SYSAUTH 
System Administrator Privileges 
Target 
Privileges 
System 
USE, CREATE [ DATABASE, CONNECTION, USER ] 
Database 
USE
ALTER
DROP
CREATE [ TABLE, VIEW, MLMODEL, USER, GROUP, TRANSLATION ] 
View 
VIEW, SELECT, ALTER, DROP 
Table 
VIEW, SELECT, ALTER, LOAD, DROP, DELETE, INSERT 
Machine Learning Model 
SELECT, DROP 
Translation 
VIEW, DROP 
Connection 
VIEW, DROP 
User 
VIEW, ALTER, DROP 
Group 
VIEW, ALTER, DROP 
System Analyst Privileges 
Target 
Privileges 
System 
USE 
Database 
USE 
View 
VIEW, SELECT 
Table 
VIEW, SELECT 
Machine Learning Model 
SELECT 
Translation 
VIEW 
Connection 
VIEW 
User 
VIEW 
Group 
VIEW 
Database Administrator Privileges 
Target 
Privileges 
Database 
USE
ALTER
DROP
CREATE [ TABLE, VIEW, MLMODEL, USER, GROUP, TRANSLATION ]
SYSAUTH 
View 
VIEW, SELECT, ALTER, DROP 
Table 
VIEW, SELECT, ALTER, LOAD, DROP, DELETE, INSERT, SYSAUTH 
Machine Learning Model 
SELECT, DROP, SYSAUTH 
Translation 
VIEW, DROP, SYSAUTH 
User 
VIEW, ALTER, DROP, SYSAUTH 
Group 
VIEW, ALTER, DROP, SYSAUTH 
Database Analyst Privileges 
Target 
Privileges 
Database 
USE 
View 
VIEW, SELECT 
Table 
VIEW, SELECT, LOAD, DELETE, INSERT 
Machine Learning Model 
SELECT 
Translation 
VIEW 
User 
VIEW 
Group 
VIEW 
Related Links
﻿Database Administration﻿﻿
﻿Manage Users, Groups, and Roles﻿﻿
﻿
﻿

Privilege	Description
CREATE [CONNECTION, DATABASE, USER]	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.
SYSAUTH	The privilege to grant and revoke all privileges on the system.
USE	The ability to connect to the system.

Privilege	Description
CREATE [ TABLE, VIEW, MLMODEL, USER, GROUP]	The privilege to create the specified object type. The user that creates the object inherits all privileges on the object.
USE	Ability to connect to the database. Users created in this database are implicitly granted this privilege.
ALTER	The ability to issue ALTER DATABASE commands on this database.
DROP	The ability to drop this database.
SYSAUTH	The ability to grant and revoke all privileges on the database.

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.

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.

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.

Privilege	Description
VIEW	Ability to read translation parameters.
DROP	Ability to drop the translation.
SYSAUTH	Ability to grant and revoke privileges on the translation.

Privilege	Description
VIEW	Ability to read connection parameters.
DROP	Ability to drop a connection.
SYSAUTH	Ability to grant and revoke privileges on the model.

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.

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.

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.

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.

Target	Privileges
System	SYSAUTH, CREATE [ USER ]
Database	SYSAUTH, CREATE [ USER, GROUP ]
View	SYSAUTH
Table	SYSAUTH
Machine Learning Model	SYSAUTH
Translation	VIEW, SYSAUTH
Connection	SYSAUTH
User	VIEW, ALTER, DROP, SYSAUTH
Group	VIEW, ALTER, DROP, SYSAUTH

Target	Privileges
System	USE, CREATE [ DATABASE, CONNECTION, USER ]
Database	USE ALTER DROP CREATE [ TABLE, VIEW, MLMODEL, USER, GROUP, TRANSLATION ]
View	VIEW, SELECT, ALTER, DROP
Table	VIEW, SELECT, ALTER, LOAD, DROP, DELETE, INSERT
Machine Learning Model	SELECT, DROP
Translation	VIEW, DROP
Connection	VIEW, DROP
User	VIEW, ALTER, DROP
Group	VIEW, ALTER, DROP

Target	Privileges
System	USE
Database	USE
View	VIEW, SELECT
Table	VIEW, SELECT
Machine Learning Model	SELECT
Translation	VIEW
Connection	VIEW
User	VIEW
Group	VIEW

Target	Privileges
Database	USE ALTER DROP CREATE [ TABLE, VIEW, MLMODEL, USER, GROUP, TRANSLATION ] SYSAUTH
View	VIEW, SELECT, ALTER, DROP
Table	VIEW, SELECT, ALTER, LOAD, DROP, DELETE, INSERT, SYSAUTH
Machine Learning Model	SELECT, DROP, SYSAUTH
Translation	VIEW, DROP, SYSAUTH
User	VIEW, ALTER, DROP, SYSAUTH
Group	VIEW, ALTER, DROP, SYSAUTH

Target	Privileges
Database	USE
View	VIEW, SELECT
Table	VIEW, SELECT, LOAD, DELETE, INSERT
Machine Learning Model	SELECT
Translation	VIEW
User	VIEW
Group	VIEW

Updated 20 May 2024

Did this page help you?

Statistics Cache Management

Errors and Warnings