DB user, ownership and access to db objects

Hi all,

I need to clarify a few things related to the sybase logins, database
users/aliases, ownership of procedures and different copies of views
(eg. views with the same name, but owned by different users). Can you
please have a look at the following scenarios and tell me whether my
assumptions are correct?

Logins:
1. db1_dbo, valid user in db1
2. db2_dbo, valid user in db2
3. db2_admin, valid user in db1 and db2
4. myuser1
5. myuser2
6. myuser3

Databases:
1. db1, dbo == db1_dbo
2. db2, dbo == db2_dbo

Aliases:
1. myuser1 is aliased to "dbo" in db2
2. myuser2 is aliased to "db2_admin" in db2
3. myuser3 is a regular user in db2
4. db1_dbo is aliased to "dbo" in db2

Procedures:
1. db1.dbo.proc1, does a select * from db2..View
2. db1.db2_admin.proc2, does a select * from db2..View

Views:
1. db2.dbo.View
2. db2.db2_admin.View

Regular queries:
1. login "db2_dbo" is logged on and using the db2 database. If the
user does a select * from View,
    this will use db2.dbo.View, because db2_dbo has the dbo role in
db2.
2. login "db2_admin" is logged on and using the db2 database. If the
user does a select * from View,
    this will use db2.db2_admin.View, because db2_admin *is* db2_admin
in db2
3. login "myuser1" is logged on and using the db2 database. If the
user does a select * from View,
   this will use db2.dbo.View, because myuser1 has effective rights
dbo in db2
4. login "myuser2" is logged on and using the db2 database. If the
user does a select * from View,
   this will use db2.db2_admin.View, because myuser2 has effective
rights db2_admin in db2
5. login "myuser3" is logged on and using the db2 database. If the
user does a select * from View,
    this will use db2.dbo.View, because there is no db2.myuser3.View
in db2, so it selects the dbo copy

Execute procedures:
1. Anybody executing db1.dbo.proc1 will cause the procedure to access
db2.dbo.View,
   because the procedure is owned by db1_dbo, who has effective rights
of dbo in db2
2. Anybody executing db1.db2_admin.proc2 will cause the procedure to
access db2.db2_admin.View,
   because the procedure is owned by db2_admin, who has effective
rights of db2_admin in db2.

Finally, if I modify login db1_dbo so that it is aliased in db2 to
"db2_admin" (instead of "db2_dbo"), but leave the rest the same:
1. Anybody executing db1.dbo.proc1 will cause the procedure to access
db2.db2_admin.View,
   because the procedure is owned by db1_dbo, who has effective rights
of "db2_admin".

Long list of possibilities, I know. I *think* I've got this right. At
least my experience/testing indicates all this. But I've been trying
to explain this to colleagues, and everybody is mightily confused
about all this...

Regards, Maarten
0
boekhold
3/24/2009 2:26:31 PM
sybase.ase.general 8655 articles. 0 followers. Follow

13 Replies
929 Views

Similar Articles

[PageSpeed] 21

On Mar 24, 6:26=A0pm, "boekh...@gmail.com" <boekh...@gmail.com> wrote:
> Hi all,
>
> I need to clarify a few things related to the sybase logins, database
> users/aliases, ownership of procedures and different copies of views
> (eg. views with the same name, but owned by different users).

OK, I think that for stored procedures, I have just found the answer
on how Sybase determines which "copy" of a view to use:

select o.name, u.name, o.loginame from sysobjects o, sysusers u where
o.uid =3D u.uid

