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 a
