Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

I am able to grant multiple privileges (SELECT, INSERT, UPDATE, DELETE) to multiple Users/Roles to one single Table each time on an Enterprise Geodatabase from ArcCatalog 10.1.

That's done by Manage -> Privileges... for each Table.

Now that I have got a large number of Tables/Views, just wondering whether there's a way/tool/PyScript of bulk granting privileges to all the Tables/Views at once.

Many thanks in advance.

Cheers, Alex

share|improve this question
    
If using Oracle it is not a recommend route but you can write a few lines of SQL to do the GRANT on TABLES (and loop each table) stackoverflow.com/questions/187886/… –  Mapperz Jun 12 '13 at 3:43
    
I am using SQL Server 2008 R2. Maybe I can write a script to grant privileges? –  alextc Jun 12 '13 at 4:08

2 Answers 2

up vote 3 down vote accepted

Have a look at the Change Privileges geoporcessing tool.

This tool:

Establishes or changes user access privileges to the input ArcSDE datasets, stand-alone feature classes, or tables.

You could write a python script and grant rights over several tables/featureclasses for several user at once.

share|improve this answer
    
Thanks Devdatta! It worked. –  alextc Jun 12 '13 at 4:57
    
@alextc: Glad to help you out! –  Devdatta Tengshe Jun 12 '13 at 4:58

Here is a function that I wrote for PostgreSQL geodatabases that grants permissions on all user tables and views. It ignores the SDE i*, sde_log*, n_* (network), nd_* (network dataset), t_* (topology) tables but includes the version tables a* and d*.

CREATE OR REPLACE FUNCTION public.mgis_gdb_grant_all(dbrole text)
RETURNS void AS
$BODY$
DECLARE schemaname text;
DECLARE usertbls text;
BEGIN
    SELECT string_agg((concat(tbllist.table_schema, '.', tbllist.table_name)), ', ') INTO usertbls
    FROM (SELECT table_schema, table_name FROM information_schema.tables
          WHERE table_schema NOT LIKE 'pg\_%'
          AND table_schema NOT IN ('information_schema', 'public', 'sde')
          AND table_name NOT LIKE 'sde\_log%'
          AND table_name !~ E'^i\\d+'
          ORDER BY table_schema, table_name) As tbllist;

    EXECUTE format('GRANT INSERT, SELECT, UPDATE, DELETE ON TABLE %s TO %I', usertbls, dbrole);
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION public.mgis_gdb_grant_all(text)
  OWNER TO postgres;
GRANT EXECUTE ON FUNCTION public.mgis_gdb_grant_all(text) TO postgres;
REVOKE ALL ON FUNCTION public.mgis_gdb_grant_all(text) FROM public;
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.