Проектирование пространственной базы данных.

[remedius]

а нет, извиняюсь, Вы ведь передаете массивы, в качестве аргументов. ОК, тогда предыдущий пост остается открытым. (на счет обращения к БД из библиотеки)

[x77]

remedius, тут есть два варианта. функции UDF вызываются на стороне сервера, а не клиента. поэтому в своей процедуре ВСТАВИТЬ_МАХОМ_ВСЕ_МОИ_ТОЧКИ (A varchar (1000)), вы вызовете UDF, которая строку вида '100:200;10:50;30:4.296' превратит в пары точек. есть, кстати стандартная функция, которая возвращает подстроку заданного размера, начиная с заданного символа. если координаты слать, как '00100002000001000050000304.296', и исходить из того, что кол-во координат всегда чётно, и каждая координата занимает 5 символов - задача вполне решаема даже на уровне стандартного убого SQL.

второй вариант -  сразу из dll коннектиться к базе и вставлять напрямую. это черевато неоправданной нагрузкой на сервер и будет плодить определённое кол-во "лишних" подключений. но и это вполне реализуемо.

оптимальный из всего этого варианта - высокоуровневую логику вынести на клиент, оставив СУБД операции над самыми примитивными сущностями (т.е. - парой точек).

[remedius]

Спасибо! я это учту

[x77]

так такое и на хранимых процедурах можно реализовать. (если я правильно поняла Ваше предложение.

совершенно верно. я и считаю, что это надо реализовать на хранимках. дело СУБД - вставить одну запись. именно это и должна обеспечивать бизнес логика сервера. то, что с логической точки зрения эта запись является частью некоей структуры, и бла-бла-бла - об этом должна болеть голова у клиентской части.

[remedius]

угу, я так и поступлю

[x77]

только не забывайте, что я писал с точки зрения IB/FB (ну, и моей личной, по-мелочи). у dimk'и, например, с точки зрения MSSQL/Oracle (и его личной ) мнение может быть другим, а выбор СУБД в данном случае может значительно сказаться на подходе к реализации.

[Dimka]

у dimk'и, например, с точки зрения MSSQL/Oracle (и его личной Ага) мнение может быть другим

Нет, у меня такое же мнение. Я не понимаю, какая выгода хранить данные на уровне СУБД в каких-то пользовательских структурах, если на уровне той же СУБД не реализуется сложная логика.

а выбор СУБД в данном случае может значительно сказаться на подходе к реализации.

Поэтому "академический" подход, опирающийся на стандартный SQL и таблицы будет применим к любой СУБД. Если же пользоваться специфическими возможностями некой СУБД, то необходимо предварительно выбрать эту СУБД - потом переделывать под другую СУБД будет уже поздно .

[remedius]

Назрел вопрос:
в общем есть этап создания всяких там хранимых процедур, тригеров и непосредственно самой БД. Так как, я знакома с sql, и то поверхностно, первое что приходит на ум, это написать файлы sql, а потом запустить батник. А как это делается обычно? (врядли наверно вручную:)) Да, вопрос также касается и db2, что и как там?
Спасибо

[Dimka]

первое что приходит на ум, это написать файлы sql, а потом запустить батник.

Правильно

врядли наверно вручную:))

Есть так называемые генераторы запросов. Но я пишу вручную в соответствующих средствах с подсветкой синтаксиса, контекстными подсказками и средствами проверки синтаксиса. Этого достаточно. Пока не доводилось писать SQL-скриптов свыше 1000 строк и с какими-либо сложными алгоритмами внутри. Сами SQL-инструкции (в первую очередь SELECT) по природе своей функциональны и состоят из следующей композиции реляционных функций:

1. Соединение (обычно реализуется с помощью инструкции JOIN в секции FROM, и бывает cross, inner и outer, где outer, в свою очередь, бывает left, right и full). В соединении участвуют отношения, являющиеся либо таблицами, либо результатами подзапросов.
2. Первичный горизонтальный фильтр (реализуется в секции WHERE и применим к результатам соединения). Можно использовать подзапросы в инструкциях IN и EXISTS.
3. Группировка и вычисление значений агрегатных функций (реализуется в секции GROUP BY, а функции в секции SELECT).
4. Вторичный горизонтальный фильтр (реализуется в секции HAVING и применим к результатам как соединения, так и группировки).
5. Вертикальный фильтр (реализуется в секции SELECT и представляет собой набор выбираемых полей из всех перечисленных в секции FROM отношений, а также вычисляемых полей). В некоторых диалектах в качестве значений полей могут быть использованы результаты подзапросов.
6. Третичный горизонтальный фильтр (реализуется при помощи квалификатора DISTINCT в секции SELECT и устраняет дублирующиеся строки). DISTINCT может быть применён до группировки, тогда записывается внутри агрегатных функций.
7. Сортировка (реализуется в секции ORDER BY).
8. (Не везде) Четверичный горизонтальный фильтр (реализуется различно, в некоторых диалектах позволяет выбирать из результата строки по их порядковым номерам в результирующей таблице).
9. (Не везде) Объединение запросов. В SQL Server реализуется оператором UNION и последовательно прикладывает результаты нескольких запросов друг к другу. Результаты запросов должны иметь одинаковые поля и одинаковый их порядок.

поэтому сравнительно легко отлаживаются без всякой потребности в отладчике - проверяется каждый этап исполнения запроса. В MS SQL Server, например, для этого существует такое понятие, как "план исполнения запроса", на котором в виде таблицы или графической диаграммы показывается, как СУБД разлагает запрос в дерево элементарных операций над данными.

[remedius]

а возможно ли скрыть таблицы из БД или создать как системную?

[x77]

remedius, а зачем? пользователь не работает с базой данных, он работает с программой, с клиентской частью. какие таблицы программа позволяет редактировать, или вообще видеть, с теми он и будет работать. зачастую, работающий с конечным приложением пользователь вообще не знает,  где находится база данных, не говоря уже о том, что она реально содержит.

[Dimka]

Это уже похоже на навязчивую идею . Зачем? и От кого?

Ещё раз объясняю: доступ к таблицам пользователей и/или прикладных приложений обеспечивает администратор баз данных. Промышленные СУБД не предназначены для развёртывания на рабочих станциях каждого пользователя и не предполагают, что каждая тётя Маня или дядя Вася будут вручную ковыряться в таблицах. Для того, чтобы совсем отвязаться от структуры некой БД, используют представления (views) или хранимые процедуры (stored procedures) в качестве интерфейсной прослойки. Такая БД чаще всего реализуется отдельно и может быть размещена в том числе и на другом сервере или в другом экземпляре (instance) сервера (если СУБД поддерживает такие решения). Однако, сразу предупреждаю, что такие решения проигрывают в производительности.

Изложите подробно ваше видение жизненного цикла вашей программной системы, расписывая по ролям, кто что с этой системой будет делать. И укажите тот этап, на котором, согласно вашему мнению, стоит острая потребность что-то (указать, что именно) от кого-то (указать роль) спрятать.

[remedius]

Вы как всегда правы. Уж извиние, возможно за глупые вопросы. Видимо у меня совсем стерлись границы, я вот просто себя тоже считаю пользователем (тот который может создавать таблицы к примеру в Enterprise Manager). Я поняла. Спасибо

[Dimka]

я вот просто себя тоже считаю пользователем (тот который может создавать таблицы к примеру в Enterprise Manager).

Вы - разрботчик и (наверняка) администратор в вашей СУБД. Пользователь же может только читать данные и, максимум, добавлять/изменять данные в таблицах (и то не всегда). Никакой пользователь не имеет права изменять структуру БД, её таблиц и других объектов. Если администратор допустил произвол пользователей, в результате которого БД оказалась испорченной - за это с администратора "шкуру спускать" будут. Разработчик БД в общем случае не несёт ответственность за организацию администрирования БД у заказчика - анархия и бардак у заказчика являются его (заказчика) внутренними проблемами.

[Sla]

Разработчик БД в общем случае не несёт ответственность за организацию администрирования БД у заказчика - анархия и бардак у заказчика являются его (заказчика) внутренними проблемами.

Не совсем согласен. А если разработчик так написал дыряво, что пользователи имеют доступ к изменению структуры БД?

[remedius]

Sla, Можете привести пример? Как разработчик может предоставить доступ пользователю к структуре БД.

[Dimka]

А если разработчик так написал дыряво, что пользователи имеют доступ к изменению структуры БД?

Только если у пользователя есть права на исполнение хранимых процедур какой-то БД, и внутри этих хранимых процедур "криво" (а не "дыряво") реализованы манипуляции со структурой БД. "Кривая" реализация хранимок - это, естественно, баг разработчика. Во многих проектах структура БД статична, и хранимки не выполняют DDL-инструкций. Что же касается дырок в безопасности, то это более относится к СУБД, а не к разработчикам прикладных систем.

