How an Aster user can see all the tables within a schema (not public schema)?

Aster
Teradata Employee

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

1 REPLY
Enthusiast

Re: How an Aster user can see all the tables within a schema (not public schema)?

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);