Scenario: Suppose you have a database table and now an external application wants to connect to your database and read/view the records of this table.
Approach: For the external application create a separate user and grant only the SELECT access to this table. This way your access is controlled and there is no chance of an inadvertent update/insert by the new-user
Here are the steps:
Login with new-user, and result of select query before the grant was given
Login with admin user, the table COMPANY in database AD, and the GRANT statement
After the grant was given
For revoking the access use below command:
db2 revoke select on table company from user ab844900
I've successfully tested these commands on these environments
1. DB2 10.5 Express C on Windows 7
2. DB2 10.1 Enterprise on Windows 2008 Server Standard Edition R2
NOTE:
1. Test all database commands in a development/test environment prior to running on Prod. Always!!
2. No matter how certain you are, make sure you have access to a database specialist in-case of trouble :)
There are plethora of options with GRANT like insert,update,alter table. Refer the IBM DB2 documentation here
http://www-01.ibm.com/support/knowledgecenter/SSEPEK_10.0.0/com.ibm.db2z10.doc.sqlref/src/tpc/db2z_sql_granttableorviewprivileges.dita
Good luck!
Approach: For the external application create a separate user and grant only the SELECT access to this table. This way your access is controlled and there is no chance of an inadvertent update/insert by the new-user
Here are the steps:
- Create a local user on the db2 machine for external application to connect. If you have a db2 user group on the machine, add this user to it and try to connect to your database. Once you are able to connect, proceed to next step.
- With the new-user, run a select command on the needed table. DB2 will throw authorization error and should not return any rows since access is not granted to the new user.
- db2 select count(*) <schema_owner_name>.MyTable.
- Open another terminal and connect to you database with the admin user.
- With your admin user, issue the GRANT command to new-user:
- db2 grant select on table MyTable to user NewUser.
- From the first terminal, again issue a select query from step 2. This time you should be able to see something returned.
Login with new-user, and result of select query before the grant was given
Login with admin user, the table COMPANY in database AD, and the GRANT statement
After the grant was given
For revoking the access use below command:
db2 revoke select on table company from user ab844900
I've successfully tested these commands on these environments
1. DB2 10.5 Express C on Windows 7
2. DB2 10.1 Enterprise on Windows 2008 Server Standard Edition R2
NOTE:
1. Test all database commands in a development/test environment prior to running on Prod. Always!!
2. No matter how certain you are, make sure you have access to a database specialist in-case of trouble :)
There are plethora of options with GRANT like insert,update,alter table. Refer the IBM DB2 documentation here
http://www-01.ibm.com/support/knowledgecenter/SSEPEK_10.0.0/com.ibm.db2z10.doc.sqlref/src/tpc/db2z_sql_granttableorviewprivileges.dita
Good luck!
