ecco

書名： IBM DB2 9.7 Advanced Application Developer Cookbook
作者名： Sanjay Kumar Mohankumar Saraswatipura
本章字數(shù)： 647字
更新時間： 2021-08-20 15:42:21

Granting and revoking object privileges

Privileges are the next level of security mechanism that can be implemented at database object level. A privilege determines the permission of performing a task on an object. A user who creates an object in the database implicitly acquires all the privileges associated with that object. Privileges can be divided into three categories:

Individual object privileges: Such privileges allow a user to perform different actions on the object. These privileges don't allow a user to grant or revoke similar privileges to or from other users. Example of such privileges can be: SELECT, EXECUTE, UPDATE, and so on. Only a user with CONTROL, ACCESSCTRL, or SECADM can grant these privileges to another user.
CONTROL privilege: This privilege allows users to grant and revoke privileges to or from other users. The CONTROL privilege is implicitly granted to the creator on the newly-created tables, indexes, and packages. It is implicitly granted on newly-created views if the object owner has the CONTROL privilege on all the tables, views, and nicknames referenced by the view definition. A user with CONTROL privilege can extend the ability to grant and revoke privileges to and from other users by using WITH GRANT OPTION in the GRANT command.
Privileges for objects inside a package or routine: Once a package is created, only the EXECUTE privilege on that package is needed for a user to execute this package. Users need not have any other privilege on objects referenced in the package or routine. If the package or routine contains static SQL, then the privileges of the package or routine owner are considered at the time of execution. If it contains dynamic SQL, then it depends on the DYNAMMICRULES BIND option of the package.

Tip

WITH GRANT OPTION does not allow the person granting the privilege to revoke the privilege, once granted. We must have the SECADM authority, the ACCESSCTRL authority, or the CONTROL privilege to revoke the privilege.

Getting ready

To grant privileges on any object, we need to have SYSADM, DBADM, or CONTROL privilege on that object, or the user must hold that privilege with WITH GRANT OPTION.

Also, to grant CONTROL privilege, we need to have the SYSADM or DBADM privilege.

How to do it...

Let's see how to grant and revoke object privileges to and from users, roles, or groups.

Granting privileges

Any object privilege can be granted to groups and roles as well as to individual users.
Use the following command to grant any privilege to a user/role/group:

GRANT <privilege> ON <object_type> <object_name> to USER/ROLE/GROUP <name>

For example, to grant the SELECT privilege to a user user_dev on the TEST.EMPLOYEE table, use the following command:

GRANT SELECT ON TABLE TEST.EMPLOYEE SAMPLE TO USER USER_DEV

We can also grant multiple privileges simultaneously in a single SQL command. For example, to grant SELECT, INSERT, DELETE, and UPDATE privileges to user user_dev on TEST.EMPLOYEE table, use the following command:

GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE TEST.EMPLOYEE TO USER USER_DEV

Revoking privileges

Use the following command to revoke privileges on an object from a user/group/role:

REVOKE the <privilege> ON <object_type> <object_name> FROM <user/group/role name>

For example, to revoke SELECT privilege on the TEST.EMPLOYEE table from user user_dev, use the following command:

REVOKE SELECT ON TABLE TEST.EMPLOYEE SAMPLE FROM USER USER_DEV

Similar to GRANT, REVOKE can also include multiple privileges at a time. For example, to revoke SELECT, INSERT, UPDATE, and DELETE privileges on the TEST.EMPLOYEE table from the user 'user_dev', use the following command:

REVOKE SELECT, INSERT, UPDATE, DELETE FROM USER USER_DEV

There's more…

We can use the SYSIBMADM.PRIVILEGES administrative view to retrieve the privileges on any database object. For example, to view the privileges on the EMPLOYEE table, we can use the following query:

SELECT CHAR(AUTHID, 10) AUTHID, AUTHIDTYPE, CHAR(PRIVILEGE, 10) PRIVILEGE,
CHAR(OBJECTNAME, 10) OBJECTNAME,
CHAR(OBJECTSCHEMA, 10) OBJECTSCHEMA,
CHAR(OBJECTTYPE, 10) OBJECTTYPE
FROM SYSIBMADM.PRIVILEGES
WHERE OBJECTNAME='EMPLOYEE';

The following table summarizes all privileges available for different types of database objects:

官术网_书友最值得收藏!

IBM DB2 9.7 Advanced Application Developer Cookbook