Creating a custom database endpoint Granting and revoking privileges Attaching to the remote DB instance

Performing common system tasks for RDS for Db2 DB instances

You can perform certain common database administrator tasks related to the system on your Amazon RDS DB instances running Db2. To deliver a managed service experience, Amazon RDS doesn't provide shell access to DB instances, and restricts access to certain system procedures and tables that require advanced privileges.

Topics

Creating a custom database endpoint
Granting and revoking privileges
Attaching to the remote RDS for Db2 DB instance

Creating a custom database endpoint

When you migrate to RDS for Db2, you can use custom database endpoint URLs to minimize changes to your application. For example, if you use db2.example.com as your current DNS record, you can add it to Amazon Route 53. In Route 53, you can use private hosted zones to map your current DNS database endpoint to an RDS for Db2 database endpoint. To add a custom A or CNAME record for an Amazon RDS database endpoint, see Registering and managing domains using Amazon Route 53 in the Amazon Route 53 Developer Guide.

Note

If you can't transfer your domain to Route 53, you can use your DNS provider to create a CNAME record for the RDS for Db2 database endpoint URL. Consult your DNS provider documentation.

Granting and revoking privileges

Users gain access to databases through membership in groups that are attached to databases. If you remove all groups attached to a database from a user, then the user can't connect to the database.

Use the following procedures to grant and revoke privileges to control access to your database.

These procedures use IBM Db2 CLP running on a local machine to connect to an RDS for Db2 DB instance. Be sure to catalog the TCPIP node and the database to connect to your RDS for Db2 DB instance running on your local machine. For more information, see Connecting to your RDS for Db2 DB instance with IBM Db2 CLP.

Granting a user access to your database

To grant a user access to your database

Connect to the rdsadmin database using the master username and master password for your RDS for Db2 DB instance. In the following example, replace master_username and master_password with your own information.
```
db2 connect to rdsadmin user master_username using master_password
```
This command produces output similar to the following example:
```
Database Connection Information
    
Database server        = DB2/LINUXX8664 11.5.8.0
SQL authorization ID   = ADMIN
Local database alias   = RDSADMIN
```
Add a user to your authorization list by calling rdsadmin.add_user. For more information, see rdsadmin.add_user.
```
db2 "call rdsadmin.add_user(
    'username',
    'password',
    'group_name,group_name')"
```
(Optional) Add additional groups to the user by calling rdsadmin.add_groups. For more information, see rdsadmin.add_groups.
```
db2 "call rdsadmin.add_groups(
    'username',
    'group_name,group_name')"
```

Confirm the authorities that are available to the user. In the following example, replace rds_database_alias, master_user, and master_password with your own information. Also, replace username with the user's username.


db2 terminate
db2 connect to rds_database_alias user master_user using master_password
db2 "SELECT SUBSTR(AUTHORITY,1,20) AUTHORITY, D_USER, D_GROUP, D_PUBLIC
       FROM TABLE (SYSPROC.AUTH_LIST_AUTHORITIES_FOR_AUTHID ('username', 'U') ) AS T
       ORDER BY AUTHORITY"

This command produces output similar to the following example:


AUTHORITY            D_USER D_GROUP D_PUBLIC
-------------------- ------ ------- --------
ACCESSCTRL           N      N       N
BINDADD              N      N       N
CONNECT              N      N       N
CREATETAB            N      N       N
CREATE_EXTERNAL_ROUT N      N       N
CREATE_NOT_FENCED_RO N      N       N
CREATE_SECURE_OBJECT N      N       N
DATAACCESS           N      N       N
DBADM                N      N       N
EXPLAIN              N      N       N
IMPLICIT_SCHEMA      N      N       N
LOAD                 N      N       N
QUIESCE_CONNECT      N      N       N
SECADM               N      N       N
SQLADM               N      N       N
SYSADM               *      N       *
SYSCTRL              *      N       *
SYSMAINT             *      N       *
SYSMON               *      N       *
WLMADM               N      N       N

Grant the RDS for Db2 roles ROLE_NULLID_PACKAGES, ROLE_TABLESPACES, and ROLE_PROCEDURES to the group that you added the user to.

