If you have a list of known usernames, you can store them in a table somewhere:
CREATE TABLE dbo.WebDevelopers(name SYSNAME PRIMARY KEY);
INSERT dbo.WebDevelopers(name)
SELECT N'YourDomain\User1'
UNION ALL
SELECT N'YourDomain\User2';
Now you can create a variety of scripts to ensure that:
- they exist as a login at the server level
for each relevant database:
- they exist as a user
- they are in the
db_datareader
role
- they have
SELECT
permission on the dbo
schema
Here is a start to solve 1. (I'll come back and address the others as I have time later today):
DECLARE @sql NVARCHAR(MAX), @cr NCHAR(2) = CHAR(13) + CHAR(10);
SELECT @sql = N'';
SELECT @sql = @sql + @cr + 'CREATE LOGIN '
+ QUOTENAME(d.name) + ' FROM WINDOWS;'
FROM dbo.WebDevelopers AS d
LEFT OUTER JOIN sys.server_principals AS p
ON d.name = p.name
WHERE p.name IS NULL;
PRINT @sql;
-- EXEC sp_executesql @sql;
For the rest, this makes some basic assumptions: you always elevate privileges using database-level roles, rather than explicit GRANT/DENY, server role membership or AD group membership. Like the table of developers, you also want to have somewhere a list of the databases you want to affect:
CREATE TABLE dbo.DeveloperDatabases(name SYSNAME PRIMARY KEY);
INSERT dbo.DeveloperDatabases(name)
SELECT N'some_database'
-- UNION ALL N'...';
Now you can build a script that will drop the user from each database, create the entire list from scratch, and add each user only to the db_datareader role. You may want to customize the script if you want to grant explicit rights on a certain schema or set of objects.
DECLARE @sql NVARCHAR(MAX), @cr NCHAR(2) = CHAR(13) + CHAR(10);
SELECT @sql = N'SELECT @sql2 = N'''';';
SELECT @sql = @sql + @cr + N'SELECT @sql2 = @sql2 + ''
USE ' + QUOTENAME(name) + ';
DROP USER '' + QUOTENAME(d.name) + '';''
FROM dbo.WebDevelopers AS d
INNER JOIN ' + QUOTENAME(name) + '.sys.database_principals AS p
ON d.name = p.name;'
FROM dbo.DeveloperDatabases;
SELECT @sql = @sql + @cr + N'SELECT @sql2 = @sql2 + ''
USE ' + QUOTENAME(name) + ';
CREATE USER '' + QUOTENAME(d.name) + '' FROM LOGIN '' + QUOTENAME(d.name) + '';
EXEC sp_addrolemember ''''db_datareader'''', '''''' + d.name + '''''';''
FROM dbo.WebDevelopers AS d
LEFT OUTER JOIN ' + QUOTENAME(name) + '.sys.database_principals AS p
ON d.name = p.name
WHERE p.name IS NULL;'
FROM dbo.DeveloperDatabases;
DECLARE @sql2 NVARCHAR(MAX);
EXEC sp_executesql @sql, N'@sql2 NVARCHAR(MAX) OUTPUT', @sql2 OUTPUT;
PRINT @sql2;
-- EXEC sp_executesql @sql2;
This is very rough, and lots of things can make it break. I could have combined the two selects into a single query but I think double-nested dynamic SQL was complex enough without adding conditional string concatenation in there as well.
As the error message itself says that user do not have the execute permissions.
mysql> SELECT hello('world');
ERROR 1370 (42000): execute command denied to user ''@'localhost' for routine 'test.hello'
You need to grant the Execute Permission to that user.For that you need to login as root user and grant the permission as
grant execute on db.* to user@localhost;
For your other queries :
Yes It is possible that your username is an empty string but it is not safe to create the users like this.
For creating and granting privileges in brief have a look at This Link.
Yes you can grant all the privileges on all entities within a database.
for this you can execute a command like
Login as root user and issue a command
GRANT ALL ON DB_NAME.* TO 'USER'@'HOST' IDENTIFIED BY 'PASSWORD' WITH GRANT OPTION;
Best Answer
No. Instead use the following as a general way to provide limited actions.
Here's how to "grant" things to the user indirectly. Create a Stored Procedure with "security definer" and be "root" when you define it. In the SP, first check that the user is not trying to touch
mysql
,information_schema
, or the readonly db. Then proceed to do theCREATE
orDROP
.This can obviously be extended to a variety of actions. You should probably have one SP per action -- one for
CreateDb('dbname')
, one forDropDb('dbname')
, and whatever else you might need.Then train the users to use
instead of
Note: you might need extra parameters for what should be added to the creation, such as
CHARACTER SET utf8mb4
. Or you could assert more control by not giving the user a choice.