Stored procedures for granting and revoking privileges for RDS for Db2
The built-in stored procedures described in this topic manage users, roles, groups, and
authorization for Amazon RDS for Db2 databases. To run these procedures, the master user must
first connect to the rdsadmin
database.
For tasks that use these stored procedures, see Granting and revoking privileges for RDS for Db2.
Refer to the following built-in stored procedures for information about their syntax, parameters, usage notes, and examples.
Stored procedures
rdsadmin.create_role
Creates a role.
Syntax
db2 "call rdsadmin.create_role( '
database_name
', 'role_name
')"
Parameters
The following parameters are required:
database_name
-
The name of the database the command will run on. The data type is
varchar
. role_name
-
The name of the role that you want to create. The data type is
varchar
.
Usage notes
For information about checking the status of creating a role, see rdsadmin.get_task_status.
Examples
The following example creates a role called MY_ROLE
for database
DB2DB
.
db2 "call rdsadmin.create_role( 'DB2DB', 'MY_ROLE')"
rdsadmin.grant_role
Assigns a role to a role, user, or group.
Syntax
db2 "call rdsadmin.grant_role( ?, '
database_name
', 'role_name
', 'grantee
', 'admin_option
')"
Parameters
The following output parameter is required:
- ?
-
A parameter marker that outputs the unique identifier for the task. This parameter only accepts
?
.
The following input parameters are required:
database_name
-
The name of the database the command will run on. The data type is
varchar
. role_name
-
The name of the role that you want to create. The data type is
varchar
. grantee
-
The role, user, or group to receive authorization. The data type is
varchar
. Valid values:ROLE
,USER
,GROUP
,PUBLIC
.Format must be value followed by name. Separate multiple values and names with commas. Example: '
USER
'. Replace the names with your own information.user1
,user2
, GROUPgroup1
,group2
The following input parameter is optional:
admin_option
-
Specifies whether the grantee
ROLE
hasDBADM
authorization to assign roles. The data type ischar
. The default isN
.
Usage notes
For information about checking the status of assigning a role, see rdsadmin.get_task_status.
Examples
Example 1: Assigning role to role, user, and group, and granting authorization
The following example assigns a role called ROLE_TEST
for database
TESTDB
to the role called role1
, the user called
user1
, and the group called group1
.
ROLE_TEST
is given admin authorization to assign roles.
db2 "call rdsadmin.grant_role( ?, 'TESTDB', 'ROLE_TEST', 'ROLE role1, USER user1, GROUP group1', 'Y')"
Example 2: Assigning role to PUBLIC
and not
granting authorization
The following example assigns a role called ROLE_TEST
for database
TESTDB
to PUBLIC
. ROLE_TEST
isn't given
admin authorization to assign roles.
db2 "call rdsadmin.grant_role( ?, 'TESTDB', 'ROLE_TEST', 'PUBLIC')"
rdsadmin.revoke_role
Revokes a role from a role, user, or group.
Syntax
db2 "call rdsadmin.revoke_role( ?, '
database_name
', 'role_name
', 'grantee
')"
Parameters
The following output parameter is required:
- ?
-
A parameter marker that outputs the unique identifier for the task. This parameter only accepts ?.
The following input parameters are required:
database_name
-
The name of the database the command will run on. The data type is
varchar
. role_name
-
The name of the role that you want to revoke. The data type is
varchar
. grantee
-
The role, user, or group to lose authorization. The data type is
varchar
. Valid values:ROLE
,USER
,GROUP
,PUBLIC
.Format must be value followed by name. Separate multiple values and names with commas. Example: '
USER
'. Replace the names with your own information.user1
,user2
, GROUPgroup1
,group2
Usage notes
For information about checking the status of revoking a role, see rdsadmin.get_task_status.
Examples
Example 1: Revoking role from role, user, and group
The following example revokes a role called ROLE_TEST
for database
TESTDB
from the role called role1
, the user called
user1
, and the group called group1
.
db2 "call rdsadmin.revoke_role( ?, 'TESTDB', 'ROLE_TEST', 'ROLE role1, USER user1, GROUP group1')"
Example 2: Revoking role from
PUBLIC
The following example revokes a role called ROLE_TEST
for database
TESTDB
from PUBLIC
.
db2 "call rdsadmin.revoke_role( ?, 'TESTDB', 'ROLE_TEST', 'PUBLIC')"
rdsadmin.drop_role
Drops a role.
Syntax
db2 "call rdsadmin.drop_role( ?, '
database_name
', 'role_name
')"
Parameters
The following output parameter is required:
- ?
-
A parameter marker that outputs the unique identifier for the task. This parameter only accepts ?.
The following input parameters are required:
database_name
-
The name of the database the command will run on. The data type is
varchar
. role_name
-
The name of the role that you want to drop. The data type is
varchar
.
Usage notes
For information about checking the status of dropping a role, see rdsadmin.get_task_status.
Examples
The following example drops a role called ROLE_TEST
for database
TESTDB
.
db2 "call rdsadmin.drop_role( ?, 'TESTDB', 'ROLE_TEST')"
rdsadmin.add_user
Adds a user to an authorization list.
Syntax
db2 "call rdsadmin.add_user( '
username
', 'password
', 'group_name
,group_name
')"
Parameters
The following parameters are required:
username
-
A user's username. The data type is
varchar
. password
-
A user's password. The data type is
varchar
.
The following parameter is optional:
group_name
-
The name of a group that you want to add the user to. The data type is
varchar
. The default is an empty string or null.
Usage notes
You can add a user to one or more groups by separating the group names with commas.
You can create a group when you create a new user, or when you add a group to an existing user. You can't create a group by itself.
Note
The maximum number of users that you can add by calling
rdsadmin.add_user
is 5,000.
For information about checking the status of adding a user, see rdsadmin.get_task_status.
Examples
The following example creates a user called jorge_souza
and assigns
the user to the groups called sales
and
inside_sales
.
db2 "call rdsadmin.add_user( 'jorge_souza', '*******', 'sales,inside_sales')"
rdsadmin.change_password
Changes a user's password.
Syntax
db2 "call rdsadmin.change_password( '
username
', 'new_password
')"
Parameters
The following parameters are required:
username
-
A user's username. The data type is
varchar
. new_password
-
A new password for the user. The data type is
varchar
.
Usage notes
For information about checking the status of changing a password, see rdsadmin.get_task_status.
Examples
The following example changes the password for jorge_souza
.
db2 "call rdsadmin.change_password( 'jorge_souza', '*******')"
rdsadmin.list_users
Lists users on an authorization list.
Syntax
db2 "call rdsadmin.list_users()"
Usage notes
For information about checking the status of listing users, see rdsadmin.get_task_status.
rdsadmin.remove_user
Removes user from authorization list.
Syntax
db2 "call rdsadmin.remove_user('
username
')"
Parameters
The following parameter is required:
username
-
A user's username. The data type is
varchar
.
Usage notes
For information about checking the status of removing a user, see rdsadmin.get_task_status.
Examples
The following example removes jorge_souza
from being able to access
databases in RDS for Db2 DB instances.
db2 "call rdsadmin.remove_user('jorge_souza')"
rdsadmin.add_groups
Adds groups to a user.
Syntax
db2 "call rdsadmin.add_groups( '
username
', 'group_name
,group_name
')"
Parameters
The following parameters are required:
username
-
A user's username. The data type is
varchar
. group_name
-
The name of a group that you want to add the user to. The data type is
varchar
. The default is an empty string.
Usage notes
You can add one or more groups to a user by separating the group names with commas. For information about checking the status of adding groups, see rdsadmin.get_task_status.
Examples
The following example adds the direct_sales
and
b2b_sales
groups to user jorge_souza
.
db2 "call rdsadmin.add_groups( 'jorge_souza', 'direct_sales,b2b_sales')"
rdsadmin.remove_groups
Removes groups from a user.
Syntax
db2 "call rdsadmin.remove_groups( '
username
', 'group_name
,group_name
')"
Parameters
The following parameters are required:
username
-
A user's username. The data type is
varchar
. group_name
-
The name of a group that you want to remove the user from. The data type is
varchar
.
Usage notes
You can remove one or more groups from a user by separating the group names with commas.
For information about checking the status of removing groups, see rdsadmin.get_task_status.
Examples
The following example removes the direct_sales
and
b2b_sales
groups from user jorge_souza
.
db2 "call rdsadmin.remove_groups( 'jorge_souza', 'direct_sales,b2b_sales')"
rdsadmin.dbadm_grant
Grants DBADM
, ACCESSCTRL
, or DATAACCESS
authorization to a role, user, or group.
Syntax
db2 "call rdsadmin.dbadm_grant( ?, '
database_name
', 'authorization
', 'grantee
')"
Parameters
The following output parameter is required:
- ?
-
A parameter marker that outputs the unique identifier for the task. This parameter only accepts
?
.
The following input parameters are required:
database_name
-
The name of the database the command will run on. The data type is
varchar
. authorization
-
The type of authorization to grant. The data type is
varchar
. Valid values:DBADM
,ACCESSCTRL
,DATAACCESS
.Separate multiple types with commas.
grantee
-
The role, user, or group to receive authorization. The data type is
varchar
. Valid values:ROLE
,USER
,GROUP
.Format must be value followed by name. Separate multiple values and names with commas. Example: '
USER
'. Replace the names with your own information.user1
,user2
, GROUPgroup1
,group2
Usage notes
The role to receive access must exist.
For information about checking the status of granting database admin access, see rdsadmin.get_task_status.
Examples
Example 1: Granting database admin access to role
The following example grants database admin access to the database named
TESTDB
for the role ROLE_DBA
.
db2 "call rdsadmin.dbadm_grant( ?, 'TESTDB', 'DBADM', 'ROLE ROLE_DBA')"
Example 2: Granting database admin access to user and group
The following example grants database admin access to the database named
TESTDB
for user1
and group1
.
db2 "call rdsadmin.dbadm_grant( ?, 'TESTDB', 'DBADM', 'USER user1, GROUP group1')"
Example 3: Granting database admin access to multiple users and groups
The following example grants database admin access to the database named
TESTDB
for user1
, user2
,
group1
, and group2
.
db2 "call rdsadmin.dbadm_grant( ?, 'TESTDB', 'DBADM', 'USER user1, user2, GROUP group1, group2')"
rdsadmin.dbadm_revoke
Revokes DBADM
, ACCESSCTRL
, or DATAACCESS
authorization from a role, user, or group.
Syntax
db2 "call rdsadmin.dbadm_revoke( ?, '
database_name
', 'authorization
', 'grantee
')"
Parameters
The following output parameter is required:
- ?
-
The unique identifier for the task. This parameter only accepts
?
.
The following input parameters are required:
database_name
-
The name of the database the command will run on. The data type is
varchar
. authorization
-
The type of authorization to revoke. The data type is
varchar
. Valid values:DBADM
,ACCESSCTRL
,DATAACCESS
.Separate multiple types with commas.
grantee
-
The role, user, or group to revoke authorization from. The data type is
varchar
. Valid values:ROLE
,USER
,GROUP
.Format must be value followed by name. Separate multiple values and names with commas. Example: '
USER
'. Replace the names with your own information.user1
,user2
, GROUPgroup1
,group2
Usage notes
For information about checking the status of revoking database admin access, see rdsadmin.get_task_status.
Examples
Example 1: Revoking database admin access from role
The following example revokes database admin access to the database named
TESTDB
for the role ROLE_DBA
.
db2 "call rdsadmin.dbadm_revoke( ?, 'TESTDB', 'DBADM', 'ROLE ROLE_DBA')"
Example 2: Revoking database admin access from user and group
The following example revokes database admin access to the database named
TESTDB
for user1
and group1
.
db2 "call rdsadmin.dbadm_revoke( ?, 'TESTDB', 'DBADM', 'USER user1, GROUP group1')"
Example 3: Revoking database admin access from multiple users and groups
The following example revokes database admin access to the database named
TESTDB
for user1
, user2
,
group1
, and group2
.
db2 "call rdsadmin.dbadm_revoke( ?, 'TESTDB', 'DBADM', 'USER user1, user2, GROUP group1, group2')"