Note
We create RDS for Db2 DB instances in RESTRICTIVE mode. Therefore, the RDS for Db2 roles ROLE_NULLID_PACKAGES, ROLE_TABLESPACES, and ROLE_PROCEDURES grant execute privileges on NULLID packages for IBM Db2 CLP and Dynamic SQL. These roles also grant user privileges on tablespaces.
1. Connect to your Db2 database. In the following example, replace database_name, master_user, and master_password with your own information.
```
db2 connect to database_name user master_user using master_password
```
2. Grant the role ROLE_NULLED_PACKAGES to a group. In the following example, replace group_name with the name of the group that you want to add the role to.
```
db2 "grant role ROLE_NULLID_PACKAGES to group group_name"
```
3. Grant the role ROLE_TABLESPACES to the same group. In the following example, replace group_name with the name of the group that you want to add the role to.
```
db2 "grant role ROLE_TABLESPACES to group group_name"
```
4. Grant the role ROLE_PROCEDURES to the same group. In the following example, replace group_name with the name of the group that you want to add the role to.
```
db2 "grant role ROLE_PROCEDURES to group group_name"
```
Grant connect, bindadd, createtab, and IMPLICIT_SCHEMA authorities to the group that you added the user to. In the following example, replace group_name with the name of the second group that you added the user to.
```
db2 "grant usage on workload SYSDEFAULTUSERWORKLOAD to public"
db2 "grant connect, bindadd, createtab, implicit_schema on database to group group_name"
```
Repeat steps 4 through 6 for each additional group that you added the user to.
Test the user's access by connecting as the user, creating a table, inserting values into the table, and returning data from the table. In the following example, replace rds_database_alias, username, and password with the name of the database and the user's username and password.
```
db2 connect to rds_database_alias user username using password
db2 "create table t1(c1 int not null)"
db2 "insert into t1 values (1),(2),(3),(4)"
db2 "select * from t1"
```

Changing a user's password

To change a user's password

Connect to the rdsadmin database using the master username and master password for your RDS for Db2 DB instance. In the following example, replace master_username and master_password with your own information.
```
db2 connect to rdsadmin user master_username using master_password
```
Change the password by calling rdsadmin.change_password. For more information, see rdsadmin.change_password.
```
db2 "call rdsadmin.change_password(
    'username',
    'new_password')"
```

Adding groups to a user

To add groups to a user

Connect to the rdsadmin database using the master username and master password for your RDS for Db2 DB instance. In the following example, replace master_username and master_password with your own information.
```
db2 connect to rdsadmin user master_username using master_password
```
Add groups to a user by calling rdsadmin.add_groups. For more information, see rdsadmin.add_groups.
```
db2 "call rdsadmin.add_groups(
    'username',
    'group_name,group_name')"
```

Removing groups from a user

To remove groups from a user

Connect to the rdsadmin database using the master username and master password for your RDS for Db2 DB instance. In the following example, replace master_username and master_password with your own information.
```
db2 connect to rdsadmin user master_username using master_password
```
Remove groups by calling rdsadmin.remove_groups. For more information, see rdsadmin.remove_groups.

Warning
If you remove all groups attached to a database from a user, then the user can't connect to the database. This is because Amazon RDS grants authority to the group, not the user.
```
db2 "call rdsadmin.remove_groups(
    'username',
    'group_name,group_name')"
```

Removing a user

To remove a user from the authorization list

Connect to the rdsadmin database using the master username and master password for your RDS for Db2 DB instance. In the following example, replace master_username and master_password with your own information.
```
db2 connect to rdsadmin user master_username using master_password
```
Remove a user from your authorization list by calling rdsadmin.remove_user. For more information, see rdsadmin.remove_user.
```
db2 "call rdsadmin.remove_user('username')"
```

Listing users

To list users on an authorization list, call the rdsadmin.list_users stored procedure. For more information, see rdsadmin.list_users.


db2 "call rdsadmin.list_users()"

Creating a role

You can use the rdsadmin.create_role stored procedure to create a role.

To create a role

Connect to the rdsadmin database. In the following example, replace master_username and master_password with your information.
```
db2 connect to rdsadmin user master_username using master_password
```
Set Db2 to output content.
```
db2 set serveroutput on 
```

Create a role. For more information, see rdsadmin.create_role.


db2 "call rdsadmin.create_role(
    'database_name',
    'role_name')"

Set Db2 to not output content.
```
db2 set serveroutput off
```

Granting a role

You can use the rdsadmin.grant_role stored procedure to assign a role to a role, user, or group.

To assign a role

Connect to the rdsadmin database. In the following example, replace master_username and master_password with your information.
```
db2 connect to rdsadmin user master_username using master_password
```
Set Db2 to output content.
```
db2 set serveroutput on 
```

Assign a role. For more information, see rdsadmin.grant_role.


db2 "call rdsadmin.grant_role(
    'database_name',
    'role_name',
    'grantee',
    'admin_option')"

Set Db2 to not output content.
```
db2 set serveroutput off
```

Revoking a role

You can use the rdsadmin.revoke_role stored procedure to revoke a role from a role, user, or group.

To revoke a role

Connect to the rdsadmin database. In the following example, replace master_username and master_password with your information.
```
db2 connect to rdsadmin user master_username using master_password
```

Revoke a role. For more information, see rdsadmin.revoke_role.


db2 "call rdsadmin.revoke_role(
    ?,
    'database_name',
    'role_name',
    'grantee')"

