Qlik Community

QlikView Security & Governance

Discussion Board for collaboration on QlikView Security and Governance.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
segador_
New Contributor II

QV Server, WebTickets. Group binding does not work

Hi! I try to setup QV Server work with DMS + Configurable ODBC + WebTickets combination.

QV Server  configured and everything fine but access with user groups not working...

Here is my "user table" specification:

CREATE TABLE [dbo].[tbl_A_qvUser](

    [entityid] [int] IDENTITY(1,1) NOT NULL,

    [name] [nvarchar](max) NOT NULL,

    [email] [nvarchar](max) NULL,

    [descr] [nvarchar](max) NULL,

    [password] [nvarchar](max) NOT NULL,

CONSTRAINT [PK_tbl_A_qvUser] PRIMARY KEY CLUSTERED

(

    [entityid] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Here is my "group table" specification:

CREATE TABLE [dbo].[tbl_A_qvGroupUser](

    [groupid] [int] NOT NULL,

    [memberid] [int] NOT NULL

) ON [PRIMARY]

The names of the groups wound up as users in the table tbl_A_qvUser. Table tbl_A_qvGroupUser contains only links between users and groups.

ODBC Configuration

I checked the User Management page. Users appear from an ODBC connection.

Then I set up access to the document for the groups from tbl_A_qvUser.

Then when i go to AccessPoint (Using a user from a group Аналитики) i don't find document moves database.

Tags (4)
1 Solution

Accepted Solutions
Not applicable

Re: QV Server, WebTickets. Group binding does not work

when requesting a ticket I'm assuming you are using SQL\burobin as the user name in the ticket request.  Are you using IIS or QlikView Web server for your ticketing mechanism?

This behavior is odd.  It's going to come down to a very small thing either in the web ticket code, request, or something really obscure.

Please grab this file (Dropbox - webticketsample.aspx) and place it onto your server.  You will have to make some minor modifications to the getwebticket server url, but then you can run this code (which is very similar to yours) without authentication.  In this page, QlikView assumes authentication has already taken place.

jg

15 Replies
Employee
Employee

Re: QV Server, WebTickets. Group binding does not work

Hi,

To using web ticketing you will have to write your own authentication login page. You will have request the ticket from QVS for the user and then past that ticket back to QVS in an URL. Qlik does not do any authentication that is outside of Qlik.

Bill

segador_
New Contributor II

Re: QV Server, WebTickets. Group binding does not work

Thank You Bill!

I did alternate the sign-in page AccessPoint and users are authenticated through it (With this there is no problem). Here is the source code.

<%@ Page Language="C#" AutoEventWireup="true" %>

<%@ Import Namespace="System.Net" %>

<%@ Import Namespace="System.IO" %>

<%@ Import Namespace="System.Data.SqlClient" %>

<html>

    <head>

        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">

        <title>QlikView - Портал</title>

        <link href='http://fonts.googleapis.com/css?family=Ubuntu&subset=cyrillic' rel='stylesheet' type='text/css' />

        <link rel="shortcut icon" type="image/x-icon" href="favicon.png">

        <link rel="stylesheet" href="css/global.css" type="text/css" media="screen">

        <link rel="stylesheet" href="css/custom.css" type="text/css" media="screen">

    </head>

    <body>

        <div id="frame">

            <div id="nav_utility">

            </div>

            <div id="header">

                <!-- <a href=""><img src="css/img/logo_hor.png" alt="QlikView" id="logo_main"></a> -->

                <span class="docNum"></span>

                <span class="lastUpdated"></span>

            </div>

            <div id="loginBoxOuter">

            <div id="loginBox">

                <form runat="Server" id="MainForm" style="font-family: Ubuntu">

                    <span class="formTitle top">Имя пользователя:</span>

                    <span class="formField"><asp:TextBox runat="server" id="txtUser"></asp:TextBox></span>

                    <span class="formTitle">Пароль:</span>                  

                    <span class="formField"><asp:TextBox runat="server" TextMode="Password" id="txtPassword"></asp:TextBox></span>

                    <asp:Button ID="GO" runat="server" class="loginSubmitButton" onclick="GO_Button_Click" />

                </form>

            </div>

            </div>

        </div>

        <div id="footer">

        </div>

 

</body>

</html>

<script language="c#" runat="server">

      

        //Firsly Set up a few properties

        static string QlikViewServerURL = @"http://localhost/QVAJAXZFC/getwebticket.aspx";  // Адрес сервера Qlikview для запроса тикета

        static string QlikViewServer = @"http://myserver.com/";                        // Адрес сервера Qlikview для перехода

        static string ticketinguser = "mylogin";                                           // Имя пользователя компьютера с правами администратора QlikView

        static string ticketingpassword = "mypassword";                                      // Пароль пользователя

        static string document = "";                                                        // Если значение пустое, то входим в AccessPoint. Иначе открываем документ

        protected void Alert(string message)

        {

            Response.Write("<div class=\"error\">"+ message + "</div>");

        }

        protected void GO_Button_Click(object sender, EventArgs e)

        {

            string username = txtUser.Text;

            string password = txtPassword.Text;

            string groups = "";

            // Проверка пользователя и пароля

            bool loginOK = ValidateUser(username, password);

            // Если проверка пользователя и пароля успешна, то пытаемся получить тикет

            string ticket = "";

            if (loginOK)

            {

                // Получаем тикет

                ticket = getTicket(username, groups, ticketinguser, ticketingpassword); // add groups into the empty string if required

                // Строим строку для переадресации используя тикет

                string RedirectLink = "";

                if (document.Length > 0)

                {

                    // Открытие отдельного документа

                    RedirectLink = QlikViewServer + "/qvajaxzfc/authenticate.aspx?type=html&try=/qvajaxzfc/opendoc.htm?document=" + document + "&back=/LoginPage.htm&webticket=" + ticket;

                }

                else

                {

                    // Перенаправление в AccessPoint

                    RedirectLink = QlikViewServer + "/qvajaxzfc/authenticate.aspx?type=html&try=/qlikview&back=/LoginPage.htm&webticket=" + ticket;

                }

                Response.Redirect(RedirectLink);

            }

            else          

            {

                Alert("Неправильный логин или пароль! Повторите ввод.");

            }

        }

          

        // Функция проверки пользователя по логину и паролю

        // Можно в теле написать все что угодно, в данном случае функция проверяет логин и пароль по таблице в MS SQL

        private bool ValidateUser(string User, string Pass)

        {

            using (SqlConnection qvUserGetConnection = new SqlConnection("user id=loginToSQL;" +

                                       "password=passToSQL;server=localhost;" +

                                       "Trusted_Connection=false;" +

                                       "database=FKAC; " +

                                       "connection timeout=30"))

            {

                try

                {

                    SqlDataReader myReader = null;

                    qvUserGetConnection.Open();

                    SqlCommand myCommand = new SqlCommand("SELECT TOP 1 entityid, name, email, descr, password FROM FKAC.dbo.tbl_A_qvUser WHERE (name = '" + User + "')" + "AND(password = '" + Pass + "')", qvUserGetConnection);

                    myReader = myCommand.ExecuteReader();

                    bool resultExists = false;

                    while (myReader.Read())

                    {

                        resultExists = true;

                    }

                    return resultExists;

                }

                catch(Exception ex)

                {

                    Alert("Ошибка соединения с сервером!");

                    return false;

                }

            }

        }

       

        // Функция получения тикета    

        private string getTicket(string user,string usergroups,string ticketinguser, string ticketingpassword)

        {

            StringBuilder groups = new StringBuilder();

            if (!string.IsNullOrWhiteSpace(usergroups))

            {

                groups.Append("<GroupList>");

                foreach (string group in usergroups.Split(new char[] { ',' }))

                {

                    groups.Append("<string>");

                    groups.Append(group);

                    groups.Append("</string>");

                }

                groups.Append("</GroupList>");

                groups.Append("<GroupsIsNames>");

                groups.Append("true");

                groups.Append("</GroupsIsNames>");

            }

            string webTicketXml = string.Format("<Global method=\"GetWebTicket\"><UserId>{0}</UserId></Global>", user);

            HttpWebRequest client = (HttpWebRequest)WebRequest.Create(new Uri(QlikViewServerURL));

            client.PreAuthenticate = true;

            client.Method = "POST";

            client.AutomaticDecompression = DecompressionMethods.GZip | DecompressionMethods.Deflate;

            // Используем указанный логин и пароль от СЕРВЕРА

            client.Credentials = new NetworkCredential(ticketinguser,ticketingpassword);

            using (StreamWriter sw = new StreamWriter(client.GetRequestStream()))

                sw.WriteLine(webTicketXml);

            StreamReader sr = new StreamReader(client.GetResponse().GetResponseStream());

            string result = sr.ReadToEnd();

            XDocument doc = XDocument.Parse(result);

            return doc.Root.Element("_retval_").Value;

        }

      

      

</script>

Employee
Employee

Re: QV Server, WebTickets. Group binding does not work

Hi Lgor,

I don't see your reply here, but I am not sure what you are authenticating against. Our web pages uses Windows authentication and that is what is passed to QVS. If you are not going to use Windows you have to provide QVS with either a Web Ticket or a Header. The connecter you applied above is only used to assign authorization to the users to see the document. QVS will check that once it receives a Web ticket or the header information.

Bill

segador_
New Contributor II

Re: QV Server, WebTickets. Group binding does not work

Hi!

Sorry the previous message with source code of authorization page has not yet been checked by a moderator... We use our own page to access AccessPoint using WebTicket. This is no problem. The users access through it. The problem only occurs in the distribution of rights on the documents.


There may be a problem I do not understand the information which sends the page in the <GroupList> XML in the body of the request? Now it's empty.

Not applicable

Re: QV Server, WebTickets. Group binding does not work

Igor,

One question about your web ticket code:

Are you passing the group in the web ticket request instead of the userid?  If you are sending the userid via web ticket, the group param in the web ticket is not necessary.  When the user is passed through, Qlik will perform group resolution against the directory service connection.

As far as your ODBC, you are correct that the group name needs to be included in the user table as well and then the group table acts as a lookup where groupID==the id for the group in the user table and the memberid==the id for the user.

Can you see that the user you are logging in as is in fact a member of that group by running a sql query to check the members for the groupID?

segador_
New Contributor II

Re: QV Server, WebTickets. Group binding does not work

I checked groups binding with this query

select *

from tbl_A_qvGroupUser groupsBind

     inner join tbl_A_qvUser groups on (groups.entityid = groupsBind.groupid)and

                                       (groups.isGroup = 1)

     inner join tbl_A_qvUser users on (users.entityid = groupsBind.memberid)and

                                       (users.isGroup <> 1)

Result

              

groupidmemberidentityidnameemaildescrpasswordisGroupentityidnameemaildescrpasswordisGroup
13513guesttest@mail.ruGuestsPassword12315iburobiniburobin@mail.ruBurobin Igor 0
14814analytic123@mail.ruAnalyticsPassword12318vpushkin2test@mail.ruPushkin Vasily 0
Employee
Employee

Re: QV Server, WebTickets. Group binding does not work

Jeff is the expect on this, but I will show you how I have my tables setup.

groups
groupidmemberid
52
61
62
65

entity
entityidentity_namedescremail
1William BatmanWbatmanwbatman@ec.rr.com
2Mongo JohnsonMjohnsonmongo@mongo.mongo
3Hawk EyeHeyehawk@bredband.net
4Laura MartinLmartinasura81@hotmail.com
5Mongo groupgroup for mongos
6Common groupa general group

Bill

Not applicable

Re: QV Server, WebTickets. Group binding does not work

ok, if I'm looking at this correctly, you have your tbl_A_qvGroupUser table returning all of this information.  One thing that is important in the creation of the user and groups table is that they do not have any additional columns in them then the required columns when they are read by the DSC.  I realize you are showing me the contents of what I asked, but I also want to make sure the tables read by the DSC are formatted properly.

For example, the user table needs to be configured like this:

and the groups table needs to look like this:

This is all that can be in the tables.  Now I think you have your groups table created properly, but I don't know if you have added the groups (see 4 and 5 in the entity table) to the users table.

Fields like your isGroup and Password could be throwing things off if those fields exist in the user and groups table respectively.

jg

segador_
New Contributor II

Re: QV Server, WebTickets. Group binding does not work

Ok!

I changed table tbl_A_qvUser to this

This is my table tbl_A_qvGroupUser

Then i deleted all data from tables tbl_A_qvUser and tbl_A_qvUserGroup.

Then I created 2 entries in the table tbl_A_qvUser and 1 entry in table tbl_A_qvUserGroup.

I changed DSP Settings (Entity name field)

I checked data

chenged options in "documents"

Then I went to the login page

Community Browser