sql数据库表对象无效_SQL数据库对象的对象权限和特权

创建数据库对象的命名约定

创建数据库对象时，开发人员可以选择一种遵循传统IBM i行为的系统命名方式（* SYS），或者遵循具有SQL命名约定（* SQL）的SQL Standard规则的命名方法。

DB2 for i和其他数据库管理系统（DBMS）之间的主要区别是DB2 for i已集成到操作系统中。 这种集成的方面允许IBM i用户使用其操作系统用户概要文件和关联的访问权限直接访问DB2 for i数据库。 其他数据库未集成在操作系统中，因此必须定义具有单独访问权限的特定数据库用户。

用于使用SQL创建数据库对象的默认命名取决于执行SQL DDL（数据定义语言）命令的环境。

系统命名是所有服务器端SQL环境 （例如STRSQL（开始SQL交互式会话）或RUNSQLSTM（运行SQL语句））以及HLL（高级语言）程序（例如RPG或COBOL）中的嵌入式SQL的默认命名 。

在基于客户端SQL环境 （例如System i Navigator，IBM Rational Developer for Power Systems软件（RDp），中间件（ODBC，JDBC等）或第三方SQL工具）上使用的默认命名值通常是SQL命名。 。

为避免对象权限和访问方法不匹配，您需要确定使用系统命名或SQL命名在您的应用程序环境中效果最佳。 您可能需要在某些环境中更改默认命名，以匹配您在应用程序环境中使用的命名约定。

System i导航器界面

如果要使用“ System i导航器界面”创建数据库对象，则可以预定义要使用的命名，如下所示：

打开连接，右键单击Database图标，然后选择Preferences任务， 如图1所示。System i Navigator –设置Preferences

首选项窗口提供3个选项。 Connection（所有系统）选项允许您预定义要用于将来的连接的命名约定。 此设置还将用作将来的“运行SQL脚本”和“生成SQL”执行的默认命名值，但不会影响任何现有窗口。

图1. System i Navigator –设置首选项

System i Navigator的运行SQL脚本工具

如果要使用“ 运行SQL脚本工具”执行存储在文件中或以交互方式输入SQL脚本，则可以通过单击“连接”下拉菜单并选择“ JDBC设置”任务来控制命名约定。 可以在“格式”选项卡上设置命名约定。

图2. System i Navigator –运行SQL脚本–设置命名约定

RUNSQLSTM –运行SQL语句

如果要通过RUNSQLSTM （运行SQL语句）执行存储在源物理文件成员或IFS（集成文件系统）文件中SQL语句，则可以在RUNSQLSTM命令上使用Naming参数指定命名约定，如下面的例子。 指定SQL脚本将通过使用SQL命名执行。

清单1. RUNSQLSTM设置命名约定

RUNSQLSTM SRCFILE(MYSCHEMA/QSQLSRC) SRCMBR(MYSCRIPT) NAMING(*SQL)

HLL程序中的嵌入式SQL

如果要在诸如RPG或COBOL之类的HLL程序中使用嵌入式SQL来处理表中的数据或创建新的数据库对象，则默认命名设置为“系统命名”。

如果要使用SQL命名，则可以在编译命令（取决于编程语言）中预定义命名约定（CRTSQLRPGI，CRTSQLCBL1或CRTSQLCI），如以下示例所示：

清单2.使用SQL命名创建嵌入式SQL程序

CRTSQLRPGI OBJ(MYPGMLIB/MYSQLPGM) SRCFILE(MYSRCLIB/QRPGLESRC) SRCMBR(MYMBR) OPTION(*SQL)

除了在compile命令中指定命名方法外，还可以通过添加SET OPTION语句将其包含在源代码中（如下面的示例所示）。 SET OPTION语句必须是源代码中的第一个SQL语句，并包括要设置的所有选项。

清单3.用于设置命名约定的SET OPTION语句

/Free Exec SQL Set Option Commit=*NONE, Naming=*SQL DatFmt=*ISO, CloSQLCsr=*ENDACTGRP; //All other source code including embedded SQL statements /End-Free

