asked on

grant user read only access to DB2 V 8.02

Dear all,

Right now using toad for DB2 to grant read only access to user and I can see that Toad's interface do not allow me to grant read only access to ALL table at once but I need to use mouse to do it one by one, this is time consuming, the script generate is :

GRANT CONNECT ON DATABASE TO USER FUNGKK;
GRANT SELECT ON ROOT.ACTLINK TO USER FUNGKK;
GRANT SELECT ON ROOT.T90 TO USER FUNGKK;
GRANT SELECT ON ROOT.T91 TO USER FUNGKK;
GRANT SELECT ON ROOT.T92 TO USER FUNGKK;
GRANT SELECT ON ROOT.USER_CACHE TO USER FUNGKK;
GRANT SELECT ON ROOT.VENDOR_MAPPING TO USER FUNGKK;
GRANT SELECT ON ROOT.VIEW_MAPPING TO USER FUNGKK;
GRANT SELECT ON ROOT.VUI TO USER FUNGKK;
COMMIT;

Open in new window

question is :

1) before granting select access right, should we grant it "connect on" database right grant for a user?
2) then only grant select should just be it, right?
3) how can I grant select only right to a user for all tables , or all table of a schema ?

anything else need to be care on assigning right on DB2 ?

Tomas Helgi Johannsson

Hi!

I posted in your previous question this comment
https://www.experts-exchange.com/questions/28437344/DB2-Admin-password-and-user-group-on-vesrion-8-02.html?anchorAnswerId=40120338#a40120338

A script that you can use to grant select on all tables in a schema is as follows

#!/bin/ksh
#######################################################
#  This scripts executes select grants to all tables
#  in schema.
#######################################################
MYDB=""
MYSCHEMA=""
MYUSER=""
MYGROUP=""
db2 "CONNECT TO $MYDB"
DBTABLES=`db2 -x "SELECT tabname FROM syscat.tables WHERE tabschema=UPPER('$MYSCHEMA')"`
for TABLENAME in $DBTABLES;
do db2 "GRANT SELECT ON $MYSCHEMA.$TABLENAME TO GROUP $MYGROUP, USER $MYUSER"
done
db2 "DISCONNECT $MYDB"

Open in new window

Copy the above script into a file in the db2 instance owners directory. Modify the variables (MYDB, MYSCHEMA, MYUSER, MYGROUP ) as needed, make the script executable and run it.

Then use this select to verify that the user has the privilege needed.

db2 "select substr(grantor,1,20), substr(grantee,1,20), selectauth from syscat.tabauth where tabschema = '<theschema>'  and grantee = '<theuser>' and  with ur"

Open in new window

Regards,
Tomas Helgi

marrowyung

ASKER

the first one can't be run in Toad for DB2, the UI tools, right?

I am not a unix user, anything else can help?

any script runable in UI is good !

Tomas Helgi Johannsson

Hi!

You can issue this command in TOAD.

SELECT 'GRANT SELECT ON ' || tabschema || '.' || tabname || ' TO GROUP <yourgroupname>, USER <yourgroupname> ' FROM syscat.tables WHERE tabschema=UPPER(<your schemaname>);

Then copy the result back to a SQL window and run that.

Regards,
Tomas Helgi

marrowyung

ASKER

tks.

but I don't know what is the group ! I check from the Toad interface I don't see any group, for example, what is the group of root ?

by toad, I right click on that user and see this :

GRANT DBADM, CREATETAB, BINDADD, CONNECT, CREATE_NOT_FENCED_ROUTINE, IMPLICIT_SCHEMA, LOAD, CREATE_EXTERNAL_ROUTINE, QUIESCE_CONNECT ON DATABASE TO USER ARADMIN;

GRANT BIND, EXECUTE ON PACKAGE NULLID.SQLA1E00 TO USER ARADMIN WITH GRANT OPTION;

GRANT CONTROL ON PACKAGE NULLID.SQLA1E00 TO USER ARADMIN;

GRANT BIND, EXECUTE ON PACKAGE NULLID.SQLA2E00 TO USER ARADMIN WITH GRANT OPTION;

GRANT CONTROL ON PACKAGE NULLID.SQLA2E00 TO USER ARADMIN;

Open in new window

the point is if our user request to have read only to all, then I have to do it one by one and it is very consuming ! any way to use one command/script to grand read only to all tables and/OR views to a DB2 user?

ASKER CERTIFIED SOLUTION

Tomas Helgi Johannsson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

marrowyung

ASKER

the how to create a group to contain new members?

then

this will grant select only command to ALL tables?

SELECT 'GRANT SELECT ON ' || tabschema || '.' || tabname || ' TO GROUP <yourgroupname> ' FROM syscat.tables WHERE tabschema=UPPER(<your schemaname>);

Open in new window

Tomas Helgi Johannsson

Hi!

You create the group and assign users to it on the host using appropriate commands.
On *nix it is you issue as root
groupadd <new group>
and then add existing users using
usermod -G <new group> <user>

See more in the unix manual.

Regards,
Tomas Helgi

marrowyung

ASKER

so both this:

groupadd <new group>

and

usermod -G <new group> <user>

can run on Toad ?

marrowyung

ASKER

also from the beginning of this post you show me:

select substr(grantor,1,20), substr(grantee,1,20), selectauth from syscat.tabauth where tabschema = '<theschema>'  and grantee = '<theuser>' and  with ur

Open in new window

to verify user right.

what is "with ur" here means ?

marrowyung

ASKER

if I tried to create a user group called FKK_Group by

groupadd <FKKGroup>

by Toad, error message comes out is:

ERROR [42601] [IBM][DB2/SUN] SQL0104N  An unexpected token "< FKKGroup >" was found following "groupadd ".  Expected tokens may include:  "<space>".

Open in new window

please advice what is that means. we are using DB2 V8.02