Granting and Revoking Object-Level Permissions

To grant object-level permissions, you use the usual syntax:

GRANT <privilege type> ON <resource> TO <username>

The object-level permissions supported by DB2 are as follows:

• ALTER allows the grantee to change the table definition using the ALTER TABLE command.

• DELETE, INSERT, SELECT, UPDATE are the permissions that apply to data tables and views.

• INDEX allows the grantee to create indexes on the mentioned data table.

• REFERENCES allows a user to insert rows into a table that has a FOREIGN KEY constraint to another table where the user doesn't have SELECT permissions.

• EXECUTE allows the user to run the specified function or stored procedure or to access any program object declared in the specification of a package.

• CONTROL grants all of the previous permissions to the user and also gives them the ability to grant any of the previous permissions, excluding CONTROL, to other users. The CONTROL permission can only ever be granted by users with database administrator or system administrator status.

So, let's start by granting some permissions to Alice. Open DB2 Command Center and enter the following code:

GRANT CONNECT ON DATABASE TO USER Alice;

So, Alice can now connect to the database. But if you try to connect to the InstantUniversity database as Alice and then view some data, you'll find that permission is denied. So, while you're logged on as the DB2Admin administrator, enter the following command:

GRANT SELECT, INSERT ON Student TO Alice;

Again, you could have specified WITH GRANT OPTION in this statement to allow Alice to grant these permissions to others.

Now, if you log on as Alice, you'll be able to run the following commands:

INSERT INTO DB2Admin.Student (StudentID, Name)
VALUES (13, 'George Bush');

SELECT StudentID, Name FROM DB2Admin.Student;

However, to be able to delete George Bush from the database, you need to give Alice DELETE privileges. Instead of granting this permission to Alice, let's revoke the INSERT permission on Alice and then experiment with adding her to a group (the equivalent of a role on DB2):

REVOKE INSERT ON Student FROM Alice;

Working with Groups

Alice is now limited to being able to select data from the table, but because DB2 doesn't have traditional users and roles, to give Alice more permissions more easily, you need to use a mixture of SQL and tools available to the operating system.

If you create a new group account on your system, called MyGroup, you can assign permissions on this group just as you did with Alice:

GRANT CONNECT ON DATABASE TO GROUP MyGroup;
GRANT SELECT, INSERT, DELETE ON Student TO MyGroup;

Because Alice isn't a member of the group yet, she still can't insert or delete students. However, if you add the Alice user to the MyGroup group on your system, you'll find that Alice now has much more power when it comes to creating and deleting students.

An interesting point to note is that if an individual member of a group has more permissions than the group itself, that user will still be able to perform all the appropriate tasks, even if other members of the group are unable to do so. To test this, create another system user called Joe and make him a member of MyGroup. Joe can now log on and select, insert, and delete students.

Now, log back in as the administrator and run the following commands:

REVOKE DELETE, INSERT On Student FROM MyGroup;
GRANT UPDATE on Student TO Alice;
GRANT CONTROL ON Student TO Joe;

Joe can now have much more fun with the Student table, creating, altering, or deleting information as much as he wants. Alice can view and update data, for example:

UPDATE DB2Admin.Student SET Name = 'George W. Bush'
WHERE StudentID=13;

Any other members in the group are limited to simply viewing data in the table.

To finish, you can revoke all privileges from the users:

REVOKE ALL PRIVILEGES ON Student FROM Alice;

Note that you must first revoke CONTROL from Joe and then revoke all the individual privileges that are added when the CONTROL privilege is added:

REVOKE CONTROL ON Student FROM Joe;
REVOKE ALL PRIVILEGES ON Student FROM Joe;

Note that, at this point, Alice and Joe can still view data in the Student table because the MyGroup group still has SELECT permission. This final command will prevent both users from viewing data in the Student table:

REVOKE ALL PRIVILEGES ON Student FROM MyGroup;

But note that the users are still able to log on until you revoke the CONNECT permission for the group (which revokes connect permission from Joe because you never explicitly granted him CONNECT permission), then from Alice, who had her own explicit CONNECT permission, which was the first thing you granted:

REVOKE CONNECT ON DATABASE FROM MyGroup;
REVOKE CONNECT ON DATABASE FROM Alice;

Setting Statement-Level Permissions

DB2 also has the ability to grant or revoke permissions for creating tables and databases. The full list of grantable permissions is quite lengthy, so you should consult the documentation.

If you want to allow a user to create tables, you could use the following command:

GRANT CREATETAB ON DATABASE TO Alice;

Note that to create a view, all you need is SELECT permission on each of the tables involved in the view.

If you want to make Alice a full administrator of the database, in case she wanted to do a lot of work on manipulating the database, you could make her a full administrator of that database:

GRANT DBADM ON DATABASE TO Alice;

Note that when you enable this permission, all of the other permissions available are added. Removing just this permission won't remove all the other permissions that were added, so you'll need to first remove DBADM and then do a REVOKE ALL PRIVILEGES, as you saw with Joe.