Assign permissions to roles - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Assign permissions to roles

Author

Topic

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2003-07-22 : 12:17:23

Is there any way to assign all permissions to a role besides for using EM and checking off each object's SELECT/UPDATE/INSERT/DELETE/EXEC/DRI box?
E.g. I want to grant Public permission on all objects, and then manually revoke the permissions on the few objects I don't want Public to have access to.

Sarah Berger MCSD

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-07-22 : 12:28:02

You can remove the checks that I do when I grant permissions. I only grant permissions on objects that are named according to the standard that we have here. It forces the developers to abide by the rules.



CREATE     PROC isp_Grant_Permissions
AS

SET NOCOUNT ON

DECLARE @objName varchar(80)
DECLARE @objType char(2)
DECLARE grant_perms_on_sps CURSOR FOR	
SELECT name, type
FROM SYSOBJECTS 
WHERE 	(	
		(type = 'P' AND name LIKE 'usp[_]%') 
		OR
		(type = 'FN' AND name LIKE 'udf[_]%')
		OR
		(type = 'TF' AND name LIKE 'udf[_]%')
		OR
		(type = 'U')
		OR
		(type = 'V' AND name LIKE 'v[_]%')
	)
	AND 
		uid = 1
	AND
		status > -1

OPEN grant_perms_on_sps
FETCH NEXT FROM grant_perms_on_sps 
INTO @objName, @objType

WHILE @@FETCH_STATUS = 0
BEGIN
	IF @objType NOT IN ('TF', 'U', 'V')
	BEGIN
		EXEC ('GRANT EXECUTE ON dbo.' + @objName + ' TO RoleName')
	END

	ELSE
	BEGIN
		EXEC ('GRANT SELECT ON dbo.' + @objName + ' TO RoleName')	
	END

	FETCH NEXT FROM grant_perms_on_sps 
	INTO @objName, @objType
END

CLOSE grant_perms_on_sps
DEALLOCATE grant_perms_on_sps

GO

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-07-22 : 12:29:44

Forgot to mention that you will need to change RoleName to whatever role or person or whatever you want to grant the permission to. At the bottom of the stored procedure, I usually add any explicit permissions that they need due to dynamic sql, so just add your revokes down there.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-22 : 12:39:19

Another one for the toolbox..

Thanks Tara..

Brett

8-)

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2003-07-22 : 13:13:03

Thanks alot, Tara!

What's TF/FN? I guess one of them is a UDF, but what's the other one? (I am still working in SQL 7, so no UDF's)

Also, do DRI permissions need to be assigned separately or will they go together with the table permissions such as constraints/triggers?

If not, then I should probably change this line

EXEC ('GRANT SELECT ON dbo.' + @objName + ' TO ' + @RoleName)

EXEC ('GRANT SELECT,UPDATE,INSERT,DELETE,REFERENCES ON dbo.' + @objName + ' TO ' + @RoleName)

Also, what you're mentioning about dynamic SQL. If the dynamic SQL runs within stored procedures, isn't it enough to assign the permissions to the stored procedure. E.g. should I expect to run into problems if stored procedures create temp tables and the user running the procedure does not have Create Table permissions or access to Tempdb?

Thanks!

Sarah Berger MCSD

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-07-22 : 13:19:16

There are two types of UDFs. One is scalar function and the other is table function (one returns a value, the other returns a result set).

You don't assign permissions to constraints or triggers.

We only use stored procedures here so that's why it is coded this way. Assigning execute permissions on a stored procedure is not enough when it has dynamic sql. Dynamic sql requires explicit permissions on the tables/views.

I believe that tempdb is done through public, but I can't remember for sure. But don't worry about assigning any permissions for tempdb.

Tara

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2003-07-22 : 13:25:15

quote:
You don't assign permissions to constraints or triggers.

Right. I was wondering about Primary/Foreign Key constraints. Technically, those are DRI on the permissions tab. I was wondering if they need to get granted explicitly on each table using the REFERENCES statement, or will get granted automatically if all tables have SELECT permissions anyway.

Sarah Berger MCSD

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-07-22 : 13:27:29

I don't understand what you mean. You don't grant permissions on any constraints including primary keys and foreign keys. The references part isn't permission related.

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-07-22 : 13:33:14

Just reading the REFERENCES part of the GRANT statement...

I always provide db_owner role to the developers in the development environment, so I have never need to use the REFERENCES option. In production, the REFERENCES part does not ever come into play because only the DBAs create objects.

So if you grant specific permissions to developers, then yes you will need to do the REFERNCES things if they need to:

"The REFERENCES permission on a table is needed in order to create a FOREIGN KEY constraint that references that table.

The REFERENCES permission is needed on an object in order to create a FUNCTION or VIEW with the WITH SCHEMABINDING clause that references that object."

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-07-22 : 13:34:15

The key point about the above quote is the create part. The references part only applies to the creation of these things.

Tara

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2003-07-22 : 13:36:08

In EM:

Open a DB, choose a user, click properties, click permissions button. Tables have 5 checkboxes: SELECT, INSERT, UPDATE, DELETE, DRI.

From what I understand, DRI is like a sub-level SELECT permission. It doesn't allow the user full SELECT permissions on the table, but does allow the table to be REFERENCEd in a foreign key constraint. Like as if the user would have SELECT permission on the columns in the foreign key only.

If a user has UPDATE permissions on TableA that has a child TableB, but the user has no SELECT on TableB, the UPDATE would fail because the foreign key cannot be checked since the user has no access to TableB. Assigning REFERENCE permission only to TableB should solve this problem.

Do I have this right?

Also, Tara, maybe it would be a good idea to add a database context to the script/procedure so that it can be created in the master database and run from any other database. Then, add it into the Script Library forum for others to enjoy.

Edited: Whoops, Tara. You sniped me! Ignore part 1 of this post, and thanks for the info about the REFERENCES.

Sarah Berger MCSD

Edited by - simondeutsch on 07/22/2003 13:38:42

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-07-22 : 13:41:00

I answered the question about REFERENCES above. REFERENCES is only for creation of objects.

I do not like adding any objects to the master database. The reason for this is when I need to move a database to another server, I don't want to also have to move specific master objects as well. In my opinion, the master database is for system objects only. If you want a database to store generic objects that can be used by other databases, then create a separate database for this. I also would not want to change the stored procedure because each database could require different permissions. For example, say I have a database called DBName1. There are 10 explicit table permissions that I must grant in order for the application to run successfully. Now I have another database called DBName2. This database does not contain the 10 objects so the stored procedure will fail because it can't find these objects.

So if you want a very generic stored procedure to grant permissions, then you will not be able to grant explicit table/view permissions in it because it will most likely fail on another database.

Tara

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2003-07-22 : 14:00:41

Point taken about the Master database... Moving stuff is a pain.

But if the checks on object names are removed from your stored procedure, I do not see how they could fail in any database. It is very generic, loops on all objects regardless of the objects properties.

Of course, another option could be to add another parameter to the procedure to hold the permission types to assign. This could be passed a string such as 'SELECT,UPDATE,DELETE' which would then be concatenated to the


EXEC ('GRANT ' + @PermType + ' ON dbo.' + @objName + ' TO RoleName')

to allow for more specific options.

Sarah Berger MCSD

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-07-22 : 14:05:32

Yes the stored procedure will work in any database as long as you don't add the explicit permissions at the bottom.

The stored procedure as it stands right now just grants the generic permissions on all objects based upon the select statement in the cursor. So if you then need to grant update/delete/insert (select is already handled by it), then you would add the grant permission in the section where it grants the selects. You could also do what you are suggesting but that would be done through a separate stored procedure.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources