May 15, 2002 at 2:46 pm
Hello all,
1.We have an application in oracle that is owned by one user A(or schema) but runs as another user B to which privileges are granted over the user A's objects. How could I achieve this security model in SQLServer? When user B is trying to access user A's objects SQLServer always asks for the owner name prefix? Is there a way to get around this?
2. How can I use global variables whose values should persist after a rollback, similar to package variables in Oracle? If I use a temporary table, the records do not persist after a rollback?
Thanks.
May 15, 2002 at 2:51 pm
If the object is not qualified with an owner, it tries to find an object belonging to the current user, if that fails it tries dbo.object. If that fails it stops. If you want to access an object owned by someone besides the current login or dbo you have to explicitly qualify it AND have permissions on the table.
Andy
May 15, 2002 at 3:18 pm
Thanks Andy for the quick reply.
So is it a good practice to create all my application objects with dbo as the owner logged in as say, user A?
May 15, 2002 at 3:27 pm
Not really a good practice per se, as when you change these you can run into a lot of issues working with ownership chains of views and stored procedures.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 15, 2002 at 3:57 pm
Agree with Antares. Avoid using owners other than dbo in SQL Server. Not sure about Oracle, but it's more headache than it's worth in SQL Server, IMHO.
Steve Jones
May 15, 2002 at 5:11 pm
Are you all saying that I should only create objects with dbo as the owner? Pardon my ignore, i am only couple of days into sql server. I am planning to do this, correct me if my approach is wrong.
1. Create a user A with sysdamin server role and db_owner database role.
2. Create all objects as dbo.table_name
3. Create another user B and grant privileges to A's objects. No A should be able to do, select * from emp instead of,
select * from A.emp?
4. Only for scalar function, B will do,
select A.udf_get_sal?
Thanks for your replies.
May 15, 2002 at 7:23 pm
Right you want dbo to own (preferably) all objects in the database so that the ownership chain is easy to work with and your not changing between owner schemas (which would require you to have to grant permissions to those objects). Also even though you have the objects created under the dbo owenership you can still grant specific users rights to work directly with those tables and other objects. The key comes in if you run into issues with a query not working and trying to find the problem especially with Stored Procedures which can be quite complex. The primary reason this is the adopted standard is the level of control you can put on the database by not granting rights directly to the tables but using Stored Procedures to limit users to what you want the to have access to. Also you can limit inserting, updating and deleting data this way. Now for your issue I would also consider making user B a role that way if you decide to add other users with the same capacity of access as B then you add them to the role and not have to grant permissions on everything again. Also yes, the only time you will need to use an owners referenced is for user defined functions.
Hope this helps.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 16, 2002 at 4:38 am
I agree, use dbo only except in extraordinary cases.
Andy
May 16, 2002 at 8:05 am
Thanks a lot Antare and Andy for your valuable inputs.
May 23, 2002 at 8:49 am
I have other question with regard to the ownerships?
If I create a login A with sysadmin role, is it not equivalent to SA? If yes, then why not use SA itself to create my application tables?
May 23, 2002 at 10:28 am
You can but this is considered a bad practice since you want to control this access and if you give everyone the SA password when any one person leaves or is removed from being able to do this then you have to change the SA password again. It is far simpler to just use an account for each user or an NT account for each use. Also makes auditing easier with Profiler and other tools as you can find the users login and tell who is who when problems are going on.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 23, 2002 at 12:27 pm
Ok, how do I create the database for my application.
1. Login in as SA or create new login, grant sysadmin,db_owner and create the databse using the new login?
2. If I use SA to create the database then when I create the tables using the application user, i will have to prefix the tables with dbo for the table to be owned by dbo. Or if create the database using the new login as defined in Step 1 I don't need to prefix dbo for all my tables will be owned by dbo by default.
Please tell which is the good practise in creating the database and tables for I am really new to MS-SQL.
May 25, 2002 at 3:01 pm
Create an admin account for yourself, and secure the SA account with a complex password so you can control access.
You can use this admin account to create your database.
When you create a tables with an account, which has either database admin rights or database dbo rights the database objects are created with an owner of dbo.
Steven
May 25, 2002 at 7:02 pm
Just keep in mind there is a difference between being the dbo and being in the db_owner role.
Andy
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply