01.Blogs :
mauriciogonzatto  
.NET, C#, Oracle, Web, tecnologia em geral e diversidades.
View Subscribers (22) | Email | Subscribe | View as RSS news feed in XML

OracleRoleProvider

Conforme dito no Post anterior, segue a implementação do RoleProvider para Oracle.

OracleRoleProvider


using System;
using System.Data;
using System.Diagnostics;
using System.Globalization;
using System.Configuration;
using System.Configuration.Provider;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.OracleClient;

/// <summary>
/// Summary description for OracleRoleProvider
/// </summary>
public sealed class OracleRoleProvider : RoleProvider
{
    private string eventSource = "OracleRoleProvider";
    private string eventLog = "Application";
    private string exceptionMessage = "Ocorreu uma exceção. Por favor cheque o Log.";
    private string connectionString;

    /// <summary>
    /// Retorna a string de conexão para OracleConnection
    /// </summary>
    private string CnnString
    {
        get
        {
            ConnectionStringSettingsCollection Coneccao = ConfigurationManager.ConnectionStrings;
            return Coneccao["OracleConnection"].ConnectionString;
        }
    }

    // Se falso, exceções são lançadas. Se verdadeiro,
    // exceções são gravadas no log.
    private bool pWriteExceptionsToEventLog = false;

    public bool WriteExceptionsToEventLog
    {
        get { return pWriteExceptionsToEventLog; }
        set { pWriteExceptionsToEventLog = value; }
    }

    public override void Initialize(string name, System.Collections.Specialized.NameValueCollection config)
    {
        if (config == null)
            throw new ArgumentNullException("config");

        if (name == null || name.Length == 0)
            name = "OracleProvider";

        if (String.IsNullOrEmpty(config["descrption"]))
        {
            config.Remove("description");
            config.Add("description", "Oracle Role Provider");
        }

        base.Initialize(name, config);

        pApplicationName = GetConfigValue(config["applicationName"],
                                System.Web.Hosting.HostingEnvironment.ApplicationVirtualPath).ToString().Replace("/","");

        if (config["WriteExceptionsToEventLog"] != null)
        {
            if (config["WriteExceptionsToEventLog"].ToUpper() == "TRUE")
            {
                pWriteExceptionsToEventLog = true;
            }
        }
        connectionString = this.CnnString;
    }

    private string GetConfigValue(string configValue, string defaultValue)
    {
        if (String.IsNullOrEmpty(configValue))
            return defaultValue;

        return configValue;
    }

    private string pApplicationName;

    public override string ApplicationName
    {
        get { return pApplicationName; }
        set { pApplicationName = value; }
    }

    public override void AddUsersToRoles(string[] usernames, string[] roleNames)
    {
        foreach (string rolename in roleNames)
        {
            if (!RoleExists(rolename))
            {
                throw new ProviderException("Nome da role não encontrada.");
            }
        }

        foreach (string username in usernames)
        {
            if (username.Contains(";"))
            {
                throw new ArgumentException("Nome do usuário não pode conter ponto e vírgula.");
            }

            foreach (string rolename in roleNames)
            {
                if (IsUserInRole(username, rolename))
                {
                    throw new ProviderException("Usuário já pertence a esta role.");
                }
            }
        }

        OracleConnection conn = new OracleConnection(connectionString);
        OracleCommand cmd = new OracleCommand("INSERT INTO UsersInRoles " +
                                        "(Username, Rolename, ApplicationName) " +
                                        "Values(:1, :2, :3)", conn);

        OracleParameter userParam = cmd.Parameters.Add(":1", OracleType.VarChar, 255);
        OracleParameter roleParam = cmd.Parameters.Add(":2", OracleType.VarChar, 255);
        cmd.Parameters.Add(":3", OracleType.VarChar, 255).Value = ApplicationName;


        try
        {
            cmd.Connection.Open();

            foreach (string username in usernames)
            {
                foreach (string rolename in roleNames)
                {
                    userParam.Value = username;
                    roleParam.Value = rolename;
                    cmd.ExecuteNonQuery();
                }
            }
        }
        catch (OracleException e)
        {

            if (WriteExceptionsToEventLog)
            {
                WriteToEventLog(e, "AddUsersToRoles");
            }
            else
            {
                throw e;
            }
        }
        finally
        {
            conn.Close();
        }
    }

