All Forums Aster
dm186036 5 posts Joined 09/13
11 Dec 2014
How an Aster user can see all the tables within a schema (not public schema)?

Hello guys,
 
I am trying to find out a solution/an answer for my problem for some days and my conclusion (till now) is this one: it's possible to grant access for a schema to a role, but nor for its (schema's) content. Please correct me if I am wrong. 
 
Here is my problem:
 
I would like to know if it's possible to grant access to a role so that every member automatically can see all the tables within a schema in Aster environment?
 
I have tried this command: GRANT USAGE ON SCHEMA schema_name TO GROUP role_name; 
However, this command shows to group members only the schema name but not the objects (in my case, the tables) from the schema. I can only grant privileges to tables but not to schema (+ its content). 
 
The only grant command to schema is this one:
GRANT { { CREATE | USAGE } [,...] | ALL [ PRIVILEGES ] } ON SCHEMA schemaname [, ...] TO { username | GROUP rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ]
 
Any help is appreciated! Thank you! 
 
Regards,
Daniel

18 Feb 2015

if you have db_admin role, you can run sql-to-generate-ddl like so:

SELECT 'GRANT SELECT ON ' || schemaname || '.' || tablename || ' TO role_name;' as ddlstr
  FROM nc_all_tables t
 INNER JOIN nc_all_schemas s
    ON t.schemaid = s.schemaid
 WHERE schemaname in ('schema1','schema2','schema3',...)
 ORDER BY schemaname, tablename;

Capture the output and run it.  (Change the grant statement to whatever privileges you want role_name to have and change the list of schema names as needed);

You must sign in to leave a comment.