IBM i Access for Windows ODBC驱动程序

可以使用IBM i Access for Windows-ODBC管理界面或连接关键字为ODBC连接指定命名约定。

下图显示了“服务器”选项卡上“ ODBC管理”界面上受控制的命名约定。

图3. ODBC设置

JDBC访问

对于JDBC访问，可以通过在连接URL中指定JDBC驱动程序连接属性来控制命名约定。

命名属性 connection属性支持sql和system的命名约定值。 SQL命名是默认设置。

使用系统命名时，可以使用库属性预定义库列表，如以下示例所示。

清单4. JDBC设置命名约定

conn = DriverManager.getConnection("jdbc:db2:*local: ... ... naming=system;libraries=MYLIBA,MYLIBB,MYLIBX");

Windows ADO.NET Provider的IBM i Access

使用ADO.NET时，可以在建立连接时设置命名约定和系统命名库列表。 iDB2Connection对象连接到i的DB2。 命名约定作为“连接字符串”属性提供。

以下代码显示了如何使用iDB2Connection对象设置系统命名和库列表：

清单5. ADO.NET设置命名约定

iDB2Connection conn = new iDB2Connection("DataSource=abc; userid=XXX;password=YYY; Naming=System; LibraryList=*USRLIBL,MYLIB");

SQL CLI-呼叫层介面

使用SQL CLI函数时，命名约定是可以通过执行SQLSetConnectAttr函数设置的属性。 要将命名约定设置为“系统命名”，必须为属性参数传递SQL_ATTR_DBC_SYS_NAMING常量，为属性值参数传递SQL_TRUE常量，如下例所示。

清单6.使用SQLCLI设置连接属性

rc = SQLSetConnectAttr(ConnHandle: SQL_ATTR_DBC_SYS_NAMING: SQL_TRUE: 4);

STRSQL –启动SQL交互式会话

如果要更改用于在交互式SQL中运行SQL语句的命名，请执行STRSQL CL命令，按功能键F13 =服务，然后选择选项1（更改会话属性）。

模式–容纳数据库对象的容器

模式是用于存储数据库对象的容器。 在IBM i上，术语“架构”等同于库。

可以使用CRTLIB（创建库）CL命令或CREATE SCHEMA SQL语句来创建模式或库。 尽管CRTLIB命令仅创建一个空容器，但SQL语句会自动添加日记，日记接收器和几个目录视图，以及有关此模式中所有数据库对象的信息。

使用CRTLIB命令创建库时，库的所有者将是创建库的用户配置文件或组配置文件。

用户或组配置文件是否成为所有者取决于用户配置文件的OWNER选项设置。 如果OWNER选项设置为* GRPPRF，则GRPPRF选项中指定的用户配置文件将成为该用户创建的所有对象的所有者，否则，用户配置文件将成为对象所有者。

以下示例显示了CHGUSRPRF（更改用户配置文件）命令，该命令用于将PGMRGRP2用户配置文件将来创建的所有对象的所有者设置为QPGMR组配置文件。

清单7.更改用户配置文件命令设置Owner =组配置文件

CHGUSRPRF USRPRF(PGMRGRP2) GRPPRF(QPGMR) OWNER(*GRPPRF)

本文创建的所有示例数据库对象都将由名为PGMRGRP2的用户概要文件创建。 该用户配置文件与QPGMR组配置文件相关联。 基于此，QPGMR组概要文件将是PGMRGRP2创建的所有数据库对象的所有者。

使用系统命名创建架构

使用“系统命名”使用CREATE SCHEMA语句创建模式时，适用以下规则：

• 模式的所有者是用户配置文件或组配置文件，具体取决于用户配置文件定义中的OWNER选项设置。
• 所有者具有* ALL对象权限，而* PUBLIC对象权限基于默认值为* CHANGE的QCRTAUT（创建默认公共权限）系统值。

使用CRTLIB命令或带有系统命名的CREATE SCHEMA语句创建模式或库将导致相同的所有权和相同的对象权限。

PGMRGRP2用户概要文件使用以下使用系统命名SQL语句创建两个模式（PGMRUSR2和PGMRXXX2）：

