I'm totally new to DB2 and need some assistance please, I need to create a user to have access to only one table on a schema. I was able to create the user. On the table permissions i gave it the relevant priveleges as well as on the schema but the user has access to the other tables as well in that schema. How is it possible to restrict access on all the other tables (NO read update insert etc. on any other tables)
Please assist

Dont give schema priv

I removed the access from the schema, but user still has access to the other tables. User can still update read insert on other tables. Need to restrict him on all tables except one

You should create the database using the RESTRICTIVE keyword. Then you grant only those privileges that your users shall have.

The database is already created, the schemas already exist for some time now. Just need to create a new user with restricted access on all the other tables except one. There is a way but it will take alot of time (to grant the exclude permssions for the user on each table, and there are alot of tables really alot. There must me simple way of doing this

Does anyone have a solution to my problem Theres 3000 tables I dont want to go to the permissions of each table, please assist in this question

The simple answer is : Create a OS user and grant CONNECT privilege on the database and SELECT access to the table to that user.

--
Connect to the database as the user and post the output of

db2 get authorizations

---

What are the permissions on the other tables ? Granted to public? Granted to a group in which the user is a part of ?

Cheers
Sathyaram

I have done that, I created the user granted access to the database and relevant table, but it has access to the other table by default somehow. The
public user has change access(read, add, update, delete, execute).

I have done that, I created the user granted access to the database and relevant table, but it has access to the other table by default somehow. The
public user has change access(read, add, update, delete, execute) and the user i created i did not place it in any group

are you on windows ? check the user you are creating dont have admin rights on the server

This is a OS400 machine, and I created the user on the database level only User doesn't have admin privileges or any other privileges on the operating system level

This is a OS400 machine, and I created the user on the database level only User doesn't have admin privileges or any other privileges on the operating system level

DB2 on os400 is a totally different beast ..

The access may be due to the operating system policies ... Request your os400 admin to check if the new user has read access to the data file.

Unlike other platforms, in simple terms, db2 on iseries is a SQL wrapper to access the native data files. Therefore, the permissions you grant for tables is same as the OS permissions you grant to the data file.

so in other words if the user has read,update access to the datafile, he'll have read, update access to all the tables. I have to individually set the permissions for each table to restrict access. Thats not very user friendly

I guess there no true answer to this question, thanks all for your assistance, logged a call with IBM/DB2 speacilists will update this subject if they come up with anything positive.
Thanks again