Manage DB2 Table Security and Accessing It Using PHP

Security always considered on many application especially when dealing with e-commerce. DB2 has an ability to set table security with only one line of codes.

Some tables might be accessed by public or anonymous users but others can only be accessed by private users. In order to set that we need to understand grant and revoke DB2 syntax.

Grant is giving user some permissions to a table whereas Revoke is taking user from some table permissions.

I will give example of DB2 syntaxes to set tables security start from creating DB2 instance, create a database, schema, table and grant tables. Also there is PHP web apps to ‘select’, ‘insert’ and ‘update’ to these tables. I’ve used Windows OS and DB2 (ver)..

I am using db2admin user to set initial configuration. db2admin is OS and DB2 user created when I installed DB2 software. db2admin acts as administrator in DB2. FYI DB2 user is also OS user.


Login OS as db2admin and create DB2 instance MYDEV:

db2icrt mydev

Creating seperate instance instead of using default is useful to distinguish development and production environment.

Set default DB2 instance:

set db2instance=mydev

So that every DB2 syntax will refer to mydev instance.

Set default drive for creating database:

db2 update dbm cfg using dftdbpath D:\

Every database file created on mydev instance will be placed on Drive D as a default.

Create a database mydb1:

db2 create database mydb1 

Mydb1 Data file is placed on Drive D:

Connect to database mydb1 using user db2admin:

db2 connect to mydb1 user db2admin

Create schema DEV:

db2 create schema DEV authorization db2admin

A schema is a collection of named objects. I stored my tables in schemas to differentiate between any of operational purposes.

Create person table on schema DEV:

To access this person table we have to write table name with schema like DEV.PERSON.

Do insert some no of records on person table:

db2 insert into dev.person (personid, personname, age) values (1, 'John Doe', 34);
db2 insert into dev.person (personid, personname, age) values (2, 'Peter Parker', 31);

Grant all to user db2guest:

db2 grant all on table dev.person to db2guest

This grant all to user db2guest syntax means db2guest can read and write to person table.

Connect to database using user db2guest and check with query syntax:

db2 connect to mydb1 user db2guest
db2 select * from dev.person
db2 insert into dev.person (personid, personname, age) values (3, 'Clark Kent', 30)

Select and insert process are success because db2guest granted to all permission on that person table.

Connect to database using user db2admin

db2 connect to mydb1 user db2admin

I re-connect to mydb1 using db2admin because of I want to create a stock table and give read only access to db2guest.

Create table stock on schema DEV:

CREATE TABLE DEV.STOCK ( STOCKID INTEGER  NOT NULL  GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1, NO CACHE ) , ITEMCODE VARCHAR (30)  NOT NULL , BALANCE INTEGER  NOT NULL  , CONSTRAINT STOCKID_PK PRIMARY KEY ( STOCKID)  ) ;

Do insert some no of records on stock table:

insert into dev.stock (itemcode, balance) values ('BK0001', 120);
insert into dev.stock (itemcode, balance) values ('BK0002', 2);
insert into dev.stock (itemcode, balance) values ('NB0006', 10);

Grant only select to db2guest:

db2 grant select on table dev.stock to db2guest

Connect to mydb1 with user db2guest and check its security with read and write query:

db2 connect to mydb1 user db2guest
db2 select * from dev.stock;
db2 update dev.stock set balance=1 where itemcode='NB0006';

Select stock table process is Ok but update is failed due to access denied for db2guest. So db2guest doesn’t allowed to change stock records. Only db2admin who can write/change stock records.

DB2 PHP Example

Accessing table Dev.Person:

$dbname = "MYDB1";
$user = "db2guest";
$passwd = "passwd";

$conn = db2_connect($dbname,$user,$passwd);
if ($conn) {
	$query = "select * from dev.person";
	$stmt = db2_prepare($conn, $query);	
	$result = db2_execute($stmt);
	
	print "<table>";
	print "<thead><tr><th>ID</th><th>PersonName</th><th>Age</th></tr></thead>";
	print "<tbody>";
	while ($row = db2_fetch_array($stmt)) {
		print "<tr><td>$row[0]</td><td>$row[1]</td><td>$row[2]</td></tr>";
	}
	print "</tbody></table>";
		
	db2_free_stmt($stmt);
	db2_free_result($stmt);
	
	$query = "insert into dev.person (personid, personname, age) values (3, 'Abid F', 4)";
	$stmt = db2_prepare($conn, $query);
	db2_execute($stmt);
	db2_free_stmt($stmt);
	
	print "<hr>Inserting one record success<hr>";
	$query = "select * from dev.person";
	$stmt = db2_prepare($conn, $query);	
	$result = db2_execute($stmt);
	
	print "New Person list:<br><table>";
	print "<thead><tr><th>ID</th><th>PersonName</th><th>Age</th></tr></thead>";
	print "<tbody>";
	while ($row = db2_fetch_array($stmt)) {
		print "<tr><td>$row[0]</td><td>$row[1]</td><td>$row[2]</td></tr>";
	}
	print "</tbody></table>";
	
	db2_free_stmt($stmt);
	db2_free_result($stmt);
	db2_close($conn);
}

Since db2guest has read write access to Dev.Person, ‘select’ and ‘insert’ process will success.

Accessing table Dev.Stock:

$dbname = "MYDB1";
$user = "db2guest";
$passwd = "passwd";

$conn = db2_connect($dbname,$user,$passwd);
if ($conn) {
	$query = "select * from dev.stock";
	$stmt = db2_prepare($conn, $query);	
	$result = db2_execute($stmt);
	
	print "<table>";
	print "<thead><tr><th>Stock ID</th><th>Item code</th><th>Balance</th></tr></thead>";
	print "<tbody>";
	while ($row = db2_fetch_array($stmt)) {
		print "<tr><td>$row[0]</td><td>$row[1]</td><td>$row[2]</td></tr>";
	}
	print "</tbody></table>";
		
	db2_free_stmt($stmt);
	db2_free_result($stmt);
	
	$query = "update dev.stock set balance=0 where stockid=3";
	$stmt = db2_prepare($conn, $query);
	
        // Error will raise
	if (db2_execute($stmt)) {
		print "<hr>Update one record success<hr>";
	} else {
		print "<hr>Update failed<hr>";
	}
	
	db2_free_stmt($stmt);
	
	
	$query = "select * from dev.stock";
	$stmt = db2_prepare($conn, $query);	
	$result = db2_execute($stmt);
	
	print "New Stock list:<br><table>";
	print "<thead><tr><th>Stock ID</th><th>Item Code</th><th>Balance</th></tr></thead>";
	print "<tbody>";
	while ($row = db2_fetch_array($stmt)) {
		print "<tr><td>$row[0]</td><td>$row[1]</td><td>$row[2]</td></tr>";
	}
	print "</tbody></table>";
	
	db2_free_stmt($stmt);
	db2_free_result($stmt);
	db2_close($conn);
}

db2guest user only has read access, so when it try to edit record an error raises. Stock table cannot be altered by db2guest or public user in e-commerce application. It is only can be updated by system or private users.

Regards,
Agung Gugiaji

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s