Manage Users, Groups, and Roles

the {{ocienthyperscaledatawarehouse}} can have one or more databases, each of which can run multiple applications administrators have the ability to define and manage users in {{ocient}} to meet the needs of a multi tenant environment through user, group, and role management capabilities in ocient, administrators can make sure that users only have privileges to the objects and databases they need they can also set security settings for passwords for details, see database password security settings docid 3fiusnpipj97zfs1tbm5g users users in ocient are represented by a username and password they can have privileges to the system or one or more databases within the system privileges of a user can be granted directly to the user or inherited through groups or roles users can also belong to zero or many groups and can have zero or many pre defined roles in the system a user identifier might be used by someone logging directly into ocient through the ocient command line interface (cli) or by an application connecting to a database fully qualified user names a fully qualified user name (fqun) is the combination of a username and the database on which the user was created for example jsmith\@metrics it is not mandatory for users to use an fqun to connect to their own database an fqun uniquely identifies a user and follows the pattern "\<user name>@\<database name>" , where \<user name> is the username and \<database name> is the name of the database use an fqun to reference a user associated with a database other than the one you are connected to 82,108,143,250 false true left unhandled content type left unhandled content type left unhandled content type left false unhandled content type left unhandled content type left unhandled content type left unhandled content type left false unhandled content type left unhandled content type left unhandled content type left unhandled content type left false unhandled content type left unhandled content type left unhandled content type left unhandled content type left false unhandled content type if you do not specify an fqun when you reference a user object, ocient assumes the user is associated with the database of the active connection this example shows the intended use of the fqun and how the database is implicitly selected when you connect to the database given an ocient system with two databases, operations and metrics , create two administrator users and give administrator rights to each on their respective database true falsefalse unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type these are two separate users those users can set up their database for the remainder of their users, including create users with various roles and privileges set up sso controls for each database create tables create pipelines to load data create service classes it is not mandatory for users to use an fqun to connect to their database the first user can connect to the operations database as the user admin with password1 the system treats this login as the user admin\@operations similarly, the second user can connect to the metrics database as the user admin with password2 in this example, neither user has to use their fqun, because they only work with the database described by their fqun when users have permission to connect to more than one database, they are required to use their fqun for any database that does not match their fqun for example, if the user jsmith\@metrics also has access to the auctions database, then the user can connect with their fqun jsmith\@metrics groups a group is a collection of users that has a set of privileges assigned to it administrators can create groups that represent an application or a specific job role for users within the database for example, a group named us analysts might only have privileges that allow access to data from the us groups belong to individual databases in other words, you cannot define a group with privileges that span multiple databases a service class defines a set of limits on groups the database applies service classes on a per group basis by default, all groups are in the default service class that has no limits for details, see workload management and service classes docid\ sibw5ky21wk028mfgeqzs roles similar to groups, users can inherit privileges by being granted one of the pre defined roles in ocient the system pre defines the names of and privileges assigned to roles in ocient roles can apply to the ocient system or one of the user defined databases system roles security administrator — can read, create, and modify all users or 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 you can grant system roles to individual users whether they are members of a specific user database or defined on system database roles db public — can grant and distribute privileges using this public role every user created in the database has this role database administrator — can read, create, and modify database objects such as tables, clusters, databases, etc the database administrator can also create users for the database and kill running queries database analyst — read only rights to database objects and data within the database these roles are named with the convention "\<database name> \<role name>" , where \<database name> is the name of the database and \<role name> is the name of the role the sys roles system catalog table displays this information to learn about the syntax for granting privileges, see data control language (dcl) statement reference docid\ asr8r6xqiyofgaz5qnbiw use dcl to assign user privileges to assign or revoke privileges to users, administrators can use data control language (dcl) statements you can execute dcl statements like any other sql statements against the ocient system and databases these statements allow for quick and easy modification of user access for details, see the data control language (dcl) statement reference docid\ asr8r6xqiyofgaz5qnbiw system database every ocient system includes an administration interface named system the ocient system lists system as a database, but it is not actually an active database system serves as a connection point for administration, particularly during the initial system setup to configure other databases, storage, user access, and other configurations when connected to system , users can see metadata for all user databases to which they have rights only users who have system administrator or system analyst roles can see metadata for nodes, node service roles, storage spaces, and storage clusters a user can log into system if any of these conditions are true you create the user on that database — e g , johndoe\@system you assign the user the role of either system administrator or system analyst the non system user (e g , myuser\@some database ) has been granted system privileges through dcl, e g , grant use on system to user some user\@some database; user databases user databases are defined by users these databases can house tables, views, and data you can create and scope users to a database to allow multi tenancy on an ocient system or limit the ability of users to see the data or schemas defined in other user databases when a user is connected to a user database, they can query the user data in this database and see the metadata for this database in system tables visibility of catalog components based on user privileges all system catalog tables (e g , sys tables , sys users , sys databases ) are queryable to all users of ocient regardless of their connection the contents of these tables can change based on the rights that the individual user has for example, if a user is blocked from a specific database, then a query on the system catalog by that user would not show that database or any of its objects related links data control language (dcl) statement reference docid\ asr8r6xqiyofgaz5qnbiw users, groups, and service classes docid\ hcnnsmzcvpai1kqlhtzud database password security settings docid 3fiusnpipj97zfs1tbm5g sql reference docid\ twelobi0rarxjfiot1a70