Privileges in DB2 v 8.1.9 linux

[Bob Stearns]

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?

[Serge Rielau]

Bob Stearns wrote:

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
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

[Bob Stearns]

Serge Rielau wrote:

Bob Stearns wrote:

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

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]

Bob Stearns wrote:

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.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Bob Stearns]

Knut Stolze wrote:

Bob Stearns wrote:

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.

Can I revoke/exclude someone from the PUBLIC group?

[Bob Stearns]

Knut Stolze wrote:

Bob Stearns wrote:

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.

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]

Bob Stearns wrote:

Can I revoke/exclude someone from the PUBLIC group?

No because this wouldn't be a public group any longer.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Knut Stolze]

Bob Stearns wrote:

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.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Bob Stearns]

Knut Stolze wrote:

Bob Stearns wrote:

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]

Bob Stearns wrote:

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.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Bob Stearns]

Knut Stolze wrote:

Bob Stearns wrote:

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.

I didn't write the applications. I want to use things like m$excel,
m$access, sas, etc.

[Bernd Hohmann]

Bob Stearns wrote:

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]

Bernd Hohmann wrote:

Bob Stearns wrote:

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]

Bob Stearns wrote:

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]

Bob Stearns wrote:

Knut Stolze wrote:

Bob Stearns wrote:

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.

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.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany