{"id":2843,"date":"2022-09-01T11:59:29","date_gmt":"2022-09-01T11:59:29","guid":{"rendered":"https:\/\/www.checkmateq.com\/blog\/?p=2843"},"modified":"2023-08-04T16:15:36","modified_gmt":"2023-08-04T16:15:36","slug":"cockroachdb-working","status":"publish","type":"post","link":"https:\/\/www.checkmateq.com\/blog\/cockroachdb-working","title":{"rendered":"Cockroach DB Working and Authorization"},"content":{"rendered":"

In this blog you can learn how data is stored in Cockroach DB and how to authorize users to create or manipulate data, if you are completely new to Cockroach DB, please refer this blog first for installing and creating a cockroach dB\u00a0<\/a>cluster.<\/u><\/span><\/p>\n

Above given Image is basic architecture of how data is written and stored in a Cockroach DB Node, Let’s understand what each thing is and how they work together<\/p>\n

Even though Cockroach DB uses its own SQL<\/a> feature set, SQL Layer helps developers to run SQL queries, this allows them to use all the familiar concepts like schema, tables, and indexes.<\/p>\n

Distributed Key-Value Store<\/strong>:\u00a0 when you store data in a table of Cockroach DB it stores that data as a key and a value, SQL layer communicates with the distributed key-value store which allows user to develop large tables and indexes.<\/p>\n

Distributed\u00a0<\/strong>Transactions<\/b> are\u00a0the important part of this application, their implementation of this feature manages the transition from SQL to stores and ranges.<\/p>\n

Nodes<\/b> are the servers that store your data, a node can be either virtual or physical machines<\/p>\n

A Node<\/strong> may have one or more stores, and each store can hold many ranges, and these are managed by Rocks DB.<\/p>\n

As mentioned above you can treat Cockroach DB as the\u00a0 SQL database , that means you can connect your app or SQL client<\/strong> to Cockroach DB and do normal SQL stuff with it , The node that the app or client connects to is called the Gateway Node<\/strong> , and the machine that Cockroach DB\u00a0 node runs on will obviously have\u00a0 characteristics like CPU, memory and number of cores , and the layer that the APP\/SQL Client connects to is the SQL<\/strong> Layer<\/strong> , this layer creates logical\u00a0 plans and\u00a0 sends them to the transaction and distribution layers , the distribution layer<\/strong> maps the SQL statements in to key-value pairs in the form of 64 MB chunks of data\u00a0 called ranges\u00a0 and writes it to the disk .<\/p>\n

Authorization in Cockroach DB<\/h3>\n

Authorization in Cockroach DB is done using Users and\u00a0<\/strong>Roles,<\/b> its controlling over who <\/b>can\u00a0do what <\/strong>like read, write, update, delete, grant operations and on which resources like databases, tables, clusters, schemas, rows, users.\u00a0 Cockroach DB’s authorization is governed by the same policies in different scenarios like accessing the SQL shell or viewing data from the database console.<\/p>\n

Users and Roles in Cockroach DB<\/h3>\n

There is no much technical differentiation between Cockroach DB ‘s role and user, A role\/user can be permitted to log in to the SQL shell, granted privileges to perform specific actions and database objects, A privilege assigned is referred as a “role” when it’s created for managing the privileges of the “users” and not for logging in directly, which is reserved for “users”.<\/p>\n

As mentioned above SQL statements CREATE USER<\/strong> and CREATE ROLE<\/strong> will almost create the same thing but\u00a0 with one difference CREATE ROLE<\/b> will use NOLOGIN\u00a0option by default, restricting the user or role from being used to log in.<\/p>\n

Creating a user for Cockroach DB<\/h3>\n

In one of your cockroach node terminals use the below given command to open the built in SQL shell<\/p>\n

cockroach sql --certs-dir=certs --host=localhost:26257<\/pre>\n
give your certs directory name for –certs-dir\u00a0<\/strong>and node host name for –host.<\/p>\n
\"\"<\/p>\n
Now create a user and give a password for authentication.<\/p>\n
CREATE USER masteruser WITH PASSWORD 'Nalmas123';<\/pre>\n
here masteruser<\/strong> is a username and Nalmas123<\/strong> is password for the user<\/p>\n
\"\"<\/p>\n
Check user creation using the command “SHOW USERS;<\/strong>”<\/p>\n
\"\"<\/p>\n
How to Grant Privileges to a User?<\/strong><\/h3>\n
GRANT admin TO masteruser;<\/pre>\n
Above given command gives administrative access to user named masteruser\u00a0<\/strong>and admin<\/strong> is a pre predefined role here.<\/p>\n
GRANT is a statement that\u00a0you can use it to directly give privileges to a role or user, users granted a privilege\u00a0with GRANT<\/strong> <\/b>option can pass on that privilege to others, owner of an object will have the\u00a0 GRANT OPTION for all privileges by default.<\/p>\n
Cockroach DB checks the user’s and role’s permissions for each statement executed If the user does not have required permission to perform a particular operation\u00a0 \u00a0Cockroach DB gives an error.<\/p>\n
\"\"<\/p>\n
<\/h3>\n
Creating a New Role<\/strong><\/h3>\n
Now let’s create a new role that can create and rename databases.<\/p>\n
CREATE ROLE can_create_database WITH CREATEDB;<\/pre>\n
SQL statement\u00a0 CREATE ROLE \u00a0creates\u00a0 roles, which are groups containing number of other users and roles as a member, you can assign privileges to roles and all members of the role even though if they are direct or indirect members.<\/p>\n
and can_create_database<\/strong> is the role that is being created with CREATEDB<\/strong> privileges, and use SHOW ROLES; to check the creation and to see already existing roles and users,<\/p>\n
\"\"<\/p>\n
 <\/p>\n
Granting New Privileges for an<\/span> Existing Role<\/h3>\n
GRANT admin TO can_create_database;<\/pre>\n
can_create_database is a role that we have created, and admin is a privilege which give administrative access<\/p>\n
\"\"<\/p>\n
As you can see in above given snapshot role can_create_database <\/strong>is a member of admin <\/strong> now.<\/p>\n
 <\/p>\n
To learn SQL statement refer this blog<\/a><\/p>\n
Please contact<\/a> our technical consultants if you have anything related to cloud<\/a> infrastructure to be discussed.<\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n","protected":false},"excerpt":{"rendered":"
In this blog you can learn how data is stored in Cockroach DB and how to authorize users to create or manipulate data, if you are completely new to Cockroach DB, please refer this blog first for installing and creating a cockroach dB\u00a0cluster. Above given Image is basic architecture of how data is written and … Continue reading “Cockroach DB Working and Authorization”<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":2910,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[1],"tags":[3,79,2,71,70,69,68,59,11,78,63,23,16,24,80,8],"_links":{"self":[{"href":"https:\/\/www.checkmateq.com\/blog\/wp-json\/wp\/v2\/posts\/2843"}],"collection":[{"href":"https:\/\/www.checkmateq.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.checkmateq.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.checkmateq.com\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.checkmateq.com\/blog\/wp-json\/wp\/v2\/comments?post=2843"}],"version-history":[{"count":17,"href":"https:\/\/www.checkmateq.com\/blog\/wp-json\/wp\/v2\/posts\/2843\/revisions"}],"predecessor-version":[{"id":4248,"href":"https:\/\/www.checkmateq.com\/blog\/wp-json\/wp\/v2\/posts\/2843\/revisions\/4248"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.checkmateq.com\/blog\/wp-json\/wp\/v2\/media\/2910"}],"wp:attachment":[{"href":"https:\/\/www.checkmateq.com\/blog\/wp-json\/wp\/v2\/media?parent=2843"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.checkmateq.com\/blog\/wp-json\/wp\/v2\/categories?post=2843"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.checkmateq.com\/blog\/wp-json\/wp\/v2\/tags?post=2843"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}