DB2 Database Permissions

Reproduced in the following content: http://blog.csdn.net/xiyuan1999/article/details/8135263

Permissions in the DB2

There are three main security mechanisms in DB2 that can help DBAs implement database security plans: Authentication (authentication), Authorization (authorization) , and privileges (privilege) . Authentication is the first security feature that a user encounters when attempting to access an DB2 instance or database. DB2 authentication works closely with the security features of the underlying operating system to validate user IDs and passwords. DB2 can also use security protocols such as Kerberos to authenticate users. Authorization determines what actions users and/or groups of users can perform and which data objects they can access. The ability of users to perform advanced database and instance management operations is determined by the permissions assigned to them. There are 5 different permission levels in DB2: SYSADM, Sysctrl, Sysmaint, Dbadm, and LOAD. The granularity of privileges is finer than authorization and can be assigned to users and/or user groups. A privilege defines an object that a user can create or delete. They also define commands that users can use to access objects such as tables, views, indexes, and packages. A new concept in DB2 9 is the label-based access Control (LBAC), which allows finer granularity to control who has access to individual rows and/or columns. 1.DB2 Authentication DB2 uses the authentication type to determine where to authenticate. For example, in a client-server environment, is the client or server verifying the user's ID and password? In the client-gateway-host environment, is the client or host verifying the user's ID and password? You can specify the appropriate authentication type in dbm CFG: DB2 GET DBM CFG Server Connection Authentication (srvcon_auth) = KERBEROS Database Manager authentication (authentication) = Server_encrypt Then the server_encrypt is used when connecting to the instance. However, KERBEROS authentication is used when connecting to the database. In a client-to-gateway-host environment, these authentication options are set on the client and gateways, not on the host. Type Describe SERVER Authentication is performed on the server. Server_encrypt Authentication is performed on the server. The password is encrypted on the client and then sent to the server. CLIENT Authentication is performed on the client computer. *kerberos Authentication is performed by Kerberos security software. *krb_server_encrypt If the client setting is Kerberos, authentication is performed by the Kerberos security software. Otherwise, use Server_encrypt. Data_encrypt Authentication is performed on the server. The server accepts the encrypted user ID and password and encrypts the data. This option operates the same way as Server_encrypt, but the data is also encrypted. data_encrypt_cmp The authentication method is the same as Data_encrypt, but allows older clients that do not support data_encrypt to connect using Server_encrypt authentication. In this case, the data is not encrypted. If the connecting client supports Data_encrypt, the data is encrypted and cannot be downgraded to server_encrypt authentication. This authentication type is valid only in the server's database manager configuration file and is not valid when using CATALOG database on a client or gateway instance. Gssplugin The authentication method is determined by an external GSS-API plug-in. Gss_server_encrypt The authentication method is determined by an external GSS-API plug-in. Use Server_encrypt authentication in cases where the client does not support one of the server's Gss-api plugins. 2. Authorization 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. 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. Get SYSADM Permissions The SYSADM permission is controlled by the Sysadm_group parameter in the DBM CFG file. On Windows, when you create an instance, this parameter is set to Administrator (but, if the command is issued db2 get dbm cfg , it appears to be empty). On UNIX, it is set to the primary group of the user who created the instance. Because only SYSADM users are allowed to update DBM CFG files, only they can grant any sys* permission to other groups. The following example shows how to grant SYSADM permissions to the DB2GRP1 Group: DB2 update dbm CFG using Sysadm_group DB2GRP1 Get Sysctrl Permissions Users with Sysctrl permissions can perform all administrative and maintenance commands in the instance. However, unlike SYSADM users, they cannot access any data in the database unless they are granted the privileges required to access the data. Examples of commands that Sysctrl users can execute against any database in an instance are as follows: db2start/db2stop db2 create/drop database db2 create/drop tablespace db2 backup/restore/rollforward database db2 runstats(for any table) db2 update db cfg for database dbname Users with SYSADM permissions can assign Sysctrl to a group using the following command: DB2 update dbm CFG using Sysctrl_group GROUP name Get Sysmaint Permissions A command that a user with Sysmaint permissions can issue is a subset of the commands that a user with Sysctrl permission can issue. Sysmaint users can only perform maintenance-related tasks, such as: db2start/db2stop db2 backup/restore/rollforward database db2 runstats(for any table) db2 update db cfg for database dbname Note that a user with Sysmaint permissions cannot create or delete a database or table space. They also cannot access any data in the database unless they are explicitly granted the privileges required to access the data. If you have SYSADM permissions, you can use the following command to assign Sysmaint permissions to a group: DB2 update dbm CFG using Sysmaint_group GROUP name Get Dbadm Permissions Dbadm permissions are a database-level permission, not an instance-level permission. Dbadm users have almost complete control over a database. Dbadm users cannot perform certain maintenance or administrative tasks, such as: drop database drop/create tablespace backup/restore database update db cfg for database db name However, they can perform the following tasks: db2 create/drop table db2 grant/revoke(Any privilege) db2 runstats(Any table) Dbadm users are also automatically granted all privileges on database objects and their contents. Because DBADM permissions are a database-level privilege, it can be assigned to users and groups of users. The following command demonstrates different ways to grant Dbadm permissions. db2 create database test This command implicitly grants the DBADM permission on the database test to the user who issued the command. db2 connect to sample  db2 grant dbadm on database to user tst1 This command can only be issued by SYSADM users, and it grants DBADM permissions on the sample database to the user Tst1 . Note that the user who issued this command must connect to the sample database before granting Dbadm permissions. db2 grant dbadm on database to group db2grp1 This command grants DBADM permissions to each user in the db2grp1 group. Similarly, only SYSADM users can issue this command. Get LOAD Permissions The LOAD permission is a database-level permission, so it can be assigned to users and user groups. As the name implies, the load permission allows the user to issue a load command to the table. When populating a table with large amounts of data, the LOAD command is often used to replace the INSERT or Import command, which is faster. Depending on the type of load operation that you want to perform, it may not be sufficient to have load permissions alone. Specific privileges on the table may also be required. Users with LOAD permissions can run the following command: db2 quiesce tablespaces for table db2 list tablespaces db2 runstats(Any table) db2 load insert(You must have insert privileges on the table) db2 load restart/terminate after load insert(You must have insert privileges on the table) db2 load replace(You must have insert and delete privileges on the table) db2 load restart/terminate after load replace(You must have insert and delete privileges on the table) Only users with SYSADM or DBADM permissions can grant or revoke LOAD permissions to users or groups of users. The following example shows how load permissions allow our users to LOAD load data into the Sales table using commands. Suppose a command has been issued db2 connect to sample . db2 grant load on database to user tst1  db2 grant insert on table sales to user tst1 With LOAD permissions and insert privileges,tst1 can issue LOAD INSERT or LOAD RESTART , or LOAD INSERT later emit, the sales table TERMINATE . db2 grant load on database to group grp1  db2 grant delete on table sales to group grp1  db2 grant insert on table sales to group grp1 With LOAD permissions and delete and insert privileges, any member of theGRP1 can issue LOAD REPLACE or LOAD RESTART , or later emit, the sales table LOAD REPLACE TERMINATE . 3. Privileges The database-level privileges that a user can have are: Createtab: Users can create tables in the database. Bindadd: The user can use the BIND command to create a package in the database. Connect: The user can connect to the database. Create_not_fenced: Users can create unfenced user-defined Functions (UDFs). Implicit_schema: The user can implicitly create schemas in the database without the need to use the CREATE Schema command. Load: The user can load data into a table. Quiesce_connect: The user can access the database in the silent (quiesced) state. Create_external_routine: Users can create processes that are used by other users of the provisioning program and database. Database objects include tables, views, indexes, schemas, and packages. Fortunately, the meaning of most object-level privileges does not need to be explained. The following table summarizes these privileges. Privileged Name Related objects Describe CONTROL Tables, views, indexes, packages, aliases, different types, user-defined functions, sequences Provides full permissions on an object. A user with this privilege can also grant or revoke privileges to an object to other users. DELETE Tables, views Allows the user to delete records from the object. INSERT Tables, views Allows the user to insert records into an object through an INSERT or IMPORT command. SELECT Tables, views Provides the ability to use selection statements to view object content. UPDATE Tables, views Allows the user to modify records in an object using an UPDATE statement. Alter Table Allows the user to change the object definition using a change statement. INDEX Table Allows the user to create an index on an object using the CREATE INDEX statement. REFERENCES Table Provides the ability to create or delete foreign KEY constraints on an object. BIND Package Allows the user to rebind an existing package. EXECUTE packages, procedures, functions, methods Allows users to execute packages and routines. Alterin Mode Allows the user to modify the object definition in the schema. Createin Mode Allows the user to create objects in the schema. Dropin Mode Allows the user to delete objects in the schema. Information about object-level privileges is stored in the system catalog view. The view names are,,, syscat.tabauth syscat.colauth syscat.indexauth syscat.schemaauth ,,, syscat.routineauth and syscat.packageauth . Explicit privileges You can explicitly grant or revoke privileges to a user or group by using the grant and REVOKE commands. Let's take a look at how to use these commands on various objects. Log on to Windows as a user with Administrator permissions and open two DB2 command windows. In these two windows, make sure the db2instance variable is set to DB2 ! In the first window, give the following life? ¤: DB2 Connect to sample Now, in the second window, issue the following command: DB2 connect to sample user test1 using password Keep in mind that the commands in the first window are issued by a user with SYSADM permissions. The command in the second window is determined by theTst1Issued, this user does not have special permissions or privileges on the sample database. Note that the schema name associated with the table in the sample database is emitteddb2samplThe name of the user for the command. In these examples, this user isGmilne。 Now, in the second window, issue the following command: DB2 SELECT * FROM gmilne.org You should see the following response: sql0551n "TEST1" does not having the privilege to perform operation "select" On Object "gmilne.org". In order to correct this situation, issue the following command in the first window: DB2 grant Select on table gmilne.org to user test1 Now, the previous command will be successful! Next, issue a more complex command in the second window: DB2 INSERT into gmilne.org values (+, ' Tutorial ', 1, ' Eastern ', ' Toronto ') You will also see the error message: sql0551n "TEST1" does not having the privilege to perform operation "INSERT" on object "gmilne.org" So, enter the following command in the first window: DB2 grant Insert on table gmilne.org to group DB2GRP1 The original failed INSERT command should now be completed successfully because test1 is a member of the DB2GRP1 group. Now, enter the following command in the second window: DB2 drop TABLE Gmilne.emp_photo You will also see the error message: sql0551n "TEST1" does not having the privilege to perform operation ' DROP TABLE ' on object ' Gmilne. Emp_photo ". So, we're going to grant this privilege. In the first window, enter the following command: DB2 Grant Dropin on the schema gmilne to all DROP TABLEThe command should now be completed successfully. Now that you have completed the example, you can undo the privilege you just granted. In the first window, issue the following command: DB2 revoke select on table gmilne.org from user test1db2 revoke insert in table gmilne.org from group DB2GRP1DB2 Revoke Dr Opin on schema Gmilne from all Note that revoking a privilege from a group does not necessarily revoke it from all members of the group. For example, the following command can be used toDB2GRP1Revoke all privileges to the gmilne.org table (except CONTROL): DB2 revoke all on table gmilne.org from group DB2GRP1 Buttest1User (He isDB2GRP1Member) still has the option to select the table because he or she is granted this privilege directly. Implicit privileges When certain commands are issued, DB2 may automatically grant privileges without having to emit explicit grant statements as previously seen. The following table summarizes some of the commands that cause the database Manager to implicitly grant privileges. Note that these attributes are implicitly revoked when the created object is deleted. However, when more advanced privileges are explicitly revoked, they are not undone. Commands issued by Privileges granted Privileges granted to the user CREATE TABLE mytable CONTROL on the mytable The user who issued the command CREATE SCHEMA myschema Createin, Alterin, and Dropin on MySchema , and the ability to grant these privileges to other users The user who issued the command CREATE VIEW myview Control on myview (only if the user has control privileges on all tables and views referenced in the MyView definition) The user who issued the command CREATE DATABASE mydb mydb on the System catalog table on the Select,mydb on the Implicit_schema * public** * When the user creates the database, the user is implicitly granted the DBADM permission on this database. Getting Dbadm permissions implicitly grants CONNECT, Createtab, Bindadd, Implicit_schema, and create_not_fenced privileges. This user retains these privileges even if the DBADM permission is revoked. **public is a special DB2 group that includes all users of a particular database. Unlike the other groups discussed earlier, public does not have to be defined at the operating system level. By default, some privileges are granted to public. For example, this group automatically accepts the CONNECT privilege on the database and the SELECT privilege on the catalog table. You can issue the GRANT and REVOKE commands to the public group, such as: DB2 grant Select on table sysibm.systables to PUBLICDB2 revoke select on table sysibm.systables from public Indirect Privileges When the database manager executes the package , it can indirectly gain privileges. The package contains one or more SQL statements that have been converted to the format that DB2 uses to execute them internally. In other words, the package contains multiple SQL statements in an executable format. If all the statements in the package are static, then the user can successfully execute the statements in the package by simply having execute privileges on the package. For example, suppose Db2package1 executes the following static SQL statement: DB2 SELECT * FROM ORGDB2 INSERT INTO test values (1, 2, 3) In this case, users who have EXECUTE privileges on db2package1 are indirectly given the SELECT privilege on the org table and the INSERT privilege on the test table.

DB2 Database Permissions