db2 Authentication
attach - get access to the instanceconnect - 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.).
页:
[1]