Learning Visually with Examples [Electronic resources] نسخه متنی

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:

[View full width]
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