老版本大发棋牌

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

Granting and revoking database-level authorities

This category of authorities allows a user to perform activities specific to a database. These activities are viewing/modifying data in the database, granting and revoking privileges to users, performing data load operations, running maintenance activities like collecting statistics, backups and restores, and so on.

Getting ready

We need SYSADM authority to grant DBADM or SECADM authority
We need SYSADM or DBADM authority to grant other database authorities

How to do it...

Let's see how we can grant and revoke database-level authorities. The process is the same for every type of database-level authority.

Granting database-level authorities

All database-level authorities can be granted to groups and roles as well as to individual users except SECADM, which can only be granted to a user.
Use the following command to grant any authority to user/role/group:

GRANT <authority_name> ON DATABASE <db_name> to USER/ROLE/GROUP <name>

For example, to grant DBADM authority to a user user_dba on SAMPLE database, use the following command:

GRANT DBADM ON DATABASE SAMPLE TO USER USER_DBA

We can also grant multiple authorities simultaneously in a single SQL command. For example, to grant DBADM, ACCESSCTRL, and DATAACCESS authorities to user user_dba on SAMPLE database, use the following command:

GRANT DBADM, ACCESSCTRL, DATAACCESS ON DATABASE SAMPLE TO USER USER_DBA

Revoking authorities

Use the following command to revoke the database-level authority from a user or group or role:

REVOKE <authority_name> ON DATABASE <database_name> FROM <user/group/role name>

For example, to revoke DBADM authority on SAMPLE database from user user_dba, use the following command:

REVOKE DBADM ON DATABASE SAMPLE FROM USER USER_DBA

Similar to GRANT, REVOKE can also include multiple authorities at a time. For example, to revoke DBADM, DATAACCESS and ACCESSCTRL authorities from user user_dba, use the following command:

REVOKE DBADM, ACCESSCTRL, DATAACCESS ON DATABASE SAMPLE FROM USER USER_DBA

How it works...

DB2 does not maintain any user itself. It uses the operating system users and groups. To grant or revoke any authority to or from any user, we need to use the operating system security facility to create new users, or modify group membership of any user. Once the users and groups have been configured in the operating system, they can be used in DB2.
The users and groups created by the operating system need to follow certain naming conventions; otherwise, they can't be used in DB2. These are:
- Length of group names should be within the SQL limit of 128 characters
- Usernames on UNIX systems can have up to 8 characters
- Usernames on Windows systems can have up to 30 characters
- Usernames and group names cannot begin with IBM, SQL, or SYS
- Usernames and group names cannot be USERS, ADMINS, GUESTS, PUBLIC, LOCAL, or any reserved SQL word
Database-level authorities can also be granted to roles along with groups and users. Only a system administrator can modify users and groups within an operating system. This adds a dependency on database administrators and security administrators of DB2 database to rely on the system administrator. To overcome this dependency, DB2 provides DATABASE ROLES. These are very similar to groups in the operating system except that they exist at database level. We can assign a database role to any set of users and grant authorities or privileges to the role. Now, the system administrator only needs to create new users at an operating system level and the rest can be taken care of by the database administrators or security administrators.

There's more…

The DB2 authorization model has been updated to separate the duties of system administrators, database administrators, and security administrators. The DBADM authority no longer has the ability to access data (DATAACCESS), to grant/revoke privileges, authorize (ACCESSCTRL), or define security policies (SECADM). If the DBADM authority needs these authorities, then the SECADM will have to explicitly grant these authorities to DBADM.

Tip

Only authorization IDs with the SECADM authority can grant the ACCESSCTRL, DATAACCESS, DBADM, and SECADM authorities. All other authorities can be granted by authorization IDs that hold ACCESSCTRL or SECADM authorities.

We can use the SYSPROC.AUTH_LIST_ROLES_FOR_AUTHID table function to retrieve the authorities for any user. This function accepts the authorization and authorization ID type. For example, to view the roles for the ADMINISTRATOR user, we can use following query:

SELECT CHAR(GRANTOR, 30) GRANTOR, GRANTORTYPE, CHAR(GRANTEE, 30) GRANTEE, GRANTEETYPE, CHAR(ROLENAME, 30) ROLENAME, CREATE_TIME, ADMIN FROM TABLE(SYSPROC.AUTH_LIST_ROLES_FOR_AUTHID('ADMINISTRATOR','U')) AS T;

The following table summarizes the activities performed by each database authority:

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

IBM DB2 9.7 Advanced Application Developer Cookbook