Шаг 7 - Описание данных, с которыми работает "Администратор Пользователей"

Ну вот, и до пользователей добрались... :-)) - что делать? Писать - то надо... :-))

И так, пользователи.... а для начала, еще раз напоминаю, что система планируется РАСПРЕДЕЛЕННОЙ (т.е. состоящей из множества территориально- разбросанных "площадок учета"). У каждой площадки есть свой набор пользователей, и, что главное для нашего разговора сейчас - на каждой площадке есть СВОЙ сервер данных.

Да, о чем это я? Ну.... да о пользователях, о них, родимых, и что? Пользователи... Считаем, что мы должны иметь возможность "централизовано" их администрировать. Что это значит? Мы должны иметь возможность:

А теперь подробнее (или с начала). Итак, "создать" пользователя? - да все "просто"... "берем" SQLTalk, "коннектимся" к базе, говорим "grant ..." и, пользователь создан :-)) дать права на таблицы... еще раз говорим "grant ....". Но, проблемы... проблемы вот где: Серверов - то много, и они (сервера) разбросаны... КАК (физически) "прицепится" ко всем, да еще и с админовскими правами??... и, Вы думаете ЭТО все? :-) хи-хи... не... "приколы" только начинаются: Документы (а ВЫ помните, я говорил, что система "состоит" из документов?) хранят данные в таблицах... и надо определять права на доступ к данным... а потом права придется редактировать... и все ЭТО делать для несметного количества пользователей, и на НЕИЗВЕСТНОМ количестве серверов и документов, короче, почти не реально.

Естественно было решить, что нужно максимально "механизировать" процесс...

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

Чтобы хотелось видеть "на выходе"? Пользователя добавили в список. Далее, сказали, в какие группы он входит, в какой базе будет работать, и чтобы система сама выдала права пользователю, и, естественно, НА НУЖНОЙ базе (сервере)

Что для этого надо? Списки - пользователей, групп, баз данных, и собственно прав...

Когда? Что "когда"? Именно, раздача, когда ОНА должна происходить "раздача прав"? Вот достаточно интересный вопрос. По-идее, если мы говорим, что у нас будет некий "менеджер", то пусть он и раздает права... все правильно, вот только... Баз Данных - то у нас МНОГО, и каналы связи... а на самом деле, про репликацию я говорил? :-) - вроде "ДА", и я решил, что раздачу прав надо приурочить именно к этому процессу... :-) - появилась на "удаленной Базе" в списке пользователей новая запись (методом репликации данных), и тут же пользователю дали право "работать с базой"... а удалили строку из таблицы "прав" и... ну естественно, права, которого "не стало" в таблице, сразу лишили и пользователя... Мне показалось, что "круче всего" будет организовать ЭТОТ процесс, с использованием системы триггеров.

Начнем со списка баз данных, принадлежащих системе:

CREATE TABLE SYSADM.ListDBase (
	ID VARCHAR(18) NOT NULL,
	NAME VARCHAR(32) NOT NULL,
	DBNAME VARCHAR(8) NOT NULL,
	PARENS VARCHAR(18),
	DBPREFF VARCHAR(2),
	ABOUT LONG VARCHAR
)
/

ID - Идентификатор базы данных.

NAME - Логическое название базы - то, которое проще употреблять и запомнить.

DBNAME - имя базы (так, как она будет называться на компьютере...).

PARENS - Идентификатор "родительской" базы данных - той базы, с которой данная будет обмениваться данными в режиме "родитель-потомок".

DBPREFF - Префикс базы данных - то, что потом будет использоваться для создания уникальных индексов.

ABOUT - Дополнительная информация о базе...

Теперь группы... зачем? Понятно, что так удобнее работать... но, (сервер (SQL) тот, для которого в настоящее время реализована вся система, "групп" не поддерживает)... вот, и пришлось их моделировать... - т.е. "группировка" существует, но "на бумаге", "процедура раздачи прав" :-)) "сообразуясь" со списком принадлежности, раздает собственно права на работу с документами и данными.... и получается, что права на работу с данными можно дать прямо группе, а всем пользователям, в том числе и будущим, права "раздадутся сами", как только пользователь "попадет в группу".

И так, список групп:

CREATE TABLE SYSADM.LISTGROUP (
	ID VARCHAR(18) NOT NULL,
	NAME VARCHAR(64) NOT NULL,
	ABOUT VARCHAR(254)
)
/

ID - Идентификатор группы.

NAME - Название группы.

ABOUT - описание группы.

А вот и список пользователей:

CREATE TABLE SYSADM.LISTUSER (
	ID VARCHAR(18) NOT NULL,
	Permit_LEVEL CHAR(1),
	NAME VARCHAR(64) NOT NULL,
	WRKPASS VARCHAR(254),
	ABOUT VARCHAR(254),
	ID_DB_LOCATE VARCHAR(18))
/

ID - Идентификатор.

Permit_LEVEL - Уровень доступа - пользователь может иметь право работать с базой, создавать новые объекты, администрировать... соответственно, нами были введены уровни доступа "C", "R", "D"... (или не нами, а разработчиками сервера, и называются они (уровни доступа) "connect", "resource", "dba"), а я просто сделал соответствие).

NAME - Имя пользователя.

WRKPASS - Пользовательский пароль.