Specifically, there is a "loginame" for each object! which is not
reported by sp_help :(

Am I on the right track here?

Maarten
0
boekhold
3/24/2009 3:16:36 PM
The sysobjects.loginame column is a bit of kludge that was added 'recently' to ASE.  In many upgraded ASE's you'll find 
that this column is NULL (because the older ASE version didn't maintain this info.)

I'm not sure what the intent of ASE engineering was in adding the loginame column, but it does not (currently) have 
anything to do with ownership of the object nor 'which "copy"' of an object is used.

Ownership is based on the sysobjects.uid column.

'which "copy"' of an object is based on a few different issues:

- if the owner of the object is explicitly stated in the query (eg, select * from mparsons.myview) then obviously (?) 
the 'myview' object owned by 'mparsons' is referenced

- if the owner of the object is left out of the query (eg, select * from myview) then the optimizer will first look for 
an object owned by sysobjects.uid = user_id(), if this isn't found then it will look for an object owned by 
sysobjects.uid = 1 (aka 'dbo' = database owner)

Once the correct object "copy" is determined the dataserver then has to validate that the user has permissions to access 
said object ... and this goes into a whole new thread having to deal with permissions granted/revoked at the user, group 
and/or role levels.

boekhold@gmail.com wrote:
> On Mar 24, 6:26 pm, "boekh...@gmail.com" <boekh...@gmail.com> wrote:
>> Hi all,
>>
>> I need to clarify a few things related to the sybase logins, database
>> users/aliases, ownership of procedures and different copies of views
>> (eg. views with the same name, but owned by different users).
> 
> OK, I think that for stored procedures, I have just found the answer
> on how Sybase determines which "copy" of a view to use:
> 
> select o.name, u.name, o.loginame from sysobjects o, sysusers u where
> o.uid = u.uid
> 
> Specifically, there is a "loginame" for each object! which is not
> reported by sp_help :(
> 
> Am I on the right track here?
> 
> Maarten
0
Mark
3/24/2009 3:44:44 PM
sysobjects.loginname has to do with "concrete identification" which was a 
security paradigm changed in 12.5 as I recall.  So, although "dbo" might 
"own" an object, it was really created by "loginname" to satisfy the 
requirements and functionality around concrete identification.
http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.sag1/html/sag1/sag1762.htm

"Mark A. Parsons" <iron_horse@no_spamola.compuserve.com> wrote in message 
news:49c8ffec@forums-1-dub...
> The sysobjects.loginame column is a bit of kludge that was added 
> 'recently' to ASE.  In many upgraded ASE's you'll find that this column is 
> NULL (because the older ASE version didn't maintain this info.)
>
> I'm not sure what the intent of ASE engineering was in adding the loginame 
> column, but it does not (currently) have anything to do with ownership of 
> the object nor 'which "copy"' of an object is used.
>
> Ownership is based on the sysobjects.uid column.
>
> 'which "copy"' of an object is based on a few different issues:
>
> - if the owner of the object is explicitly stated in the query (eg, select 
> * from mparsons.myview) then obviously (?) the 'myview' object owned by 
> 'mparsons' is referenced
>
> - if the owner of the object is left out of the query (eg, select * from 
> myview) then the optimizer will first look for an object owned by 
> sysobjects.uid = user_id(), if this isn't found then it will look for an 
> object owned by sysobjects.uid = 1 (aka 'dbo' = database owner)
>
> Once the correct object "copy" is determined the dataserver then has to 
> validate that the user has permissions to access said object ... and this 
> goes into a whole new thread having to deal with permissions 
> granted/revoked at the user, group and/or role levels.
>
> boekhold@gmail.com wrote:
>> On Mar 24, 6:26 pm, "boekh...@gmail.com" <boekh...@gmail.com> wrote:
>>> Hi all,
>>>
>>> I need to clarify a few things related to the sybase logins, database
>>> users/aliases, ownership of procedures and different copies of views
>>> (eg. views with the same name, but owned by different users).
>>
>> OK, I think that for stored procedures, I have just found the answer
>> on how Sybase determines which "copy" of a view to use:
>>
>> select o.name, u.name, o.loginame from sysobjects o, sysusers u where
>> o.uid = u.uid
>>
>> Specifically, there is a "loginame" for each object! which is not
>> reported by sp_help :(
>>
>> Am I on the right track here?
>>
>> Maarten 


0
Sherlock
3/24/2009 3:50:53 PM
OK, so the loginame column adds another level of 'referential integrity' (RI) to the system tables ... which in turn 
leads to a new level of problems for dumping/loading databases across dataservers ... which in turn is addressed by 
allowing the user to override this new RI requirement (eg, sp_dropalias/force).

But getting back to the original thread/issue ... loginame has nothing to do with determining which "copy" of an object 
a user accesses, right?  Or am I missing something?

Sherlock, Kevin [TeamSybase] wrote:
> sysobjects.loginname has to do with "concrete identification" which was a 
> security paradigm changed in 12.5 as I recall.  So, although "dbo" might 
> "own" an object, it was really created by "loginname" to satisfy the 
> requirements and functionality around concrete identification.
> http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.sag1/html/sag1/sag1762.htm
> 
> "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com> wrote in message 
> news:49c8ffec@forums-1-dub...
>> The sysobjects.loginame column is a bit of kludge that was added 
>> 'recently' to ASE.  In many upgraded ASE's you'll find that this column is 
>> NULL (because the older ASE version didn't maintain this info.)
>>
>> I'm not sure what the intent of ASE engineering was in adding the loginame 
>> column, but it does not (currently) have anything to do with ownership of 
>> the object nor 'which "copy"' of an object is used.
>>
>> Ownership is based on the sysobjects.uid column.
>>
>> 'which "copy"' of an object is based on a few different issues:
>>
>> - if the owner of the object is explicitly stated in the query (eg, select 
>> * from mparsons.myview) then obviously (?) the 'myview' object owned by 
>> 'mparsons' is referenced
>>
>> - if the owner of the object is left out of the query (eg, select * from 
>> myview) then the optimizer will first look for an object owned by 
>> sysobjects.uid = user_id(), if this isn't found then it will look for an 
>> object owned by sysobjects.uid = 1 (aka 'dbo' = database owner)
>>
>> Once the correct object "copy" is determined the dataserver then has to 
>> validate that the user has permissions to access said object ... and this 
>> goes into a whole new thread having to deal with permissions 
>> granted/revoked at the user, group and/or role levels.
>>
>> boekhold@gmail.com wrote:
>>> On Mar 24, 6:26 pm, "boekh...@gmail.com" <boekh...@gmail.com> wrote:
>>>> Hi all,
>>>>
>>>> I need to clarify a few things related to the sybase logins, database
>>>> users/aliases, ownership of procedures and different copies of views
>>>> (eg. views with the same name, but owned by different users).
>>> OK, I think that for stored procedures, I have just found the answer
>>> on how Sybase determines which "copy" of a view to use:
>>>
>>> select o.name, u.name, o.loginame from sysobjects o, sysusers u where
>>> o.uid = u.uid
>>>
>>> Specifically, there is a "loginame" for each object! which is not
>>> reported by sp_help :(
>>>
>>> Am I on the right track here?
>>>
>>> Maarten 
> 
> 
0
Mark
3/24/2009 4:44:47 PM
"Mark A. Parsons" <iron_horse@no_spamola.compuserve.com> wrote in message 
news:49c90dff$1@forums-1-dub...
> OK, so the loginame column adds another level of 'referential integrity' 
> (RI) to the system tables ... which in turn leads to a new level of 
> problems for dumping/loading databases across dataservers ... which in 
> turn is addressed by allowing the user to override this new RI requirement 
> (eg, sp_dropalias/force).
>
> But getting back to the original thread/issue ... loginame has nothing to 
> do with determining which "copy" of an object a user accesses, right?

Right.  I think.  I wasn't following that part of the thread.  I was just 
addressing your comment about why ASE added the loginname to the sysobjects 
table.

>> Or am I missing something?

I doubt that you are missing anything.  More than likely, it would be me who 
is missing something :)


> Sherlock, Kevin [TeamSybase] wrote:
>> sysobjects.loginname has to do with "concrete identification" which was a 
>> security paradigm changed in 12.5 as I recall.  So, although "dbo" might 
>> "own" an object, it was really created by "loginname" to satisfy the 
>> requirements and functionality around concrete identification.
>> http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.sag1/html/sag1/sag1762.htm
>>
>> "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com> wrote in message 
>> news:49c8ffec@forums-1-dub...
>>> The sysobjects.loginame column is a bit of kludge that was added 
>>> 'recently' to ASE.  In many upgraded ASE's you'll find that this column 
>>> is NULL (because the older ASE version didn't maintain this info.)
>>>
>>> I'm not sure what the intent of ASE engineering was in adding the 
>>> loginame column, but it does not (currently) have anything to do with 
>>> ownership of the object nor 'which "copy"' of an object is used.
>>>
>>> Ownership is based on the sysobjects.uid column.
>>>
>>> 'which "copy"' of an object is based on a few different issues:
>>>
>>> - if the owner of the object is explicitly stated in the query (eg, 
>>> select * from mparsons.myview) then obviously (?) the 'myview' object 
>>> owned by 'mparsons' is referenced
>>>
>>> - if the owner of the object is left out of the query (eg, select * from 
>>> myview) then the optimizer will first look for an object owned by 
>>> sysobjects.uid = user_id(), if this isn't found then it will look for an 
>>> object owned by sysobjects.uid = 1 (aka 'dbo' = database owner)
>>>
>>> Once the correct object "copy" is determined the dataserver then has to 
>>> validate that the user has permissions to access said object ... and 
>>> this goes into a whole new thread having to deal with permissions 
>>> granted/revoked at the user, group and/or role levels.
>>>
>>> boekhold@gmail.com wrote:
>>>> On Mar 24, 6:26 pm, "boekh...@gmail.com" <boekh...@gmail.com> wrote:
>>>>> Hi all,
>>>>>
>>>>> I need to clarify a few things related to the sybase logins, database
>>>>> users/aliases, ownership of procedures and different copies of views
>>>>> (eg. views with the same name, but owned by different users).
>>>> OK, I think that for stored procedures, I have just found the answer
>>>> on how Sybase determines which "copy" of a view to use:
>>>>
>>>> select o.name, u.name, o.loginame from sysobjects o, sysusers u where
>>>> o.uid = u.uid
>>>>
>>>> Specifically, there is a "loginame" for each object! which is not
>>>> reported by sp_help :(
>>>>
>>>> Am I on the right track here?
>>>>
>>>> Maarten
>> 

0
Sherlock
3/24/2009 5:07:53 PM
"Mark A. Parsons" <iron_horse@no_spamola.compuserve.com> wrote in message 
news:49c90dff$1@forums-1-dub...
> OK, so the loginame column adds another level of 'referential integrity' 
> (RI) to the system tables ... which in turn leads to a new level of 
> problems for dumping/loading databases across dataservers ... which in 
> turn is addressed by allowing the user to override this new RI requirement 
> (eg, sp_dropalias/force).
>
> But getting back to the original thread/issue ... loginame has nothing to 
> do with determining which "copy" of an object a user accesses, right?  Or 
> am I missing something?
>

(SNIP)

Correct with respect to user access.  The loginame, as Kevin mentioned, has 
to do with (Syabse Terminology) "concrete identification".  I believe that 
this is driven by NIST SP 800-53.  I'm not sure which specific control 
mandates this but I do find it to be ironic that they have weasel-words to 
allow application servers to essentially be anonymous (e.g., generic BEA 
account to access a database).  Once in a blue moon pragmatics actually 
overcome this ivory tower.

If I'm wrong above then I suspect that the requirement is driven by one of 
the ANSI SQL standards. 


0
Carl
3/24/2009 5:18:55 PM
On Tue, 24 Mar 2009 07:50:53 -0800, Sherlock, Kevin [TeamSybase] wrote:

> sysobjects.loginname has to do with "concrete identification" which was a 
> security paradigm changed in 12.5 as I recall.  So, although "dbo" might 
> "own" an object, it was really created by "loginname" to satisfy the 
> requirements and functionality around concrete identification.
> http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.sag1/html/sag1/sag1762.htm

One effect here is that:

If loginA is dbo in dbA and dbB, and loginA creates procA in dbA does a
select on a table in dbB *and* if that table has also been created by
loginA, then any user who is granted execute on that proc can run it and
get the correct data. In this case (loginA is the *real* dbo) then the
loginame column is null.

If loginA is *aliased* to dbo when the procA (or the table) is created
then the loginame will contain loginA, and a user will have to be granted
both execute on procA but also select on the table in dbB.

This is something that has caused us some grief on large servers (with
LOTS of databases) where there is a generic DBO for all the databases,
plus various other application or project specific logins who are aliased
to DBO in a subset of the databases. As we need to do a lot of cross
database queries we have recurring permission issues when objects aren't
created by the "real" DBO.

Michael

0
Michael
3/24/2009 6:52:46 PM
On Mar 24, 7:44=A0pm, "Mark A. Parsons"
<iron_horse@no_spamola.compuserve.com> wrote:
> The sysobjects.loginame column is a bit of kludge that was added 'recentl=
y' to ASE. =A0In many upgraded ASE's you'll find
> that this column is NULL (because the older ASE version didn't maintain t=
his info.)
>
> I'm not sure what the intent of ASE engineering was in adding the loginam=
e column, but it does not (currently) have
> anything to do with ownership of the object nor 'which "copy"' of an obje=
ct is used.

Well, let me give you a test case then. The only way I can explain
this behaviour (especially why it's picking the Main.user1.Cust view
in the first case) is by looking at the sysobjects.loginame column...
but I'd be the first to admit I am no expert in this area...

$ isql -Uuser1 -w2000 -DMain
Password:
1> select o.name, u.name, o.loginame from sysobjects o, sysusers u
where o.uid =3D u.uid and o.name =3D 'Cust'
2> go
 name                           name
loginame
 ------------------------------ ------------------------------
------------------------------
 Cust                           user1                          user1
 Cust                           dbo                            NULL

 (2 rows affected)
1> use UsersDB
2> go
1> drop procedure proc1
2> go
1> sp_helpuser dbo
2> go
 Users_name        ID_in_db    Group_name        Login_name
 ----------------- ----------- ----------------- -----------------
 dbo                         1 public            usersdb_dbo
Users aliased to user.
 Login_name
 ------------------------------

 user1
 user1_dbo
 test
 return status =3D 0)
1> select user_name()
2> go

 ------------------------------
 dbo

(1 row affected)
1> select suser_name()
2> go

 ------------------------------
 user1

(1 row affected)
1> create procedure proc1
2> as
3> begin
4> select count(*) from Main..Cust
5> end
6> go
1> select o.name, u.name, o.loginame from sysobjects o, sysusers u
where o.uid=3Du.uid and o.name =3D 'proc1'
2> go
 name                           name
loginame
 ------------------------------ ------------------------------
------------------------------
 proc1                          dbo                            user1

(1 row affected)
1> quit
$ isql -Utest -w2000 -DUsersDB
Password:
1> set showplan on
2> go
1> exec proc1
2> go

=3D=3D=3D> I can see it's using the Main.user1.Cust view

1> quit
$ isql -Uuser1_dbo -w2000 -DUsersDB
Password:
1> drop procedure proc1
2> go
1> create procedure proc1
2> as
3> begin
4> select count(*) from Main..Cust
5> end
6> go
1> select o.name, u.name, o.loginame from sysobjects o, sysusers u
where o.uid=3Du.uid and o.name =3D 'proc1'
2> go
 name                           name
loginame
 ------------------------------ ------------------------------
------------------------------
 proc1                          dbo
user1_dbo

1> quit
$ isql -Utest -w2000 -DUsersDB
Password:
1> set showplan on
2> go
1> exec proc1
2> go

=3D=3D=3D> I can see it's using the Main.dbo.Cust view

1> quit

Both user1 as well as user1_dbo are aliased to dbo for UsersDB. So if
the "selection of objects" is purely based on sysobjects.uid, then why
do they behave differently?

Rgds
0
boekhold
3/25/2009 9:54:39 AM
<boekhold@gmail.com> wrote in message 
news:a067314b-9377-497d-99c1-8d80827ea18f@o11g2000yql.googlegroups.com...
> On Mar 24, 7:44 pm, "Mark A. Parsons"
> <iron_horse@no_spamola.compuserve.com> wrote:
>> The sysobjects.loginame column is a bit of kludge that was added 
>> 'recently' to ASE. In many upgraded ASE's you'll find
>> that this column is NULL (because the older ASE version didn't maintain 
>> this info.)
>>
>> I'm not sure what the intent of ASE engineering was in adding the 
>> loginame column, but it does not (currently) have
>> anything to do with ownership of the object nor 'which "copy"' of an 
>> object is used.
>
> Well, let me give you a test case then. The only way I can explain
> this behaviour (especially why it's picking the Main.user1.Cust view
> in the first case) is by looking at the sysobjects.loginame column...
> but I'd be the first to admit I am no expert in this area...
>
> $ isql -Uuser1 -w2000 -DMain
> Password:
> 1> select o.name, u.name, o.loginame from sysobjects o, sysusers u
> where o.uid = u.uid and o.name = 'Cust'
> 2> go
> name                           name
> loginame
> ------------------------------ ------------------------------
> ------------------------------
> Cust                           user1                          user1
> Cust                           dbo                            NULL
>
> (2 rows affected)
> 1> use UsersDB
> 2> go
> 1> drop procedure proc1
> 2> go
> 1> sp_helpuser dbo
> 2> go
> Users_name        ID_in_db    Group_name        Login_name
> ----------------- ----------- ----------------- -----------------
> dbo                         1 public            usersdb_dbo
> Users aliased to user.
> Login_name
> ------------------------------
>
> user1
> user1_dbo
> test
> return status = 0)
> 1> select user_name()
> 2> go
>
> ------------------------------
> dbo
>
> (1 row affected)
> 1> select suser_name()
> 2> go
>
> ------------------------------
> user1
>
> (1 row affected)
> 1> create procedure proc1
> 2> as
> 3> begin
> 4> select count(*) from Main..Cust
> 5> end
> 6> go
> 1> select o.name, u.name, o.loginame from sysobjects o, sysusers u
> where o.uid=u.uid and o.name = 'proc1'
> 2> go
> name                           name
> loginame
> ------------------------------ ------------------------------
> ------------------------------
> proc1                          dbo                            user1
>
> (1 row affected)
> 1> quit
> $ isql -Utest -w2000 -DUsersDB
> Password:
> 1> set showplan on
> 2> go
> 1> exec proc1
> 2> go
>
> ===> I can see it's using the Main.user1.Cust view
>
> 1> quit
> $ isql -Uuser1_dbo -w2000 -DUsersDB
> Password:
> 1> drop procedure proc1
> 2> go
> 1> create procedure proc1
> 2> as
> 3> begin
> 4> select count(*) from Main..Cust
> 5> end
> 6> go
> 1> select o.name, u.name, o.loginame from sysobjects o, sysusers u
> where o.uid=u.uid and o.name = 'proc1'
> 2> go
> name                           name
> loginame
> ------------------------------ ------------------------------
> ------------------------------
> proc1                          dbo
> user1_dbo
>
> 1> quit
> $ isql -Utest -w2000 -DUsersDB
> Password:
> 1> set showplan on
> 2> go
> 1> exec proc1
> 2> go
>
> ===> I can see it's using the Main.dbo.Cust view
>
> 1> quit
>
> Both user1 as well as user1_dbo are aliased to dbo for UsersDB. So if
> the "selection of objects" is purely based on sysobjects.uid, then why
> do they behave differently?

The difference is that there is an object Main.user1.Cust, but not an object
Main.user1_dbo.Cust. Although user1 is dbo in UsersDB, it still is user1
in Main. By specifying db..table you leave it to the server to determine the
username. it first checks if the user itself owns an object of that name, if
not it looks for an object owned by dbo. If you always want to reference
the dbo Cust object, you have to specify Main.dbo.Cust.

Luc.


0
Luc
3/25/2009 10:40:37 AM
On Mar 25, 2:40=A0pm, "Luc Van der Veurst" <dba_az...@hotmail.com>
wrote:
>
> The difference is that there is an object Main.user1.Cust, but not an obj=
ect
> Main.user1_dbo.Cust. Although user1 is dbo in UsersDB, it still is user1
> in Main. By specifying db..table you leave it to the server to determine =
the
> username. it first checks if the user itself owns an object of that name,=
 if
> not it looks for an object owned by dbo. If you always want to reference
> the dbo Cust object, you have to specify Main.dbo.Cust.

Well, yes, but if you look at the owner of proc1 (eg. sysobjects.uid),
it's all set to "1", eg dbo... there's no reference in that to "user1"
or "user1_dbo", /except/ for that "loginame" column...

So if loginame is not relevant in this context, I would assume that
the owner of proc1 is "UsersDB.dbo", which is "usersdb_dbo", and that
therefore no matter whi created the procedure, the access rights would
be as per "usersdb_dbo".

Maarten
0
boekhold
3/25/2009 4:56:21 PM
It looks like Michael Peppler posted about a similar (the same?) issue yesterday in this same thread.

 From Michael's post and your sample it looks like the dataserver *is* using loginame to determine which copy of the 
table to access when performing a cross-database reference.

If this is the case then I would have to retract my statement about loginame never being used to determine which copy of 
a table to access (at least when talking about cross-database references); though this seems to weaken a benefit of 
aliasing logins to another user (eg, dbo) in the database, and it obviously (?) complicates things for the DBA and 
developers (as Michael's pointed out) when writing cross-database queries where the databases have different users and 
aliases.

On the other hand I can kinda/sorta see where they may have been going with this re: cross-database references.  Since 
there's no relationship between users (or aliases) in different databases the only way to map the users/aliases (for the 
case of cross-database references) is at the login level.  So in this case, even though the proc is owned by 'dbo' in 
the UserDB database you can't map the logical user 'dbo' to a user in the Main database, so which of the logins aliased 
to the 'dbo' account (user1, usersdb_dbo, test) in the UserDB database gets mapped when accessing an object in the Main 
database?

Obviously (?) automatically mapping between logical 'dbo' users in different databases punches a big hole in security 
(eg, no users in dataase db1 are allowed access to database db2 and vice versa, so automatically mapping between the 
dbo's would not be desirable).

Hmmm ... this gets messy when a given login is an alias (eg, to dbo) in one database and a 'real user' in another 
database (as in your example).  And no doubt this gets even messier when there are further cross-database references and 
mappings between a mish-mash of database users and user aliases.

 From a K.I.S.S. standpoint this sounds like one good reason to allow only dbo-owned tables when cross-database 
references are in play.

-------------------

Out of curiosity ... is user1_dbo a user or alias in the Main database?



boekhold@gmail.com wrote:
> On Mar 25, 2:40 pm, "Luc Van der Veurst" <dba_az...@hotmail.com>
> wrote:
>> The difference is that there is an object Main.user1.Cust, but not an object
>> Main.user1_dbo.Cust. Although user1 is dbo in UsersDB, it still is user1
>> in Main. By specifying db..table you leave it to the server to determine the
>> username. it first checks if the user itself owns an object of that name, if
>> not it looks for an object owned by dbo. If you always want to reference
>> the dbo Cust object, you have to specify Main.dbo.Cust.
> 
> Well, yes, but if you look at the owner of proc1 (eg. sysobjects.uid),
> it's all set to "1", eg dbo... there's no reference in that to "user1"
> or "user1_dbo", /except/ for that "loginame" column...
> 
> So if loginame is not relevant in this context, I would assume that
> the owner of proc1 is "UsersDB.dbo", which is "usersdb_dbo", and that
> therefore no matter whi created the procedure, the access rights would
> be as per "usersdb_dbo".
> 
> Maarten
0
Mark
3/25/2009 6:20:16 PM
On Mar 24, 7:26 am, "boekh...@gmail.com" <boekh...@gmail.com> wrote:
> Hi all,
>
> I need to clarify a few things related to the sybase logins, database
> users/aliases, ownership of procedures and different copies of views
> (eg. views with the same name, but owned by different users). Can you
> please have a look at the following scenarios and tell me whether my
> assumptions are correct?

Your safest bet is to try to make sure the users saved
in syslogins match the users saved in sysusers.  Try to
keep these synchronized across all the databases where
someone might be transferred from one to another.

It's one my beefs against Sybase. When the syslogins and
sysusers get out of sync, you run into all sorts of issues.
I don't think Sybase plans on fixing this any time soon,
so it's incumbent on administrators to just keep everything
everywhere in sync.  Add a login to system 1, make sure to
add same login to systems 2 thru N.  Add N logins, then
add N logins, in the same order, everywhere else.

If, for whatever reason, someone is not supposed to have
access to a given system, but you need to add their login
to stay in sync -- use sp_locklogin.

\:-\
0
ThanksButNo
3/25/2009 6:42:39 PM
On Mar 25, 10:20=A0pm, "Mark A. Parsons"
<iron_horse@no_spamola.compuserve.com> wrote:
> It looks like Michael Peppler posted about a similar (the same?) issue ye=
sterday in this same thread.
>
> =A0From Michael's post and your sample it looks like the dataserver *is* =
using loginame to determine which copy of the
> table to access when performing a cross-database reference.
>
> If this is the case then I would have to retract my statement about login=
ame never being used to determine which copy of
> a table to access (at least when talking about cross-database references)=
; though this seems to weaken a benefit of
> aliasing logins to another user (eg, dbo) in the database, and it obvious=
ly (?) complicates things for the DBA and
> developers (as Michael's pointed out) when writing cross-database queries=
 where the databases have different users and
> aliases.

Glad to see I'm not the only one who's confused about this :)

>
> Out of curiosity ... is user1_dbo a user or alias in the Main database?

It's a user with dbo role.

Maarten
0
boekhold
3/26/2009 6:52:46 AM
Reply:

Similar Artilces:

Q: Disable SSO access to db were SSO not a db user
Is it possible to prevent a login user with sso_role from entering a database where the login user is not registered in the database? e.g. unless sso_role login user is in somedb..sysusers - no access for the sso user This would make a handy cfg disable/enable setting JH <John_Hennessy> wrote in message news:8D74E5B3757833AF001B033285256A01.001B034185256A01@webforums... > Is it possible to prevent a login user with sso_role from > entering a database where the login user is not registered in > the database? > > e.g. unless sso_role login user is...

User DB as audit DB
Can I create a user db (eg: myaudit) and change some attributes finding its behavior be like sybsecurity, in other words, sa_roles can not use it, just any particular user?. thank you. On 08-Oct-2010 23:34, Miguel Riano wrote: > Can I create a user db (eg: myaudit) and change some > attributes finding its behavior be like sybsecurity, in > other words, sa_roles can not use it, just any particular > user?. thank you. No, you cannot exclude sa_role: it will always be able to access the database with sa_role. Sybsecurity is special since the permission restrictions are ...

Dbi objects to access an MS Access Db
I'm having a problem getting Tran-ide to recognize my access 2000 database. I've created a system dsn called Midas and created the attached nnsyreg.dat file and put it into e:\sybase\ImpactClient-5_3\config directory. I've also put this file in my projects directory and still get the following message. Unable to connect to session Midas. Any Advice? Thanks Brent begin 666 nnsyreg.dat M4V5S<VEO;BY-:61A<PT*"4Y.3U1?4TA!4D5$7TQ)0E)!4ED@/2!D8G0R-VUS M;V1B8PT*"4Y.3U1?1D%#5$]265]&54Y#5$E/3B ]($Y.4V5S3T1"0T9A8W1O M<GD-"@E.3E]315-?4T525D52(#T@24U...

Linking Access Db tables to a different Access Db using the Internet.
I am looking for the simplest way in ms Access to link up to tables in a different Access database (using ASP or not). It works in a LAN, now I want to use the internet instead. If possible without inviting a heavy load of redundant features. Anybody willing to advice?Robert...

Lost user.db and msg.db
Dear all, I have a question. I have lost my user.db and msg.db from my archive, I have all the other files. It's possible to recreate, with a tool, these missing dbs. Thanks in advance for your help. DG Probably not, those are the CORE files. <dgraziani@menarini.it> wrote in message news:cGYeg.6228$Mr.2660@prv-forum2.provo.novell.com... > Dear all, > I have a question. > I have lost my user.db and msg.db from my archive, I have all the other > files. > It's possible to recreate, with a tool, these missing dbs. > Thanks in advance for your help...

Access DB instead of SQL DB?
Hi folks, Is it possible to set up a Membership-Type Access DataBase in the same manner as an SQL DataBase with ASP.NET automatically configuring all of the DataBase components so that the DataBase will be the same as an SQL DataBase? In other words, could I do something like create an empty .mdb file, put it in the App_Data folder, and ASP.NET will set up the Tables, Views, Functions, etc. and configure it as I add the Login, Password, etc. Controls? Many Thanks. hypercode: Hi folks, Is it possible to set up a Membership-Type Access DataBase in the...

access db to sql db rewright
I am changing a page from Access to SQL express database.  I usually use Dreamweaver Datasets and no problem with them but my hit counter is different.  Can someone rewright this code to work with a SQL database?  If t is possable to use it with a dreamweaver dataset that would be even better but i will take what I can get to learn. ThanksRusty  _________________________________________________________________ <%@ Page Language="vb" Debug=false %><%@ import Namespace="System.Data.OLEDB" %><%@ import Namespace="System.Drawing.Imag...

Convert from Access DB to SQL DB....
Dear All,  I would like to convert from Access To SQL DB undervisual Studio.Net 2005... How can I do it easily, or if there any software to do this automaticlly, please your help..  Awaiting your valuable reply. Many thanks in advance for your cooperation and continuous support....Aح What version of MS Access are you using?Most of the Access versions like XP/2003 all have an upgrade wizard that allows you to convert your existing database to SQL Server.In Access 2003 I believe you can use the Upsizing Wizard which can be accessed from Tools -> Database Utilities -> Upsi...

NGWGUARD.db and USER.DB files
Today, when our file server was being brought down, I noticed that one of our users had an NGWGUARD.DB and USER.DB file open on the server. With the file server down, this user could not access the post office to retrieve attachments. Why he couldn't do this, I understand. What confuses me is why he had an NWGUARD.DB and USER.DB file open on our file server, instead of the GW server. Is this normal? Where are these files usually located/created? Has he changed something he shouldn't have? I know no other user had this issue, including myself, while the FS was do...

DB accessing problems after moving the DB
Hi guysI just moved my system from a workstation to a notebook. So far so good. But that's the beginning of my trouble. I backed up all databases I need for my developing work installed the SQL Server (Dev Edition) on the notebook an restored the databases. Following to that I enabled the Shared Memory, the TCP/IP and the Named Pipes for the Instance. When I now try to run an ASP.Net Website using one of my databases I'm getting this error message:Cannot open database "DatabaseName" requested by the login. The login failed. Login failed for user 'DOMAIN\user.name'. ...

Restore User DB with Message DB?
After our MSG9.DB file went missin I restored a previous nights version of the file. Problem is that the users still can't read the mails that have been converted to Posted items. I have restored a test users db for the same night and of course it works. Do you have to restore the message and user db together or can you run a fix on the user db so it points to the correct bits of the message db? does that make any sense! Cheers, Shane Just to add to the confusion, we have restored a backup of the GroupWise system from last week. If I delete the mails that have been con...

membership DB, profile DB, and role DB
I use the following code to programatically cycle through each record in an mdb database and create users with the Membership.CreateUser method.  The code also creates a profile for the same user before moving to the next record.  The membership and profile DB is on an SQL 2000 server. How can I assign all these users to the role of user in this section of code? Also any idea why only 104 out of 148 records are added to the Membership table while all 148 profiles are created?  A sample record would be:UserName      DIST    ...

DB DB LG ML PR DB
http://www.youtube.com/watch?v=h2-zD5lj8Hg Craig Leidy wrote: > http://www.youtube.com/watch?v=h2-zD5lj8Hg WTF!!! -- Strong Bo "Reality is merely an illusion, albeit a very persistent one." -- Albert Einstein (1879-1955) Play it again... then you'll understand. Craig Leidy wrote: > http://www.youtube.com/watch?v=h2-zD5lj8Hg That's one of the more brain-damaged Youtube clips I've seen, yet kinda catchy! -- Bruce ...

How to implment the single db connection for User Object and Transaction Object?
Hi, I deployed the PB component with Transaction property as 'Require New Transaction' and in the activate event I coded to connect the database. At the begining of the update method script, I declared and instantiate the local transaction object (which contins declared local external function for calling the SP which do some updates and retrieval) and connected to the database. Then I issued sequence of update statements. In between the update statement, I called the external function to trigger the SP (SP does not contain a commit/rollback statement). After all succes...

Web resources about - DB user, ownership and access to db objects - sybase.ase.general

Talk:State ownership - Wikipedia, the free encyclopedia
This article is within the scope of WikiProject Politics , a collaborative effort to improve the coverage of politics on Wikipedia. If you would ...

Why Facebook Said No to Individual Code Ownership
Should tech companies stick with the practice of individual code ownership, where one engineer is responsible for his or her code throughout ...

What Responsible Gun Ownership Really Looks Like - Facebook
Erin Neathery hat eine Notiz mit dem Titel What Responsible Gun Ownership Really Looks Like geschrieben. Du kannst den vollständigen Text hier ...

DLA Piper Stops Representing Paul Ceglia in Controversial Facebook Ownership Claim
Paul Ceglia’s claims to a serious ownership share in Facebook had looked intriguing when they first surfaced last summer — and even more so this ...

Insider Trading Report - Institutional Ownership & Insider Trades
Crude Futures Break Below $60, Supply Glut, Weak Demand... Asia Continues to Rally, India Shines, China...

HUNT App: Public/Private Land Ownership GPS Map on the App Store on iTunes
Get HUNT App: Public/Private Land Ownership GPS Map on the App Store. See screenshots and ratings, and read customer reviews.

Home Ownership and President Bush - YouTube
I think the Republican Party and their cronies on Wall Street could certainly use some "faith-based programs" right now... but the truth is that ...

Etisalat inclusion in MSCI index possible this year after opening up to foreign ownership
Etisalat’s inclusion in the MSCI Emerging Markets Index would result in the company representing 0.14 per cent of the index.

ACTU to seek rise in minimum wages as home ownership becomes a pipedream
Home ownership is beyond the reach of an estimated 1.5 million Australians because the growth in house prices has outstripped the rise in minimum ...

Why our foreign ownership laws don't work
Watch: Three recent episodes highlight the farcical nature of foreign buyer regulations in Australia.

Resources last updated: 11/27/2015 5:21:04 AM