|
liminating the need to preface the object name with the owner name
Many developers create all their objects with dba as the owner.This is reasonable since the dba user is already on the database and has rights to everything in the system.The problem comes when adding users to the system and granting them rights to the database objects.Most developers start out like this:
CREATE TABLE mytable (mycolumn CHAR(10) PRIMARY KEY)
GRANT CONNECT TO mygroup
GRANT GROUP TO mygroup
GRANT CONNECT TO myuser IDENTIFIED BY mypassword
GRANT MEMBERSHIP IN GROUP mygroup TO myuser
GRANT SELECT,INSERT,UPDATE,DELETE ON mytable TO mygroup
This works just fine if you construct your queries like this:
SELECT * FROM "dba"."mytable"
However, if the query is constructed like this and executed by "myuser" then it will fail with a "Table 'mytable' not found" error:
SELECT * FROM "mytable"
The following query will work, however some problems have been seen with earlier versions of the database engine where it didn't like to see the dba user without double quotes around it.
SELECT * FROM dba."mytable"
"dba" is also a keyword and that can cause problems.
To completely eliminate the need to preface the object name with the name of the owner you should upgrade the "dba" user to a group and then grant rights off of that group. Here is a sample of how to do it.
GRANT GROUP TO "dba"
CREATE TABLE mytable (mycolumn CHAR(10) PRIMARY KEY)
GRANT CONNECT TO mygroup
GRANT GROUP TO mygroup
GRANT MEMBERSHIP IN GROUP "dba" TO mygroup
GRANT CONNECT TO myuser IDENTIFIED BY mypassword
GRANT MEMBERSHIP IN GROUP mygroup TO myuser
GRANT SELECT,INSERT,UPDATE,DELETE ON mytable TO mygroup
Once this is completed the following query will work just fine when logged in as "myuser":
SELECT * from "mytable"
An Alternative
Some developers prefer to not have the "dba" user as the owner of all the objects. Much of this preference stems from the use of dba as a keyword. To resolve this, create a new user, upgrade the user to a group and then create all objects with that user as the owner.
One Reason To Avoid Prefacing
During development it is often necessary for developers to create their own tables to test with. If your application prefaces all the database objects with the owner name then the developer cannot utilize the temporary table. For example:
CONNECT USER dba IDENTIFIED BY sql
CREATE TABLE "dba". "mytable" (mycolumn CHAR(10) PRIMARY KEY)
CONNECT USER developer1 IDENTIFIED BY password
CREATE TABLE "mytable" (mycolumn CHAR(10) PRIMARY KEY)
Now, when the user "developer1" queries "mytable" they will be
working against their own table instead of the table that is owned by "dba":
SELECT * from "mytable"
In PowerBuilder if you are logged in to the database as the owner of the objects then the SQL painters will construct SQL without the owner name. If you are logged in as a user other than the owner (but with rights to the object) then your SQL will be constructed with the owner name prefacing the objects.
[聊天工具]用Windows98第二版直接共享Internet [系统软件]Internet Explorer 编程简述（繁体版）
[系统软件]单击链接后无法打开新的 Internet Explorer 窗口或… [系统软件]Explanation of UFT-8 and Unicode
[系统软件]Using dllimport and dllexport in C++ Classes [系统软件]OLE with the internet explorer
[常用软件]Internet Explorer 6 Public Preview 最新出击！！ [常用软件]Internet Explorer中的潘多拉魔盒 作者：江海
[常用软件]神奇 我家的照片会唱歌 照片会唱歌 [常用软件][网络]Becky Internet Mail 软件评测
| 
注：本站部分文章源于互联网，版权归原作者所有！如有侵权，请原作者与本站联系，本站将立即删除！ 本站文章除特别注明外均可转载，但需注明出处！ [MinTao学以致用网] 