    public override void CreateRole(string roleName)
    {
        if (roleName.Contains(";"))
        {
            throw new ArgumentException("Nome da role não pode conter ponto e vírgula.");
        }

        if (RoleExists(roleName))
        {
            throw new ProviderException("Nome da role já existe");
        }

        OracleConnection conn = new OracleConnection(this.connectionString);
        OracleCommand cmd = new OracleCommand("INSERT INTO Roles " +
                                "(Rolename, ApplicationNAme) " +
                                "Values(:1, :2)", conn);

        cmd.Parameters.Add(":1", OracleType.VarChar, 255).Value = roleName;
        cmd.Parameters.Add(":2", OracleType.VarChar, 255).Value = ApplicationName;

        try
        {
            conn.Open();
            cmd.ExecuteNonQuery();
        }
        catch (OracleException e)
        {
            if (WriteExceptionsToEventLog)
            {
                WriteToEventLog(e, "CreateRole");
            }
            else
            {
                throw e;
            }
        }
        finally
        {
            conn.Close();
        }
    }

    public override bool DeleteRole(string roleName, bool throwOnPopulatedRole)
    {
        if (!RoleExists(roleName))
        {
            throw new ProviderException("Role não existe");
        }

        if (throwOnPopulatedRole && GetUsersInRole(roleName).Length > 0)
        {
            throw new ProviderException("Não é possível deletar uma role que contenha dados.");
        }

        OracleConnection conn = new OracleConnection(connectionString);
        OracleCommand cmd2 = new OracleCommand("DELETE FROM Roles " +
                                "WHERE Rolename = :1 AND ApplicationName = :2", conn);

        cmd2.Parameters.Add(":1", OracleType.VarChar, 255).Value = roleName;
        cmd2.Parameters.Add(":2", OracleType.VarChar, 255).Value = ApplicationName;

        try
        {
            conn.Open();        
            cmd2.ExecuteNonQuery();
        }
        catch (OracleException e)
        {

            if (WriteExceptionsToEventLog)
            {
                WriteToEventLog(e, "DeleteRole");
                return false;
            }
            else
            {
                throw e;
            }
        }
        finally
        {
            conn.Close();          
        }

        return true;
    }

    public override string[] GetAllRoles()
    {
        string tmpRoleNames = "";
        OracleConnection conn = new OracleConnection(connectionString);
        OracleCommand cmd = new OracleCommand("SELECT Rolename FROM Roles " +
                                    "WHERE ApplicationName = :1", conn);

        cmd.Parameters.Add(":1", OracleType.VarChar, 255).Value = ApplicationName;

        OracleDataReader reader = null;

        try
        {
            conn.Open();
            reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                tmpRoleNames += reader.GetString(0) + ",";
            }
        }
        catch (OracleException e)
        {
            if (WriteExceptionsToEventLog)
            {
                WriteToEventLog(e, "GetAllRoles");
            }
            else
            {
                throw e;
            }
        }
        finally
        {
            if (reader != null) { reader.Close(); }
            conn.Close();
        }

        if (tmpRoleNames.Length > 0)
        {
            tmpRoleNames = tmpRoleNames.Substring(0, tmpRoleNames.Length - 1);
            return tmpRoleNames.Split(',');
        }
        return new string[0];
    }

    public override string[] GetRolesForUser(string username)
    {
        string tmpRoleNames = "";

        OracleConnection conn = new OracleConnection(connectionString);
        OracleCommand cmd = new OracleCommand("SELECT Rolename FROM UsersInRoles " +
                                        "WHERE Username = :1 AND ApplicationName = :2", conn);

        cmd.Parameters.Add(":1", OracleType.VarChar, 255).Value = username;
        cmd.Parameters.Add(":2", OracleType.VarChar, 255).Value = ApplicationName;

        OracleDataReader reader = null;

        try
        {
            conn.Open();
            reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                tmpRoleNames += reader.GetString(0) + ",";
            }
        }
        catch (OracleException e)
        {
            if (WriteExceptionsToEventLog)
            {
                WriteToEventLog(e, "GetRolesForUser");
            }
            else
            {
                throw e;
            }
        }
        finally
        {
            if (reader != null) { reader.Close(); }
            conn.Close();
        }

        if (tmpRoleNames.Length > 0)
        {
            tmpRoleNames = tmpRoleNames.Substring(0, tmpRoleNames.Length - 1);
            return tmpRoleNames.Split(',');
        }

        return new string[0];
    }

    public override string[] GetUsersInRole(string roleName)
    {
        string tmpUserNames = "";

        OracleConnection conn = new OracleConnection(this.connectionString);
        OracleCommand cmd = new OracleCommand("SELECT Username FROM UsersInRoles " +
                "WHERE Rolename = :1 AND ApplicationName = :2", conn);

        cmd.Parameters.Add(":1", OracleType.VarChar, 255).Value = roleName;
        cmd.Parameters.Add(":2", OracleType.VarChar, 255).Value = ApplicationName;

        OracleDataReader reader = null;

        try
        {
            conn.Open();
            reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                tmpUserNames += reader.GetString(0) + ",";
            }
        }
        catch (OracleException e)
        {
            if (WriteExceptionsToEventLog)
            {
                WriteToEventLog(e, "GetUsersInRole");
            }
            else
            {
                throw e;
            }
        }
        finally
        {
            if (reader != null) { reader.Close(); }
            conn.Close();
        }

        if (tmpUserNames.Length > 0)
        {
            tmpUserNames = tmpUserNames.Substring(0, tmpUserNames.Length - 1);
            return tmpUserNames.Split(',');
        }

        return new string[0];
    }

    public override bool IsUserInRole(string username, string roleName)
    {
        bool userIsInRole = false;

        OracleConnection conn = new OracleConnection(connectionString);
        OracleCommand cmd = new OracleCommand("SELECT COUNT(*) FROM UsersInRoles " +
                    "WHERE Username = :1 AND Rolename = :2 AND ApplicationName = :3", conn);

        cmd.Parameters.Add(":1", OracleType.VarChar, 255).Value = username;
        cmd.Parameters.Add(":2", OracleType.VarChar, 255).Value = roleName;
        cmd.Parameters.Add(":3", OracleType.VarChar, 255).Value = ApplicationName;

        try
        {
            conn.Open();
            int numRecs = Convert.ToInt32(cmd.ExecuteScalar());
            if (numRecs > 0)
            {
                userIsInRole = true;
            }
        }
        catch (OracleException e)
        {
            if (WriteExceptionsToEventLog)
            {
                WriteToEventLog(e, "IsUserInRole");
            }
            else
            {
                throw e;
            }
        }
        finally
        {
            conn.Close();
        }

        return userIsInRole;
    }

    public override void RemoveUsersFromRoles(string[] usernames, string[] roleNames)
    {
        foreach (string rolename in roleNames)
        {
            if (!RoleExists(rolename))
            {
                throw new ProviderException("Role não encontrada.");
            }
        }

        foreach (string username in usernames)
        {
            foreach (string rolename in roleNames)
            {
                if (!IsUserInRole(username, rolename))
                {
                    throw new ProviderException("Usuário não está na role.");
                }
            }
        }

        OracleConnection conn = new OracleConnection(connectionString);
        OracleCommand cmd = new OracleCommand("DELETE FROM UsersInRoles " +
                        "WHERE Username = :1 AND Rolename = :2 AND ApplicationName = :3", conn);

        OracleParameter userParam = cmd.Parameters.Add(":1", OracleType.VarChar, 255);
        OracleParameter roleParam = cmd.Parameters.Add(":2", OracleType.VarChar, 255);
        cmd.Parameters.Add(":3", OracleType.VarChar, 255).Value = ApplicationName;

        try
        {
            cmd.Connection.Open();

            foreach (string username in usernames)
            {
                foreach (string rolename in roleNames)
                {
                    userParam.Value = username;
                    roleParam.Value = rolename;
                    cmd.ExecuteNonQuery();
                }
            }
        }
        catch (OracleException e)
        {
            if (WriteExceptionsToEventLog)
            {
                WriteToEventLog(e, "removeUsersFromRoles");
            }
            else
            {
                throw e;
            }
        }
        finally
        {
            cmd.Connection.Close();
        }
    }

    public override bool RoleExists(string roleName)
    {
        bool exists = false;

        OracleConnection conn = new OracleConnection(connectionString);
        OracleCommand cmd = new OracleCommand("SELECT COUNT(*) FROM Roles " +
                    "WHERE Rolename = :1 AND ApplicationName = :2", conn);

        cmd.Parameters.Add(":1", OracleType.VarChar, 255).Value = roleName;
        cmd.Parameters.Add(":2", OracleType.VarChar, 255).Value = ApplicationName;

        try
        {
            conn.Open();

            int numRecs = Convert.ToInt32(cmd.ExecuteScalar());

            if (numRecs > 0)
            {
                exists = true;
            }
        }
        catch (OracleException e)
        {
            if (WriteExceptionsToEventLog)
            {
                WriteToEventLog(e, "RoleExists");
            }
            else
            {
                throw e;
            }
        }
        finally
        {
            conn.Close();
        }

        return exists;
    }

    public override string[] FindUsersInRole(string roleName, string usernameToMatch)
    {
        OracleConnection conn = new OracleConnection(connectionString);
        OracleCommand cmd = new OracleCommand("SELECT Username FROM UsersInRoles " +
                "WHERE Username LIKE :1 AND RoleName = :2 AND ApplicationName = :3", conn);

        cmd.Parameters.Add(":1", OracleType.VarChar, 255).Value = usernameToMatch;
        cmd.Parameters.Add(":2", OracleType.VarChar, 255).Value = roleName;
        cmd.Parameters.Add(":3", OracleType.VarChar, 255).Value = pApplicationName;

        string tmpUserNames = "";
        OracleDataReader reader = null;

        try
        {
            conn.Open();
            reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                tmpUserNames += reader.GetString(0) + ",";
            }
        }
        catch (OracleException e)
        {
            if (WriteExceptionsToEventLog)
            {
                WriteToEventLog(e, "FindUsersInRole");
            }
            else
            {
                throw e;
            }
        }
        finally
        {
            if (reader != null) { reader.Close(); }
            conn.Close();
        }

        if (tmpUserNames.Length > 0)
        {
            tmpUserNames = tmpUserNames.Substring(0, tmpUserNames.Length - 1);
            return tmpUserNames.Split(',');
        }

        return new string[0];
    }

    private void WriteToEventLog(OracleException e, string action)
    {
        EventLog log = new EventLog();
        log.Source = eventSource;
        log.Log = eventLog;

        string message = exceptionMessage + "\n\n";
        message += "Action: " + action + "\n\n";
        message += "Exception: " + e.ToString();

        log.WriteEntry(message);
    }
}


