db2 Authentication

jackyrar

attach - get access to the instance
connect - get access to the database

db2 attach to db2inst1 user myuser using mypassword
db2 connect to mydatabase user myuser using mypassword
db2 connect to mydatabase user myuser using mypassword new newpass confirm newpass

changing auth.type of the database server:
db2 update dbm cfg using AUTHENTICATION <authentication_type>

Auth.types:
  SERVER, SERVER_ENCRYPT,
  CLIENT,
  DCE, DCE_SERVER_ENCRYPT (DCE = Distributing Computer Environment),
  KERBEROS, KRB_SERVER_ENCRYPT
  DCS, DCS_ENCRYPT (DCS = Database Connection Services)

TRUST_ALLCLNTS parameter - YES, NO, DRDAONLY
YES:

On the server:
db2 update dbm cfg using authentication client
db2 update dbm cfg using trust_allclnts yes
db2 update dbm cfg using trust_clntauth server

On the client:
db2 catalog database mydb at node nd1 authentication client

NO:

On the server:
db2 update dbm cfg using authentication client
db2 update dbm cfg using trust_allclnts no
db2 update dbm cfg using trust_clntauth server

On the client:
db2 catalog database mydb at node nd1 authentication client

TRUST_CLNTAUTH parameter - CLIENT, SERVER

CLIENT:

On the server:
db2 update dbm cfg using authentication client
db2 update dbm cfg using trust_allclnts no
db2 update dbm cfg using trust_clntauth client

On the client:
db2 catalog database mydb at node nd1 authentication client

SERVER:

On the server:
db2 update dbm cfg using authentication client
db2 update dbm cfg using trust_allclnts yes
db2 update dbm cfg using trust_clntauth server

On the client:
db2 catalog database mydb at node nd1 authentication client

Gaining authorization = gaining group membership in one of 5 groups:
SYSADM - instance king,
SYSCTRL - instance control - almost as powerful as SYSADM, but needs explicitly granted privileges,
SYSMAINT - instance maintenance, can not access user data
DBADM - database-level authority (db - not instance).
LOAD - database-level authority to insert large amounts of data.

SYS* authorities - can be assigned only to groups, are instance-level
DBADM, LOAD authorities - can be assigned to group and users, are database-level

Opration SYSADM SYSCTRL SYSMAINT DBADM LOAD
update dbm cfg Yes -- -- -- --
control which group have SYSADM, SYSCTRL, SYSMAINT authority Yes -- -- -- --
Control which users or groups have DBADM authority Yes -- -- -- --
Create/Drop database Yes Yes -- -- --
Create/Drop tablespace Yes Yes -- -- --
Backup database/Restore/Rollforard Yes Yes Yes -- --
db2start / db2stop Yes Yes Yes -- --
update database cfg file Yes Yes Yes --  --
grant/revoke db privileges Yes -- -- Yes --
grant control privilegeon an object Yes -- -- Yes --
create table Yes -- -- Yes --
load into tables Yes -- -- Yes Yes
runstats Yes Yes Yes Yes Yes

db2 update dbm cfg using SYSCTRL_GROUP db2grp
db2update dbm cfg SYSMAINT_GROUP db2mnt

db2 grant dbadm on database to user <username>
db2 grant dbadm on database to group <groupname>

db2 revoke dbadm on database from user <username>
db2 revoke dbadm on database from group <groupname>

db2 grant load on database to user <username>
db2 grant load on database to group <groupname>

db2 revoke load on database from user <username>
db2 revoke load on database from group <groupname>

Privileges - are stored in the database system catalog views:
SYSCAT.DBAUTH - databases authorities (db2 get authorization - will report the database authorities of the current user)
   (DBADM, CREATETAB, BINDADD, CONNECT, CREATE_NOT_FENCED, IMPLICIT_SCHEMA, LOAD)
SYSCAT.TABAUTH - table and view priveleges
   (CONTROL, ALTER, DELETE, INDEX, INSERT, SELECT, REFERENCE, UPDATE)
SYSCAT.INDEXAUTH - index privileges
   (CONTROL)
SYSCAT.SCHMAAUTH - schema privileges
   (ALTERIN, CREATEIN, DROPIN)
SYSCAT.PACKAGEAUTH - package privileges
   (CONTROL, BIND, EXECUTE)

Here is how to grant/revoke those privileges:
db2 grant select on mytable to public

db2 grant <db_privilege> on database to user <username>
db2 grant <db_privilege> on database to group <groupname>

db2 revoke <db_privilege> on database from user <username>
db2 revoke <db_privilege> on database from group <groupname>

More examples:
db2 grant <privilege> on <tbl/vw name> to user <username>
db2 grant control on index <index_name> to user <user_name>
db2 grant <privilege> on schema <schema_name> to user <user_name>
db2 grant <privilege> on package <package_name> to user <user_name>
2 revoke all privileges on table mytable from myuser

Privileges to an object are also granted implicitly when somebody creates an object. (database, table, view, etc.).