ABOUT - Описание пользователя.

ID_DB_LOCATE - Идентификатор той базы банных, с которой будет работать пользователь... (из списка баз данных)

Ну, и естественно, ограничение:

ALTER TABLE SYSADM.LISTUSER FOREIGN KEY LUser001 (
ID_DB_LOCATE) REFERENCES SYSADM.ListDBase
/

Хорошо, ввели список пользователей, и список групп... ну и что? Нужна, на самом деле нужна некая табличка, в которой содержался бы список "перекрестных" ссылок, о том, кто из пользователей состоит в каких группах. Эта табличка очень активно используется при редактировании, да и при раздаче прав.

CREATE TABLE SYSADM.CROSSUSGROUP (
	IDUSER VARCHAR(18) NOT NULL,
	IDGROUP VARCHAR(18) NOT NULL)
/

IDUSER - Идентификатор пользователя.

IDGROUP - Идентификатор группы.

Ограничения:

ALTER TABLE SYSADM.CROSSUSGROUP FOREIGN KEY CrossUs001 (
IDUSER ) REFERENCES SYSADM.LISTUSER
/

ALTER TABLE SYSADM.CROSSUSGROUP FOREIGN KEY CrossUs002 (
IDGROUP ) REFERENCES SYSADM.LISTGROUP
/

Вот... а теперь уже интереснее: список прав на объекты базы... что ЭТО может быть? Документы... Таблицы... да, и еще... "ветки" на дереве "субконт" что ЭТО такое, я расскажу чуть позже, а пока, просто таблица:

CREATE TABLE SYSADM.LISTRIGTHUS (
	IDUSER VARCHAR(18),
	IDGROUP VARCHAR(18),
	IDDOC VARCHAR(18),
	IDTABLE VARCHAR(18),
	RIGTH DOUBLE PRECISION NOT NULL,
	IDSUBC VARCHAR(18)
)
/

IDUSER - Идентификатор пользователя.

IDGROUP - Идентификатор группы. Тут получается вот что: список прав, а права могут принадлежать или группе, или пользователю лично. Вот поэтому и пришлось сделать 2 колонки. Хотя, может я и не прав. Может, стоило бы заменить их на одну, не знаю...

IDDOC - Идентификатор документа.

IDTABLE - Идентификатор таблицы.

RIGTH - Права на различные действия. Мы решили их оформить в виде целых чисел каким образом? А вот: Список действий, которые может производить пользователь:

Каждому действию соответствует соответствующий разряд, и, в итоге, получается некое число... вида 110000 (что дает право пользователю исполнять и разносить некий документ ) или, для таблицы 1100 (просматривать и добавлять данные)... или, допустим, 1111 - и можно делать все - и смотреть, и добавлять, и изменять, и удалять... (надеюсь я нормально рассказал о идее)

IDSUBC - Идентификатор "узла" на дереве субконт...(а вот об ЭТОМ поговорим позже...)

Ограничения, как же без них:

ALTER TABLE SYSADM.LISTRIGTHUS FOREIGN KEY RIGTHUS001 (
IDUSER ) REFERENCES SYSADM.LISTUSER
/

ALTER TABLE SYSADM.LISTRIGTHUS FOREIGN KEY RIGTHUS002 (
IDGROUP ) REFERENCES SYSADM.LISTGROUP
/

ALTER TABLE SYSADM.LISTRIGTHUS FOREIGN KEY RIGTHUS001 (
IDDOC ) REFERENCES SYSADM.LISTPATTERN
/

ALTER TABLE SYSADM.LISTRIGTHUS FOREIGN KEY RIGTHUS002 (
IDTABLE ) REFERENCES SYSADM.LPATTABLES
/

А вот ЭТО ограничение наложим позже.. - как только опишем табличку...

ALTER TABLE SYSADM.LISTRIGTHUS FOREIGN KEY RIGTHUS002 (
IDTABLE ) REFERENCES SYSADM.subconto
/

Ну, а теперь "прелюдия" завершилась, пора заняться правами. И, начинаем описание триггеров - наши "рабочие" таблицы:

"LISTUSER" - что происходит с данными в этой таблице? - Можно пользователя добавить, удалить, или подредактировать его параметры... соответственно, нам нужно три триггера прицепить к этой таблице... - на "добавить", "изменить", "удалить" строки...

"LISTGROUP" - группы... просто группы... и, никаких триггеров... они не нужны... :-)

"CROSSUSGROUP" - вот это как раз очень важная таблица, и с ней связывается пара триггеров - на добавление, и удаление строк...

"LISTRIGTHUS" для работы на эту таблицу сделаем 3 триггера.

В общем, получается "компашка" из 8-ми штук... :-) Но, для начала покажем процедуры для работы.

Процедура определения пользователя:

STORE SYSADM.SETGRUS Procedure: SetGrUs
	Parameters
		String: strUserName
		String: strUserPassv
		String: strLocAutori
	Local variables
		String: strCommand
		String: strUserAutoriLevel
	Actions
		On Procedure Execute
			Set strCommand = 'grant connect to ' || strUserName
			Set strCommand = strCommand || ' identified by ' || strUserPassv
			Call SqlImmediate ( strCommand )
! раздам права на исполнение процедур, которые нужны любому пользователю нашей системы.
			Set strCommand = ' grant execute on SYSADM.GetUsersID to ' || strUserName
			Call SqlImmediate ( strCommand )
			Set strCommand = 'grant execute on SYSADM.GETMAXID to ' || strUserName
			Call SqlImmediate ( strCommand )
! а вот дальше - проверка уровня аккредитации, и раздача слоников.
			If strLocAutori = 'R'
				Set strCommand = ' grant resource to ' || strUserName
				Call SqlImmediate ( strCommand )
			If strLocAutori = 'D'
				Set strUserAutoriLevel = 'dba'
				Set strCommand = ' grant dba to ' || strUserName
				Call SqlImmediate ( strCommand )
/

Теперь набор команд, которые нам понадобятся в дальнейшем. И, сначала команда подготовки списка таблиц, права на которые описаны в "LISTRIGTHUS", и уже присвоены в таблице... селект получился достаточно большим, так как захотелось иметь сразу и список того, что нужно, и список того, что уже есть в базе.

STORE SYSADM.GETLIRIGALL select c.name, b.TabName,
	@if (sum(@mod( @int( Rigth/1000 ), 10 )), 1, 0) AS Sel,
	@if (sum(@mod( @int( Rigth/100 ), 10 )), 1, 0) AS Ins,
	@if (sum(@mod( @int( Rigth/10 ), 10 )), 1, 0) AS Upd,
	@if (sum(@mod( @int( Rigth/1 ), 10 )), 1, 0) AS Del,
	@if ( @length( DELETEAUTH || INSERTAUTH || SELECTAUTH || UPDATEAUTH), 1, 0 )
from ListRigthUs a, LPatTables b, LISTUSER c, ListDBase d, SYSTABAUTH
where
	a.IDTABLE = b.ID 
	and a.IDUSER = c.ID
	and c.ID = :1
	and c.IDSERVLOCATE = d.SR$ID
	and @scan (DBNAME, @upper ( :2 ) ) = 0
	and GRANTEE (+) = @upper ( c.name )
	and TTNAME (+) = @upper ( b.TabName )
group by 1, 2, 7
union
select c.name, b.TabName,
	@if (sum(@mod( @int( Rigth/1000 ), 10 )), 1, 0),
	@if (sum(@mod( @int( Rigth/100 ), 10 )), 1, 0),
	@if (sum(@mod( @int( Rigth/10 ), 10 )), 1, 0),
	@if (sum(@mod( @int( Rigth/1 ), 10 )), 1, 0),
	@if ( @length( DELETEAUTH || INSERTAUTH || SELECTAUTH || UPDATEAUTH), 1, 0 )
from ListRigthUs a, LPatTables b, LISTUSER c, CrossUsGroup d, ListDBase e, SYSTABAUTH
where
	a.IDTABLE = b.ID
	and a.IDGROUP = d.IDGroup
	and c.ID = d.IDUser
	and a.IDTABLE not in (select IDTABLE from ListRigthUs where IDUSER = d.IDUser )
	and c.ID = :1
	and c.IDSERVLOCATE = e.SR$ID
	and @scan (DBNAME, @upper ( :2 ) ) = 0
	and GRANTEE (+) = @upper ( c.name )
	and TTNAME (+) = @upper ( b.TabName )
group by 1,2, 7
union
select c.name, b.TabName, 0, 0, 0, 0,
@if ( @length( DELETEAUTH || INSERTAUTH || SELECTAUTH || UPDATEAUTH), 1, 0 )
from LPatTables b, LISTUSER c, CrossUsGroup d, ListDBase e, SYSTABAUTH
where
	c.ID = d.IDUser
	and c.ID = :1
	and c.IDSERVLOCATE = e.ID
	and @scan (DBNAME, @upper ( :2 ) ) = 0
	and GRANTEE = @upper ( c.name )
	and TTNAME = @upper ( b.TabName )
	and c.ID not in ( select a.IDUSER from ListRigthUs a where a.IDTABLE = b.ID )
	and c.ID not in ( select d.IDUSER from ListRigthUs a, CrossUsGroup d
where a.IDTABLE = b.ID and a.IDGROUP = d.IDGroup )
union
select c.name, b.TabName,
	@if (sum(@mod( @int( Rigth/1000 ), 10 )), 1, 0),
	@If (sum(@mod( @int( Rigth/100 ), 10 )), 1, 0),
	@If (sum(@mod( @int( Rigth/10 ), 10 )), 1, 0),
	@If (sum(@mod( @int( Rigth/1 ), 10 )), 1, 0),
	0
from ListRigthUs a, LPatTables b, LISTUSER c, ListDBase e
where
	a.IDTABLE = b.ID
	and ( a.IDUser = c.ID or a.IDGroup in (select q.IDGroup from CrossUsGroup q where q.IDUser = c.ID))
	and c.ID = :1
	and c.IDSERVLOCATE = e.ID
	and @upper (DBNAME ) = @upper ( :2 )
group by 1, 2
/

А теперь команда удаления прав пользователя...

STORE SYSADM.REVUSTABLERIGTH Procedure: RevUsTablRigth
Parameters
	String: strUserName
	String: strTableName
Local variables
	String: Grant1
	Actions
	On Procedure Execute
		Set Grant1 = ' revoke all on ' || strTableName || ' from ' || strUserName
		Call SqlImmediate ( Grant1 )