清单8.创建模式示例

CREATE SCHEMA PGMRXXX2; CREATE SCHEMA PGMRUSR2;

这两种模式的所有者都是QPGMR组概要文件。 组概要文件具有* ALL对象权限，而* PUBLIC权限根据QCRTAUT系统值设置为* CHANGE。

图4.使用系统命名创建SCHEMA

可以使用EDTOBJAUT（编辑对象权限）命令或“ System i导航器权限”界面显示，设置或删除对象所有者和分配的对象权限。 使用System i Navigator，可以通过右键单击数据库对象并选择Permissions任务来访问此界面。

可以使用CHGOBJOWN（更改对象所有者）CL命令来更改对象所有权。 没有可用SQL更改对象所有者SQL语句或System i Navigator接口。

使用SQL命名创建架构

实际上，使用SQL命名创建模式时，规则会更加复杂：

• 如果存在与该架构同名的用户简要表，则该架构的所有者以及在该架构中创建的所有对象都是该用户简要表。 例如，开发人员为新的基于Web的应用程序创建模式WEBERP。 碰巧有一个名为Weber Peter的员工，其用户个人资料也是WEBERP。 用户配置文件WEBERP成为WEBERP模式的所有者。
• 如果架构名称与用户概要文件名称不匹配，那么架构的所有者就是执行CREATE SCHEMA语句的作业的用户概要文件。 使用SQL命名创建架构时，将忽略用户配置文件定义的OWNER选项设置。

所有者是唯一有权访问架构的用户配置文件。 如果其他用户需要该模式的对象权限，则所有者或具有安全管理权限（* SECADM）或所有对象权限（* ALLOBJ）的用户配置文件可以使用GRTOBJAUT（授予对象权限）CL命令向该模式授予权限。

没有可用SQL语句授予模式的对象权限。

• 对于使用SQL命名创建的数据库对象，* PUBLIC对象权限始终设置为* EXCLUDE。 QCRTAUT系统值将被忽略。

为了比较系统命名和SQL命名之间的差异，同一用户使用SQL命名删除并重新创建了以前使用系统命名创建的架构。

比较所有权和对象权限方面的架构时，我们将发现一些差异：

• 模式PGMRXXX2的所有者是模式的创建者PGMRGRP2。 PGMRGRP2用户配置文件的所有者设置将被忽略。
• 所有者PGMRGRP2获得* ALL对象权限，而* PUBLIC对象权限设置为* EXCLUDE。 与* PUBLIC对象权限取决于QCRTAUT系统值的系统命名相反。 因此，不允许同时也是QPGMR组概要文件成员的开发人员修改架构或在该架构中创建对象。 对于使用组概要文件进行密集工作并且任何开发人员创建的所有对象的所有者都必须成为组概要文件的公司，此行为可能会出现问题。
• 模式PGMRUSR2的所有者是PGMRUSR2，因为存在具有该名称的现有用户配置文件。 以前，使用“系统命名”创建架构时，两个架构的所有者都是QPGMR组配置文件。

• PGMRUSR2模式的所有者PGMRUSR2获得* ALL对象权限，而* PUBLIC权限设置为* EXCLUDE。 即使用户PGMRGRP2能够创建模式PGMRUSR2，该用户对模式也没有任何权限。 PGMRGRP2无法修改架构，也不能在该架构内创建或更改任何对象。

以下屏幕快照显示了使用SQL命名创建的架构的权限（也称为权限）。

图5.使用SQL命名创建SCHEMA

表，视图和索引–维护数据的对象

表是用于将持久用户数据存储在多列和多行中的对象。

视图和索引是与表关联的数据库对象，但不包含任何数据。

使用系统命名创建表，视图和索引

确定所有权和应用对象权限的规则与用于创建架构的规则匹配。 所有者是对象的创建者或组概要文件，并且* PUBLIC对象权限设置为QCRTAUT系统值。

对于下一个示例（ 图6.具有系统命名的CREATE TABLE ），使用以下SQL语句在两个不同的模式PGMRUSR和PGMRXXX中使用系统命名创建表EMPLOYEE：

