I've got a table called proc_logs that is intended to be used for logging/troubleshooting stored procs. I'd like to create a stored proc that inserts into this table and adds a timestamp. I'd like any stored proc in any other database in my account to have rights to call this procedure.
First attempt at this is:
grant USAGE on procedure WRITELOG(string,string) to PUBLIC
However I think this would only apply to the PUBLIC schema in the same database. Is this "cross-database" proc considered a terrible idea? How can I grant usage on that proc to any other proc executing in my account? Do I need to create a "logger" role and grant that role to all of my functional roles or do procedures use a special role? Also keep in mind that anyone who has the usage rights on that proc also needs to be able to select from it.
Besides using Usage Privilege Then why not use CREATE PROCEDURE WITH EXECUTE OWNERS RIGHT , with this Statement the procedure would be executed with Owner's right ; even if Caller has no privilege to insert the data into table proc_logs the procedure would execute with Ownwer's right and Caller would be able to Insert the data into this table. Read this material when to use Execute Procedure With Owner right or Caller right. https://docs.snowflake.com/en/sql-reference/stored-procedures-rights.html