Разработчик может допустить только одну ошибку: реализовать прикладную программу таким образом, что для её нормальной работы потребуются максимально широкие права. Т.е. программа сознательно будет игнорировать подсистему безопасности СУБД. Такая программа может быть "дырявой", но администратор баз данных заказчика (если ему не захочется нести ответственность за чужие проблемы) просто не примет такую программу в эксплуатацию.

[x77]

remedius, однажды меня попросили помочь заюзать электронную базу предприятий, написанную на аксессе и поставлявшуюся на диске. задача состояла в том, чтобы по определённым критериям сформировать список электронных адресов. база была зашифрована, и просто так в аксессе она не открывалась. а программа, работавшая с базой, нас не устраивала ну никак. но программа была написана на Delphi. умный разработчик имел привычку держать базу открытой в design-time. это действительно удобно - добавил db-aware компонент - и сразу увидел, что он содержит. а ещё это означает, что в design-time введён логин/пароль, и Delphi, как любая среда разработки, где-то его хранит. а потом компилит в исходных код вместе со всеми остальными параметрами, живущими в *.dfm файлах. таким образом процесс вскрытия базы свёлся к открытию экзешника Far'овским просмотровщиком и поиском по слову "password". примерно третье найденное вхождение содержало и сам пароль. писавший эту муру студент не ограничился таким крутым паролированием, он ещё предусмотрел шифрование одного из ключевых полей в таблице. т.е. прямой ссылочной целостности не было, чтобы по фирме выцепить всё принадлежащие ей электронные адреса, требовался не тупой join, а приведение этого кода фирмы к определённому виду по достаточно простому алгоритму, а потом уже - запрос по полученному "расшифрованному" коду. после того, как мы с коллегой разобрались, как шифруется первичный ключ, сделать в базе ещё одну таблицу и перенести в неё фирмы с изменённым ПК труда не составляло. на всю байду ушло всего лишь два дня. вот это - один из примеров.

другой пример. в FireBird логин/пароль по умолчанию = sysdba/masterkey. к 90% баз, писанных всякими студентами, можно подконнектится по этому логину/паролю. вскрытие базы сводится к поиску места, где она лежит. (вообще говоря, я уже писал, что с FB/IB/Yaffil для взлома любой базы достаточно иметь доступ к каталогу, в которому установлена СУБД).

но эти примеры - это только часть ответа на твой вопрос. есть ещё третий вариант - это непродуманность программных решений. например, некоторые программеры, запарившись бодаться с многочисленными отчётами, предоставляют пользователю. возможность самим формировать запросы на основе введённого SELECT. при этом они не делают синтаксической проверки на то, что выполняется ли там именно SELECT, а не, скажем, UPDATE. это - тоже пример потенциальной дыры.

[remedius]

dimka, x77, Да, это еще одна задача, над которой следует хорошо призадуматься.

Следующая проблема собственно вот в чем:
надо написать функцию, принимающую неограниченное число параметров (Функция подсчета mbr объекта). Что я могу предложить: Написать UDF (например, принмающую по 4 точки), которая будет в глобальной переменной складывать их.
Но насколько правильно это? как еще можно реализоваться такую задачу?

Спасибо

P.S.Уважаемые модераторы, ничего страшного, если я не создаю новые темы, а пишу все в одной ( не хочется захламлять форум:) - если лучше разнести по темам - напишите.

[Dimka]

Давно уж доказано, что отношение N-й степени эквивалентно N-1 отношениям 2-й степени. И этим активно пользуются при проектировании БД в том случае, когда N переменно.

Например, имеется у вас сущность "Географический объект" (GO). Вы точно не знаете, какими атрибутами потребуется снабдить эту сущность при решении разных прикладных задач - имеется в общем случае неопределённое N каких-то атрибутов этой сущности, образующих отношение (ID, X1, X2, ..., XN). Проблема решается элементарно. Нужно ввести мета-сущность "Свойства географического объекта" (GOP), представляющую собой настраиваемый справочник атрибутов сущности; такая мета-сущность описывается отношением (ID, GO.ID, PropertyName, [PropertyType]); последний атрибут вводится по желанию. Нужно разделить отношение GO: освободить его от всех могущих изменяться атрибутов, тогда, в общем случае, останется только ключ (ID); и ввести новое отношение "Описание географического объекта" (GOD), имеющее структуру (GOP.ID, PropertyValue).

После такой декомпозиции возникает техническая проблема получения представления, содержащего свойства объекта в колонках. Обобщённо и неформально проблема именуется "развёртыванием строк в столбцы" и решается в основном при помощи динамических запросов (код которых формируется во время исполнения).

