All the Views expressed here are my own and do not reflect opinions or views of the anyone else.All the views are tested on my testing environment and kindly test the post before applying anything on production.You can reach to me at email@example.com .
Wednesday, September 28, 2011
Grant privileges on all tables in particular schema
In oracle, we cannot grant the privileges on schemas level .If we have to grant the privileges on all the tables of a particular schemas, then it is very tedious to grant privileges on all the tables one-by-one to a particular user. This task can be performed by using a simple pl/sql procedure. Here is a Demo for this :
Suppose we have to grant "select" privileges on all the tables to user then we need to do something like this .
SQL>FOR x IN (SELECT * FROM user_tables) LOOP EXECUTE IMMEDIATE 'GRANT SELECT ON ' || your.table_names || ' TO <<user>>' ; END LOOP ;