Based on the last above links in my edit to my question I found my answer. I cannot tweak the system account since our logons to our system are from an LDAP and I cannot control the groups and what not.
So I did the following:
- Opened the DB2 GUI (was easiest to do this way).
- Connected to the desired database as db2admin.
- Added my logon to the database as a user.
- GRANT all authorities to that id (my id).
- Disconnect.
- Connect to the desired database as my id.
- GRANT all authorities to db2admin.
- Disconnect.
Voila! The db2admin logon now as all authorities.
EDIT: I'm going to leave the above as it helped me learn how to do some interesting stuff in DB2. However, I have learned that the
DBADM/SECADM with DATAACCESS and ACCESSCTRL authorities granted the
instance owner (in my case the db2admin id) have all the authority
needed to interact with the database. I could have actually just
commented out those grant lines above in the script. Those were left
over from a script which ran against an older version of DB2. I have
also found if I need to have the instance owner DBADM after doing a
restore to database A from database B, it is easiest just to set the
registry variable DB2_RESTORE_GRANT_ADMIN_AUTHORITY to YES (available
in Fix Pack 2 and above). Then I don't have to try to grant instance
owner DBADM. It automatically is granted that to any database restored
into the instance. If you are not at Fix Pack 5, you have to bounce
the instance for this to take affect.
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.
Best Answer
There is no read only privilege on a database in DB2. You will need to grant
SELECT
privileges on specific tables, preferably via a role to avoid doing this multiple times.create role readonly
.SELECT
privileges on the required tables to that role:grant select on myschema.mytable to role readonly
.# mkuser newuser
.grant role readonly to user newuser
.Note that the new user will likely need to change the initial password before he or she can connect to the database.