/

Присвоение прав пользователя на таблицу:

STORE SYSADM.SETUSTABLERIGTH Procedure: SetUsTableRigth
Parameters
	String: strUserName
	String: strTableName
	Number: nSel
	Number: nIns
	Number: nUpd
	Number: nDel
Local variables
	String: Grant3
	String: Grant2
Sql Handle: hsqlWWW
	Number: nOk
Actions
	On Procedure Execute
	Set Grant2 = ''
	If nSel = 1               
	If Grant2 != ''
	Set Grant2 = Grant2 || ', '
	Set Grant2 = Grant2 || 'select'
	If nIns = 1               
	If Grant2 != ''
	Set Grant2 = Grant2 || ', '
	Set Grant2 = Grant2 || 'insert'
	If nUpd = 1
	If Grant2 != ''
	Set Grant2 = Grant2 || ', '
	Set Grant2 = Grant2 || 'update'
	If nDel = 1
	If Grant2 != ''
	Set Grant2 = Grant2 || ', '
	Set Grant2 = Grant2 || 'delete'
	If Grant2 != ''
	Set Grant3 = ' grant ' || Grant2 || ' on ' || strTableName
	Set Grant3 = Grant3 || ' to ' || strUserName
	Call SqlImmediate ( Grant3 )
/

Вот и раздача прав на все таблицы, права на которые мы дали пользователю...

STORE SYSADM.SETUSERTRIGTH Procedure: SetUserTRigth static
Parameters
	String: strIDUser
Local variables
	Sql Handle: hsqlWWW
	Sql Handle: hsqlW01
	Number: nOk
	Number: numIi
	Number: nCount
	Number: nIsSet
	String: strBaseName
	String: strLocNameUser
	String: strTableName
	Boolean: bIsOk
	Number: nSel
	Number: nIns
	Number: nUpd
	Number: nDel
Actions
	On Procedure Startup
	Call SqlConnect ( hsqlWWW )
	Call SqlConnect ( hsqlW01 )
	On Procedure Execute
	When SqlError
		Set nOk = SqlError ( hsqlWWW )
		Set nOk = SqlError ( hsqlW01 )
	Return FALSE
		Set nCount = 0
! имя "рабочей базы"
	Call SqlGetParameterAll ( hsqlWWW, 2001, nOk, strBaseName, bIsOk )
! нужно получить список всех табличек, на
которые пользователь уже имеет права, и те права,
которые мы хотим дать...
	Call SqlRetrieve ( hsqlWWW, 'SYSADM.GetLiRigAll',
		' :strIDUser, :strBaseName ',
		':strLocNameUser,:strTableName, :nSel, :nIns, :nUpd, :nDel, :nIsSet ')
	Call SqlExecute( hsqlWWW )
	Call SqlGetResultSetCount ( hsqlWWW, nCount )
	Set numIi = 0
	Set nIsSet = 0
	If ( nCount > 0 )
		Loop
	Call SqlFetchNext ( hsqlWWW, nOk )
	If (nOk != FETCH_Ok ) OR (numIi > 1000 )
		Break
	Set numIi = numIi +1
! а вот теперь переберем все строчки, что мы
выбрали, и, если нужна "переустановка" прав,
то надо ЭТО сделать... и, для начала, удалить права                   
	If nIsSet = 1
	Call SqlRetrieve ( hsqlW01, 'SYSADM.RevUsTableRigth',
		' :strLocNameUser, :strTableName ', '' )
	Call SqlExecute ( hsqlW01 )
! и, если есть что "раздать", то надо их
дать...                   
	If ( nSel + nIns + nUpd + nDel > 0 )
	Call SqlRetrieve ( hsqlW01, 'SYSADM.SetUsTableRigth',
		' :strLocNameUser, :strTableName, :nSel, :nIns, :nUpd, :nDel ', '')
	Call SqlExecute ( hsqlW01 )
		On Procedure Close
	Call SqlDisconnect ( hsqlWWW )
	Call SqlDisconnect ( hsqlW01 )
/

Получение Идентификатора пользователя и базы...

STORE SYSADM.GETCURBASEID select b.ID, a.ID
from ListDBase b, listuser a
where
	@upper ( b.DBNAME ) = @upper ( :1 )
	and @upper ( a.Name ) = @upper ( :2 )
/

А вот теперь все описанное сведем "до кучи"... И получим действия, которые должны произойти после добавления пользователя:

STORE SYSADM.INSUS220402 Procedure: InsUs220402 static
Parameters
	String: strNameUser
	String: strPassUser
	String: strBaseID
	String: strIDUser
	String: strLocAutoriLevel
Local variables
	Sql Handle: hsqlWWW
	Number: nOk
	String: strBaseName
	String: strIDLocBase
	String: strIDName
	Boolean: bIsOk
Actions
	On Procedure Startup
		Call SqlConnect ( hsqlWWW )
	On Procedure Execute
		When SqlError
		Set nOk = SqlError ( hsqlWWW )
		Return FALSE
! получение имени текущей базы.
		Call SqlGetParameterAll ( hsqlWWW, 2001, nOk, strBaseName, bIsOk )
