Grant Option

[Naomi Walker]

Hey guys! I'm a little stymied here. I need to be able to grant
SELECT privileges only on all the tables in a database to certain
users.

I've written a little (isqlperl) script to do this, but cannot cleanly
GRANT SELECT on all tables (even as Informix), since the tables were
created by different people. I get the error message:

302: No Grant option or illegal option on multi-table view
(its definitely not a multi-table view)

It appears I would have to become to creator of each table, and user the
"WITH GRANT OPTION" to accomplish such a feat. I must be missing
something.

As informix, or a user with DBA, how may I GRANT SELECT table by
table without becoming the explicit owner of each table? Shouldnt
the DBA be able to do anything?

While we are on this subject, can someone explain dbaccess and its
features to me? Maybe that will be a better solution.

Naomi
--
Naomi Walker (aka N7FSA) na...@anasazi.com
Phoenix, Arizona

The best things in life aren't things.

[Dave Kosenko]

Naomi Walker writes:
|> It appears I would have to become to creator of each table, and user the
|> "WITH GRANT OPTION" to accomplish such a feat. I must be missing
|> something.
|>
|> As informix, or a user with DBA, how may I GRANT SELECT table by
|> table without becoming the explicit owner of each table? Shouldnt
|> the DBA be able to do anything?

Try this:

GRANT SELECT ON tabname TO userlist AS owner;

though it may not work. The rules on permission "chaining" can be a bit
complex (I don't use privs much myself). You may wind up having to be
the owner, or have the owner grant some common user appropriate privs WITH
GRANT OPTION.

|> While we are on this subject, can someone explain dbaccess and its
|> features to me? Maybe that will be a better solution.

In a nutshell, dbaccess is isql with Perform, Ace, and User Menu removed.

Dave
Disclaimer: The opinions expressed in this message are not those of Informix
Software, its partners or lackeys. Anyone who says otherwise is itching for
a fight.
****************************************************************************
"I look back with some satisfaction on what an idiot I was when I was 25,
but when I do that, I'm assuming I'm no longer an idiot." - Andy Rooney

[Lester Knutsen]

> Hey guys! I'm a little stymied here. I need to be able to grant
> SELECT privileges only on all the tables in a database to certain
> users.
> I've written a little (isqlperl) script to do this, but cannot cleanly
> GRANT SELECT on all tables (even as Informix), since the tables were
> created by different people. I get the error message:
> 302: No Grant option or illegal option on multi-table view
> (its definitely not a multi-table view)

> It appears I would have to become to creator of each table, and user the
> "WITH GRANT OPTION" to accomplish such a feat. I must be missing
> something.
> As informix, or a user with DBA, how may I GRANT SELECT table by
> table without becoming the explicit owner of each table? Shouldnt
> the DBA be able to do anything?

Naomi

I hate to disappoint you but the ANSI standard is that only the owner of
a table can grant privileges OR the owner must grant the privilege to
grant privileges to another user. I run into this problem all the time.
Each table owner must to something like the following:

GRANT ALL ON tablename to dba_username WITH GRANT OPTION;

The dba_username can then grant privileges to everyone else.
Howvere if the orginal owner revokes the privilege form the
dba_username, ALL users that have granted privileges by dba_username
to will also be revoked.

This is one of the best reasons to have a dba login id ( not a user
who may come or go ) create your production databases and all tables.

>
> While we are on this subject, can someone explain dbaccess and its
> features to me? Maybe that will be a better solution.
>

dbaccess comes with the Informix engines in 5.0++ releases and is
the same as isql minus forms and reports.

Regards - Lester

#############################################################################
# Lester Knutsen les...@access.digex.net #
# Advanced DataTools Corporation Voice: 703-256-0267 #
# Control you Informix database security with DB Privileges #
#############################################################################