Table permission problems when attempting to drop a table

Question

Informix-SQL(SE) 4.10.DD6 (MS-DOS 6.22):

I have a table created as: "pcuser".tablename. I attempted to drop this table with:

DROP TABLE tablename; 

and received the following error message:

545: No write permission for table pcuser.tablename.

Since my app is single-user and I'm not concerned with restricting privileges for any table, I installed ISQL 4.10 without password protection (i.e. no user name or password is required to startup the SE engine). So the default and only user name/table owner is always "pcuser". With ISQL 2.10, I didn't have to specify "table-owner".tablename when dropping, creating, reading or writing to a table. However I did grant all on tablename to public and grant dba to public. I also executed the same grant statements in 4.10.

Do I have to specify the table owner when dropping a table like:

DROP TABLE "pcuser".tablename;

Sorry, I don't have the documentation for ISQL 4.10.

The following are perform.out screen outputs of SYSTABAUTH and SYSTABLES row for tablename:

SYSTABAUTH:

grantor            [pcuser  ]
grantee            [public  ]
tabid              [102        ]
tabauth            [su-idxa]


SYSTABLES:

tabname            [tablename           ]
owner              [pcuser  ]
dirpath            [C:\DBFILES.DBS\TABLENAME        ]
                   [                                ]
tabid              [102        ]
rowsize            [256   ]
ncols              [48   ]
nindexes           [5     ]
nrows              [1082594    ]
created            [07-13-2010]
version            [9          ]
tabtype            [T]
audpath            [                                ]
                   [                                ]

Below are two sql procs in my app. The first one properly execs, but the second one fails with the err 545 on the drop table statement:

{CREATEDB.SQL - First SQL Proc}

DROP DATABASE dbfiles;

CREATE DATABASE dbfiles;

CREATE TABLE tablename
    (
     col1 char(18),
     col2 char(60),
     [...]
    ) in "C:\DBFILES.DBS\TABLENAME";

LOAD FROM "tablename.unl" INSERT INTO tablename;

CREATE UNIQUE INDEX tablename_idx1 ON tablename (col1);

GRANT ALL ON tablename TO PUBLIC;

GRANT DBA TO PUBLIC;

UPDATE STATISTICS;

---

{DROPTAB.SQL - Second SQL Proc}

DROP TABLE tablename;
           ^
           ERROR 545: No Write Permission....

Answer 1

Running 'finderr -545', I get the information:

-545 No write permission for table table-name.

Check the accompanying ISAM error code for more information. With this database server, a database is a directory with the name dbname.dbs, while tables and indexes are files within that directory. You need to have read and write access to all these files in order to exercise normal database functions.

You will need to look at the directory permissions on the database directory (dbname.dbs). If you are logged in as 'pcuser', you need to own the directory and have permission to remove files from it (and create files, etc).

I'm not sure how ISQL and SE from DOS (where there really weren't users) adapt to modern versions of Windows (where there are users).

If you were running on any other platform, I'd be counselling you against 'GRANT DBA TO PUBLIC'; that is a recipe for disaster. I'm still not convinced it is a good idea, but I don't have any specifics that I can point to that definitively argue against it - but it feels wrong; you should be caring who accesses the database and who has the ability to rebuild the database.

Answering the question about 'Do I have to specify the user name in the DROP TABLE statement?', the answer is 'No'. The error message is from a stage after the query has been parsed and validated; it understands the table name. It is just that the user running the query appears to have insufficient privilege to do the requested operation.