Batch DB2 User table authorization

Batch DB2 User table authorization

1. Create a 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 the current ghan password:


Database connection information


Database Server = DB2/LINUXX8664 9.7.5
SQL authorization id = GHAN
Local Database alias = GHAN


Ii. Access the db2int2. t2 table and report an error

[Db2inst2 @ localhost ~] $ Db2 "select count (1) from db2inst2. t2"
SQL0551N "GHAN" does not have operations on the object "DB2INST2. T2" SELECT"
Required permissions or privileges. 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 scripts
[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 Table authorization
[Db2inst2 @ localhost ~] $ Vi commands. SQL

***

5. Execute authorization statements
[Db2inst2 @ localhost ~] $ Db2-tsvf commands. SQL
Grant select, insert, update, delete on table DB2INST2. EMPL to user ghan
The DB20000I SQL command is successfully completed.


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


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


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



[Db2inst2 @ localhost ~] $ Db2 connect to ghan user ghan
Enter the current ghan password:


Database connection information


Database Server = DB2/LINUXX8664 9.7.5
SQL authorization id = GHAN
Local Database alias = GHAN

Vi. Test authorization results

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


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


1 record selected.