Skip to content
logo
platformaerp.com

Podstawowe tabele uzytkownikow, grup

Obiekty bazy danych platformaCRM

SQL
1
2
3
4
5
6
7
8
/*TABELE konfiguracyjne*/
select * from [user].[group]            -- grupy
select * from [user].[user]             -- uzytkownicy
select * from [user].[userGroup]        -- polaczenie uzytkownikow z grupami
select * from [user].[groupRelation]
select * from [user].[rolePermission]   -- polaczenie permission z grupami
    select [user].[group] ug inner join [user].[rolePermission] rp on ug.groupid=rp.groupid
select * from [configuration].[screen] where application='system' -- przechowuje inforamcje o ekranach

Dodanie wszystkich grup do uprawnien

SQL
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
declare @userid uniqueidentifier= 'FD6F59BE-E2C2-4896-8F43-5F47331ADC32' -- select * from [user].[user] where username ='m.kotynia'
INSERT INTO [user].[userGroup]
([groupID]
,[userID]
,[addDATE]
,[addUSERNAME]
,[modDATE]
,[modUSERNAME])
select
groupid
,@userid
,getutcdate()
,'sys'
,getutcdate()
,'sys' from .[user].[group]
where groupid not in (select groupid from [user].[userGroup] where userid =@userid)

Kopiowanie użytkowników

SQL
1
2
3
4
exec  [user].[user_copy]
@username= 'marcin.kotynia',
@fromUserid= '8fd4e8a1-d3b7-47d9-8121-bbf36ea7e6af',
@toUserID= 'd8936fd5-d0f6-4b9d-9de2-bd55131073a7' --test35

Zakładanie użytkownika administracyjnego z poziomu bazy danych

SQL
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
set xact_abort on -- roolback in case of any error

declare @userid uniqueidentifier = newID();
declare @randompassword varchar(30) = (select left(cast(NEWID() as varchar(36)),11))
Begin Transaction
    --dodanie uzytkownika
    insert into [user].[user]
    ([userID] ,[userName] ,[userNameShort]  ,[userNameFull] ,[email] ,[password] ,[organizationalGroupID] ,[IsApproved])
    values
    (@userid,'Pomoc','sys','Pomoc Platforma','pomoc@platformaerp.com',@randompassword ,(select top 1 groupid from   [user].[userGroup] ),1)

    --dodanie brakujacych
    INSERT INTO [user].[usergroup]
                ([groupid],
                 [userid],
                 [addusername],
                 [modusername])
    SELECT g.groupid,
           @userid,
           'sys',
           'sys'
    FROM   [user].[group] g
           LEFT JOIN (SELECT groupid
                      FROM   [user].[usergroup]
                      WHERE  userid = @userid) existt
                  ON g.groupid = existt.groupid
    WHERE  existt.groupid IS NULL

print 'PASWORD ' + @randompassword
print 'Pamietaj o reset  http://platforma/reset'
Commit Transaction

--optional cleanupfor not connected grops
--delete  [user].[usergroup] where userid not in( select userid from [user].[user])

Oczyszczanie grup użytkowników

SQL
1
2
3
--oczyszczenie brakujacych
delete from [user].[usergroup] where userid not in (select userid from [user].[user])
delete from [user].[usergroup] where groupid not in (select groupid from [user].[group])

Audyt uprawnień

SQL
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
select
    g.groupName GRUPA,
    --permission,
    uu.userName UZYTKOWNIK,
    isnull(sdg.hasgroup,'') SDG,
    isnull(sdp.hasgroup,'') SDP
from
    [user].[group] g
    inner join [user].[rolePermission] rp on g.groupid=rp.groupid
    inner join [user].[userGroup] ug on ug.groupid = g.groupid
    inner join [user].[user] uu on uu.userid = ug.userID
    left join
        (select 'TAK' hasgroup,ug.userID from [user].[group] g inner join [user].[userGroup] ug on ug.groupid = g.groupid
        where g.groupShortcut='SDG') sdg on sdg.userid = uu.userid
    left join
        (select 'TAK' hasgroup,ug.userID from [user].[group] g inner join [user].[userGroup] ug on ug.groupid = g.groupid
        where g.groupShortcut='SDP') sdp on sdp.userid = uu.userid
where
    permission ='invoice_insertupdate'
order by 2