how to grant all tables

[john_woo]

Hi,

db2 GRANT all on a_table_name to user me

is for a table, I'm wondering possible to grant all on all_tables to
user me, without listing all tables' names?

--
Thanks
John
Toronto

[bwmiller16]

jo******@canada.com wrote:

Hi,

db2 GRANT all on a_table_name to user me

is for a table, I'm wondering possible to grant all on all_tables to
user me, without listing all tables' names?

--
Thanks
John
Toronto

Try something like this:

function grantme {
echo "select 'grant SELECT ON ' || RTRIM(TABSCHEMA) || '.' ||
RTRIM(tabname) || chr(10)
|| ' TO ME '
|| ' ; ' || chr(10)
from syscat.tables
where tabschema = '$1' ${2+" and tabname = \'$2\'"}
;" | db2 -xt +p > .tmp

db2 -vtf .tmp
}

[john_woo]

>

function grantme {
echo "select 'grant SELECT ON ' || RTRIM(TABSCHEMA) || '.' ||
RTRIM(tabname) || chr(10)
|| ' TO ME '
|| ' ; ' || chr(10)
from syscat.tables
where tabschema = '$1' ${2+" and tabname = \'$2\'"}
;" | db2 -xt +p > .tmp

db2 -vtf .tmp
}

Thanks lots, but

1. in linux, when run it in db2 console:

db2 => db2 => function grantme {echo "select 'gra....

it returned error:
SQL0007N The character "{" following "function grantme " is not valid.

SQLSTATE=42601

2. in linux, when run it in shell:
$ function grantme {echo "select 'grant SELECT ...

it returned error:
-bash: syntax error near unexpected token `{echo'

so, can u tell how to execute it in linux (any version for windows? I
have cgywin in windows)

--
Thanks again
John

[Jayesh Thakrar]

Note that this will have to be re-done as soon as a new table is added
to the database.
Depending upon the environment, database, security etc., another option
is to grant your id DBADM access - this will give you implicit ALL
access to ALL tables.
Here's how you would do it -
1. Connect to the database as SYSADM or DBADM user
2. execute the SQL - "grant DBADM on database to user <your-id>"

-- Jayesh

jo******@canada.com wrote:

function grantme {
echo "select 'grant SELECT ON ' || RTRIM(TABSCHEMA) || '.' ||
RTRIM(tabname) || chr(10)
|| ' TO ME '
|| ' ; ' || chr(10)
from syscat.tables
where tabschema = '$1' ${2+" and tabname = \'$2\'"}
;" | db2 -xt +p > .tmp

db2 -vtf .tmp
}

Thanks lots, but

1. in linux, when run it in db2 console:

db2 => db2 => function grantme {echo "select 'gra....

it returned error:
SQL0007N The character "{" following "function grantme " is not valid.

SQLSTATE=42601

2. in linux, when run it in shell:
$ function grantme {echo "select 'grant SELECT ...

it returned error:
-bash: syntax error near unexpected token `{echo'

so, can u tell how to execute it in linux (any version for windows? I
have cgywin in windows)

--
Thanks again
John

[john_woo]

> Here's how you would do it -

1. Connect to the database as SYSADM or DBADM user
2. execute the SQL - "grant DBADM on database to user <your-id>"

-- Jayesh

Thanks Jayesh.

It's good but let's say user1 create table, then when user2 login and
tried to use tables created by user1, user2 has to use like

select * from user1.table;

How to make tables created by user1, for other users use it without
specifying schema, just

like select * from table, as user1 uses?

--
Thanks
John

[mike]

db2 currently lacks an oracle-style concept of public schema, but that
does not matter so much because you have at least these alternate ways
of addressing the objects.

(a) after user2 successfully connects to the database, have that user
perform "set schema user1" to ensure that any unqualified names
mentioned by user2 map to the user1 schema (assuming that user2 has
privileges to access those objects).
That setting of the default schema for the connection can be done
interactively or programatically, possibly by db2cli.ini configuration
also.

(b) create a set of aliases so that some or all of the objects in the
user1 schema
are visible (via those aliases) from the user2 schema. Example: create
alias user2.some_table for user1.some_table. Assumes that user2 has
privileges to access the objects in the user1 schema.



jo******@canada.com wrote:

Here's how you would do it -
1. Connect to the database as SYSADM or DBADM user
2. execute the SQL - "grant DBADM on database to user <your-id>"

-- Jayesh

Thanks Jayesh.

It's good but let's say user1 create table, then when user2 login and
tried to use tables created by user1, user2 has to use like

select * from user1.table;

How to make tables created by user1, for other users use it without
specifying schema, just

like select * from table, as user1 uses?

--
Thanks
John

[Serge Rielau]

John,

You can also do the same in an SQL Procedure using EXECUTE IMMEDIATE.
that will be portable. (there are plenty of examples doing DDL in
procedures)

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab