By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,199 Members | 1,474 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,199 IT Pros & Developers. It's quick & easy.

Privileges in DB2 v 8.1.9 linux

P: n/a
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?
Jun 13 '06 #1
Share this Question
Share on Google+
14 Replies


P: n/a
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/
Jun 14 '06 #2

P: n/a
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?
Jun 14 '06 #3

P: n/a
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
Jun 14 '06 #4

P: n/a
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?
Jun 14 '06 #5

P: n/a
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.
Jun 14 '06 #6

P: n/a
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
Jun 15 '06 #7

P: n/a
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
Jun 15 '06 #8

P: n/a
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.)
Jun 15 '06 #9

P: n/a
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
Jun 15 '06 #10

P: n/a
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.
Jun 15 '06 #11

P: n/a
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
Jun 15 '06 #12

P: n/a
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.
Jun 15 '06 #13

P: n/a
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
Jun 15 '06 #14

P: n/a
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
Jun 16 '06 #15

This discussion thread is closed

Replies have been disabled for this discussion.