several hundreds objects. All these objects are owned by the dbo. I
would like to find:
Item (a)
a sql script or some automated way of finding the minimum set of
permissions so users accessing the database via applications can
successfully do their job.
CASE 1:
For example, say in one of the databases, DB1, I have a Stored
Procedure called SP1, which depends on 10 objects (views, tables, and
other Stored Procedures). I would like to give only Execute permission
on SP1, and leave permissioning on all dependent objects alone. Since
all dependent objects are owned by dbo, Sql Server will bypass
permission check, once it successfully checks permission on SP1.
CASE 2:
I have another stored procedure SP2, which deletes some rows from
table T4. Again, I would like to only give execute permission on SP2,
and not touch permissioning on T4.
CASE 3:
A form within an application references a view V1, which depends on
table T6. Only select and updates are allowed on V1.
So, in CASE 1, 2 and 3 above, I would like a sql script, which lists
only SP1, SP2, and V1 and the required permissions. This way I know I
only need to give execute permission on SP1 and SP2, and select and
update permission on V1.
Ideally, a script which then assigns these permissions to a role is
even better.
Item (b)
As objects are added to the databases, a similar code as in item (a),
but this time only gives me the objects, which require minimum
persssion and are not in (a) above. So, in this item (b), I am only
looking at objects not shown in (a) above.
I am aware of sp_depends, but not sure how to generate a sql script to
do the above jobs. I would appreciate if someone could give me
pointers, or some samples, which can be used to construct the above
script. Additionally, in conjunction with sp_MSforeachdb, the script
can then generate the results for all the databases.gudia (gudia97@.yahoo.com) writes:
> Item (a)
> a sql script or some automated way of finding the minimum set of
> permissions so users accessing the database via applications can
> successfully do their job.
> CASE 1:
> For example, say in one of the databases, DB1, I have a Stored
> Procedure called SP1, which depends on 10 objects (views, tables, and
> other Stored Procedures). I would like to give only Execute permission
> on SP1, and leave permissioning on all dependent objects alone. Since
> all dependent objects are owned by dbo, Sql Server will bypass
> permission check, once it successfully checks permission on SP1.
> CASE 2:
> I have another stored procedure SP2, which deletes some rows from
> table T4. Again, I would like to only give execute permission on SP2,
> and not touch permissioning on T4.
> CASE 3:
> A form within an application references a view V1, which depends on
> table T6. Only select and updates are allowed on V1.
> So, in CASE 1, 2 and 3 above, I would like a sql script, which lists
> only SP1, SP2, and V1 and the required permissions. This way I know I
> only need to give execute permission on SP1 and SP2, and select and
> update permission on V1.
> Ideally, a script which then assigns these permissions to a role is
> even better.
I may be missing something, but I cannot see how you could build such
a script. Such a script would have to know your application and see
what it actually acesses.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Thu, 2 Dec 2004 22:42:15 +0000 (UTC), Erland Sommarskog wrote:
> gudia (gudia97@.yahoo.com) writes:
>> Item (a)
>>
>> a sql script or some automated way of finding the minimum set of
>> permissions so users accessing the database via applications can
>> successfully do their job.
>>
>> CASE 1:
>> For example, say in one of the databases, DB1, I have a Stored
>> Procedure called SP1, which depends on 10 objects (views, tables, and
>> other Stored Procedures). I would like to give only Execute permission
>> on SP1, and leave permissioning on all dependent objects alone. Since
>> all dependent objects are owned by dbo, Sql Server will bypass
>> permission check, once it successfully checks permission on SP1.
>>
>> CASE 2:
>> I have another stored procedure SP2, which deletes some rows from
>> table T4. Again, I would like to only give execute permission on SP2,
>> and not touch permissioning on T4.
>>
>> CASE 3:
>> A form within an application references a view V1, which depends on
>> table T6. Only select and updates are allowed on V1.
>>
>> So, in CASE 1, 2 and 3 above, I would like a sql script, which lists
>> only SP1, SP2, and V1 and the required permissions. This way I know I
>> only need to give execute permission on SP1 and SP2, and select and
>> update permission on V1.
>> Ideally, a script which then assigns these permissions to a role is
>> even better.
> I may be missing something, but I cannot see how you could build such
> a script. Such a script would have to know your application and see
> what it actually acesses.
I was thinking about this off and on, and I think something like it should
theoretically be possible. If this table defines what access we ultimately
want:
CREATE TABLE Desired_Activity (
OBJECT_TYPE varchar(5), -- 'Table', 'view', 'proc', etc.
OBJECT_NAME varchar(64), -- object's name
DESIRED_ACCESS varchar(64), 'SELECT','UPDATE','DELETE','EXECUTE'
)
then the magic procedure examines the dependencies of each object listed,
figures out the minimal way (or maybe only close to the minimal way; the
problem seems quite hard) to arrange the permissions into roles, and prints
the role definitions.
That way, the script doesn't have to know the application and what it does,
just what you tell it your application does. The rest of what it needs
should be in the dependency information.|||Hi Erland and Ross,
Thanks for your responses. I have myself been thinking about this
general problem. Any solution to this same problem can be helpful to
different people in different ways. One quick query, which I thought
about, has saved me sometime. It is as follows. I have excluded Tables
from this sql.
select name, sysobjects.id from sysobjects inner join (select distinct
id, cnt=count(id) from sysdepends group by id having count(id)>1)
DepObj on DepObj.id=sysobjects.id
where type in ('P','V','FN') and category=0
order by name
This sql gives me all the objects with dependencies.
Ross, going per your idea or in that direction, would be one way of
building accessibility of the apps to the DB, and securing database(s)
via explicit permissioning, which I am hoping could be managed easily
by a DBA or that group.
Erland, your point is valid that the script would not know by itself
what permissioning to give to the objects. I did not express myself
very well. I am still trying to figure out the best way to structure
and manage permissioning on the database(s).
Another question I have is related to managing permissioning of the
entire set of distinct databases residing on multiple servers,
possibly from one centralized location.
By the way, Erland and Ross, how do you deal with permissioning, in
your applications, in terms of both the manageability (the structure
holding the permissions) and keeping the permissions minimum?
Your efforts in steering me in the right direction is helpful.
Ross Presser <rpresser@.imtek.com> wrote in message news:<13zc1e6jo9uwv$.dlg@.rpresser.invalid>...
> On Thu, 2 Dec 2004 22:42:15 +0000 (UTC), Erland Sommarskog wrote:
> > gudia (gudia97@.yahoo.com) writes:
> >> Item (a)
> >>
> >> a sql script or some automated way of finding the minimum set of
> >> permissions so users accessing the database via applications can
> >> successfully do their job.
> >>
> >> CASE 1:
> >> For example, say in one of the databases, DB1, I have a Stored
> >> Procedure called SP1, which depends on 10 objects (views, tables, and
> >> other Stored Procedures). I would like to give only Execute permission
> >> on SP1, and leave permissioning on all dependent objects alone. Since
> >> all dependent objects are owned by dbo, Sql Server will bypass
> >> permission check, once it successfully checks permission on SP1.
> >>
> >> CASE 2:
> >> I have another stored procedure SP2, which deletes some rows from
> >> table T4. Again, I would like to only give execute permission on SP2,
> >> and not touch permissioning on T4.
> >>
> >> CASE 3:
> >> A form within an application references a view V1, which depends on
> >> table T6. Only select and updates are allowed on V1.
> >>
> >> So, in CASE 1, 2 and 3 above, I would like a sql script, which lists
> >> only SP1, SP2, and V1 and the required permissions. This way I know I
> >> only need to give execute permission on SP1 and SP2, and select and
> >> update permission on V1.
> >> Ideally, a script which then assigns these permissions to a role is
> >> even better.
> > I may be missing something, but I cannot see how you could build such
> > a script. Such a script would have to know your application and see
> > what it actually acesses.
> I was thinking about this off and on, and I think something like it should
> theoretically be possible. If this table defines what access we ultimately
> want:
> CREATE TABLE Desired_Activity (
> OBJECT_TYPE varchar(5), -- 'Table', 'view', 'proc', etc.
> OBJECT_NAME varchar(64), -- object's name
> DESIRED_ACCESS varchar(64), 'SELECT','UPDATE','DELETE','EXECUTE'
> )
> then the magic procedure examines the dependencies of each object listed,
> figures out the minimal way (or maybe only close to the minimal way; the
> problem seems quite hard) to arrange the permissions into roles, and prints
> the role definitions.
> That way, the script doesn't have to know the application and what it does,
> just what you tell it your application does. The rest of what it needs
> should be in the dependency information.|||gudia (gudia97@.yahoo.com) writes:
> Thanks for your responses. I have myself been thinking about this
> general problem. Any solution to this same problem can be helpful to
> different people in different ways. One quick query, which I thought
> about, has saved me sometime. It is as follows. I have excluded Tables
> from this sql.
> select name, sysobjects.id from sysobjects inner join (select distinct
> id, cnt=count(id) from sysdepends group by id having count(id)>1)
> DepObj on DepObj.id=sysobjects.id
> where type in ('P','V','FN') and category=0
> order by name
> This sql gives me all the objects with dependencies.
Beware that sysdepends is far from a reliable source. For instance, if you
alter a procedure, all rows for references to this procedure are wiped out
from sysdepends.
You should not build any critical infrastructure on sysdepends. Sysdepends
can sometimes be useful, but you need to be aware of its limitations.
> By the way, Erland and Ross, how do you deal with permissioning, in
> your applications, in terms of both the manageability (the structure
> holding the permissions) and keeping the permissions minimum?
Our approach is actually very simple-minded, a carry-over from 4.x days.
Basically all users supposed to belong to a group, eh role as it's called
these days, dvp, and this group is granted EXECUTE rights on all procedures
and functions and SELECT rights on all tables.
However, we have the tools to do this more fine-grained if we wish. We
keep all our code under source-control, and each subsystem - as we call
it - has its SQL file in a certain structure. In the top level of this
structure is a file grant.template. This file is parsed by our load tool,
and from this file, the tool determines which GRANT statements to run
for the object.
So we could list individual objects in this file if we wanted to. However,
if there is some object that requires some special permission, we prefer
to put the GRANT statement in the file for that object, and the
grant.template files has the general rules.
Overall, I would say that in a large and complex system you cannot afford
to have too complex rules, because with 3700 stored procedures, it is
inevitable that you will get errors unless you apply some standard rules.
It may be more worth the effort to have a fine-grained control for tables
and views. In our case, we give SELECT rights since we use dynamic SQL.
But we are quite conservative in the usage of dynamic SQL, and it would be
quite conceivable that we would have a rule that says "No SELECT access,
unless there is a need for dynamic SQL on that table".
I should that our application has its own permission system, which is
separate from what SQL Server offers. This permission system controls
which GUI forms which users have access to, and our application comes
with forms for our customers to maintain that permission system.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Sun, 5 Dec 2004 17:44:09 +0000 (UTC), Erland Sommarskog wrote:
> Beware that sysdepends is far from a reliable source. For instance, if you
> alter a procedure, all rows for references to this procedure are wiped out
> from sysdepends.
> You should not build any critical infrastructure on sysdepends. Sysdepends
> can sometimes be useful, but you need to be aware of its limitations.
Is sysdepends reliable on a cleanly recreated database? In other words, if
I script all objects, then recreate them in a new database in order (using
"create schema authorization dbo" so I can create the tables and views
together without caring about dependency, then doing the procedures and
functions), will sysdepends then report correctly?
(I'm begging the question here ... I tried doing this and it seemed to
work.)|||Ross Presser (rpresser@.imtek.com) writes:
> Is sysdepends reliable on a cleanly recreated database? In other words, if
> I script all objects, then recreate them in a new database in order (using
> "create schema authorization dbo" so I can create the tables and views
> together without caring about dependency, then doing the procedures and
> functions), will sysdepends then report correctly?
> (I'm begging the question here ... I tried doing this and it seemed to
> work.)
If you first create the tables, then the dependencies for the tables
will be in order. Presuming that you don't have constraints that calls
UDFs that refers to tables. For views, functions and stored procedures,
you need to create them in dependency order. That is, if proc A calls B,
and you create A first, this dependency will not be recorded. And since
ALTER PROC wipes out the dependencies, you can work around this by loading
all procedures twice. (ALTER PROC has been fixed in SQL 2005, though.)
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks, Erland and Ross for all the suggestions you have made.
Erland, I agree that for a large system, security has to be implemented
such that it is fairly easy to manage, and yet leave no holes.
Erland Sommarskog wrote:
> Ross Presser (rpresser@.imtek.com) writes:
> > Is sysdepends reliable on a cleanly recreated database? In other
words, if
> > I script all objects, then recreate them in a new database in order
(using
> > "create schema authorization dbo" so I can create the tables and
views
> > together without caring about dependency, then doing the procedures
and
> > functions), will sysdepends then report correctly?
> > (I'm begging the question here ... I tried doing this and it seemed
to
> > work.)
> If you first create the tables, then the dependencies for the tables
> will be in order. Presuming that you don't have constraints that
calls
> UDFs that refers to tables. For views, functions and stored
procedures,
> you need to create them in dependency order. That is, if proc A calls
B,
> and you create A first, this dependency will not be recorded. And
since
> ALTER PROC wipes out the dependencies, you can work around this by
loading
> all procedures twice. (ALTER PROC has been fixed in SQL 2005,
though.)
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.aspsql
No comments:
Post a Comment