> I just created a new user and granted connect and select on a single
> view, only. When I connect to my database, the new user has at least
> select privileges on the whole database. What am I doing wrong or
> misunderstanding? How do I discover all the privileges granted on my
> database? How do I revoke all privileges and then restore just the ones
> I want? Does public get any privileges by default?
Bob,
How did you test your hypothesis. I suspect you tried to select from a
SYSCAT view or a SYSIBM table.
By default PUBLIC gets granted SELECT on the catalog objects (SYSCAT,
SYSIBM, SYSFUN and SYSPROC).
In DB2 9 there is a new RESTRICT option that creates the database very
tight to begin with.
On DB2 V8 a simple procedure revoking SEELCT from PUBLIC on these
objects should do just fine.
Something like:
CREATE PROCEDURE revokepublic(IN objecttype VARCHAR(20))
BEGIN
DECLARE revtxt VARCHAR(1000);
DECLARE curtxt VARCHAR(1000);
DECLARE SQLCODE INTEGER;
DECLARE SQLSTATE CHAR(5);
DECLARE objname VARCHAR(128);
DECLARE objschema VARCHAR(128);
DECLARE stmt STATEMENT;
DELCARE cur CURSOR FOR stmt;
SET curtxt = CASE UCASE(objecttype) WHEN 'TABLE'
THEN 'SELECT TABSCHEMA, TABNAME FROM SYSCAT.TABLES WHERE
TABSCHEMA LIKE ''SYS%'''
...
END;
PREPARE stmt FROM curtxt;
OPEN cur;
LOOP
FETCH TABSCHEMA, TABNAME INTO OBJSCHEMA, OBJNAME;
IF SQLCODE = 100 THEN LEAVE; END IF;
SET revtxt = 'REVOKE SELECT ON ' || objtype || ' "' || objschema ||
'"."' || objname || '" FROM PUBLIC';
EXECUTE IMMEDIATE revtxt;
END LOOP;
END
Well, something like that....
Cheers
Serge

Signature
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
IOD Conference
http://www.ibm.com/software/data/ondemandbusiness/conf2006/
Bob Stearns - 14 Jun 2006 03:17 GMT
>> I just created a new user and granted connect and select on a single
>> view, only. When I connect to my database, the new user has at least
[quoted text clipped - 44 lines]
> Cheers
> Serge
Actually I tried a select on one of my own tables, since I granted
SELECT to a VIEW based on one of my tables.
However I figured out what I did wrong. This the new user I was having
so much trouble with last week and one of the straws I grasped was to
make this new user as like some of my working users as possible,
including groups. At least one of those groups must have admin
authorization. As soon as I removed the unnecessary groups, the userid
behaved as I wish.
Thanks for the procedure, I will keep it against future need.
Is everyone with connect authorization in the group public? Is there a
way to make a schema invisible to public?
Knut Stolze - 14 Jun 2006 07:49 GMT
> Is everyone with connect authorization in the group public?
Yes, everyone is in the PUBLIC group.
> Is there a
> way to make a schema invisible to public?
Short of revoking the privileges on all objects in this schema from PUBLIC:
no.

Signature
Knut Stolze
DB2 Information Integration Development
IBM Germany
Bob Stearns - 14 Jun 2006 19:59 GMT
>>Is everyone with connect authorization in the group public?
>
[quoted text clipped - 5 lines]
> Short of revoking the privileges on all objects in this schema from PUBLIC:
> no.
Can I revoke/exclude someone from the PUBLIC group?
Knut Stolze - 15 Jun 2006 11:43 GMT
> Can I revoke/exclude someone from the PUBLIC group?
No because this wouldn't be a public group any longer.

Signature
Knut Stolze
DB2 Information Integration Development
IBM Germany
Bob Stearns - 14 Jun 2006 20:18 GMT
>>Is everyone with connect authorization in the group public?
>
[quoted text clipped - 5 lines]
> Short of revoking the privileges on all objects in this schema from PUBLIC:
> no.
PUBLIC has no privileges on any object (according to the error message)
that I don't wish my user to see. When he lists tables/views from my
schema, I want him to see only those he can use.
Knut Stolze - 15 Jun 2006 11:44 GMT
> PUBLIC has no privileges on any object (according to the error message)
> that I don't wish my user to see. When he lists tables/views from my
> schema, I want him to see only those he can use.
Then you could do a join with SYSCAT.TABAUTH and filter-out all tables/views
on which the user has no direct or indirect privileges.

Signature
Knut Stolze
DB2 Information Integration Development
IBM Germany
Bob Stearns - 15 Jun 2006 16:24 GMT
>>PUBLIC has no privileges on any object (according to the error message)
>>that I don't wish my user to see. When he lists tables/views from my
>>schema, I want him to see only those he can use.
>
> Then you could do a join with SYSCAT.TABAUTH and filter-out all tables/views
> on which the user has no direct or indirect privileges.
I am not displaying the list, the odbc application is. The users
involved will have access to about a dozen views, out of over 150 tables
and views in the schema (even more when the table display routine shows
all tables/views from all schemas, intermixed). They don't need to see
the large number of "irrelevant" tables (I believe it was Bismarck who
said "Laws are like sausages. It's better not to see them being made."
It applies here to.)
Knut Stolze - 15 Jun 2006 16:48 GMT
> I am not displaying the list, the odbc application is. The users
> involved will have access to about a dozen views, out of over 150 tables
> and views in the schema (even more when the table display routine shows
> all tables/views from all schemas, intermixed). They don't need to see
> the large number of "irrelevant" tables
Well, in a way that sounds to me as if your application is not doing what it
should, i.e. request only the "relevant" tables in the first place.

Signature
Knut Stolze
DB2 Information Integration Development
IBM Germany
Bob Stearns - 15 Jun 2006 23:02 GMT
>>I am not displaying the list, the odbc application is. The users
>>involved will have access to about a dozen views, out of over 150 tables
[quoted text clipped - 4 lines]
> Well, in a way that sounds to me as if your application is not doing what it
> should, i.e. request only the "relevant" tables in the first place.
I didn't write the applications. I want to use things like m$excel,
m$access, sas, etc.
Bernd Hohmann - 15 Jun 2006 23:25 GMT
> I didn't write the applications. I want to use things like m$excel,
> m$access, sas, etc.
Why you drive Volkswagen if you can afford a Porsche?
Bernd
Bob Stearns - 15 Jun 2006 23:36 GMT
>> I didn't write the applications. I want to use things like m$excel,
>> m$access, sas, etc.
>
> Why you drive Volkswagen if you can afford a Porsche?
>
> Bernd
I don't. That is what my end users (twice separated from me) know and want.
Bernd Hohmann - 15 Jun 2006 23:42 GMT
>>> I didn't write the applications. I want to use things like m$excel,
>>> m$access, sas, etc.
>>>
>> Why you drive Volkswagen if you can afford a Porsche?
>>
> I don't. That is what my end users (twice separated from me) know and want.
Uhh.... You're in bad company.
Unfortunately I cannot help you in this case :-(
Bernd
Knut Stolze - 16 Jun 2006 08:48 GMT
>>>I am not displaying the list, the odbc application is. The users
>>>involved will have access to about a dozen views, out of over 150 tables
[quoted text clipped - 7 lines]
> I didn't write the applications. I want to use things like m$excel,
> m$access, sas, etc.
So what _is_ the application doing exactly? Depending on how it determines
the relevant (and irrelevant) tables, we may be able to slip something in
that does the filtering.

Signature
Knut Stolze
DB2 Information Integration Development
IBM Germany