Grant Execute Permission for a User on All Stored Procedures in Database

GRANT EXECUTE to all stored procedures

SQL Server 2008 and Above:

/* CREATE A NEW ROLE */
CREATE ROLE db_executor

/* GRANT EXECUTE TO THE ROLE */
GRANT EXECUTE TO db_executor

For just a user (not a role):

USE [DBName]
GO
GRANT EXECUTE TO [user]

Grant execute permission for a user on all stored procedures in database?

Create a role add this role to users, and then you can grant execute to all the routines in one shot to this role.

CREATE ROLE <abc>
GRANT EXECUTE TO <abc>

EDIT
This works in SQL Server 2005, I'm not sure about backward compatibility of this feature, I'm sure anything later than 2005 should be fine.

Grant execute on all stored procedures using SSMS

Looks like you can do this, though not as "simple" as you would expect.

I would, personally create a database role to do this. Go to your database in the Object explorer, and expand the Security Folder. Then right click Roles and Select New -> New Database Role.

Give the Role a name (I'll use db_executor) on the General Pane and then go to the Securables Pane. Click Search... at the top and select the radio option Specific Objects... and click OK. Click Object Types... and then tick Databases and then OK. Now click Browse... and tick the database you are adding the role to, then click OK and then OK.

In the datagrid at the bottom locate the Permission Execute, and tick the box in the column Grant. Then OK. This will run the below SQL on your instance:

USE [YourDatabase]
GO
CREATE ROLE [db_executor]
GO
use [YourDatabase]
GO
GRANT EXECUTE To [db_executor]
GO

Yes, Microsoft really is inconsistent with the casing of USE for that statement, and it omits the ; in it's commands.

Now you have created the role, locate the user you want to give access to in the object explorer in the Users folder. Right Click them and select Properties. Go to the Membership Pane and tick the box next to db_executor. Then click OK. This will run the below SQL on your instance:

USE [YourDatabase]
GO
ALTER ROLE [db_executor] ADD MEMBER [YourUser]
GO

Of course, why you wouldn't just run the 2 above commands, which is far quicker, I do not know.

Granting execute permission on all stored procedures in a certain database

Generate the statements, then copy them and paste into query window to run them

select 'grant execute on ' + 
    QuoteName(specific_schema) + '.' +
    QuoteName(specific_name) + ' to someone'
from information_schema.routines
where routine_type='PROCEDURE'

How to grant a user access to all stored procedures on mysql?

Use this instead, it will work:

GRANT EXECUTE ON mydb.* TO 'my_user'@'%';

GRANT EXECUTE permission to ALL STORED PROCEDURES in snowflake

A few things that might help you.

1. I'd recommend fully-qualifying that table name in the SELECT statement, this way whenever the stored procedure is called, the "context" of the user's session will not matter, as long as the session's current role has access to the table and schema you should be good.
   
   A fully-qualified table has the form: database_name.schema_name.object_name

   Example: hr_prod_db.hr_schema.employees

You can read more about object name resolution at this link: https://docs.snowflake.net/manuals/sql-reference/name-resolution.html

2. I'd recommend you spend a little bit of time reading about "Session State", at the following link, as this link discuses "Caller's rights" vs. "Owner's rights" stored procedures. If your procedure is only going to be called from a session with the role of the stored procedure owner, this shouldn't matter, but if you are granting USAGE on the procedure to another role, it's very important to understand this and set this properly.
   https://docs.snowflake.net/manuals/sql-reference/stored-procedures-usage.html#session-state

3. If your procedure is going to be called by a session that has it's current role set to a different role than the "owning role, you'll need to ensure the proper grants on the procedure (and schema + database) to the role that is going to be executing the procedure. This is all outlined here in this document quite thoroughly, pay particular attention to this as in your example code you have a table or view name that is different than what your error message is reporting, so perhaps stproc_test_employees is a view on top of SYEMPLOYEES:
   https://docs.snowflake.net/manuals/sql-reference/stored-procedures-usage.html#access-control-privileges
   Note: When/if you grant usage on this procedure to another role, you will need to include the datatype of the arguments, example:

   GRANT USAGE ON database_name.schema_name.get_column_scale(float) TO ROLE other_role_name_here;

I hope this helps...Rich

how to give the user to execute all procedures under the db (regular methods doesn't works)?

I created below Procedure with root user

 DELIMITER //
 
 CREATE PROCEDURE GetAllTitles() BEGIN  SELECT *  FROM titles; END //
 
 DELIMITER ;

Then gave the proper permission as you mentioned

 CREATE USER 'my_user'@'%' IDENTIFIED BY 'Not_so_secure!1'; GRANT
 CREATE, DROP, DELETE, INSERT, SELECT, UPDATE ON employees.* TO
 'my_user'@'%'; GRANT EXECUTE ON employees.* TO 'my_user'@'%'; FLUSH
 PRIVILEGES;

Then connected with that user my_user

       mysql> select user();
      +-------------------+ 
      | user()            |
      +-------------------+ 
      | my_user@localhost |
      +-------------------+ 
      1 row in set (0.00 sec)
 

Then check the if I can see the procedure with below command connected to mysql using my_user

 mysql> SHOW PROCEDURE STATUS \G;
 *************************** 1. row ***************************
                   Db: employees
                 Name: GetAllTitles
                 Type: PROCEDURE
              Definer: root@localhost
             Modified: 2021-05-17 12:55:05
             Created: 2021-05-17 12:55:05
       Security_type: DEFINER
              Comment:  character_set_client: latin1 collation_connection: latin1_swedish_ci   Database Collation:
 latin1_swedish_ci 1 row in set (0.00 sec)

Then tried to call it's working

 CALL employees.GetAllTitles();

Make sure you are flushing the privileges

How do you grant execute permission for a single stored procedure?

GRANT EXECUTE ON dbo.procname TO username;

Do I need to grant permission on the database if I already granted it to stored procedure?

It looks like you've been introduced (whether you wanted to be or not!) to something called database permissions chaining. At a high level, you're allowed to have objects in your own database reference other objects and only have to grant permissions on the referencing object so long as both the referenced and referencing object are owned by the same database principal (i.e. user). For example, if I have a table that I own, I can write a stored proc doing whatever (say a SELECT) against the table and then grant execute on the proc to another user. When the other user goes to execute the proc, permission chaining kicks in and says "the proc and the table are owned by the same user - the execute permission is sufficient"

But! By default, the permissions chain is broken when the referenced object is in another database. Why? I can only speculate as to creators' intent, but imagine a multi-hosted database server and I'm an malicious actor. If I have my own database, I could write a proc that says select * from OtherDb.dbo.Users;, grant permissions on that proc and exfiltrate data from other users' databases.

There are a couple of ways around this:

1. You can enable cross db ownership chaining at the server level. I don't recommend this, but it is an easy button out of the problem you have.

2. You can grant permissions on the objects referenced in the procedure. This may be okay, depending on why you're gating data access through stored procedures (which, full disclosure, I like to do in general). This would be a simple grant select on dbo.APP_USERS to «some DB2 principal - a user or group»;. The downside here is that the principal to whom the permissions are granted can do any select on the table now, thereby bypassing the proc.

3. You can sign your stored procedures. This is a little more involved, but is the more secure option. It involves creating a certificate or asymmetric key in both databases, creating a user based on the same, granting permissions to that user, and finally calling add signature on the related procs. You'd think you're done, but you'll need to re-apply that signature any time someone changes the procedure definition. Why? Let's say that you sign the proc today but then I change it to do something unintended (either innocently or maliciously). If the signature persisted through an alter procedure, the original proc could be a Trojan horse.

Here is a rough sketch of the module signing dance.

use Db1;

create certificate ModuleSigningCert ...;
add signature to dbo.YourProc by certificate ModuleSigningCert;

use Db2;
-- import ModuleSigningCert - either by backup certificate/create certificate
-- you technically only need the public key portion

create user SigningUser from certificate ModuleSigningCert;
grant select on dbo.YourTable to SigningUser;

---

For what it's worth, I don't know that "database will be accessed by a public app" necessarily means "and now we need to do cross-database stuff". It may, but it may not. For instance, if the public app still accesses the database through an internal application server, you're not getting much security-wise with the multi-database setup.

---

Related Topics