Гость
Целевая тема:
Создать новую тему:
Автор:
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Problems with GRANT / 3 сообщений из 3, страница 1 из 1
07.11.2002, 09:12:52
    #32065643
Dimitri
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Problems with GRANT
I have problem with Transact-SQL:
my database uses this schema:
USER->STORED PROCEDURE->TABLE
a) USER can execute the STORED PROCEDURE
b) this STORED PROCEDURE can chahge(insert/update/delete) the TABLE
c) USER can not directly acess to the TABLE

and I use two GRANT:

GRANT EXECUTE ON myProc TO myUser
GRANT ALL ON myTable TO myProc

I use such schema on Oracle and Interbase, but I can not realize it on MS SQL:
I read all Transtact-SQL help, use GRANT/DENY/Dabase Roles - nothing...
Because MS SQL can grant only to "roles" and "roles" can be attached only to
users...
So there are no "object to object grant" and "object can not be attached to a
role (only user)"

Can you help me, because I do not believe, that MS SQL do not support it.

P.S. May be I must use another schema on MS SQL.

__Werty_
...
Рейтинг: 0 / 0
07.11.2002, 10:32:16
    #32065649
Glory
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Problems with GRANT
Hi
All you need is grant permission for the procedure owner .
Because
"If the same user owns a stored procedure and all the views or tables it references, and if the procedure and objects are all in the same database, SQL Server checks only the permissions on the procedure".

This security mechanism also known as ownership chain.

"If the ownership chain of a procedure or view is broken (not all the objects in the chain are owned by the same user), SQL Server checks permissions on each object in the chain whose next lower link is owned by a different user. Only those statements where the user has the necessary permissions will be executed, and the remaining statements will get an "Insufficient Permissions" error. In this way, SQL Server allows the owner of the original data to retain control over its accessibility.
"

PS
Microsoft recommends to use dbo as owner of all database objects.
...
Рейтинг: 0 / 0
07.11.2002, 10:35:43
    #32065650
Nickolay
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Problems with GRANT
You do not need to do anything more than
Код: plaintext
GRANT EXECUTE ON myProc TO myUser 
Sp has access to the ALL database objects by default (without any user/group context).
...
Рейтинг: 0 / 0
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Problems with GRANT / 3 сообщений из 3, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]