! проверим, имеет ли ЭТОТ пользователь "авторизацию" в текущей базе...
а заодно и идентификаторы и базы и пользователя.
		Call SqlRetrieve ( hsqlWWW, 'SYSADM.GetCurBaseID',
		' :strBaseName, :strNameUser ', ' :strIDLocBase, :strIDName ' )
		Call SqlExecute( hsqlWWW )
		Call SqlFetchNext ( hsqlWWW, nOk )
		If strIDLocBase = strBaseID
! а теперь проверим, и если совпадают
идентификатор текущей базы, и той, на которой
нужно авторизовать текущего пользователя, то
выполним ... и авторизуем его...               
		Call SqlRetrieve ( hsqlWWW, 'SYSADM.SetGrUs',
		' :strNameUser, :strPassUser, :strLocAutoriLevel ', '')
		Call SqlExecute ( hsqlWWW )
! а вот теперь можно раздать права на работу с таблицами.
		Call SqlRetrieve ( hsqlWWW, 'SYSADM.SetUserTRigth',
		' :strIDUser ', '')               
		Call SqlExecute ( hsqlWWW )

	On Procedure Close
		Call SqlDisconnect ( hsqlWWW )
/

Ну и вот, сам триггер... (на самом деле, я надеюсь, что синтаксис всего и так понятен)

CREATE TRIGGER SYSADM.INS_USLI220401 After Insert
ON SYSADM.LISTUSER
( Execute SYSADM.InsUs220402 ( LISTUSER.Name, LISTUSER.WrkPass, LISTUSER.IDSERVLOCATE,
LISTUSER.ID, LISTUSER.AutirLevel ) )
For Each Row
/

А вот что должно происходить, если произошли изменения в описании параметров пользователя. И для начала определение уровня "аккредитации" пользователя.

STORE SYSADM.GETUSRIGTH220403 select RESOURCEAUTH, DBAAUTH , 'C'
from SYSUSERAUTH
where
@upper ( name ) = @upper ( :1 )
/

Реальное удаление прав. Сначала посмотрим, ЧТО есть у пользователя:

STORE SYSADM.LISRREALRIGTH select TTNAME from SYSTABAUTH
where
@upper ( GRANTEE ) = @upper ( :1 )
/

А теперь уже собственно...

STORE SYSADM.USREVGRANTS Procedure: UsRevGrants
Parameters
	String: strUserName
Local variables
	String: strCSelect
	String: strSelWhere
	String: strSelInto
	Number: nOk
	Number: numIi
	String: strLockTabName
	Number: nCount
	String: Grant1
	String: Grant2
	String: Grant3
	Sql Handle: SqlW
	Number: NotCreate
	String: SetStatus
Actions
	On Procedure Startup
		When SqlError
			Set nOk = SqlError ( SqlW )
		Return FALSE
		Call SqlConnect ( SqlW )
	On Procedure Execute
		When SqlError
			Set nOk = SqlError ( SqlW )
		Return FALSE
! получим список таблиц, права на которые есть у пользователя.
		Call SqlRetrieve ( SqlW, 'sysadm.LisrRealRigth',
			' :strUserName ', ' :strLockTabName ' )
		Call SqlExecute( SqlW )
		Call SqlGetResultSetCount ( SqlW, nCount )
		Set numIi = 0
		Set Grant1 = ' revoke all on '
		Set Grant2 = ''
		If nCount > 0
			Set Grant2 = ''
		Loop
! если есть таблицы, на которые розданы права пользователю...
		Call SqlFetchNext ( SqlW, nOk )
		If (nOk != FETCH_Ok ) OR (numIi > 1000 )
			Break
		Set numIi = numIi +1
! переберем ВСЕ, и " сгенерим" команду, которую исполним...
		If Grant2 != ''
			Set Grant2 = Grant2 || ', '
			Set Grant2 = Grant2 || strLockTabName
		If Grant2 != ''
			Set Grant3 = Grant1 || Grant2
			Set Grant3 = Grant3 || ' from ' || strUserName
		Call SqlImmediate ( Grant3 )
	On Procedure Close
		Call SqlDisconnect ( SqlW )
/

А вот теперь уже просто удаление прав пользователя:

STORE SYSADM.REVGRUS Procedure: RevGrUs
Parameters
	String: strUserName
	String: strFlag
Local variables
	Number: nOk
	Number: numIi
	String: strLockTabName
	String: strCommand
	Sql Handle: SqlW
Actions
	On Procedure Startup
		Call SqlConnect ( SqlW )
	On Procedure Execute
		When SqlError
			Set nOk = SqlError ( SqlW )
		Return FALSE
		Call SqlRetrieve ( SqlW, 'sysadm.UsRevGrants',
			' :strUserName ', '' )
		Call SqlExecute( SqlW )
		If strFlag = 'D'
			Set strCommand = ' revoke DBA from ' || strUserName
		Call SqlImmediate ( strCommand )
		If strFlag = 'R'
			Set strCommand = ' revoke resource from ' || strUserName
		Call SqlImmediate ( strCommand )
			Set strCommand = ' revoke connect from ' || strUserName
		Call SqlImmediate ( strCommand )
/

Надо сменить "уровень доступа":

STORE SYSADM.CHANGEGRUS Procedure: ChangeGrUs
Parameters
	String: strUserName
	String: strOldFlag
	String: strNewFlag
Local variables
	String: strCommand
Actions
	On Procedure Execute
