Create Database Scheme for Your Custom Application via SQL File

For many activities like developing a web application, creating data lake or creating trusted/target system for learning IAM, a custom Database schema is required.

There is always an option to use tools (like SQL developer) or execute one off commands. This works well but the only issue is that we have to set these databases up so frequently that it would be nice to have a template which can be used everytime.

For this template, we simply need to create a .sql file and store it. Every time a new database has to be created, this can be leveraged. You will still have to update the table names etc.

This exercise will also help you understand, how to share your database creation details with fellow developers or even how the IAM tools like SailPoint IIQ or Oracle Identity Manager (RCU) shares the same with us.

I have used a MySQl server but it will work on all RDBMS with minor changes.

Create a .sql file and use the below structure:

/*

SQL file to create a custom database

-- Date: 2021-19-09 08:15

*/

CREATE DATABASE customapp;

SET GLOBAL validate_password_policy=LOW;

GRANT ALL PRIVILEGES ON customapp.*

    TO 'appUser' IDENTIFIED BY 'admin@12345';

GRANT ALL PRIVILEGES ON customapp.*

    TO 'appUser'@'%' IDENTIFIED BY 'admin@12345';

GRANT ALL PRIVILEGES ON customapp.*

    TO 'appUser'@'localhost' IDENTIFIED BY 'admin@12345';

USE customapp;

CREATE TABLE `UserTable` (

  `dbID` varchar(45) NOT NULL,

  `empID` varchar(45) DEFAULT NULL,

  `userName` varchar(45) DEFAULT NULL,

  `Inactive` varchar(45) DEFAULT NULL,

  `lastlogin` date DEFAULT NULL,

  PRIMARY KEY (`dbID`)

);

INSERT INTO `financeuser` (`dbID`,`empID`,`userName`,`Inactive`,`lastlogin`) VALUES ('112','1a2c3a','RichardJackson','FALSE','2009-04-04');

INSERT INTO `financeuser` (`dbID`,`empID`,`userName`,`Inactive`,`lastlogin`) VALUES ('113','1a2c3b','MariaWhite','FALSE','2009-04-04');

INSERT INTO `financeuser` (`dbID`,`empID`,`userName`,`Inactive`,`lastlogin`) VALUES ('114','1a2c3c','CharlesHarris','FALSE','2009-04-04');

INSERT INTO `financeuser` (`dbID`,`empID`,`userName`,`Inactive`,`lastlogin`) VALUES ('115','1a2c3d','SusanMartin','TRUE','2009-04-04');

INSERT INTO `financeuser` (`dbID`,`empID`,`userName`,`Inactive`,`lastlogin`) VALUES ('156','1a2c3a4a','LarryMorgan','FALSE','2009-04-04');

INSERT INTO `financeuser` (`dbID`,`empID`,`userName`,`Inactive`,`lastlogin`) VALUES ('159','1a2c3a4d','MelissaBailey','FALSE','2009-04-04');

INSERT INTO `financeuser` (`dbID`,`empID`,`userName`,`Inactive`,`lastlogin`) VALUES ('160','1a2c3a4e','FrankRivera','FALSE','2009-04-04');

INSERT INTO `financeuser` (`dbID`,`empID`,`userName`,`Inactive`,`lastlogin`) VALUES ('161','1a2c3b4a','BrendaCooper','TRUE','2009-04-04');

INSERT INTO `financeuser` (`dbID`,`empID`,`userName`,`Inactive`,`lastlogin`) VALUES ('162','1a2c3b4b','ScottRichardson','FALSE','2009-04-04');

INSERT INTO `financeuser` (`dbID`,`empID`,`userName`,`Inactive`,`lastlogin`) VALUES ('163','1a2c3b4c','AmyCox','FALSE','2009-04-04');

Now login into database:

> mysql -u root -p

> source fileName.sql

The sql file can be shared and the devs will be able to create the schema in their sandboxes.

Hope this helps !!!