Cockroach DB Working and Authorization

 
Share
 
 
 

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 cluster.

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

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

Distributed Key-Value Store:  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.

Distributed Transactions are the important part of this application, their implementation of this feature manages the transition from SQL to stores and ranges.

Nodes are the servers that store your data, a node can be either virtual or physical machines

Node may have one or more stores, and each store can hold many ranges, and these are managed by Rocks DB.

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

Authorization in Cockroach DB

Authorization in Cockroach DB is done using Users and Roles, its controlling over who can do what like read, write, update, delete, grant operations and on which resources like databases, tables, clusters, schemas, rows, users.  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.

Users and Roles in Cockroach DB

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”.

As mentioned above SQL statements CREATE USER and CREATE ROLE will almost create the same thing but  with one difference CREATE ROLE will use NOLOGIN option by default, restricting the user or role from being used to log in.

Creating a user for Cockroach DB

In one of your cockroach node terminals use the below given command to open the built in SQL shell

cockroach sql --certs-dir=certs --host=localhost:26257

give your certs directory name for –certs-dir and node host name for –host.

Now create a user and give a password for authentication.

CREATE USER masteruser WITH PASSWORD 'Nalmas123';

here masteruser is a username and Nalmas123 is password for the user

Check user creation using the command “SHOW USERS;

How to Grant Privileges to a User?

GRANT admin TO masteruser;

Above given command gives administrative access to user named masteruser and admin is a pre predefined role here.

GRANT is a statement that you can use it to directly give privileges to a role or user, users granted a privilege with GRANT option can pass on that privilege to others, owner of an object will have the  GRANT OPTION for all privileges by default.

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   Cockroach DB gives an error.

 

Creating a New Role

Now let’s create a new role that can create and rename databases.

CREATE ROLE can_create_database WITH CREATEDB;

SQL statement  CREATE ROLE  creates  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.

and can_create_database is the role that is being created with CREATEDB privileges, and use SHOW ROLES; to check the creation and to see already existing roles and users,

 

Granting New Privileges for an Existing Role

GRANT admin TO can_create_database;

can_create_database is a role that we have created, and admin is a privilege which give administrative access

As you can see in above given snapshot role can_create_database is a member of admin now.

 

To learn SQL statement refer this blog

Please contact our technical consultants if you have anything related to cloud infrastructure to be discussed.


Comments

Leave a Reply

Your email address will not be published. Required fields are marked *