Inserting Authorization

Sep 3, 2009 at 5:31 PM
Edited Sep 3, 2009 at 6:52 PM

Hi ,

I just started learning azman today .Please forgive me if my questions are immature.

Question 1 :

I am trying to Add DB users to Role Authorization from the code using the stored procedure netsqlazman_AuthorizationInsert.

Where do i get the following information?

ownerSid - Is like a unique identifier for each application ? where do i get it from for my application ? . When inserting through the interface i see one created for my application

ownerSidWhereDefined - where do i get this info?

objectSidWhereDefined - When inserting through the interface i see a value of 4 where do i get the other values

AuthorizationType - where can i get this info? 

Is there a function the Azman dll to insert authorizations?  Which i can use? However that also might need these above mentioned information.

Question 2 )

I have a role called Members and need to add users to this role dynamically from my code .I think I can achieve it in 2 ways

a)       Create a Application Group  and add the group to the Role Authorization through the NetsqlAzman console and then add users to that group through  code using [dbo].[netsqlazman_ApplicationGroupMemberInsert]

 

b)      As mentioned in question 1 can add user to Role authorization using netsqlazman_AuthorizationInsert.

Which one is a better approach? Please suggest. If I go with approach a) do I have to do anything other than [dbo].[netsqlazman_ApplicationGroupMemberInsert] ?

What is dbo].[netsqlazman_StoreGroupMemberInsert] used for do I need to consider this

Thanks! in advance

Regards

Joseph

 

 

 

 

 

 

Coordinator
Sep 3, 2009 at 6:56 PM

Hi,

you can find all you need inside the NetSqlAzMan Guide.pdf.

This is an example of how use the NetSqlAzMan_AuthorizationInsert:

DECLARE @RC int

DECLARE @ItemId int

DECLARE @ownerSid varbinary(85)

DECLARE @ownerSidWhereDefined tinyint

DECLARE @objectSid varbinary(85)

DECLARE @objectSidWhereDefined tinyint

DECLARE @AuthorizationType tinyint

DECLARE @ValidFrom datetime

DECLARE @ValidTo datetime

DECLARE @ApplicationId int

SET @ownerSid = SUSER_SID('Domain\User inserting the new authorization')

-- WhereDefined can be:

-- 0 - Store;

-- 1 - Application;

-- 2 - LDAP;

-- 3 - Local;

-- 4 - Database

SET @ownerSidWhereDefined = 2

SET @objectSid = SELECT TOP 1 CONVERT(VARBINARY(85), UserID) AS DBUserSid

                         FROM dbo.UsersDemo

                         WHERE UserName = 'my db user'

-- WhereDefined can be:

-- 0 - Store;

-- 1 - Application;

-- 2 - LDAP;

-- 3 - Local;

-- 4 - Database

SET @ownerSidWhereDefined = 4

EXECUTE @RC = [NetSqlAzManStorage].[dbo].[netsqlazman_AuthorizationInsert]

   @ItemId

  ,@ownerSid

  ,@ownerSidWhereDefined

  ,@objectSid

  ,@objectSidWhereDefined

  ,@AuthorizationType

  ,@ValidFrom

  ,@ValidTo

  ,@ApplicationId

And here is an example of doing the same thing using the NetSqlAzMan.dll DOM (C# Samples.cs … inside the installation folder):

/// <summary>

        /// Create a Full Storage through .NET code

        /// </summary>

        private void CreateFullStorage()

        {

            // USER MUST BE A MEMBER OF SQL DATABASE ROLE: NetSqlAzMan_Administrators

            //Sql Storage connection string

            string sqlConnectionString = "data source=(local);initial catalog=NetSqlAzManStorage;user id=netsqlazmanuser;password=password";

            //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("My Store", "Store description");

            //Create a new Basic StoreGroup

            IAzManStoreGroup newStoreGroup = newStore.CreateStoreGroup(SqlAzManSID.NewSqlAzManSid(), "My Store Group", "Store Group Description", String.Empty, GroupType.Basic);

            //Retrieve current user SID

            IAzManSid mySid = new SqlAzManSID(((System.Threading.Thread.CurrentPrincipal.Identity as WindowsIdentity) ?? 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("New Application", "Application description");

            //Create a new Role

            IAzManItem newRole = newApp.CreateItem("New Role", "Role description", ItemType.Role);

            //Create a new Task

            IAzManItem newTask = newApp.CreateItem("New Task", "Task description", ItemType.Task);

            //Create a new Operation

            IAzManItem newOp = newApp.CreateItem("New Operation", "Operation description", ItemType.Operation);

            //Add "New Operation" as a sid of "New Task"

            newTask.AddMember(newOp);

            //Add "New Task" as a sid of "New Role"

            newRole.AddMember(newTask);

            //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");

            //Create an authorization for DB User "Andrea" on "New Role"

            IAzManAuthorization auth2 = newRole.CreateAuthorization(mySid, WhereDefined.Local, storage.GetDBUser("Andrea").CustomSid, WhereDefined.Local, AuthorizationType.AllowWithDelegation, null, null);

            //Commit transaction

            storage.CommitTransaction();

            //Close connection

            storage.CloseConnection();

        }

Regards,

Andrea.

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

Sep 3, 2009 at 7:10 PM

Thanks very much !

Appreciate the help

Can you please asnwer my second question

Question 2 )

I have a role called Members and need to add users to this role dynamically from my code .I think I can achieve it in 2 ways

a)       Create a Application Group  and add the group to the Role Authorization through the NetsqlAzman console and then add users to that group through  code using [dbo].[netsqlazman_ApplicationGroupMemberInsert] 

 

b)      As mentioned in question 1 can add user to Role authorization using netsqlazman_AuthorizationInsert.

Which one is a better approach? Please suggest. If I go with approach a) do I have to do anything other than [dbo].[netsqlazman_ApplicationGroupMemberInsert] ?

What is dbo].[netsqlazman_StoreGroupMemberInsert] used for do I need to consider this

Regards

Kiran.J

 

Coordinator
Sep 3, 2009 at 9:10 PM
Hi,
why not using .NET and NetSqlAzMan.dll (DOM) ?
(look at the c# samples of the previous post).
All that the MMC SnapIn do .. can be done using the NetSqlAzMan.dll.
Just reference it in your code and us it.
Regards,
Andrea.

Sep 4, 2009 at 1:46 PM

Thanks! Shall look into it.

Regards

Kiran.J

 

Dec 1, 2012 at 11:51 PM

Thanks Andrea. One very minor mistake I noted was that you set @ownerSidWhereDefined twice, but never set @objectSidWhereDefined. An easy enough change to make (just modify the second SET statement).

Very helpful example, thank you!

Coordinator
Dec 2, 2012 at 8:26 AM
jasonbaum wrote:

Thanks Andrea. One very minor mistake I noted was that you set @ownerSidWhereDefined twice, but never set @objectSidWhereDefined. An easy enough change to make (just modify the second SET statement).

Very helpful example, thank you!

But it is not a mistake. 

In the example, who gave permission was the same as that received them.
It 'a possible case.
However, you understand how it works.

Regards,

Andrea.