清单9.创建表EMPLOYEE

Create Table MySchema/Employee (FirstName VarChar(50) Not NULL Default '', Name VarChar(50) Not NULL Default '', Street VarChar(50) Not NULL Default '', ZipCode VarChar(15) Not NULL Default '', City VarChar(50) Not NULL Default '', Country Char(3) Not NULL Default '', Birthday Date Not NULL);

两种模式先前都是由用户配置文件PGMRGRP2使用系统命名使用CREATE SCHEMA语句创建的。 基于PGMRGRP2用户配置文件的OWNER设置，两个模式的所有者均为QPGMR组配置文件。 即使有用户概要文件PGMRUSR，组概要文件也是在模式PGMRUSR中创建的表的所有者。

拥有的用户配置文件QPGMR具有* ALL对象权限，而* PUBLIC权限设置为* CHANGE（基于QCRTAUT系统值）。

因此，与QPGMR组概要文件关联的所有用户都被允许访问，修改甚至删除PGMRXXX和PGMRUSR这两种模式中的EMPLOYEE表。

图6.具有系统命名的CREATE TABLE

使用SQL命名创建表，视图和索引

使用SQL命名时，适用不同的规则：

• 如果存在与创建表，视图或索引所使用的架构同名的用户简要表，则表的所有者就是该用户简要表。

• 如果没有与架构名称相同的用户配置文件，则所有者将是用户配置文件或组配置文件，具体取决于用户配置文件定义中的OWNER选项设置。
• 使用SQL命名创建除架构以外的数据库对象时，将考虑用户概要文件定义中的OWNER选项设置，并且组概要文件将成为数据库对象的所有者。

图7.在与用户概要文件不匹配的模式中使用SQL命名的CREATE TABLE显示用户PGMRGRP2在模式PGMRXXX2中创建的EMPLOYEE表的权限结果。

EMPLOYEE表的所有者是QPGMR组概要文件。 所有者QPGMR的对象权限值为* ALL，而* PUBLIC权限设置为* EXCLUDE。 结果，不仅允许与QPGMR组概要文件关联的所有用户访问EMPLOYEE表，而且还允许其修改或删除该表。

图7.在与用户配置文件不匹配的模式中使用SQL命名的CREATE TABLE

在下一个示例中（ 图8。在与用户简要表匹配的模式中使用SQL命名创建表 ），用户PGMRGRP2尝试在PGMRUSR2模式中创建EMPLOYEE表。

该模式先前由用户PGMRGRP2使用带有SQL命名的CREATE SCHEMA语句创建。 因为有一个名为PGMRUSR2的用户概要文件，所以该用户概要文件成为了模式的所有者，并且在* PUBLIC权限设置为* EXCLUDE的情况下获得了该模式的* ALL对象权限。

CREATE TABLE语句的执行失败，SQL状态值为24501，这是因为即使该用户创建了模式，也未向PGMRUSR2模式授权PGMRGRP2。 （ 图5。使用SQL命名的CREATE SCHEMA演示了用户PGMRGRP2在PGMRUSR2模式上缺少的权限）。

要允许PGMRGRP2通过SQL命名在PGMRUSR2模式中创建表或任何对象，必须通过执行GRTOBJAUT或EDTOBJAUT命令对该模式中的用户配置文件或关联的QPGMR组配置文件进行显式授权。

图8.在与用户概要文件匹配的模式中使用SQL命名的CREATE TABLE

假设已将QPGMR组概要文件明确授权给PGMRUSR2模式，则PGMRGRP2用户将能够在此模式中创建EMPLOYEE表。

因为该表是使用SQL Naming创建的，并且PGMRUSR2是现有的用户概要文件，所以该用户概要文件再次成为EMPLOYEE表的所有者，具有* ALL对象权限，而* PUBLIC对象权限设置为* EXCLUDE， 如图9所示。 Schema = User Profile和Table的权限 。

在这种情况下，PGMRGRP2用户可以创建表，但不允许以任何方式使用它。 必须明确授权PGMRGRP2用户或QPGMR组概要文件才能访问他们先前创建的对象。

