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 /#ocient privileges reference supported dcl sql statements 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 grant { { create { \<systemobjects> } | view { table | view } \| alter { table | view } \| drop { table | view } \| select | sysauth | use | all } on system \| { create { \<databaseobjects> } | view { table | view } \| alter { table | view } \| drop { table | view } \| select | alter | drop | sysauth | use | all } on database database 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> = table | view | mlmodel | pipeline | pipeline function | 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 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 /#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 revoke \[ grant option for ] { { create { \<systemobjects> } | view { table | view } \| alter { table | view } \| drop { table | view } \| select | sysauth | use | all } on system \| { create { \<databaseobjects> } | view { table | view } \| alter { table | view } \| drop { table | view } \| select | alter | drop | sysauth | use | all } on database database 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> = table | view | mlmodel | pipeline | pipeline function | user | group 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 /#revoke 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 \[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 \[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 \[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 \[table, view] the privilege to see and read schema information of the specified object type on any database where the user can connect 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 database privileges privilege description create \[table, view, mlmodel, pipeline, pipeline function, user, group] the privilege to create the specified object type the user that creates the object inherits all privileges on the object drop \[table, view] the privilege to drop the specified object type on this database alter \[table, view] the privilege to modify the specified object type on this database view \[table, view] the privilege to see and read schema information of the specified object type on 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 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 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 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 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 nor know the existence of the 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 for specifics on each ocient role, see /#default role privileges 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 ] 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 ] database use alter drop create \[ 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 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 database use alter drop create \[ table, view, mlmodel, user, group, pipeline, pipeline function ] 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 database use view view, select table view, select, load, delete, insert machine learning model select data pipeline view data pipeline function view user view group view query visibility the visibility of queries depends on which database you are logged into and your system role visibility by role users with no additional role privileges can view only queries that they submit themselves users with granted roles have extra visibility to see queries from any user, either for the entire system or individual databases this table summarizes the roles that have query privileges role query visibility system administrator can see and delete any queries system wide system analyst can see a ll queries in the system database administrator can see and delete all queries in the database database analyst can see all queries in the database for an overview of other privileges by role, see /#roles 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\ ip50tlb6xbcyv5hrnv0x5 docid\ gkk8nmzjjsbyfjvqzihla docid 4guttcppvu7wbxzxo1cam