schedules: add VIEWSCHEDULES global permission

[amruss]

In 23.1, we added the VIEWJOB Global permission (https://github.com/cockroachdb/cockroach/pull/97860) targeted at the operator persona to allow them to view jobs without being able to manipulate them. We prioritized jobs because of the need to monitor jobs from a stability standpoint. We've also received a request for a similar VIEWSCHEUDLES permission for operators. Today, you need to be an admin to view schedules. https://cockroachlabs.slack.com/archives/CHKQGKYEM/p1684165555682029?thread_ts=1682023821.412739&cid=CHKQGKYEM

Jira issue: CRDB-28096

Epic CRDB-32494

[amruss]

We should add CONTROLSCHEDULE as well

[rafiss]

Is there a strong reason that VIEWJOB/CONTROLJOB should not also give access to viewing/controlling schedules?

edit: @adityamaru points out

I wouldn’t want a user that is only able to PAUSE/CANCEL/RESUME a backup job to be able to do the same to the backup schedule spawning those jobs. One can imagine a use case where the user has been granted the CONTROLJOB privilege to be able to nurse a schema change to completion during off-peak hours but should not have the privileges to interfere with the clusters’ backup schedule

[rafiss]

Note that as part of https://github.com/cockroachdb/cockroach/issues/109814 checks like this one that previously required admin were converted to also allow for the VIEWCLUSTERMETADATA system privilege.

I will leave this issue open, since in the future, we still might want to change SHOW SCHEDULES specifically to check for VIEWSCHEDULES rather than VIEWCLUSTERMETADATA.

[dikshant]

@rafiss On 23.1.15 granting VIEWCLUSTERMETADATA still doesn't allow for SHOW JOBS;

dbuser@redacted.cockroachlabs.cloud:26257/defaultdb> SHOW SCHEDULES;
ERROR: user dbuser does not have SELECT privilege on relation scheduled_jobs
SQLSTATE: 42501

This is after I ran

dikshant@redacted.cockroachlabs.cloud:26257/defaultdb> GRANT SYSTEM VIEWCLUSTERMETADATA TO dbuser;
GRANT

Time: 169ms total (execution 74ms / network 95ms)

[rafiss]

On 23.1.15 granting VIEWCLUSTERMETADATA still doesn't allow for SHOW JOBS;

to clarify, you're asking about SHOW SCHEDULES, correct?

for that, use the VIEWSYSTEMTABLE system privilege, since SHOW SCHEDULES is currently implemented by delegating to a system table.

the work in this ticket has still not been completed. SHOW SCHEDULES can be changed so that it works either (1) by using the existing VIEWJOB privilege, or (2) by using a new VIEWSCHEDULE privilege. I would recommend (1), but that's up to the DR team.

[dikshant]

Yep I was asking about SHOW SCHEDULE and VIEWSYSTEMTABLE was it thanks!