! Сначала Удалим... права...
	If strOldFlag = 'D'
		Set strCommand = ' revoke DBA from ' || strUserName
		Call SqlImmediate ( strCommand )

	If strOldFlag = 'R'
		Set strCommand = ' revoke resource from ' || strUserName
		Call SqlImmediate ( strCommand )

! потом добавим...
	If strNewFlag = 'R'
		Set strCommand = ' grant resource to ' || strUserName
		Call SqlImmediate ( strCommand )

	If strNewFlag = 'D'
		Set strCommand = ' grant dba to ' || strUserName
		Call SqlImmediate ( strCommand )
/

И вот, уже все составные части описаны, и можно показать триггерную процедуру:

STORE SYSADM.UPDUS220402 Procedure: UpdUs220402 static
Parameters
	String: strNameUser
	String: strOldPass
	String: strNewPass
	String: strOldBaseID
	String: strNewBaseID
	String: strOldAutoriLevel
	String: strNewAutoriLevel
Local variables
	String: strCommand
	String: strWhere
String: strInto
Sql Handle: hsqlWWW
Number: nOk
Number: nCount
String: strBaseName
String: strIDLocBase
String: strRESOURCE
String: strDBA
String: strUserAutoriLevel
String: strIDUsss
Boolean: bIsOk
Actions On Procedure Startup Call SqlConnect ( hsqlWWW ) On Procedure Execute When SqlError Set nOk = SqlError ( hsqlWWW ) Return FALSE !получим название текущей базы и Идентификаторы базы и пользователя Call SqlGetParameterAll ( hsqlWWW, 2001, nOk, strBaseName, bIsOk ) Call SqlRetrieve ( hsqlWWW, 'GetCurBaseID', ' :strBaseName, :strNameUser ', ' :strIDLocBase, :strIDUsss ' ) Call SqlExecute( hsqlWWW ) Call SqlFetchNext ( hsqlWWW, nOk ) ! и теперь можно получить и уровень "аккредитации"... Call SqlRetrieve ( hsqlWWW, 'sysadm.GetUsRigth220403', ' :strNameUser ', ' :strRESOURCE, :strDBA, :strUserAutoriLevel ' ) Call SqlExecute( hsqlWWW ) Call SqlGetResultSetCount ( hsqlWWW, nCount ) ! если пользователя ТУТ не было, то его НАДО создать... If ( nCount = 0 ) If ( ( strOldBaseID != strIDLocBase ) and ( strNewBaseID = strIDLocBase ) ) Call SqlRetrieve(hsqlWWW, 'SYSADM.SetGrUs', ' :strNameUser, :strNewPass, :strNewAutoriLevel ', '') Call SqlExecute ( hsqlWWW ) Call SqlRetrieve ( hsqlWWW, 'SYSADM.SetUserTRigth', ' :strIDUsss ', '') Call SqlExecute ( hsqlWWW ) ! если что - то есть, то можно работать... If ( nCount > 0 ) ! если изменился пароль, то что делать НЕ понятно.... :-(( ! If strOldPass != strNewPass ! Set strCommand = ' alter password ' || strNameUser ! Call SqlImmediate ( strCommand ) ! так, пользователь был (то есть, есть ПОКА), и его НАДО "убрать"... If ( ( strOldBaseID = strIDLocBase ) and ( strNewBaseID != strIDLocBase ) ) Call SqlRetrieve(hsqlWWW, 'SYSADM.RevGrUs', ' :strNameUser, :strOldAutoriLevel ', '') Call SqlExecute ( hsqlWWW ) If ( strOldAutoriLevel != strNewAutoriLevel ) and ( strOldBaseID = strNewBaseID ) ! смена "уровня" доступа Call SqlRetrieve(hsqlWWW, 'SYSADM.ChangeGrUs', ' :strNameUser, :strOldAutoriLevel, :strNewAutoriLevel ', '') Call SqlExecute ( hsqlWWW ) On Procedure Close Call SqlDisconnect ( hsqlWWW ) /

Вот, можно показать собственно триггер:

CREATE TRIGGER SYSADM.UPD_USLI220401 After Update
ON SYSADM.LISTUSER REFERENCING OLD oldTAble NEW NewTAble
( Execute sysadm.UpdUs220402 ( NewTAble.Name, oldTAble.WrkPass, NewTAble.WrkPass,
oldTAble.IDSERVLOCATE, NewTAble.IDSERVLOCATE, oldTAble.AutirLevel, NewTAble.AutirLevel ))
For Each Row
/

А вот теперь "позаботимся" о "выкусывании" прав пользователя, если закончились его "полномочия" в ЭТОЙ базе.

STORE SYSADM.DELUS220402 Procedure: DelUs220402 static
Parameters
	String: strNameUser
	String: strLocAutoriLevel
Local variables
	Boolean: bIsOk
	Number: nOk
	Number: nCount
	Sql Handle: hsqlWWW
	String: strBaseName
	String: strRESOURCE
	String: strDBA
	String: strUserAutoriLevel
Actions
	On Procedure Startup
            Call SqlConnect ( hsqlWWW )
	On Procedure Execute
		When SqlError
			Set nOk = SqlError ( hsqlWWW )
		Return FALSE
! получение имени текущей базы.
		Call SqlGetParameterAll ( hsqlWWW, 2001, nOk, strBaseName, bIsOk )
! проверим, имеет ли ЭТОТ пользователь "авторизацию" в текущей базе...
		Call SqlRetrieve ( hsqlWWW, 'sysadm.GetUsRigth220403',
			' :strNameUser ', ' :strRESOURCE, :strDBA, :strUserAutoriLevel ' )
		Call SqlExecute( hsqlWWW )
		Call SqlGetResultSetCount ( hsqlWWW, nCount )
		If nCount > 0
! если есть, то, вместо "здрасти" удалим его в доступе...
			Call SqlRetrieve ( hsqlWWW, 'SYSADM.RevGrUs',
			' :strNameUser, :strLocAutoriLevel ', '' )
			Call SqlExecute ( hsqlWWW )
	On Procedure Close
		Call SqlDisconnect ( hsqlWWW )
/
CREATE TRIGGER SYSADM.DEL_USLI220401 Before Delete
ON SYSADM.LISTUSER
( Execute SYSADM.DelUs220402 ( LISTUSER.Name, LISTUSER.AutirLevel ) )
For Each Row
/

Ну вот, с первой "тройкой" разобрались... надеюсь, я все рассказал хорошо... и, продолжим.

Теперь поглядим, что у нас получается с "кроссами":

CREATE TRIGGER SYSADM.INS_CROSS220429 After Insert
ON SYSADM.CrossUsGroup
( Execute SYSADM.SetUserTRigth ( CrossUsGroup.IDUSER ) )
For Each Row
/

Что происходит при добавлении пользователя в группу я сказал, а вот что произойдет, если его убирать подозреваю, что все, что можно, пользователю УЖЕ выдали, :-)) и сейчас надо "только" что - то забрать...

