DB2 User Rights

Objective:

DB2 database permissions are divided into instance-level permissions (SYSADM, Sysctrl, Sysmaint, SYSMON) and DB2 database-level permissions (DBAMD, LOAD). The permissions that users have in DB2 are mainly considered in three areas: instance level, database level, database operation level, and view command is DB2 get authorizations. DB2 authorization can be divided into instance-level permission and database-level authorization, the instance-level permissions must be granted to the user group by the update CFG by the user with the Sysadm permission, and the database object-level permission is granted to the user or user group by the DBAMD permission user through the grant command.

I. Introduction of DB2 rights

DB2 database permissions are divided into instance permission levels (SYSADM, Sysctrl, Sysmaint, SYSMON) and DB2 database permission levels (DBAMD, LOAD). Users with Sysctrl, Sysmaint, Sysmon permission levels cannot access data, and users with DBAMD, load permission levels can access the data.

Instance-level permissions (can only be assigned at the user group level):

SYSADM----System administrative rights (Unix systems, the SYSADM user group is set by default to the primary user group where the instance owner resides, so any user in that group has SYSADM permissions)

SYSCTL----System control permissions

Sysmaint----System Maintenance permissions

Database level:

DBADM----Database administrative permissions (such as maintenance and management tasks such as deleting a database, creating and deleting tablespaces, backing up or recovering a database, etc.)

Load----permissions on the load operation on the table

1, DB2 Database permissions:

connect-allow users to connect to the database

bindadd-allow users to create new packages in the database

createtab-allow users to create new tables in the database

Create_not_fenced-allows users to register user-defined functions (UDFs) or stored procedures that are defined as not FENCED

Implicitschema-Allows the user to create an object in a schema that does not yet exist (it automatically creates the schema) *

quiesce_connect-allow users to connect to a database in quiesced state

Create_external_routine-allows users to register external routines (routines written in external languages such as C and Java)

2. Table Space Privileges:

Use-Allows the user to create tables in the specified table space

3. Mode privileges:

createin-allows the user to create objects in the schema

alterin-allows the user to modify objects in the schema

dropin-allows the user to delete objects from the schema

4. Table/View Privileges:

control-grants users all the privileges on tables and views, and grants these privileges (except CONTROL) to others

ALTER-Allows the user to add a column to a table, add or modify comments on a table and its columns, add a primary key or UNIQUE constraint, and create or delete a table check constraint

delete-allow users to delete rows from a table or view

Index-Allows the user to create an index on the table

Insert-allows users to insert data into a table or view

references-allows users to create and delete foreign keys, which requires a parent table in the specified relationship

Select-allows users to retrieve rows from a table or view, create a view on a table, and run the export utility

Update-allows users to modify data in tables, views, or some columns in a table or view, and users can have this privilege only on specific columns

5. Index Privileges:

control-allow users to delete indexes

6. Package Privileges:

control-allows users to rebind, delete, or execute packages, as well as grant these privileges (except CONTROL) to others

Bind-Allows the user to rebind an existing package

execute-allow users to execute packages

7. Routine Privileges:

Execute allows the user to invoke routines, create functions from routines (only for functions), and refer to routines in any DDL statement (such as Create VIEW, create TRIGGER, or define constraints)

9. Sequence Privileges:

Usage-allows users to use Nextval and prevval expressions for sequences

Alter-allows the user to modify sequence properties using the ALTER SEQUENCE statement

Privileged information is stored in the seven system catalog view:

*syscat. Dbauth-Database privileges

*syscat. Colauth-table and view column privileges

*syscat. Indexauth-Index Privileges

*syscat. Packageauth-Package Privileges

*syscat. Schemaauth-Mode privileges

*syscat. TABAUTH-table and view privileges

*syscat. Tbspaceauth-Table Space privileges

View current connection user rights: Db2get authorizations

The "direct" permission means that this permission is explicitly granted to this user. The "indirect" permission means that this user belongs to a group that has this permission.

Permissions Summary: DB2 in the user has the main consideration three aspects: the instance level, the database level, the database object level, the view method is Db2get authorizations, the specific table's authorization information is stored in the Syscat.tabauth.

Ii. Authorized operation

DB2 database authorization consists of privileged groups and advanced database management programs (instance-level) maintenance and utility operations. Of the 5 types of permissions available to DB2, SYSADM, Sysctrl, and Sysmaint are instance-level permissions. This means that their scope contains instance-level commands and commands for all databases in this instance. These permissions can only be assigned to groups, and these permissions are assigned through the DBM CFG file.

1. Get SYSADM

Users with SYSADM permissions can emit any DB2 command that acts on DB2 instances, any databases within the instance, and any objects within those databases. They are also able to access data within the database, granting or revoking privileges and permissions. SYSADM users are the only users that are allowed to update Database Manager configuration (DBM CFG) files.

SYSADM permissions are controlled by the Sysadm_group parameter in DBM CFG. When you create an instance, this parameter is set to Administrator on Windows (although it appears empty when you issue a command DB2 get dbm CFG). On UNIX, this parameter is set to the primary group of the user who created the instance. Because SYSADM users are the only users that are allowed to update dbmcfg, they are also the only users who can grant any sys* permissions to other groups.

The following example shows how to grant SYSADM permissions to the DB2GRP1 group:

DB2 update dbm CFG usingsysadm_group DB2GRP1

2. Get Sysctrl

Users with Sysctrl permissions can perform all administrative and maintenance commands within the instance. However, unlike SYSADM users, they cannot access any data in the database unless they are granted the privileges required to do so. Sysctrl users can execute on any database in the instance

Users with SYSADM permissions can assign Sysctrl to a group using the following command:

DB2 update dbm CFG usingsysctrl_group GROUP name

3. Get Sysmaint

A command that a user with Sysmaint permissions can issue is a subset of the commands that the Sysctrl permission is allowed to be considered as some tasks related to "maintenance."

Note that a user with Sysmaint cannot create or delete a database or table space (tablespace). They also cannot access any data in the database unless they are granted the privileges required to do so.

Users with SYSADM permissions can assign sysmaint to a group using the following command:

DB2 update dbm CFG usingsysmaint_group GROUP name.

4. Grant

DB2 database authorization is implemented through the grant command, DB2 the database authorization, the user can do the corresponding operation DB2. The Dbadm and load permissions for a specific database can be assigned to users or groups of users. You can explicitly assign these permissions by using the Grant command.

Grammar:

>>-grant--authorization-specification--------------------------->

.-,----------------------.

V |

>--to----+-authorization-name-+-+--+-------------------+-------><</em>

+-role--role-name----+ '-with GRANT option-'

'-public-------------'

Simple example (the database needs to be connected first):

--authorizing the database to administer permissions to the user

Db2grant Dbadm on database to user db2admin

--Authorizing database administration permissions to user groups

DB2 Grant DBADM on the database to group DB2GRP1

--Authorization query:

Db2grant Select on table tableName to user db2admin

--All licenses

DB2 Grantall on TABLE owk. " Favmenu "to USER Sckdev

DB2 User Rights