Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
Database Servers
DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybase
Desktop Databases
FileMakerFoxProMS AccessParadox
General
General DB TopicsDatabase Theory
Related Topics
Java Development.NET DevelopmentVB DevelopmentMore Topics ...

Database Forum / DB2 Topics / June 2006

Tip: Looking for answers? Try searching our database.

Privileges in DB2 v 8.1.9 linux

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Bob Stearns - 13 Jun 2006 22:33 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
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 - 14 Jun 2006 01:57 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
> 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

 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2009 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.