Re: Need help on how to organize users and objects



Henrik
I have just stareted to learn Oracle :-)))))

Developers are people that need to
create and execute procedures and such

Well you can assign them to DBO schema (default) , so they can create and
execute .

2) I dont think its a good idea to assign the db_datareader to the
viewers,
then they can access ANY table. The best thing is for them to tell me what
tables they need and I grant them select permissions to them. Or if the
users
are not too technical, I create views for them.

You can read ANY table but not access. Yes , you can create views and grant
SELECT permissions on VIEW

For example, user A has created 30 procedures he want to share with user
B.
Which is the easiest way to do that in 2005?

You did not mention that between developes we need to keep permissons too.
so I think that the best way will be to create Schema to Role and then you
can share obkects between users. Don't forget that there is new commnad
EXECUTE AS intoduced in SQL Server 2005
http://www.sommarskog.se/grantperm.html



I keep thinking about the subject. Perhaps on windows level you need to
create more than two group to separate users as they need acceess to the
database
and then create an appropriate permissons for those roles in database





"Henrik Nordgren" <HenrikNordgren@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message news:E06B07A3-7A49-4668-9897-CE0E5A7A8601@xxxxxxxxxxxxxxxx
Hi!

Yeah I have only worked as SS 2005 dba for 2 months so Im pretty new. I
have
worked a lot with oracle before as a developer, and there the dba assigned
a
personal schema to each user.

Im not to keen on the concept of roles though...

1) I definetly dont want every developer to become a sysadmin, possible a
dbcreator. I could assign a custom database role to them though.

2) I dont think its a good idea to assign the db_datareader to the
viewers,
then they can access ANY table. The best thing is for them to tell me what
tables they need and I grant them select permissions to them. Or if the
users
are not too technical, I create views for them.

3) One thing that still confuses me with schemas is... in sql server 2000,
if a user had permissions to create stored procedures, he became the owner
by
default. In 2005, if a user creates an sp, which schema does it belong to?
For me the most logical thing is if a user has a default schema.

For example, user A has created 30 procedures he want to share with user
B.
Which is the easiest way to do that in 2005?


Lots of questions....
Henrik - which is grateful for your patience.

.NET Developer


"Uri Dimant" wrote:

Henrik
Security is a huge subject in SQL Server 2005 , so I'd suggest you to
spend
a few days to learn it

1) Create two Windows Group (Dev and Viewers)
2) I don't know your company's polices but you can add Dev Group to
sysadmin server role to make sure that they can do any thing on the
server.
3) For Viewers Windows group you need to grant them access to the
databases
that they need and add them to db_datareader database role to view the
data.
Well if they have to run SP to get results you need to grant EXECUTE
permissions for this group too

And as you said that some people from Viewers need to access 1-3 tables
in
database A and 6-9 tables in Database B you can assign explicity GRANT
SELECT :: permissions to the role.
I have my doubt that creating 150 Schemas is agood idea , my belief that
you can operate with ROLES very well.






"Henrik Nordgren" <HenrikNordgren@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message news:89344D98-041F-4E77-8DD6-BB488352CD4A@xxxxxxxxxxxxxxxx
Hi!

Im not a very experienced SQL Server 2005 Dba, and not I got a really
big
job on my hand. Im pretty sure how to proceed with most of the stuff,
but
there are a few things that I need some insight on.

We are setting up a SS 2005 server that initially will hold 3 large
databases which I have successfully migrated from SS 2000.

The first issue is the security. In the initial phase I have 150 users
in
an
excel sheet that will need access to the server, all with different
initial
databases. The users are currently located in our active directory,
hence
I
will be using windows authentication.

In order to create SS 2005 users I created a temptable where I imported
the
users from the excel sheet. Then I created an SP which basically
performed
CREATE LOGIN 'mydomain\myuser' FROM WINDOWS WITH
DEFAULT_DATABASE=Oneofthedatabases.

Now I have all the users in my SS 2005 installation. Next is the job of
assigning roles and permissions. Basically there are two roles except
myself
the sysadmin; developers and viewers. Developers are people that need
to
create and execute procedures and such, whereas viewers are people from
the
company that ONLY do selects on certain tables.

Now things start to get a little bit interesting. I dont think I can
use
any
of the predefined roles here. For example, a CEO only need tables 1-4
in
database A, and another executive need tables 6-9 in database A and
tables
1-3 in database B.

And the developers usually want to be able to see all tables, plus the
ability to create SPs and stuff. But this depends on what project the
developers are currently working on.

Now Im thinking about creating schemas, one schema for each user. And
then
I
add whatever tables and procedures they need to their schema. This way
I
separate the user from the objects and get a more tier like approach?
What
do
you think? Is this a good idea?

Im worried about creating complex trees of permissions that will be a
nightmare to administrate where orphaned objects will haunt me in my
dreams...

your thoughts so far?

/Henrik





.



Relevant Pages

  • Re: Execute Persmission denied on object sp_OACreate
    ... SQL Server doesn't check permissions on indirectly referenced objects as ... You can prevent ad-hoc execution of powerful master database procs while ... >I have a user who has execute permissions on a store procedure in a>database> which in turns executes 4 stored procedures in the master database. ...
    (microsoft.public.sqlserver.security)
  • Re: Execute Persmission denied on object sp_OACreate
    ... > SQL Server is creating a job behind the scenes. ... > permissions. ... > SA account password and gaining access to the database. ... >>> How can get a user permissions to execute these stored procedures ...
    (microsoft.public.sqlserver.security)
  • SQL 2000 Windows Authentication - Same User Multiple Groups
    ... view-level permissions such that we can permit/deny a database action ... Execute permission on UpdateResearch to only IT (and explicitly Denied ... Windows group and we have assigned the appropriate group permissions on ...
    (microsoft.public.sqlserver.security)
  • Re: Effective Permissions Error with Domain User
    ... I set the database compatibility to 2005. ... server profile trace and found that it was calling the Execute As User. ... This leads me to believe it is some sort of permissions issue. ... Did you get these database from SQL Server 2000 by using a RESTORE command? ...
    (microsoft.public.sqlserver.security)
  • Re: Generating code for Application Roles.
    ... You can get the syntax for creating the approle out of Books Online. ... If you want to generate a script to assign permissions to all objects, ... pane, edit, and execute. ... >database, ...
    (microsoft.public.sqlserver.security)