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 true 290,290left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left 1 1 unhandled content type left 1 1 unhandled content type left 1 1 unhandled content type left 1 1 unhandled content type left 1 1 unhandled content type left 1 1 unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left 1 1 unhandled content type left 1 1 unhandled content type database privileges true 290,290left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left 1 1 unhandled content type left 1 1 unhandled content type left 1 1 unhandled content type left 1 1 unhandled content type left 1 1 unhandled content type left 1 1 unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left 1 1 unhandled content type left 1 1 unhandled content type schema privileges true 290,290left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left 1 1 unhandled content type left 1 1 unhandled content type granting privileges using dcl statements promotes an implicit schema to an explicit schema for details, see docid\ m9zbpvfqpm96jidxyhqvi view privileges 140,495 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type table privileges true 290,290left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type machine learning model privileges 143,467 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type data pipeline privileges true 290,290 unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type data pipeline function privileges true 290,290 unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type user privileges 156,443 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type group privileges 178,386 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type 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 205,95,303 true unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type 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 186,98,288 true unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type example this example revokes the system administrator role from user1 revoke role "system administrator" from user user1; default role privileges security administrator privileges true 290,290 unhandled content type unhandled content type unhandled content type unhandled content type 1 1 unhandled content type 1 1 unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type system administrator privileges 158,100 true unhandled content type unhandled content type unhandled content type unhandled content type 1 1 unhandled content type 1 1 unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type system analyst privileges true 290,290 unhandled content type unhandled content type unhandled content type unhandled content type 1 1 unhandled content type 1 1 unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type database administrator privileges 176,100 true unhandled content type unhandled content type 1 1 unhandled content type 1 1 unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type database analyst privileges true 290,290 unhandled content type unhandled content type 1 1 unhandled content type 1 1 unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type public privileges true 290,290 unhandled content type unhandled content type unhandled content type unhandled content type 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