Granting database authorization

The master user, who has DBADM authorization, can grant DBADM, ACCESSCTRL, or DATAACCESS authorization to a role, user, or group.

To grant database authorization

Connect to the rdsadmin database using the master username and master password for your RDS for Db2 DB instance. In the following example, replace master_username and master_password with your own information.
```
db2 connect to rdsadmin user master_username using master_password
```
Grant a user access by calling rdsadmin.dbadm_grant. For more information, see rdsadmin.dbadm_grant.
```
db2 "call rdsadmin.dbadm_grant(
    ?,
    'database_name,
    'authorization',
    'grantee')"
```

Example use case

The following procedure walks you through creating a role, granting DBADM authorization to the role, and assigning the role to a user.

To create a role, grant `DBADM` authorization, and assign the role to a user

Connect to the rdsadmin database using the master username and master password for your RDS for Db2 DB instance. In the following example, replace master_username and master_password with your own information.
```
db2 connect to rdsadmin user master_username using master_password
```
Create a role called PROD_ROLE for a database called TESTDB. For more information, see rdsadmin.create_role.
```
db2 "call rdsadmin.create_role(
    'TESTDB',
    'PROD_ROLE')"
```
Assign the role to a user called PROD_USER. The PROD_USER is given admin authorization to assign roles. For more information, see rdsadmin.grant_role.
```
db2 "call rdsadmin.grant_role(
    ?,
    'TESTDB',
    'PROD_ROLE',
    'USER PROD_USER',
    'Y')"
```
(Optional) Provide additional authorization or privileges. The following example grants DBADM authorization to a role named PROD_ROLE for a database called FUNDPROD. For more information, see rdsadmin.dbadm_grant.
```
db2 "call rdsadmin.dbadm_grant(
    ?,
    'FUNDPROD',
    'DBADM',
    'ROLE PROD_ROLE')"
```
Terminate your session.
```
db2 terminate
```
Connect to the testdb database using the master username and master password for your RDS for Db2 DB instance. In the following example, replace master_username and master_password with your own information.
```
db2 connect to testdb user master_username using master_password
```

Add more authorizations to the role.


db2 "grant connect, implicit_schema on database to role PROD_ROLE"

Revoking database authorization

The master user, who has DBADM authorization, can revoke DBADM, ACCESSCTRL, or DATAACCESS authorization from a role, user, or group.

To revoke database authorization

Connect to the rdsadmin database using the master username and master password for your RDS for Db2 DB instance. In the following example, replace master_username and master_password with your own information.
```
db2 connect to rdsadmin user master_username using master_password
```
Revoke user access by calling rdsadmin.dbadm_revoke. For more information, see rdsadmin.dbadm_revoke.
```
db2 "call rdsadmin.dbadm_revoke(
    ?,
    'database_name,
    'authorization',
    'grantee')"
```

Attaching to the remote RDS for Db2 DB instance

To attach to the remote RDS for Db2 DB instance

Run a client-side IBM Db2 CLP session. For information about cataloging your RDS for Db2 DB instance and database, see Connecting to your RDS for Db2 DB instance with IBM Db2 CLP. Make a note of the master username and master password for your RDS for Db2 DB instance.
Attach to the RDS for Db2 DB instance. In the following example, replace node_name, master_username, and master_password with the TCPIP node name that you catalogued and the master username and master password for your RDS for Db2 DB instance.
```
db2 attach to node_name user master_username using master_password
```

After attaching to the remote RDS for Db2 DB instance, you can run the following commands and other get snapshot commands. For more information, see GET SNAPSHOT command in the IBM Db2 documentation.


db2 list applications
db2 get snapshot for all databases
db2 get snapshot for database manager
db2 get snapshot for all applications

Warning Javascript is disabled or is unavailable in your browser.

To use the Amazon Web Services Documentation, Javascript must be enabled. Please refer to your browser's Help pages for instructions.

Document Conventions

Administering your RDS for Db2 DB instance

Database tasks

Performing common system tasks for RDS for Db2 DB instances

Topics

Creating a custom database endpoint

Note

Granting and revoking privileges

Topics

Granting a user access to your database

To grant a user access to your database

Note

Changing a user's password

To change a user's password

Adding groups to a user

To add groups to a user

Removing groups from a user

To remove groups from a user

Warning

Removing a user

To remove a user from the authorization list

Listing users

Creating a role

To create a role

Granting a role

To assign a role

Revoking a role

To revoke a role

Granting database authorization

To grant database authorization

To create a role, grant DBADM authorization, and assign the role to a user

Revoking database authorization

To revoke database authorization

Attaching to the remote RDS for Db2 DB instance

To attach to the remote RDS for Db2 DB instance

To create a role, grant `DBADM` authorization, and assign the role to a user