database level priveleges

[kan1234]

Hi,

I am planning to give for a set of users having all rights to all the tables in the databse and for a set of user no access on the database.
For which i am following role based approach.

for to give access all privileges on the table
Grant ALL on <table name > to <role name >

it is higly impossible to give on each and every tables in the database.

can anybody help me in getting a script by which i can get all table name automatically from the database.

kindly help me in getting the script or is there any way to give/restrict the access on database level privleges

regards
nanda

 

[vpshriyan]

Get every user defined table names in a database use SQL below:
select tabname from systables where tabid > 99;

Please note that to set the access granularity you may have to revoke access given to the object "public" if any, for each table.

eg. revoke all on "owner".table_x from "public";