importing roles

Oct 29, 2009 at 8:58 PM

Hello,

We have custom users and roles database tables . We would like to import roles(roles and users mappings) from DB  to our application NetSqlAzmanStorage data store .

Wondering if it is possible?

 

Thanks,

Satya.

 

Coordinator
Oct 29, 2009 at 9:01 PM

Hi,

Of course … yes.

You have to write just a few lines of code and using NetSqlAzMan.dll reference to do all you need.

For further information see the Samples.cs (netssqlazman Web Site or you local folder installation).

Regards,

Andrea.

__________________________________
Andrea Ferendeles
NetSqlAzMan Project Coordinator  
E-mail aferende@hotmail.com Web http://netsqlazman.codeplex.com

Oct 29, 2009 at 9:17 PM

Thanks Andrea!! for quick response and it is good to know it is possible. I am pretty new to Azman ,could you refer me to source which I could reuse or any guide lines would be appreciatable.

Thanks,

Satya.

 

 

Coordinator
Oct 29, 2009 at 9:51 PM

http://netsqlazman.codeplex.com/wikipage?title=Samples&referringTitle=Documentation

see the CreateFullStorage() method.

Regards,
Andrea.

Oct 30, 2009 at 2:31 AM

hello Andrea,

I am afraid I did not put my question in detail. we have created our application store(My Store) using in SQL Server 2005 using NetSqlAzman.msc and it is working fine. Noe we have a  requirement of importing users and roles from custom database  and we need to import those existing roles from roles table of our custom database.

My question is whether it is possible to import existing roles from our custom database to My Store? if yes ,how?

Thanks,

Satya

 

 

Coordinator
Nov 2, 2009 at 9:21 PM

Hi Satya,

please post here the schema (TSQL) of your source structure (import from) where you have your custom Roles.

Regards,

Andrea.

__________________________________
Andrea Ferendeles
NetSqlAzMan Project Coordinator  
E-mail aferende@hotmail.com Web http://netsqlazman.codeplex.com

Nov 3, 2009 at 5:35 PM

Hello Andrea,

Here is our datbase schema for roles and users

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[sct_UserRoles](
 [UserID] [int] NOT NULL,
 [RoleID] [int] NOT NULL,
 [AddedByGroup] [bit] NOT NULL,
 [InsertedDate] [datetime] NULL CONSTRAINT [DF_sct_UserRoles_InsertedDate]  DEFAULT (getdate()),
 [LastUpdateBy] [int] NULL
) ON [PRIMARY]

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[sct_Users](
 [UserID] [int] IDENTITY(1,1) NOT NULL,
 [UserCode] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
 [UserName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 [Password] [varchar](36) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
 [CountyID] [tinyint] NULL,
 [CreateDate] [datetime] NULL,
 [ModifyDate] [datetime] NULL,
 [EMail] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 [GroupID] [int] NULL,
 [GroupStartDate] [datetime] NULL,
 [GroupEndDate] [datetime] NULL,
 [CreatedBy] [int] NULL,
 [ModifiedBy] [int] NULL,
 [AddressID] [int] NULL,
 [PhoneGroupID] [int] NULL,
 [Cnt] [int] NULL,
 [ExpirationDate] [datetime] NULL,
 [RecentAccess] [datetime] NULL,
 [BlockGroupRole] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 [Salutation] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 [FName] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 [MName] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 [LName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 [Suffix] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 [ConcurrentLogon] [int] NULL,
 [RequestPass] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 [DivisionDCID] [int] NULL,
 [DeptOtherID] [int] NULL,
 [IsPassChange] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 [PassCreateDate] [datetime] NULL,
 CONSTRAINT [PK_sct_Users_1] PRIMARY KEY CLUSTERED
(
 [UserID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OF

We need to import roles(from sct_UserRoles) to NetSQLAzman Store and map them with users in user table(sct_Users).

 

Thanks,

Satya.

 

 

 

 

 

 

Nov 5, 2009 at 3:58 PM

I have imported roles and got all assigned users from custom tables and established mapping between roles and users ...using below code

 

public partial class Form1 : Form
    {
        CustomRoleProvider provider = new CustomRoleProvider();
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            //CustomRoleProvider provider = new CustomRoleProvider();
            string [] roles =  provider.GetAllRoles();
           // string [] userNames = provider.FindUsersInRole("4", "");
            CreateFullStorage();
        }

        private void CreateFullStorage()
        {
            // USER MUST BE A MEMBER OF SQL DATABASE ROLE: NetSqlAzMan_Administrators

            //Sql Storage connection string
            string sqlConnectionString = "server=mrdddevlsql;database=NetSqlAzManStorage;""=devl;pwd=""";
            //Create an instance of SqlAzManStorage class
            IAzManStorage storage = new SqlAzManStorage(sqlConnectionString);
            //Open Storage Connection
            storage.OpenConnection();
            //Begin a new Transaction
            storage.BeginTransaction(AzManIsolationLevel.ReadUncommitted);
            //Create a new Store
            IAzManStore newStore = storage.CreateStore("CPT Store", "Store description");
            //Create a new Basic StoreGroup
            IAzManStoreGroup newStoreGroup = newStore.CreateStoreGroup(SqlAzManSID.NewSqlAzManSid(), "CPT Store Group", "ho", String.Empty, GroupType.Basic);
            //Retrieve current user SID
            IAzManSid mySid = new SqlAzManSID(WindowsIdentity.GetCurrent().User);
            //Add myself as sid of "My Store Group"
            IAzManStoreGroupMember storeGroupMember = newStoreGroup.CreateStoreGroupMember(mySid, WhereDefined.Local, true);
            //Create a new Application
            IAzManApplication newApp = newStore.CreateApplication("CPT Application", "Cost Projection Tool");
            //foreach (string role in provider.RoleIDCollection.Values)
           
              IDictionaryEnumerator myEnumerator =  provider.RoleIDCollection.GetEnumerator();
                while(myEnumerator.MoveNext())
                {

                //Create a new Role
                IAzManItem newRole = newApp.CreateItem(myEnumerator.Value.ToString(), "Role description", ItemType.Role);
                            //Create an authorization for myself on "New Role"
                IAzManAuthorization auth = newRole.CreateAuthorization(mySid, WhereDefined.Local, mySid, WhereDefined.Local, AuthorizationType.AllowWithDelegation, null, null);
                //Create a custom attribute
                IAzManAttribute<IAzManAuthorization> attr = auth.CreateAttribute("New Key", "New Value");
            

                string[] providerFindUsersInRole = provider.FindUsersInRole(myEnumerator.Key.ToString(), "");


                if (providerFindUsersInRole != null)
                {
                    foreach (string userName in providerFindUsersInRole)
                    {
                        //Create an authorization for DB User "Andrea" on "New Role"
                        IAzManAuthorization auth2 = newRole.CreateAuthorization(mySid, WhereDefined.Local, storage.GetDBUser(userName).CustomSid, WhereDefined.Database, AuthorizationType.AllowWithDelegation, null, null);
                    }
                }
               }
           
                //Commit transaction
            storage.CommitTransaction();
            //Close connection
            storage.CloseConnection();
        }

Hope it helps someone!

Satya

 

Coordinator
Nov 5, 2009 at 4:31 PM
Well done.
Andrea.