Agreed, slide 30 of ICC510 is the best documentation resource
Edit to add: in terms of ways to search through the docs, I would recommend searching the ICC_AllCourseContent PDF as that is a merge of the all of the PDFs.
Oh, and I highly recommend enabling auditing and looking at the audit log to debug while configuring security. To do so, in the Management Portal, use System Administration > Security > Auditing.
Most likely the reason you can't connect to terminal is the user does not have use on the %Development and %Service_Terminal resources, and Read on whatever resource protects the default database for the default namespace of the user via a role or public permission. However, be careful here because then they can run almost code in the namespace that only requires Read access. So, you are giving them more privileges than you may realize.
As far as only granting select on all tables in a namespace, that is difficult to future proof as Pravin mentioned. An option would be to not give any specific permissions on tables and to only give Read on the resource(s) protecting the database(s) containing the tables. However, this would then give them read through OOP.
As far as I know, to truly only give someone SQL select on all tables in a namespace, they'd have to connect through an xDBC client. You'd need to give them select on all tables through the tables tab of the role (and update whenever there's a new table). That's all they need. Fun fact, if a user has a SQL privilege on a table, role escalation while running a SQL statement will occur to give them Read/Write on the resource protecting the database and the %SQL role grant Use on %Service_SQL. But, terminal is more than running SQL so the built in role escalation is insufficient. That's why you would need to do what I explained in the first paragraph if you want them to be able to run SQL in the terminal
Log in or create a new account to continue