This chapter describes IBM DB2 database security features and how they are handled by SQL Developer.
Security in IBM DB2 UDB is managed by a system containing Authority Levels and Privileges. Users can only access objects which they have the required privilege or authority for.
SQL Developer maps IBM DB2 users to Oracle users. Users are mapped to Oracle with the prefix USR_, for example USR_GUEST. IBM DB2 groups are mapped to Oracle as roles. Some system level privileges and table and column level privileges are migrated from IBM DB2 to users in Oracle.
When migrating IBM DB2 groups, SQL Developer creates an Oracle role with the same name as the IBM DB2 group. In Oracle, a role is a set of privileges that can be granted to users, or to other roles. The users belonging to an IBM DB2 group cannot be determined from the system catalog tables so, therefore, SQL Developer does not grant the migrated roles to users. It is the responsibility of the Oracle database administrator to grant the role to the migrated users. For more information about granting, dropping, and altering roles, see Oracle Database SQL Language Reference.
IBM DB2 authorities can be categorized into two types: instance level authorities and database level authorities.
There are three instance level authorities available in IBM DB2 UDB:
Administration Authority (SYSADM)
System Control Authority (SYSCTRL)
System Maintenance Authority (SYSMAINT)
All three instance level authorities are controlled by the installation platform's security system. Due to cross-platform differences in security models, SQL Developer does not migrate instance level authorities. For more information about Oracle system level authorities, see Oracle Database Administrator's Guide.
There are six database level authorities available in IBM DB2. These authorities are available on a specific database rather than a single IBM DB2 instance. There is no direct mapping for IBM DB2 database level privileges in Oracle.
The following table outlines the privileges assigned to users and schemas that are migrated as users to Oracle by SQL Developer.
| Oracle Privilege | Description |
|---|---|
| CONNECT | This privilege grants users the right to use Oracle. Users who have the CONNECT role may also create tables, views, sequences, clusters, synonyms, sessions and links to other databases. |
| RESOURCE | This privilege grants users right to create their own tables, sequences, procedures, triggers, indexes and clusters. |
| CREATE PUBLIC SYNONYM | This privilege grants users the right to create public synonyms in an Oracle database. This emulates the ability in IBM DB2 UDB for users to create aliases. |
| DROP PUBLIC SYNONYM | This privilege grants users the right to drop public synonyms in the Oracle database. |
There are over 100 separate system privileges alone in Oracle. For more information about how to configure authorization in Oracle, see Oracle Database Administrator's Guide.
A database privilege grants the right to perform a specific action or operation on a specific object. In IBM DB2 privileges can be categorized into the following types:
Table and Column Privileges
Index Privileges
Sequence Privileges
Schema Privileges
Package Privileges
The following privileges can be granted for a table and column.
Grants ALTER, INDEX, REFERENCES, INSERT, DELETE and UPDATE privileges on a table. This privilege also gives the right to grant any applicable privilege on a table to other users and groups. This privilege also grants the right to drop a table and to update the statistics that apply to a table.
Grants the right to alter a tables definition, for example to add a column to the table. It also grants the right to add a comment to a table, using the COMMENT statement, and to create a trigger on a table, using the CREATE TRIGGER statement).
Grants the right to create or drop foreign key constraints in other tables that reference a parent key in the current table. A REFERENCE privilege may be granted on a whole table or on columns.
Grants the right to select data from a table using the SELECT statement and also grants the right to use table in a subquery.
Grants the right to update rows in a table. The UPDATE privilege may be granted on a whole table or on columns.
The following tables list the privilege mappings from IBM DB2 UDB to Oracle for tables and columns respectively.
| IBM DB2 UDB Table Privilege | Oracle Table Privilege |
|---|---|
| CONTROL | ALTER, DELETE, INDEX, INSERT, REFERENCES, SELECT, UPDATE |
| ALTER | ALTER |
| DELETE | DELETE |
| INDEX | INDEX |
| INSERT | INSERT |
| REFERENCES | REFERENCES |
| SELECT | SELECT |
| UPDATE | UPDATE |
| IBM DB2 UDB Column Privilege | Oracle Column Privilege |
|---|---|
| CONTROL | INSERT, REFERENCES, UPDATE |
| INSERT | INSERT |
| REFERENCES | REFERENCES |
| UPDATE | UPDATE |
The CONTROL privilege is the only privilege available for an index. This privilege is granted to a single user, or groups of users, and grants the user the drop privilege on an index. A user must have SYSADM or DBADM authorities to grant this privilege, however it is granted automatically to the index creator.
To drop an index in Oracle, the index must be contained in your user account or you must have the DROP ANY INDEX system privilege. In order to drop an index defined on a constraint you must disable or drop the constraint itself, before dropping the index.
However, the DROP ANY INDEX system privilege in Oracle grants the right to drop an index in any user account in the database. As grants the right to drop only a specified index, and not all indexes, this is not mapped to the index CONTROL privilege in IBM DB2 UDB. SQL Developer determines the creator of an index, and this user is granted the right to drop the index in the Oracle database.
Oracle also provides the ability to alter an index. You can rebuild or coalesce an index, alter its real and default storage characteristics, but you cannot change its column structure. To alter an index, your user account must contain the index, or you must have the ALTER ANY INDEX system privilege. Again, when SQL Developer maps indexes from IBM DB2 to Oracle, it determines the creator of the index, and this user is granted the right to alter privileges on the index.
Sequence objects are not migrated by SQL Developer. Therefore the migration of the privileges that apply to them are outside the scope of this document.
An IBM DB2 schema is a collection of named objects. Schemas provide a logical classification for objects in an IBM DB2 database and can contain tables, indexes, views, and triggers. A schema is itself an object in the database and is created using the CREATE SCHEMA statement. A schema name is used as the high order part of a two part object name, for example tab_schema.mytable. If the schema name is specified during the creation of an object, the object is assigned to that schema. If it is not specified, the value for the IMPLICIT_SCHEMA is used. This is the default schema name.
The following privileges can be granted for a schema:
This grants the right to alter the definition of a table in the schema, and to add a comment for any object in the schema.
This grants the right to drop an object in the schema.
The IBM DB2 schema object does not have a direct mapping in Oracle. An Oracle database can have multiple users, each of which has a user account. Each user account is a logical collection of database objects, such as tables and indexes. Therefore, a schema in IBM DB2 is mapped to a user in Oracle. When SQL Developer encounters an IBM DB2 schema during migration, it creates a user account in the target Oracle database using the name of the IBM DB2 schema.
The following explains how SQL Developer emulates the IBM DB2 schema in Oracle, using a table called TAB owned by a user called USR in an IBM DB2 schema called SCH:
SQL Developer creates a user account called SCH in the Oracle database.
SQL Developer creates a table called TAB in the SCH user account. The SCH user account automatically becomes the owner of that table.
SQL Developer creates another user account called USR in the Oracle database. USR is mapped to a user called USR in Oracle.
SQL Developer creates a private synonym called TAB in the USR user account. This synonym ensures that a user connected as USR can manipulate the TAB table without having to explicitly prefix the table name with SCH.
All objects that resided in the SCH schema are created in the SCH user account in Oracle. All table and column level privileges granted to the USR user in IBM DB2, for objects residing in the SCH schema, are migrated to Oracle. The USR user account in Oracle is granted the appropriate privileges on objects residing in the SCH user account.