how to grant select, insert on all tables to role
Posted on 2009-02-18
I have few roles like roleA, roleB, roleC, roleD etc created. Few Schemas created like schemaL, schemaM, SchemaN, SchemaP
On first role say roleA
I need to grant all privileges on all objects within the schemaM including all tables (like select, insert, update, delete), stored procedures, triggers.
On second role say roleB
I need to grant privilege on few stored procedures(say 12 of them) which updates a particular table in schemaM
On third roleC
I need to grant all privilege on all objects within the schemaM including all tables (like select, insert, update, delete), stored procedures, triggers similar to roleA. Later on based on need i will remove some privileges
On fourth roleD.
I need to give only select privilege on tables and execute access on particular stored procedures (say 10 of them) within the SchemaM.
How can i achieve this. Any syntax, ideas, resources highly appreciated. How can i achieve it. Thanks in advance.