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 {{ocient}} 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 docid\ f55ngxtki0f7kkmyatvug 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 docid\ f55ngxtki0f7kkmyatvug 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 docid\ rurudicbkdqxfu y99p1r 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 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 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 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 grant select on system to group trusted employees; for examples of granting privileges for individual users, see docid\ f55ngxtki0f7kkmyatvug 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 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 p rivileges 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 revoke select on table company data from group untrusted; for examples of revoking privileges for individual users, see docid\ f55ngxtki0f7kkmyatvug 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 docid\ m9zbpvfqpm96jidxyhqvi 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 a ll 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 docid\ f55ngxtki0f7kkmyatvug grant role membership grants a role to a user or group syntax 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 grant role "system administrator" to user user1; revoke role membership revokes a role from a user or group syntax 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 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 login impacts on query visibility 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 related links docid\ t7tsayn4pqz7ymaotomvp docid\ k rpf30u 1c3jfq 7ahiu docid\ mqfbwzxff95wlq4flpobn