Поскольку функции не принимают неизвестное количество параметров, описанный выше способ позволяет избавиться от этой проблемы - в функцию нужно передать не неопределённое количество параметров, а таблицу в N строчек. Обычно же UDF сами делают запросы, самостоятельно выбирая необходимые для расчётов данные из БД. В этом случае параметрами UDF являются идентификаторы, позволяющие функции выбрать только нужные данные.

В частности задача расчёта MBR сводится к реализации функции, параметром которой является один лишь идентификатор (значение первичного ключа) географического объекта (или географической области). Точки, описывающие объект, функция получит в строках таблицы, выполнив соответствующий запрос к БД.

[remedius]

А можете немного рассказать о выполнении sql запрсов в UDF функциях Firebird ( вроде писали, что это как-то страшно:()

(в sql server все вроде ясно)
Спасибо

[Dimka]

Нет, не могу.

Примечание к вышенаписанному (тонкий момент ). К той схеме, что представлена выше, задача развёртывания строк в столбцы неприменима, поскольку каждый объект имеет уникальное множество свойств. Если же все объекты имеют одинаковое, но настраиваемое множество атрибутов, то декомпозиция немного меняется: GO(ID), GOP(ID, PropertyName, [PropertyType]), GOD(GO.ID, GOP.ID, PropertyValue). Т.е. внешний ключ GO.ID из отношения "свойство" переносится в отношение "описание". Вот к такой схеме задача развёртывания строк в столбцы применима.

[remedius]

ясно.
К сожалению дело имела только с  уже встроенными функциями написанными на sql, а как дело обстоит с внешней библиотекой, если писать на Си, к примеру. Как там реализуются sql запросы? как использовать их? (я так понимаю, после того, как библиотека будет подключена через USE LIBRARY, таблицы будут видны и sql запросы будут по идеи выполняться. Но какой синтаксис, где можно посмотреть примеры?

[Dimka]

Вроде бы есть реализации языков, в которых SQL рассматривается как синтаксическое подмножество языка программирования общего назначения. Однако чаще всего SQL-запросы записываются в строчки и в таком виде передаются на исполнение СУБД.

Именно про это говорил x77, комментируя "дыры" в программах.

[remedius]

Как поступают в следующей ситуации: есть пользователь, у которого есть права на исполнение одной хранимой процедуры, в которой создается столбец как FK на таблицу, к которой у пользователя нет прав. Соответсвенно процедура возвращает фалз:(

[Dimka]

Идея следующая (на примере SQL Server):

Для начала потребуется логин рядовых пользователей к СУБД вообще.

Код: (Text)

EXEC sp_addlogin
  @loginame = 'TestUser',
  @passwd   = 'TestPassword'
GO

Этот логин позволяет пользователю подключится к СУБД, но не даёт права доступа к базам данных.

Далее потребуется для экспериментов тестовая база данных, содержащая некую таблицу и хранимую процедуру, выполняющую некие действия.

Код: (Text)

CREATE DATABASE Test
GO

USE Test
GO

CREATE TABLE Test(Id INT IDENTITY(1, 1) PRIMARY KEY)
GO

CREATE PROCEDURE spTest
AS
  SELECT *
  FROM Test

Теперь нужно позволить пользователю с созданного логина работать с БД. Однако мы заранее знаем, что, с одной стороны, пользователи будут ограничены в правах, но, с другой стороны, должен существовать доверенный пользователь, которому разрешено выполнять действия, запрещённые обычным пользователям. Поэтому мы создаём обычного пользователя базы данных, которому разрешено мало что, и пустую группу доверенных пользователей, членам которой разрешено больше, чем обычным пользователям. Поскольку группа доверенных пользователей пуста, ни один пользователь базы данных не сможет самостоятельно выполнять операции, разрешённые для этой группы.

Код: (Text)

EXEC sp_adduser
  @loginame   = 'TestUser',
  @name_in_db = 'TestUser'

EXEC sp_addgroup
  @grpname = 'Trustees'

Теперь у обычных пользователей есть доступ к базе данных, и есть пустая группа доверенных пользователей.

Настраиваем права:

Пустой группе доверенных пользователей разрешаем: во-первых, исполнять хранимую процедуру, во-вторых, делать запросы к таблице (то, что выполняется внутри хранимой процедуры). Всё остальное запрещено.

Код: (Text)

REVOKE ALL TO Trustees
GRANT SELECT ON Test TO Trustees
GRANT EXECUTE ON spTest TO Trustees WITH GRANT OPTION

Право исполнения хранимых процедур имеет специальную пометку, позволяющую делегировать это право другим пользователям. Права выполнения других действий не делегируются.

Обычным пользователям базы данных разрешаем только исполнять хранимые процедуры, но делать это с правами доверенных пользователей, а всё остальное запрещаем.

Код: (Text)

REVOKE ALL TO TestUser
GRANT EXECUTE ON spTest TO TestUser AS Trustees

Теперь права настроены, проверяем результат. Подключаемся с помощью созданного нами логина к СУБД и выполняем тест:

Код: (Text)

USE Test
GO

SELECT *
FROM Test

EXEC spTest

Получаем результат:

Код:

Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'Test', database 'Test', owner 'dbo'.

Id          
----------- 

(0 row(s) affected)

СУБД не позволила нам выполнить запрос от имени пользователя, но позволила выполнить хранимую процедуру, содержащую такой запрос - хранимая процедура вернула результат запроса.

Убираем за собой:

Код: (Text)

USE master
GO

DROP DATABASE Test
GO

EXEC sp_droplogin
  @loginame = 'TestUser'

[remedius]

Спасибо! Очень полезный пост.
А вот группы это случайно не роли ли? можно ли группу создать вручную в Enterprise Manager?

[Dimka]

Нет, группы - это не роли. У SQL Server существует 2 способа авторизации: Windows NT и собственный.

В случае авторизации средствами Windows NT доступ к базам данных и объектам в них получают доменные пользователи и группы. Для пользователей и групп настраиваются права.

В случае собственной авторизации необходимо создать серверный логин, а также  пользователей баз данных и роли. Роли бывают серверными и баз данных. В серверные роли включаются логины (без привязки к базам данных). В стандартные роли баз данных включаются пользователи баз данных и другие роли. Для пользователей и ролей настраиваются права.

Помимо стандартных ролей баз данных имеются особые роли для приложений баз данных. В эти роли никого нельзя включать. Эти роли имеют отдельные пароли. Приложение, желающее воспользоваться ролью приложения баз данных должно активировать роль с помощью хранимой процедуры sp_setapprole. Права ролей приложений баз данных полностью перекрывают все права логина, пользователя, ролей пользователя, доменного пользователя или группы.

Для приведённого выше примера можно заменить группу на роль базы данных.

P.S. Меня же более интересует, как выдать права пользователю на модификацию таблицы (ALTER TABLE). А вот в SQL Server 2000 и более ранних, похоже, нет. С другой стороны группу или роль недостаточно включить в какую-нибудь системную роль (например, db_ddladmin), поскольку права этой роли не отмечены с помощью WITH GRANT OPTION и не могут делегироваться в секции AS конструкции GRANT...

В SQL Server 2005 есть GRANT ALTER, поэтому там такой проблемы нет. Однако вышеприведённые вызовы хранимых процедур для 2005 сервера можно заменить на соответствующие синтаксические конструкции CREATE LOGIN, CREATE USER, CREATE ROLE, DROP LOGIN.

[remedius]

мда, видимо-то не стрельнуло:)
значит так. У меня есть процедура, которая добавляет колонку в таблицу пользователя (у него на создание и т.д. к этой таблицы есть права). При чем эта колонка помечается как FK, на колонку другой таблицы, к которой у него нет пользователя. Проделав все предыдущее сказанное, все равно получаю ошибку:

User does not have correct permissions on referenced table 'sys_objects' to crea
te foreign key 'FK__test_table__spid__160F4887'.
Could not create constraint. See previous errors.

Видимо, мне еще что-то разрешить пользователя делать?

[remedius]

Код:

CREATE PROCEDURE sp_create_spacial_index @table_name varchar(32), @column_name varchar(32)=NULL
AS

DECLARE @s varchar(500)

IF NOT EXISTS (SELECT * FROM sysobjects WHERE name = @table_name)
BEGIN
	RETURN 1
END

IF(@column_name IS NULL)
	SET @column_name='spid'
ELSE
	IF EXISTS (SELECT * FROM syscolumns WHERE name = @column_name and id IN (SELECT id FROM sysobjects WHERE name=@table_name ))
		RETURN 1

SET @s = 'ALTER TABLE '+ @table_name+' ADD '+ @column_name+' int NULL REFERENCES sys_objects(id_obj_geom)  ON DELETE CASCADE'
EXEC (@s)

RETURN 0

GO

Вот сама процедура, которая вызывается пользователем. Но вот к таблице sys_objects у него-то прав нет:(