public - beware of this pseudo group

It was almost 3 years back when my career started , I had a chance of creating users and granting them privileges where i created a user and was shocked the moment i created a user in OS he could connect to the database even before granting privileges to him.

This eventually led me to the documentation where i came to know the pseudo group rather PUBLIC group is the culprit.Culprit ? No No ... Not actually , DB2 may have launched this to make things a bit easier for DBA , granting some predefined set of privileges to the user created .

But if you don't believe in accidents and don't want to leave any loopholes , then you may not be very comfortable with this group as it leaves some control over to others

Now lets get into work , db2 has provided us with various tables under SYSCAT catalog schema for administrating authorizations and privileges , here is one way you can look them

db2 "list tables for schema syscat"|grep -i auth

COLAUTH                         SYSCAT          V     2008-06-19-11.57.01.421920
DBAUTH                          SYSCAT          V     2008-06-19-11.57.01.759878
INDEXAUTH                       SYSCAT          V     2008-06-19-11.57.02.799219
LIBRARYAUTH                     SYSCAT          V     2008-06-19-11.57.03.044683
PACKAGEAUTH                     SYSCAT          V     2008-06-19-11.57.03.208011
PASSTHRUAUTH                    SYSCAT          V     2008-06-19-11.57.03.279642
ROUTINEAUTH                     SYSCAT          V     2008-06-19-11.57.03.426382
SCHEMAAUTH                      SYSCAT          V     2008-06-19-11.57.03.689145
SEQUENCEAUTH                    SYSCAT          V     2008-06-19-11.57.04.030049
SURROGATEAUTHIDS                SYSCAT          V     2008-06-19-11.57.04.108399
TABAUTH                         SYSCAT          V     2008-06-19-11.57.04.110576
TBSPACEAUTH                     SYSCAT          V     2008-06-19-11.57.04.432799
XSROBJECTAUTH                   SYSCAT          V     2008-06-19-11.57.04.800011

If you pick the DBAUTH table and query it as below you will come to know the set of privileges that db2 have pre-granted the PUBLIC group

db2 "select * from syscat.dbauth where grantee='PUBLIC'"

GRANTOR   GRANTEE  GRANTEETYPE BINDADDAUTH CONNECTAUTH CREATETABAUTH DBADMAUTH EXTERNALROUTINEAUTH IMPLSCHEMAAUTH LOADAUTH NOFENCEAUTH QUIESCECONNECTAUTH LIBRARYADMAUTH SECURITYADMAUTH
--------- -------- ----------- ----------- ----------- ------------- --------- ------------------- -------------- -------- ----------- ------------------ -------------- ---------------
SYSIBM    PUBLIC   G           Y           Y           Y             N         N                   Y              N        N           N                  N              N

  1 record(s) selected.

So every user who successfully surpassed the OS authentication can perform all the activities flagged Y in the above result set i.e., user can BIND,CREATE TABLES,CONNECT TO DATABASE , CREATE OBJECTS WITHOUT MENTIONING SCHEMA ....

db2 have been putting in more and more set of privileges every version and if you run the above query in a db2 v10.5 you get the result show below

db2 "select * from syscat.dbauth where grantee='PUBLIC'"

GRANTOR  GRANTORTYPE GRANTEE GRANTEETYPE BINDADDAUTH CONNECTAUTH CREATETABAUTH DBADMAUTH EXTERNALROUTINEAUTH LOADAUTH NOFENCEAUTH QUIESCECONNECTAUTH LIBRARYADMAUTH SECURITYADMAUTH SQLADMAUTH WLMADMAUTH EXPLAINAUTH DATAACCESSAUTH ACCESSCTRLAUTH CREATESECUREAUTH
-------  ----------- ------- ----------- ----------- ----------- ------------- --------- ------------------- -------- ----------- ------------------ -------------- --------------- ---------- ---------- ----------- -------------- -------------- ----------------
SYSIBM       S       PUBLIC      G           Y           Y           Y             N         N                  N        N           N                  N              N               N          N          N           N              N              N

  1 record(s) selected.

REVOKE the privileges from PUBLIC group as shown below

REVOKE BINDADD ON DATABASE FROM GROUP PUBLIC ;
REVOKE CONNECT ON DATABASE FROM GROUP PUBLIC ;
REVOKE CREATETAB ON DATABASE FROM GROUP PUBLIC ;
REVOKE DBADM ON DATABASE FROM GROUP PUBLIC ;
REVOKE EXTERNALROUTINE ON DATABASE FROM GROUP PUBLIC ;
REVOKE LOAD ON DATABASE FROM GROUP PUBLIC ;
REVOKE NOFENCE ON DATABASE FROM GROUP PUBLIC ;
REVOKE QUIESCECONNECT ON DATABASE FROM GROUP PUBLIC ;
REVOKE LIBRARYADM ON DATABASE FROM GROUP PUBLIC ;
REVOKE SECURITYADM ON DATABASE FROM GROUP PUBLIC ;
REVOKE SQLADM ON DATABASE FROM GROUP PUBLIC ;
REVOKE WLMADM ON DATABASE FROM GROUP PUBLIC ;
REVOKE EXPLAIN ON DATABASE FROM GROUP PUBLIC ;
REVOKE DATAACCESS ON DATABASE FROM GROUP PUBLIC ;
REVOKE ACCESSCTRL ON DATABASE FROM GROUP PUBLIC ;
REVOKE CREATESECURE ON DATABASE FROM GROUP PUBLIC ;

Always make it a point immediately after installing database you REVOKE the privileges of the pseudo group PUBLIC . Make your database more tight and secure.

There's a wonderful developerWorks article on db2 security naming Understand how authorities and privileges are implemented in DB2 UDB