STORE SYSADM.REVUSERTRIGTH Procedure: RevUserTRigth static
Parameters
	String: strIDUser
	String: strIDGroup
Local variables
	Sql Handle: hsqlWWW
	Sql Handle: hsqlW01
	Number: nOk
	Number: numIi
	Number: nCount
	Number: nIsSet
	String: strBaseName
	String: strLocNameUser
	String: strTableName
	Boolean: bIsOk
	String: strPrCName
Actions
	On Procedure Startup
		Call SqlConnect ( hsqlWWW )
		Call SqlConnect ( hsqlW01 )
	On Procedure Execute
		When SqlError               
			Set nOk = SqlError ( hsqlWWW )
			Set nOk = SqlError ( hsqlW01 )
			Return FALSE
		Set nCount = 0
! получим имя текущей базы:
		Call SqlGetParameterAll ( hsqlWWW, 2001, nOk, strBaseName, bIsOk )
! полный перечень прав: (кстати, с учетом "групповых предпочтений")
		Call SqlRetrieve ( hsqlWWW, 'SYSADM.GetLiR220506',
			' :strBaseName, :strIDUser, :strIDGroup ',
			' :strLocNameUser, :strTableName ' )
		Call SqlExecute( hsqlWWW )
		Call SqlGetResultSetCount ( hsqlWWW, nCount )
		Set numIi = 0
		Set nIsSet = 0
		If nCount > 0
			Loop
		Call SqlFetchNext ( hsqlWWW, nOk )
		If (nOk != FETCH_Ok ) OR (numIi > 1000 )
			Break
		Set numIi = numIi +1
! собственно, выкусим:                   
		Call SqlRetrieve ( hsqlW01, 'SYSADM.RevUsTableRigth',
			' :strLocNameUser, :strTableName ', '' )
		Call SqlExecute ( hsqlW01 )

	On Procedure Close
		Call SqlDisconnect ( hsqlWWW )
		Call SqlDisconnect ( hsqlW01 )
/

CREATE TRIGGER SYSADM.DEL_CROSS220429 Before Delete
ON SYSADM.CrossUsGroup
( Execute SYSADM.RevUserTRigth ( CrossUsGroup.IDUSER, CrossUsGroup.IDGroup ) )
For Each Row
/

А вот что происходит, когда мы работаем собственно с правами:

STORE SYSADM.SELGRUS220506 select a.IDUser
from CrossUsGroup a
where
	a.IDGroup = :1
/

STORE SYSADM.INSTABRIG220415 Procedure: InsTabRig220415 static
Parameters
	String: strIDUser
	String: strIDGroup
Local variables
	Sql Handle: hsqlWWW
	Sql Handle: hsqlW00
	Number: nOk
	Number: numIi
	Number: nCount
	String: strLocIDUser
	Boolean: bIsOk
Actions
	On Procedure Startup
		Call SqlConnect ( hsqlWWW )
		Call SqlConnect ( hsqlW00 )
	On Procedure Execute
		When SqlError
			Set nOk = SqlError ( hsqlWWW )
			Set nOk = SqlError ( hsqlW00 )
		Return FALSE
		If strIDUser != ''
! установим права для одного пользователя
		Call SqlRetrieve ( hsqlWWW, 'SYSADM.SetUserTRigth',
			' :strIDUser ', '' )
		Call SqlExecute( hsqlWWW )
		If strIDGroup != ''
