RE: grant command & wildcard --> wiki howto

-----Original Message-----
From: Thomas Stegbauer [mailto:[email protected]]
Sent: Monday, August 11, 2003 12:55 PM
To: Kevin Wilson
Cc: [email protected]
Subject: Re: grant command & wildcard --> wiki howto


hi kevin,

thanx for the quick answer.

Kevin Wilson schrieb:
> I am not sure if one exists yet or not. That email denotes using
usergroups
> which is a drawback because usergroup inclusion must be specified at user
> creation and cannot be done in a ALTER statement.
> 
> The way I have done it is to have the DBA user create say 2 RESOURCE level
> users to *own* the respective database tables, etc. You have to grant
rights
> to the DBA user for both of the user's set of objects then have the DBA
user
> define ROLES with permissions that allow cross-user object access. Then
> later should you have to add a new user just have the DBA add the user(s)
to
> a particular ROLE and that should do it. Very similar to the canned ROLES
> setup found in MSSQL Server v7.

in what systemtable are all the roles existing are notified?

*** I couldn't tell ya. As far as I know there are not any existing roles. 

> Unfortunately, using this method, I experienced that specifying a wildcard
> (e.g., all tables = "<USER>"."*") is not possible so you must go through
and
> specify each object you want to allow access to. This is tedious I know
but
> this does give you flexibility of adding/removing/altering user
permissions
> past that of the CREATE USER stage when employing the use of a USERGROUP.

elke zabach mentoined a script which should do this, was discussed a 
while ago in sapdb.general. but until now i was unable to find it.

*** If you have it please pass it along as I am unware of it.  

> Also, this setup allows you to run multiple (pseudo) databases in a single
> server instance.

beside triggers (and may be procedures) which must be unique in the 
complete instance.

*** The users themselves and then anything contained under the respective
user. if there are 2 tables name ABCD you can fully qualify them to get
which one you want if each user has read access to each others objects. I
have tested this yet but the theory should prove ok in practice.

> Was this what you were looking for?

yes nearly. but what are difference between a usergroup and a role?

what i found out is:

1. the usergroup a user is belonging to cant get changed.
2. a user can belong only to one group
3. a role can ask for a separate password when it is not activated by 
default on logon.

***number 1 & 2 is all you need to know if flexibility is your goal, then
you will know that usergroups may not be the way to go.

what i didn't in the docs until know (is there a good search interface - 
beside google?) how to add/remove a user from a role

***just use the online sql reference at sapdb.org, here is the link for
version 7.3:
http://www.sapdb.org/htmhelp/e2/55683ab81fd846e10000000a11402f/frameset.htm

greetings
thomas



> -----Original Message-----
> From: Thomas Stegbauer [mailto:[email protected]]
> Sent: Monday, August 11, 2003 11:31 AM
> To: Kevin Wilson
> Cc: [email protected]
> Subject: grant command & wildcard --> wiki howto
> 
> 
> hi kevin,
> 
> you wrote on 2003-04-11 that the above statement could a candidate for a 
> wiki howto. 
> http://listserv.sap.com/pipermail/sapdb.general/2003-April/036927.html
> 
> does such a howto already exist?
> 
> greetings
> thomas
> 


-- 
# Thomas Stegbauer
# http://www.keyserver.de:11371/pks/lookup?op=get&search=0xFF837A1A
# Key fingerprint = E469 F5DC 42FB B530 F5CB  99CB CEB2 BFC6 FF83 7A1A