SEO
vBulletin Search Engine Optimization
| |||||||
| Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
02-27-2008, 07:13 AM
| |||
| |||
 Re: how to grant all tables john_woo@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 }      |
|
02-27-2008, 07:13 AM
| |||
| |||
| Re: how to grant all tables > > 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 |
|
02-27-2008, 07:13 AM
| |||
| |||
| Re: how to grant all tables 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 john_woo@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 |
|
02-27-2008, 07:13 AM
| |||
| |||
| Re: how to grant 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 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 |
|
02-27-2008, 07:13 AM
| |||
| |||
| Re: how to grant all tables 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. john_woo@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 |
Linear Mode
