DB2 user table batch authorization_PHP tutorial

DB2 user table batch authorization

1. New user
[root@localhost ~]# passwd ghan
Changing password for user ghan.
New UNIX password:
BAD PASSWORD: it is too short
Retype new UNIX password:
passwd: all authentication tokens updated successfully.
[root@localhost ~]# su - db2inst2
[db2inst2@ localhost ~]$ db2 connect to ghan user ghan
Enter ghan’s current password:


Database connection information


Database server = DB2/LINUXX8664 9.7.5
SQL authorization ID = GHAN
Local database alias = GHAN


2. Error reported when accessing the db2int2.t2 table

[db2inst2@localhost ~]$ db2 "select count( 1) from db2inst2.t2"
SQL0551N "GHAN" does not have the necessary permissions or privileges to perform operation "SELECT"
on object "DB2INST2.T2". SQLSTATE=42501



[db2inst2@localhost ~]$ db2 connect to ghan


Database connection information


Database server= DB2/LINUXX8664 9.7.5
SQL authorization ID = DB2INST2
Local database alias = GHAN

3. Generate authorization script
[db2inst2@localhost ~]$ db2 -x o -z commands. sql "select 'grant select,insert, update, delete on table ' || trim(tabschema) || '.' || trim(tabname) || ' to user ghan;' from syscat.tables where type = 'T' "
4. Modify the authorization for the write table
[db2inst2@localhost ~]$ vi commands.sql

**** slightly***

5. Execute the authorization statement
[db2inst2@localhost ~]$ db2 -tsvf commands.sql
grant select,insert, update, delete on table DB2INST2.EMPL to user ghan
DB20000I SQL command completed successfully.


grant select,insert, update, delete on table DB2INST2.EMPLDD to user ghan
DB20000I SQL command completed successfully.


grant select,insert, update, delete on table DB2INST2.STAFF to user ghan
DB20000I SQL command completed successfully.


grant select,insert, update, delete on table DB2INST2.T1 to user ghan
DB20000I SQL command completed successfully.



[db2inst2@localhost ~]$ db2 connect to ghan user ghan
Enter ghan’s current password:


Database connection information


Database server = DB2/LINUXX8664 9.7.5
SQL authorization identifier = GHAN
Local database alias = GHAN

6. Test authorization results

[db2inst2@ localhost ~]$ db2 "select count(*) from db2inst2.t1"


1
-----------
4


1 record selected.