图9.模式的权限=用户概要文件和表

潜在问题情况

使用SQL命名为现有应用程序创建数据库对象可能会在IBM i上导致意外问题。

假设现有物料管理应用程序的所有数据库对象都存储在一个名为MAWI的库中。 该库是很早以前使用CRTLIB命令创建的。 MAWI库的所有者是QPGMR组个人资料。 库MAWI的* PUBLIC对象权限是* CHANGE。

在此系统上，所有用户配置文件名称均由姓氏的前2个字符和姓氏的前2个字符组成。 人力资源部门的数据输入文员Willy Maier，因此，他的用户个人资料是MAWI。

如果开发人员使用SQL命名在库MAWI中创建新表或视图，则该新表的所有者将是Willy Maier，因为他的用户配置文件与库的名称匹配。 只有MAWI用户配置文件将具有新表或视图的访问权限。 由于SQL命名强制将* PUBLIC访问权限默认设置为* EXCLUDE，因此排除了开发人员和任何其他用户。

SQL例程

SQL例程是类似于高级语言（HLL）程序的可执行SQL对象。 “ SQL例程”一词用于指代存储过程，触发器或用户定义函数（UDF）。

这些例程用SQL或高级语言（例如RPG或Cobol）编写。 无论是哪种情况，都使用以下SQL语句之一创建存储过程或UDF：

• 建立程序
• 创建功能

SQL例程的所有权和对象权限

确定对象所有权和权限的规则与用于使用系统或SQL命名创建表，视图或索引的规则匹配。

即使可以在使用不同命名模式的运行时环境中调用SQL例程，也将根据创建例程时使用的命名约定来执行嵌入在例程中SQL语句。

例如，从使用SQL命名的接口创建存储过程。 如果默认情况下使用“系统命名”的带有嵌入式SQL的RPG程序中调用了此存储过程，则在使用SQL命名执行存储过程中SQL语句时，RPG程序中的嵌入式SQL语句将使用“系统命名”。

例程对象的所有权和访问权限仅用于调用此例程。 对象所有权和权限值可以应用于也可以不应用于例程本身执行SQL语句。 应用于例程运行SQL请求的授权ID（或用户配置文件）取决于创建例程时使用的命名约定以及例程执行SQL语句是静态的还是动态准备的 。

使用系统命名时，DB2利用调用例程的用户概要文件。 当使用SQL命名在例程中执行静态SQL语句时，缺省情况下，DB2使用例程的所有者对静态SQL语句执行授权处理。

默认情况下，调用例程的用户配置文件始终始终由例程应用于动态SQL语句的执行，而与使用系统命名法还是使用SQL命名无关。

通过指定USRPRF（静态SQL语句的用户配置文件）和DYNUSRPRF（动态SQL语句的用户配置文件）选项，可以在SET OPTION语句中手动控制应用于静态和动态SQL语句的安全性验证以及执行的用户配置文件。

可以将USERPRF选项设置为以下值之一：

• * NAMING：* USER用于系统命名，* OWNER用于SQL命名
• *所有者：静态SQL语句由所有者授权执行
• * USER：静态SQL语句由用户授权执行

可以将选项DYNUSRPRF设置为：

• * USER：系统和SQL命名的默认值。 动态SQL语句由用户授权执行
• *所有者：动态SQL语句由所有者授权执行

如果您正在使用SQL命名，并且希望动态SQL语句由与静态SQL语句相同的用户配置文件执行，则需要将这两个选项USRPRF和DYNUSRPRF设置为* OWNER或* USER。

以下SQL语句显示了将使用SQL命名创建SQL存储过程的简化源代码。 在运行时，* OWNER将基于SET OPTION子句中指定的值来执行过程中嵌入的所有静态和动态SQL语句。

清单10.创建过程

Create Procedure PGMRUSR2.HSINFO (In Parm1 Integer) Dynamic Result Sets 1 Language SQL Set Option DYNUSRPRF = *OWNER,USRPRF = *NAMING Begin /* Routine code goes here */ End ;

触发

