10.8. Case Study

Assume that you have just created a database (SAMPLE) in a DB2 instance (DB2) for application testing. Before the testers can access the database, you need to make sure they have all the necessary privileges and authorities to perform requested database operations.

First of all, you want to enforce that all client authentications are validated at the server. To retrieve the current authentication setting, issue the following command:

					get dbm cfg
				

The result indicates that user IDs and passwords will be authenticated at the DB2 server:

					Database manager authentication        (AUTHENTICATION) = SERVER
				

Recently, a company-wide alert announced that encryption should be used wherever possible to prevent eavesdropping. To do this, you can update the database manager authentication to SERVER_ENCRYPT so that both user IDs and passwords are encrypted.

You decide to update the authentication type using the update dbm cfg command:

					update dbm cfg using authentication server_encrypt
				

You then stop and restart the DB2 instance.

Besides encrypting user IDs and passwords, data requested from and returned to the database can also be encrypted. You configure this at each DB2 client using the following command:

					catalog db sample at node dbsrv authentication sql_authentication_dataenc_cmp
				

Besides configuring the DB2 clients and server to perform secured authentication and data encryption, data access must be restricted to authorized users only. As you can see from the result of the get dbm cfg command, the system groups all default to NULL (i.e., users who have system administrative or root privileges).

To make sure only the assigned users can perform DB2 administrative tasks, you set the system groups to the appropriate groups:

					update dbm cfg using sysadm grpadmin sysctrl grpctrl
					sysmaint grpmaint sysmon grpmon
				

Cynthia, who is a member of the grpadmin group, connects to the SAMPLE database and issues the following statements:

					CREATE TABLE topsecret_table
					( empno          CHAR(6)
					, name           VARCHAR(50)
					, perf_grade     CHAR(1)
					, salary_inc_pct INTEGER);
					INSERT INTO topsecret_table
					VALUES ('000010', 'Bill', 'F', 0)
					, ('000020', 'John', 'A', 80)
					, ('000030', 'Kathy','C', 20);
				

The topsecret_table was successfully created and populated because Cynthia is a member of the SYSADM group who can perform any operation on the instance and its associated databases.

Bill found out that he will be getting an F grade in his performance review and will not get any salary increase. Being a member of the grpmaint group, he thinks he has the authority to update the records in the topsecret_table table. Therefore, he connects to the SAMPLE database and issues the following statement:

					UPDATE topsecret_table
					SET perf_grade = 'A', salary_inc_pct = 100
					WHERE empno = '000010'
				

The update was not successful. The following error was received:

					SQL0551N  "DB2USER" does not have the privilege to perform operation "UPDATE" on object 
"DB2ADMIN.EMPLOYEE".  SQLSTATE=42501
				

This works as expected because users who have just the SYSCTRL, SYSMAINT, or SYSMON authority do not have the ability to retrieve or modify data. They only have the authority to perform system maintenance or monitor tasks.

By default, four privileges are granted to PUBLIC when a database is created. You should lock down the database by revoking privileges that are implicitly granted:

					REVOKE CONNECT, CREATETAB, BINADD, IMPLICIT_SCHEMA
					ON DATABASE FROM PUBLIC
				

Then, grant privileges to users which they only need.

					GRANT SELECT, UPDATE, DELETE, INDEX ON TABLE employee TO USER john