创建数据库RESTRICTIVE参数导致的问题记录

2013.1.18因为创建数据库时增加了RESTRICTIVE后导致用户创建并分配权限后使用时报了大量的系统权限错误。

分析：
       当创建数据库时使用RESTRICTIVE时会导致系统不对PUBLIC用户分配任何默认权限。这些权限包括所有的系统表、ROUTIN等。因为数据库中的多个操作都会对系统表等进行访问，所以需要根据特定需求对用户进行详细的权限再分配。

对于PUBLIC默认获取的权限可以参加信息中心（Default privileges granted on creating a database）。下面给出列表：

Default privileges granted on creating a database

When you create a database, default database level authorities and default object level privileges are granted to you within that database.
The authorities and privileges that you are granted are listed according to the system catalog views where they are recorded:

1. SYSCAT.DBAUTH
• The database creator is granted the following authorities:
• ACCESSCTRL
• DATAACCESS
• DBADM
• SECADM
• In a non-restrictive database, the special group PUBLIC is granted the following authorities:
• CREATETAB
• BINDADD
• CONNECT
• IMPLICIT_SCHEMA
2. SYSCAT.TABAUTH
3. In a non-restrictive database, the special group PUBLIC is granted the following privileges:
• SELECT on all SYSCAT and SYSIBM tables
• SELECT and UPDATE on all SYSSTAT tables
• SELECT on the following views in schema SYSIBMADM:
• ALL_*
• USER_*
• ROLE_*
• SESSION_*
• DICTIONARY
• TAB
4. SYSCAT.ROUTINEAUTH
5. In a non-restrictive database, the special group PUBLIC is granted the following privileges:
• EXECUTE with GRANT on all procedures in schema SQLJ
• EXECUTE with GRANT on all functions and procedures in schema SYSFUN
• EXECUTE with GRANT on all functions and procedures in schema SYSPROC (except audit routines)
• EXECUTE on all table functions in schema SYSIBM
• EXECUTE on all other procedures in schema SYSIBM
• EXECUTE on the following modules in schema SYSIBMADM:
• DBMS_JOB
• DBMS_LOB
• DBMS_OUTPUT
• DBMS_SQL
• DBMS_UTILITY
6. SYSCAT.PACKAGEAUTH
• The database creator is granted the following privileges:
• CONTROL on all packages created in the NULLID schema
• BIND with GRANT on all packages created in the NULLID schema
• EXECUTE with GRANT on all packages created in the NULLID schema
• In a non-restrictive database, the special group PUBLIC is granted the following privileges:
• BIND on all packages created in the NULLID schema
• EXECUTE on all packages created in the NULLID schema
7. SYSCAT.SCHEMAAUTH
8. In a non-restrictive database, the special group PUBLIC is granted the following privileges:
• CREATEIN on schema SQLJ
• CREATEIN on schema NULLID
9. SYSCAT.TBSPACEAUTH
10. In a non-restrictive database, the special group PUBLIC is granted the USE privilege on table space USERSPACE1.
11. SYSCAT.WORKLOADAUTH
12. In a non-restrictive database, the special group PUBLIC is granted the USAGE privilege on SYSDEFAULTUSERWORKLOAD.

A non-restrictive database is a database created without the RESTRICTIVE option on the CREATE DATABASE command.