触发器是一种特殊SQL例程。 触发器程序链接到表或SQL视图，并由数据库管理器针对指定事件（插入，更新或删除）激活。

触发器程序的所有权是通过与所有其他SQL例程相同的方式确定的，但是对象和执行权限的设置由CREATE TRIGGER语句不同。

* PUBLIC对象权限设置为* EXCLUDE，与使用系统命名还是SQL命名无关。 对于使用系统命名创建的所有其他SQL对象，* PUBLIC对象权限设置为QCRTAUT系统值。

下一个示例显示了使用系统命名创建SQL触发器的源代码。

清单11.创建触发器

CREATE TRIGGER PGMRUSR/TRGEMPLOYEE BEFORE INSERT ON PGMRUSR/EMPLOYEE REFERENCING NEW ROW AS N FOR EACH ROW MODE DB2ROW BEGIN ATOMIC /* Source code goes here */ END;

图10显示了使用系统命名创建的该触发器程序的权限图。 所有者是QPGMR组概要文件，并且所有者具有所有对象权限，而* PUBLIC对象权限设置为* EXCLUDE。

图10.使用系统命名创建的触发器

若要在具有与现有用户概要文件相同名称的架构中使用SQL命名创建触发器程序，必须将创建者显式授予表或视图，或具有特殊权限* ALLOBJ或* SECADM之一。 触发器程序的所有者将是与架构名称相同的用户，或者是创建者的用户简要表或其关联的组简要表，具体取决于创建者的用户简要表定义中的OWNER选项设置。

下图11。使用SQL命名创建的触发器显示了用户PGMRGRP2使用SQL命名创建的触发器程序到模式PGMRUSR2中的许可权图表。 由于PGMRUSR2也是现有的用户配置文件，因此该用户配置文件成为触发程序的所有者。 所有者PGMRUSR2具有* ALL对象权限，而* PUBLIC对象权限设置为* EXCLUDE。

图11.使用SQL命名创建的触发器

始终将通过触发器程序所有者的采用权限来激活触发器，而与创建触发器所使用的命名约定无关。

授予/撤销权限

无论您的数据库对象是使用系统命名还是SQL命名创建的，所有权和对象权限都必须仔细检查。 如果默认行为不符合您的安全性要求，则可以使用GRANT或REVOKE SQL语句来调整设置。

可以通过GRANT语句设置任何用户或组概要文件甚至* PUBLIC的对象权限。 如果必须删除对象授权，则可以使用REVOKE语句。 GRANT和REVOKE语句可以与除模式和触发器之外可以访问或执行的所有数据库对象一起使用。

也可以使用GRTOBJAUT（授予对象权限）和EDTOBJAUT（编辑对象权限）CL命令来修改数据库对象的对象权限。 但是，向权威机构提供CL命令或SQL语句存在一些差异。

设置会话授权

使用SQL命名时，SET SESSION AUTHORIZATION和SET SESSION USER语句会影响对象所有权和权限。

建立连接后，可以使用SET SESSION AUTHORIZATION或SET SESSION语句将用户配置文件切换到其他用户配置文件（授权ID）以采用该用户配置文件的访问权限。 您已经了解了使用SQL命名创建对象时如何将用户配置文件值应用于对象所有权和权限。

结论

现在，您应该对为什么使用System或SQL Naming创建的DB2对象具有不同的所有权和访问权限分配有了很好的了解。

由于这些不同的行为，您应该为使用SQL创建的所有数据库对象（或至少位于单个模式中的所有数据库对象）确定一个命名约定方法。

• 如果您打算设计一个能够在不同数据库系统上运行的应用程序，则SQL命名是实现最大可移植性的正确方法。
• 如果仅使用DB2 for i，并且必须使用基于DDS的对象和SQL数据库对象来维护较旧的应用程序，并使用IBM i特定的对象授权机构（例如组概要文件），那么System Naming是更好的解决方案。

现在，可以通过系统或SQL命名在规划，设计，创建和维护数据库对象中获得乐趣。

翻译自: https://www.ibm.com/developerworks/ibmi/library/i-sqlnaming/index.html