Para isso devemos ter as tabelas ROLES e USERSINROLES

CREATE TABLE roles
    (rolename                       VARCHAR2(255) NOT NULL,
    applicationname                VARCHAR2(255) NOT NULL)
  PCTFREE     10
  INITRANS    1
  MAXTRANS    255
  TABLESPACE  users
  STORAGE   (
    INITIAL     73728
    MINEXTENTS  1
    MAXEXTENTS  2147483645
  )
/


CREATE TABLE usersinroles
    (username                       VARCHAR2(255) NOT NULL,
    applicationname                VARCHAR2(255) NOT NULL,
    rolename                       VARCHAR2(255) NOT NULL)
  PCTFREE     10
  INITRANS    1
  MAXTRANS    255
  TABLESPACE  users
  STORAGE   (
    INITIAL     73728
    MINEXTENTS  1
    MAXEXTENTS  2147483645
  )
/

Na seção <system.web> do WebConfig devemos colocar:

        <roleManager enabled="true" cacheRolesInCookie="true" defaultProvider="OracleRoleProvider">
            <providers>
                <add name="OracleRoleProvider" type="OracleRoleProvider"/>
            </providers>
        </roleManager>


Agora é só partir pro abraço!

Estou olhando a sugestão do amigo Dennes para o controle de permissões por página para ver se realmente este satisfaz a minha necessidade. E após analisar o método proposto por ele, verei se modifico a minha implementação ou não. Mas mesmo assim, postarei os dois métodos de controle aqui para um possível debate sobre as questões de segurança, bem como melhores práticas!

[]'s

posted on Monday, September 11, 2006 8:39 AM by mauriciogonzatto

# re: OracleRoleProvider @ Monday, September 11, 2006 4:26 PM

yow joe, the oracle man!
bom te ver aqui...
a escassez de cerebros pensantes esta cada vez maior por estas bandas!

[]s Juliano

julianocarvalho

# re: OracleRoleProvider @ Monday, September 11, 2006 4:50 PM

what's uuuuupppp!?!?!?!

Caramba chefia, ando numa correria que só Deus sabe mas agora estou de volta!! Esse feriado acabei tirando pra descansar a cabeça da informática um pouco. Saí com os amigos, churrasco com a família e a leitura de minha última aquisição: "QUANDO NIETZSCHE CHOROU", e o mais importante meu brother...me preparando fisicamente, psicologicamente e metafisicamente para a minha formatura!!! Que é agora sábado, dia 16. Estou contanto os segundos. Mas como dizem aqui no sul:

"Não tá morto quem peleia!"

[]'s e fico feliz pelo estímulo!

mauriciogonzatto

# devASPNet Magazine No 29 : Muitas Realiza&amp;#231;&amp;#245;es @ Monday, July 16, 2007 2:30 PM


.main {
SCROLLBAR-FACE-COLOR: #ffffff; FONT-SIZE: 11px; BACKGROUND-IMAGE: url(http://www.devaspnet.com.br/revista/imagensv2/images/fundo_main.gif);...

Dennes


 
03.UPDATE CALENDAR :
<September 2006>
SunMonTueWedThuFriSat
272829303112
3456789
10111213141516
17181920212223
24252627282930
1234567
04.CATEGORIES :

05.MY LINKS :

06.Archives :

07.Subscriptions :

Subscriptions
© Copyright 2005 Microsoft Corporation. All Rights Reserved.
Terms of Use | Privacy Statement | Code of Conduct | Hosted by MaximumASP for Microsoft
WHO-BAR