! мы работали с групповыми правами, и теперь
! надо их (права) отредактировать ВСЕМ членам
! группы... :-))
		Set nCount = 0
		Call SqlRetrieve ( hsqlWWW, 'SYSADM.SelGrUs220506',
			' :strIDGroup ', ' :strLocIDUser ' )
		Call SqlExecute( hsqlWWW )
		Call SqlGetResultSetCount ( hsqlWWW, nCount )
		If ( nCount > 0 )
			Set numIi = 0
		If nCount > 0
			Loop
! и мы просто переберем всех членов, и...                       
		Call SqlFetchNext ( hsqlWWW, nOk )
		If (nOk != FETCH_Ok ) OR (numIi > 1000 )
			Break
		Set numIi = numIi +1
! с каждым поступим, так же, как и с "единственным"...                       
		Call SqlRetrieve ( hsqlW00, 'SYSADM.SetUserTRigth',
			' :strLocIDUser ', '' )
		Call SqlExecute( hsqlW00 )
	On Procedure Close
		Call SqlDisconnect ( hsqlWWW )
		Call SqlDisconnect ( hsqlW00 )
/

CREATE TRIGGER SYSADM.INS_USRIGH220415 After Insert
ON SYSADM.ListRigthUs
( Execute SYSADM.InsTabRig220415 ( ListRigthUs.IDUSER, ListRigthUs.IDGROUP ) )
For Each Row
/

Процедуру обработки триггера я написал таким образом, что ее можно вызвать и в случае редактирования прав...

CREATE TRIGGER SYSADM.UPD_TABRIG220424 After Update
ON SYSADM.ListRigthUs
( Execute SYSADM.InsTabRig220415 ( ListRigthUs.IDUSER, ListRigthUs.IDGROUP ) )
For Each Row
/

А теперь посмотрим, что происходит при удалении права...

STORE SYSADM.GETCURRUSTABLE select a.Name, b.TABNAME
from LISTUSER a, LPATTABLES b, SYSTABAUTH d
where
	a.ID = :1
	and b.ID = :2
	and GRANTEE = @upper ( a.Name )
	and TTNAME = @upper ( b.TABNAME)
/

STORE SYSADM.SELGRUSTT220508 select a.Name, b.TABNAME
from LISTUSER a, LPATTABLES b, SYSTABAUTH d, CrossUsGroup c
where
	c.IDGroup = :1
	and a.ID = c.IDUser
	and b.ID = :2
	and GRANTEE = @upper ( a.Name )
	and TTNAME = @upper ( b.TABNAME)
/

STORE SYSADM.RUSTARIG220504 Procedure: RUsTaRig220504
Parameters
	String: strUserName
	String: strTabNAme
Local variables
	String: strCommand
Actions
	On Procedure Execute
		Set strCommand = ' revoke all on ' || strTabNAme
			|| ' from ' || strUserName
		Call SqlImmediate ( strCommand )
/

STORE SYSADM.DELTABRIG220415 Procedure: DelTabRig220415 static
Parameters
	String: strIDUser
	String: strIDGroup
	String: strIDTable
Local variables
	Sql Handle: hsqlWWW
	Sql Handle: hsqlW00
	Number: nOk
	Number: numIi
	Number: nCount
	String: strLocNameUser
	String: strLocNameTable
	Boolean: bIsOk
Actions
	On Procedure Startup
		Call SqlConnect ( hsqlWWW )
		Call SqlConnect ( hsqlW00 )
	On Procedure Execute
		When SqlError
			Set nOk = SqlError ( hsqlWWW )
			Set nOk = SqlError ( hsqlW00 )
			Return FALSE
		If strIDUser != ''
! что, удаляем право для пользователя? %-)
		Call SqlRetrieve ( hsqlWWW, 'SYSADM.GetCurrUsTable',
		' :strIDUser, :strIDTable ', ' :strLocNameUser, :strLocNameTable ' )
		Call SqlExecute( hsqlWWW )
		If strIDGroup != ''
! или для группы?                   
		Set nCount = 0
		Call SqlRetrieve ( hsqlWWW, 'SYSADM.SelGrUsTT220508',
			' :strIDGroup, :strIDTable ', ' :strLocNameUser, :strLocNameTable ' )
		Call SqlExecute( hsqlWWW )
		Call SqlGetResultSetCount ( hsqlWWW, nCount )
		If ( nCount > 0 )
			Set numIi = 0
		If nCount > 0
			Loop
! что - то есть, вот тогда и "выкусываем"...
		Call SqlFetchNext ( hsqlWWW, nOk )
		If (nOk != FETCH_Ok ) OR (numIi > 1000 )
			Break
		Set numIi = numIi +1
		Call SqlRetrieve ( hsqlW00, 'SYSADM.RUsTaRig220504',
			' :strLocNameUser, :strLocNameTable ', '' )
		Call SqlExecute( hsqlW00 )

	On Procedure Close
		Call SqlDisconnect ( hsqlWWW )
		Call SqlDisconnect ( hsqlW00 )
/

И собственно, триггер...

CREATE TRIGGER SYSADM.DEL_TABRIG220415 Before Delete
ON SYSADM.ListRigthUs
( Execute SYSADM.DelTabRig220415 ( ListRigthUs.IDUSER,
	ListRigthUs.IDGROUP, ListRigthUs.IDTABLE ) )
For Each Row
/

Ну вот, вроде описал... надеюсь, что понятно...


Загрузить проект | Предыдущий Шаг | Следующий Шаг | Оглавление
Автор Голиброда Александр.