System privilege Database role
The master user is assigned to the masterdba
group and assigned the master_user_role.
SYSMON,
DBADM with DATAACCESS DBA
AND ACCCESSCTRL, BINDADD, DBA_RESTRICTED
CONNECT, DEVELOPER
CREATETAB, ROLE_NULLID_PACKAGES
CREATE_SECURE_OBJECT, ROLE_PROCEDURES
EXPLAIN, ROLE_TABLESPACES
IMPLICIT_SCHEMA,
LOAD, SQLADM,
WLMADM
create_role --> Creates a role called my for database DB2DB
call rdsadmin.create_role(‘intdb2 ', 'my_role')
grant_role -->Assigns a role to a role, user, or group
call rdsadmin.grant_role( ?, intdb2, 'ROLE_TEST', 'ROLE role1, USER user1, GROUP group1', 'Y')
Example: assigns a role called ROLE_TEST for database TESTDB to the role called role1, the user called user1, and the group called group1
add_user --> Adds a user to an authorization list.
call rdsadmin.add_user( 'jorge_souza', '*******', 'sales,inside_sales')"
Other solution RDSADMIN
call rdsadmin.add_user('TXNDBAPP','F9uincU8Q8$6', 'TXNGRPRW')
call rdsadmin.create_role('intdb2','MY_ROLE')
call rdsadmin.grant_role(?,'intdb2','MY_ROLE', 'PUBLIC')
call rdsadmin.add_user('jorge_souza','F9uincU8Q8$6','sales,inside_sales')
call rdsadmin.add_groups('jorge_souza','direct_sales,b2b_sales')
RDSADMIN Grants DBADM, ACCESSCTRL, or DATAACCESS authorization to a role, user, or group.
call rdsadmin.create_role('intdb2','MY_ROLE')
call rdsadmin.grant_role(?,'intdb2','MY_ROLE', 'PUBLIC')
call rdsadmin.add_user('jorge_souza','F9uincU8Q8$6','sales,inside_sales')
call rdsadmin.dbadm_grant( ?,'intdb2','DBADM','USER faiz')
------------------Revoke Access---------------
call rdsadmin.dbadm_revoke( ?,'intdb2','DBADM','USER jorge_souza')
MASTER user
grant connect, implicit_schema on database to USER jorge_souza
grant connect, bindadd, createtab, implicit_schema on database to group sales
grant role ROLE_NULLID_PACKAGES to group sales
GRANT SELECT ON TABLE SYSCAT.SCHEMATA TO USER jorge_souza;
grant usage on workload SYSDEFAULTUSERWORKLOAD to public
grant role ROLE_TABLESPACES to group sales
grant role ROLE_PROCEDURES to group sales
Check Queries-----RDSADMIN-
select task_id, task_type, database_name, lifecycle,
varchar(bson_to_json(task_input_params), 500) as task_params,
cast(task_output as varchar(500)) as task_output
from table(rdsadmin.get_task_status(null,null,null))
call rdsadmin.list_users()
Check Querie MASTER
SELECT * FROM SYSIBMADM.PRIVILEGES WHERE lower(AUTHID) = 'faiz' AND AUTHIDTYPE = 'U'
SELECT * FROM SYSCAT.DBAUTH WHERE lower(GRANTEE) = 'faiz' AND GRANTEETYPE = 'U'
https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/db2-sp-granting-revoking-privileges.html
----1 Step ---RUN RDSADMIN WINDOWS---
call rdsadmin.add_groups('MFGRP','SRVGRP')
call rdsadmin.add_user('username','password','group_name,group_name')--Format----
call rdsadmin.add_user('DBUSER','F9uincU8Q8$6', 'DBEGROUP')
---2 Steps -RUN Main Dtabase -----
GRANT CONNECT on DATABASE TO USER DBUSER
grant connect, implicit_schema on database to USER DBUSER
call rdsadmin.dbadm_grant(?,'intdb2,'authorization','grantee')
call rdsadmin.dbadm_grant( ?,'intdb2','DBADM','USER DBUSER')
select 'grant SELECT on ' || trim(tabschema) || '.' || trim(tabname) || ' to GROUP DBETKTDR, DBETKTDW; '
from syscat.tables where tabschema='EWALLET' and TYPE='T'
select 'grant INSERT, UPDATE, DELETE on table ' || trim(tabschema) || '.' || trim(tabname) || ' to GROUP DBETKTDW; ' from syscat.tables where tabschema='EWALLET' and TYPE='T'
Other Solution
------RDSADMIN-Grants DBADM, ACCESSCTRL, or DATAACCESS authorization to a role, user, or group.
Check User & Group listà call rdsadmin.list_users()
call rdsadmin.create_role('intdb2','fp_depeloper')
call rdsadmin.add_user('FPDBAPP','F9uincU8Q8$6', 'FPGRPDW')
call rdsadmin.grant_role(?,'intdb2','fp_depeloper', 'PUBLIC')
--------------------------ADMIN PRIVALLAGE-----------------
call rdsadmin.dbadm_grant( ?,'intdb2','DBADM','USER faiz')
-------------------------------Revoke Access---------------
call rdsadmin.dbadm_revoke( ?,'intdb2','DBADM','USER jorge_souza')
-----------------Execute --MASTER-USER ---------------------------
GRANT connect, bindadd, createtab, implicit_schema on database to group FPGRPDW
GRANT role ROLE_NULLID_PACKAGES to group FPGRPDW
GRANT role DEVELOPER to group FPGRPDW
GRANT connect, implicit_schema on database to USER
GRANT SELECT ON TABLE SYSCAT.SCHEMATA TO USER jorge_souza;
GRANT usage on workload SYSDEFAULTUSERWORKLOAD to public
GRANT role ROLE_TABLESPACES to group sales
GRANT role ROLE_PROCEDURES to group sales
-------------------Check Queries-----USER RDSADMIN-----------
select task_id, task_type, database_name, lifecycle,
varchar(bson_to_json(task_input_params), 500) as task_params,
cast(task_output as varchar(500)) as task_output
from table(rdsadmin.get_task_status(null,null,null))
call rdsadmin.list_users()
---------------------Check Querie MASTER----------------------------
SELECT * FROM SYSIBMADM.PRIVILEGES WHERE lower(AUTHID) = 'FPDBAPP'AND AUTHIDTYPE = 'U'
SELECT * FROM SYSCAT.DBAUTH WHERE lower(GRANTEE) = 'FPDBAPP'AND GRANTEETYPE = 'U'
-************************ERROR AND Solution***************
ISSUE FPDBAPP;EXECUTE;SYSIBM.SQLCAMESSAGECCSID
SOLOUTION --> grant role DEVELOPER to group txngrprw
ISSUE FPDBAPP;EXECUTE;NULLID.SYSSH200$6
SOLOUTION --> grant role ROLE_NULLID_PACKAGES to group FPGRPDW
--If error getting TXNDBAPP;EXECUTE;NULLID.SYSSH200
GRANT EXECUTE ON PACKAGE NULLID.SYSSH200 TO USER TXNDBAPP;
---Check Permissions:SYSCAT.PACKAGEAUTH catalog:
SELECT * FROM SYSCAT.PACKAGEAUTH WHERE PKGNAME = 'SYSSH200';
-------------------DB2 database Connect with EC2 Linux Machine Create Catalogs-----------
db2 catalog TCPIP node db2test remote db2-apps-prd-.ca-central-1.rds.amazonaws.com server 50000
db2 catalog database db2database as db2db at node db2test
db2 connect to db2db user db_admin using 'test123'
db2top -d db2db -n db2test -u db_admin -p 'test123'
-----Check Existing Catalog list----------------------------------
[db2inst1@ip-10-113-34-60 ~]$ db2 list node directory
-------Detached Node for Catalog list------------------
[db2inst1@ip-10-113-34-60 ~]$ db2 UNCATALOG NODE db2test