Grant global select/insert/update/delete to all users

[Nate Jones via DBMonster.com]

I hope this isn't to simple of a question, but google/deja/DB2 doc have not
given me an answer yet.

In Oracle, we have the ability to grant select to all tables at the user
level.

In DB2, this doesn't seem to be possible. We can grant by going into the
control center, selecting the schema in the table section, and granting
access to all tables. But this won't help us when any new tables are
created.

Is there a way to do this? Or even granting access to Public would be
acceptable?

Thanks in advance!
Nate

--
Message posted via http://www.dbmonster.com

[Serge Rielau]

Nate Jones via DBMonster.com wrote:

I hope this isn't to simple of a question, but google/deja/DB2 doc have not
given me an answer yet.

In Oracle, we have the ability to grant select to all tables at the user
level.

In DB2, this doesn't seem to be possible. We can grant by going into the
control center, selecting the schema in the table section, and granting
access to all tables. But this won't help us when any new tables are
created.

Is there a way to do this? Or even granting access to Public would be
acceptable?

Thanks in advance!
Nate

Seems like a pretty dangerous feature to me....
You can write a procedure that walke thorugh all tables in SYSCAT.TABLES
and GRANTs SELECT to PUBLIC.
Then all you need to do is call it "on ocasion".

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[bwmiller16]

Here's a script that I use to do this...

../grant_user.sh <mydb> <username>

As follows:

# Generate grant statements for DB
## Generate and optionally execute GRANTS
# Unix platforms
# Provide the database name
# e.g. grant_user.sh database-name USER

.. /home/dbuser/sqllib/db2profile

DB=$1
USER=$2

#For script debugging
#if [ -f grant_user.tmp ]
# then rm grant_user.tmp
#fi

db2 -t -n +p<<enddb2>grant_user.tmp 2>&1

connect to $DB;

select distinct 'connect to $DB ;'
from sysibm.sysversions ;

--Generate GRANT statements for all user tables in a UDB database
select distinct substr('grant select, insert, update, delete on table
'||
rtrim(tbcreator)||'.'||rtrim(tbname)||

--Transaction Processing
' to user $USER ;',1,200)

from sysibm.syscolumns c, sysibm.systables t
where c.tbcreator not in ('SYSIBM','SYSSTAT', 'SYSCAT')
and c.tbcreator = t.creator
and c.tbname = t.name
and t.type = 'T'
and not c.tbname like'%EXCP'
order by 1 asc ;
enddb2
# Remove any lines with the word select in them:
#
cat grant_user.tmp | grep -v "select" | grep ";" > grant_user.out

cat grant_user.tmp | grep ";" > grant_user.out

rm grant_user.tmp

#Optionally execute it if you're happy with the generated output
db2 -tvf grant_user.out

[Nate Jones via DBMonster.com]

Thanks for the replies.

Just so I'm clear: DB2 has no built-in method, whereby User A can create a
table, and User B automatically has select access to that table? (Aside
from User B having to have DBA privs, of course.)

--
Message posted via http://www.dbmonster.com

[Serge Rielau]

Nate Jones via DBMonster.com wrote:

Thanks for the replies.

Just so I'm clear: DB2 has no built-in method, whereby User A can create a
table, and User B automatically has select access to that table? (Aside
from User B having to have DBA privs, of course.)

Correct. First time I hear about such a request, actually

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Mark Townsend]

Serge Rielau wrote:

Seems like a pretty dangerous feature to me....

Why do you say that ?

[Serge Rielau]

Mark Townsend wrote:

Serge Rielau wrote:

Seems like a pretty dangerous feature to me....

Why do you say that ?

The old philosophical question whether guns are dangerrous or people.
Personally(!) I think they are and so is _by_default_ giving every user
read access to all tables.
Add default userids, default passwords and SMB market and you got
yourself a truly open system :-)

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab