Azure SQL Security: Understanding Access and Authentication (Ep. 2) | Data Exposed

[Music] hi i'm anna hoffman and welcome to this special episode of data exposed live we have a very interesting episode planned for today to continue our security series so without further ado i'm going to go ahead and bring up our wonderful speakers that are going to be with us today um the first person i'm going to bring in is merrick hi i'm eric thanks for joining us can you tell us a little bit about what you do hello anna my name is mirik steiner i'm a senior program manager in azure sql in a security team and i focus on uh authentication primary on the azure active directory authentication that is part of this presentation today so welcome awesome we are happy to have you here and it seems like you're gonna be one of the right people for us to have on the show now today we're also joined by uh andreas andreas thanks for joining us can you tell us a little bit about what you do hey sure thanks a lot for having me here so i trust asmirik i'm a senior program manager and azure sql security team my area is access control basically anything around permissions that you want to grant your users that's where i'm working on awesome great well it's going to be a great show today uh we're excited to have both of you here and today we're actually going to be continuing our azure sql security series so for those of you who might have missed it uh we will put a link in the description to the whole security series this is actually uh the second episode in that series um now andreas can you tell us and can you tell viewers who might not have seen the first episode can you tell us kind of what this series is about what we covered last week and what we're going to cover this week sure enough to all right so if you have joined last week you may have already seen this little overview so basically we try to guide our customers in this vast topic of security to consider five or five fundamental pillars one of them being authentication access management we're gonna talk about this area today um another one being the data protection things like encryption would fall under that network security i guess uh you have an understanding what this means and data governance is looking a little bit from higher up uh what data you have and how to protect that and eventually also threat prevention and detection um so those are the fundamental uh security pillars that we are looking at and piece by piece we will uh look under the underhood of those so today we're going to talk about authentication and access management um and i will hand over to myrick to cover the first part awesome thanks andreas i'm looking forward to learning more about this i know a little bit about it but i'm looking forward to uh learning more so on uh merrick uh would love to hear from you more about uh authentication okay well hello everybody again and welcome i like to start where the previous episode stop uh that was emphasizing the act active director authentication uh giving the benefits in terms of the single sign-on uh using the multi-factor authentication the mfa and the conditional access so um let's briefly talk about azure active directory that we also call azure ad or sometimes aad that represents the central uh identity repository uh for azure cloud allowing to store the identities and allow to manage identities and it has plenty of uh users customers who who use it and and groups one of them is the i.t administration who can provision uh users groups imposing mfa uh there's another big group of of users application developers who can use their single sign-on uh to develop their applications in azure there are uh the the big variety of the azure services uh that that are offered with with azure cloud uh such as azure sql as one of them we have others related kind of to the database like cosmos db or or azure storage and uh last but not least there are these big products that that microsoft uh is uh releasing and or did release like microsoft 365 or office 365 there are the customers of azure active directory they store in azure id the identity identities from the sql standpoint uh we support the following skews we start these are the azure skus we start with the sqldb this is the uh all this uh version where azure ad is working uh following by by the hyperscale uh following with the managed identity and it is also supported for synapse analytics so all all these uh all these skus are supporting today our proactive directory and we encourage customers to use them from the authentication methods that we support for uh sql azure uh there are several of them uh and the menu is the user password where you provide the user condition and password for the applications this password must be hard coded there's the integrated or single sign-on authentication that is possible when we provide the federation between active directory and azure active directory uh using windows authentication there's another method called universal or interactive with multi-factor authentication and this method is a differ differs from the first one from user password regarding the password itself first of all um it allows to uh impose the multi-factor the second factor authentication itself but the important thing is that the password here is provided dynamically which is slightly different than in the user password where you have a hard code it dynamically means that during the authentication part you have to you have to specify the password there's a token authentication where in case if you can obtain a token from azure id you can pass this token to sql and token will be authenticated this way uh there's the uh existing uh from the beginning sql authentication that is supported for sql on-prem it's also supported for uh azure id but uh we encourage customers users really to use uh azure um authentication to uh to authenticate with with azure sql from the object standpoint that we support for azure sql authentication there are mostly the objects that are supported today with the azure id the users that can be the the native users that that can be created in the azure active directory uh the guest users uh these are the users invited from other active directories uh azure ads or from outside like outlook.com like live.com or even gmail.com uh we support also groups and these are the native groups that that can be created in the um in the uh uh active directory these groups are either created there are or they can be inherited from windows when we do the um federation between active directory and azure active directory and there are the groups and there are also the users that that can be used from active directory with windows support there's another class called service principles that are also called the azure applications that can authenticate using their application id and using the secrets or certificates the last one is very important it's a azure ad administrator that must be created for azure sql so this is a requirement to enable the azure ad authentication for sql we always have to have azure ad administrator there gotcha this is really useful mirac it seems like there are a lot of different ways uh to connect or that are possible for you to set up for authenticating uh but what is the current security policy on using you know say hard-coded passwords uh to authenticate to azure sql so it is uh it is allowable but it is not a recommended solution uh because it's it's hard coded you have to store it either in the application or somewhere in the disk on the disk so uh the better method is really to use the universal interactive always impose multi-factor authentication but also other options like they use the integrated signal sign-on using windows authentication or token where we use secrets or certificates uh with this let me go to the diagram communication diagram and here i have two scenarios that i like to present and this is a uh on this on this first scenario which is related to user and and password authentication we have an application on the left side we have a driver on the in the middle which is a sequel driver with the msr on the top which is the library this is the access library that allows to access azure active directory and then on the right side we have cloud that has two components azure sql database and azure active directory so let me just go through this communication diagram for those who are less interest who have less interest in the details please take a backseat but for others it may be an interesting scenario so uh typically we start with sending a connection string from application and you can see the collection connection string contains a keyword authentication equal active directory password and this contents is then sent to the sql database since it cannot be authenticated there it it goes back with additional information like for example tenant id and then msr is is accessing the azure active directory with user password create a credentials it is authenticate there comes back with the jwt token it is sent back to the azure sql database there's the optional group expansion for users with the there are members of more than 250 56 groups and then it comes back to the driver and it's sent back to uh to the application so this is how the user password authentication works in case we use the federated authentication so windows authentication we have another component that that you can see on the left side which is the on-premise active directory and you can see also that this string difference because the authentication right now is called active directory integrated uh again the first part is the same uh the previous one we connect the uh to azure sql database as a pre-login we send back this information to the driver uh contact the other active directory and this time the user cannot be identified there are authenticator because we don't have a password there so uh it goes back and the request goes now to the on-premise active directory where we find the the information windows information about this particular user it comes back here goes back again to azure active directory uh comes back and the the part goes as in previous one we send all this information to sql database there's the optional group expansion and finally after we have the authentication in sql database it goes back to the driver and goes back to uh to the client with this i'd like to show a quick demo on the multi-factor authentication for users that are outside of azure azure active directory so let me switch now to um wow this is really cool merrick i'm excited i didn't know we were going to get to take a sneak peek at this so i'm looking forward to seeing this demo in action yes so uh what we have here is uh i'm having a i created a server here i call episode two this server has one database uh the test mfa and important as i mentioned in the past we have the we need to create the active directory administrator which is this group in this case b2b with my credentials so let's look at the active directory itself at the azure id and let's check the group so i create here a user group called guest this group has one member and this is the outlook.com member s.p.m and the final let's look at the uh conditional access where you can allow the policy so if i i created this policy here right now is off the gas mfa let's click on the policy and let's revisit what is there there's a name of the policy there's a species specific users that we are including in this case for this policy i'm including user guests and knack next we need to indicate who is the receiver for this policy and in this case this is the azure sql database and finally we need to define what kind of control are we using in this case this is the multi-factor authentication so let me enable this policy and with this let's go to ssms so uh this is my ssms where i log in as an admin and i'm in the test mfa database i i have to map the guest user from the azure active directory to sql please do not confuse this guest user with a sql authentication guest user that you may create this is really the azure active directory map of mapping of the guest user in aad so let's create this and you can see that the user was created from the external group and now with this what we can do is we can connect let's connect to the database let's use this user my mirecast.pm and let's check that we are connecting to the right database so with this we connect here i'm putting my password and then it asks me for the authentication and i type the number so i receive it on my phone and you can see that i was able to authenticate as mirecast.pm outlook.com so with this let's come back to the slides that was a pretty cool demo mirror i thank you i i really appreciate it i think this is very useful especially for a small business that is not using uh federated authentications that guys that are really using outlook.com or gmail.com but they have a weak password which means that in this case they can impose the multi-factor authentication on it and so that's that's a nice thing that the administrator that azure administrator can do in this case the sql administrator um i like to talk about the authentication configuration options that we have and from the cloud standpoint we have two of them we have azure ad cloud only so this is the authentication that does not require on-premises or it's it's not related to on-premises and it's it's only the objects are only created in in cloud there's the second part that is called the hybrid identities and this is where we use using a special tool azure ad connect you can federate uh or you can synchronize your active directory with azure active directory and the first method is for the adfs for active directory federated services that we confederated that we can use single sign-on this option is mostly used uh for the enterprise customers uh for many of you you are familiar with this for sql on-prem you can use this but there are two other much lighter options that do not require adfs and this is the azure ad password hash and password through as i said you can use for both of them the windows passwords but in the first case the password is stored as a hash password in in azure id in the second case the password is still stored in the on-premises from the azure sql authentication standpoint we have two uh uh methods there's the hybrid where you can authenticate with sql either using azure id authentication or sql authentication the second one is is a new one that will be available in public for public in may for public preview and this is called azure id only authentication first of all it's this particular authentication disables sql authentication so it disables the server admin it disables the sql logins the the user logins and it is driven uh or is managed using arbitrals uh uh arbek is the azure uh rollback uh role base access control uh so you cannot control it from sql you have to do it from uh from the arbeit and andreas will talk more about it in the in this presentation awesome thanks merrick i've learned a lot about you know how we can authenticate the different ways we can authenticate the different considerations for authentication as well as you know some of the recommendations that you know you have had given to us as well as some of these like hybrid or cloud situations that are probably very relevant to many users that are in that hybrid or transitioning state uh so thanks so much merrick um you know i'm gonna bring up andreas here as well and andreas i'm i'm interested to get your take because we've talked a lot about you know how you authenticate to these databases but there's also this idea of access and merit kind of alluded to this in that you know how do i access the database from different places and what do i have access to even see so like the azure portal uh sql tools so azure data studio ssms um so i'd love to get your take dive a little deeper on how access works and what organizations need to know like do you need to be a subscription owner uh so lots of questions uh looking forward to to learning from you archers oh you're on mute yeah it happens thanks for the great hand over anna and yeah absolutely point on so what mirik showed was who can access the system um as you want to know basically now what can you actually do right and in sql we differentiate or in azure security differentiate between the ability to deploy services not just sql but other sources in the azure cloud they have a that are controlled with a similar with the same system azure our back whole base access control um so you use that to actually deploy sql database the first time right when you install this service you can also configure it with that you would automate the the whole deployment processes and things like that so use arm templates to roll out your services for automation and for all for doing all that you would use this arbi concept whereas once we have a database and you want to use your classic t sql tools like ssms azure data studio etc um then you would use a different permission system inside the engine itself all right and i'm going to speak about that in the topic after this one so the first one being our back um that's how we look at that one so if you look at your subscription um as you rightfully mentioned right the subscription owner certainly can do all of those things ideas thing as a superpower that's the first level all right you would have a uh you would have your azure aad uh configured mirror prepared users there for example that's what you could see and apply different policies which would apply to certain resources like a sql database right and you would organize them into resource groups you can mix services on the resource group um in this slide i just have a resource have individual resource groups one for uh sql in the vm and the other one for managed instance and sql database together it's really up to you how you organize that you might be i want to have one for different teams projects etc the idea is basically you apply then those are back holes which then contain permissions on those levels in the hierarchies um to allow you to operate on those levels so for example i could make you a a person who can deploy new resources new managed instances or secret databases in the first resource group but not in that other one where you only want to have vms which may be different um overall access things like that so in azure outbreak you support the very um fine grain access control and you can create your own definition definitions to support all these different roles that you will have for example somebody deploying versus somebody just looking what is there and how is it configured does it comply with my arc with my organization's security um uh posture uh is the requirements that i have to fulfill like do i have mfa enabled here and there things like that all right so you can apply all these uh these holes and all these levels that you have you can also make make it very rough and just say in the whole subscription everything is equal if this is a small subscription may work but you can have different levels of hierarchies there so this is the first magic or that you should absolutely use for azure arbitrage use the hierarchy levels now let's have a short look inside such a hole what do i mean by granularity so we have built-in roles in azure armback for example owner contributor but also for sql specific goals like a secret security manager um who has permissions to configure security um settings in sql database and manage instance all right if you want to look in the definition of that all i have a small x appear on the slide you see permissions that are actually individual activities actions they are called for example you can set fireball rules uh auditing settings vulnerability assessment assessment settings etc there's much more all right it's just really a small accept and based on that you could also create your own custom os for just partial some partial of these actions that are that we are um giving you basically this arbitrage all right and once you have these holes you would then assign them to one of your aad groups or users all right obviously groups make it much more manageable uh somewhere in the resource group uh on on some resource using the iem blade we see in the screenshot here uh the implant on the sql database where i assigned myself as an owner on this uh level on the hierarchy quite deep right on an individual database as an owner but again this is really up to you gotcha thanks andreas this is this is really interesting uh it seems like our back is really powerful and you kind of have this way to manage and we even have these built-in roles you can use i think you mentioned there's a lot more of them but you know i'm sure we have customers or is there scenarios where you might have like thousands of databases and maybe even these databases are being dropped and created on a regular basis like how how can you is there a way to make that a little bit easier to manage sure so the idea is we really need to make sure that you use these hierarchies that we provide in in the azure in the azure system basically in the arm model where you can have your resource groups with specific sets of services that belong maybe to a certain functional role our application that uses let's say a database azure functions and some other sources or synapse right and to use those and once you have them applied at the resource group level they apply to anything below that even if there's nothing as of right now so if you deploy something freshly under that those will then be inhabited and in this structure all right cool all right so this was the management deployment of of services on that layer what you can see in the portal basically to keep it simple obviously also you can do anything with powershell cli arm scripts on templates to deploy those things all right now as i said in the beginning right inside the database once you have the database deployed by your object permissions um you can then connect with your traditional sql tools and this is where now the next level sql roles and permissions come into play so the whole idea here is similar as an our back that we want to enable you to enforce the principle of least privilege so basically to enable you to apply as little as possible permissions really just what you really need to fulfill your tasks for the user all right so only that to support the job function um also the idea or why would you bother right in case there is a compromise right this limits substantially the blast radius as we call it in case of an attack right if an attacker takes over an account and that has been properly locked down with only the really minimal permissions then but he probably can't do much more um or not as much as he may wish to be all right so on the contrary for example if you had always like very extended permissions on dbo level or sa level uh sysadmin level then obviously every compromise would immediately put you in high danger versus when you really apply this principle of least privilege then you can assure that the blast radios it may be only at certain tables involved that can be manipulated but not the whole system all right so this is one piece that's really important and we provide you with a very vast permission system in azure and azure sql and currently that's about 254 permissions that we support in sql database it depends a bit on the actual skew that you're using right mention instance has a few less actually so it has technical reasons what functionalities are available and they are all covered by permissions now the key here again is you want to be efficient so you want to use voice this is similar to the idea in our back in azure portal right you use groups in aad you can use our blank rows to combine permissions so in sql use developed term roles as well built in ones and you can create custom ones to support your individual needs all right and the third that is usually very recommended to use when it comes to working with database objects tables views procedures um is to really use the concept of schemas that we have so group your objects inside your database within schemas and this then all together makes security manageable all right so let's have a little bit deeper look here if you want an example of the permissions this is actually just a small small accent right it's not 254 that i squeezed onto here so you can download a big poster online um just to give you an idea of the hierarchical system that we have in in sql database and how that all works what's the power of that so looking at the slide we see from left to right uh a database um everything is inside the database here right now on the left part we see the highest level permissions so you can grant the permission select on the whole database that would be one simple way when you do that that means you have granted that select permission on every sub object that's in every level of the hierarchy puzzle next step of the hierarchy is the schema that i mentioned before and the last level of that of the hierarchy is the object itself be it a table or view so the select permission would apply to basically all the tables in the whole database and all the schemas all right and you do it like that so as an example we have this built in all db data writer that grants you really insert update delete those are the three operations that will change data inside the database inside tables all right on the whole database right obviously inherited down to every individual table all right as you can by the way see it's not like that every level always has the same permissions so for example the show permission only exists on the whole database versus the view change tracking permission doesn't exist on a database it only comes into play once you're at the schema level all right but again this is just a small excerpt to give you an idea of the typical permissions to use now as i said maybe this is not exactly what you want is a big database hundreds of tables maybe you don't want them to write to all of those tables or read them so here you would use then custom rows where you would basically define a whole name and assign individual permissions on any level of this hierarchy wherever you want it right but again to keep it simple i advise strongly against assigning it to to object level permissions it's very hard to report on that ideally you can manage your or ideally organize your object on the schema level so you can can simply assign the permission on on a few schemas only so the less permission statements the less grant statements you have the cleaner it is and the more easy to report on so andre this is really interesting it seems like you know like you said this is just kind of a glimpse at some of it i did put a url up on the page if people want to go download this post it's an actual poster i think there's a big one yeah if you have a plotter that's the one use case for that one um but you know i have to ask like is there any difference when we think about this when we're setting it up for azure sql database versus azure sql managed instance there is indeed um so one piece is actually also hidden on this slide already on the left hand side we see a solver admin and an ada admin this is one of the objects actually this exists in both of these words but it has a little bit different meaning right in sql database you're usually oriented on a database level you have log users that you are that are database users uh based on aed accounts um the server admin always has every access right that's what i want to point out in this slide here but the real key difference then is and by the way here's a little hint if this is not granular enough right i know it's a big slide have a look at module signing you can solve about any any problem any challenge with this module signing anyhow not to derive from that one from the original question um so the difference between instance and sql database is kind of hidden on this side the sql database approach would usually be you have an ad account shakti here in this case being assigned to a database user or mapped to a database user shakti um but you assign them to a database or in the permissions are applied to that role all right versus in managed instance you start at the instant at the server level all right that where you have your login there's a small type you see login alice should of course match to a database user shakti or the database user shakti should be on a login shakti um slight mismatch here although actually in theory it's supported by commands you can do that but shouldn't it's not really a clean way anyhow so you see this extra layer right because these logins you can refer to from every database whereas versus aed based users like mirik showed in our in his part of the demo they are database centric right and uh yeah we're looking into extending that um but i can't quite give you a timeline uh where we will align these models all right so this is the main main difference today okay cool that makes sense yeah so in azure sql database we have contained database users for all azure ad accounts makes sense yep yeah and yeah i try to bring up sometimes these acronyms here erp versus lerp i know it sounds funny but maybe that makes it easy to remember but the concept idea is really user user user all and permissions assigned to the whole that's the r in the loop if you keep this in mind then you're on the right path nice i like it yeah it's kind of it's to the compared to the agdlp uh acronym that windows users sometimes remember when they come from security trainings and windows anyhow all right let's see that in action small example here with me so i prepared obviously a database already um called whiteboard importers you may have seen that in other demos so more or less uh starting from scratch i have an account this is myself right now so i'm just showing some examples here how you can have a look at your system all right so this is me right now i'm a dbo dbo means i'm a database owner i have all power inside this very database which happens to be a wide world importer so i'm connected to that one you can also see it here in the bottom there's a way to also find out am i maybe member of any other groups or anything like that or voice in sql no i am not here i am dbo that's sufficient you can't be more than dbo and by the way a dbo can't even be denied anything this is a basic concept all right so there's absolutely no need of anything else all right i will always have all power within the database because i'm dbo all right i can have a look at which permissions are assigned to me by f and my permissions on the database level you would see we have 84 permissions that are database scoped doesn't mean there's all permissions that exist right there are other objects in the database that have other permissions this is just an example of the database level permissions back from that slide that i showed not all of them are our pair schema or per object and vice versa all right we can also have a look on which other principles we call them exist in this database so which other users or roles all of those are principles users and roles you see a couple of accounts here being set up we see a difference most of them are sql users and one of them is an external user based on an aed account and some other information like when they were created obviously we also have a view to uh detect are there any memberships in any roles and if you combine all of that you can see who has which permissions via which holes all right so we see it's pretty pretty clean here and this this model is not too much customization the guest has a special permission and the trainee account has the unmasked permission um other than that nothing out of the user yet pretty basic setup here so now i want to enable new users to work with this database ideally again i would create a hole for each of my scenarios so i will show two scenarios one for developers and one for just some random application purchasing team that needs to read some tables all right so i create a row and that's all i need to do right it's just a name at this point um i will have two users which i will then assign to those holes so devshakti a password and a sql user purchasing jr just to make sure that we understand which role they will be member of obviously this is a sql account not an aed account so this is why they have a password here and then i assign them to those prepared roles right hold this that gets the member that shakti and purchasing is gr so pretty clean one-to-one relationship between roles and users at this point and i'm gonna give some permissions so my devs i do it the simple way we have this per this build and all db ddl admin um that will be assigned to the or the other way around the whole devs will be made a member of the whole dpddl admin so as you can also see i can nest roles i can make one role member of another role you should definitely not overdo that but this case like you have a built-in hole that actually matches your scenario that is a valid scenario but i wouldn't do it like multiple layers even though it's supported and the other role the purchasing team i will basically grant a permission select and execute two permissions on a whole schema this is this next level of objects that we have inside a database so schema has a name purchasing so if you have ever seen our example databases you would have seen that these tables have prefixes it's expanding a bit and then you'll see the table names and they usually have some intelligent names like this application dot and then it comes table name right so first the first piece of the name here is really the schema name it's not the table name you also see if i press f2 to change the table name you would suddenly see no purchasing is not part of the table name it's purchase orders which happens to be in the purchasing schema all right so this is where you can notice this in the ui so i'm basically granting the select permission and execute permission on any object that is in the purchasing schema to that world purchasing so this is a very simple way i basically just assigned two permissions um in one statement on multiple objects any table in now and also in the future if anybody creates new tables all right so let's test that um there's an interesting method you can use is execute as user inside your database so you just me as a dvr obviously have this power not everybody can do that but a dbo can always impersonate any other database user so i'm gonna switch my current context now i can prove you i am now really devshakti this is who i am right now and if i look at the user token i will see a big difference to what i saw above under my own token now i'm a secret user devshakti no surprise here member of public okay not too interesting the public usually doesn't have acne permissions i'm also a member of the old deaths and i'm also a member of ddl admin so this basically all um combined now because role devs is member of ddl admin and shakti is member of all devs all these roles are basically part of my security token now and i inherit all their permissions so pitch permissions on the database we remember this uh function you will see we have 48 permissions right so remember up there dbr had 84. well we don't need to remember all the details but obviously dev the ddl admin all has a big chunk of those database level permissions all right so this is one way to tell okay so what can you actually see like objects that exist all right select star from systems so you can really see every table and obviously any schema because there are different schemas there are 48 tables um what else can you do grammar is a developer so one thing they cannot do is select from those tables this is really just for to support dda commands so you can change tables definitions but they can't look inside the tables so this is maybe exactly what you want all right so what else can he do he create can create a new schema all right that's part of develop developers work he creates a schema and creates a table inside that schema that would be the syntax for that great schema great table in that schema with some columns and in that table here is actually now full power you can also read on that and before you're wandering and the reason is simply it is his table he is the owner of the table he created that himself right now part of his development process right so he does have full permissions um there is nothing he's not exposing new data he just put it there himself right but it's something to be aware of how this whole system works right he can obviously drop his own tables in any other table as well and he can create procedures like i'm going to create a procedure per se churches in select purchase orders non-finalized with some where clause now we're gonna use this one later on um by the way he does not have the execute permission for his own procedure interesting right so this can make testing a bit tough but you could assign the execute permission and then obviously also you would see the data so he just created a or she just created a procedure here all right let's get out of here let's remember the name procedure is purchasing now that's part of the schema um if you remember purchasing um team member purchasing gel she has permission execute and select on that schema so see that let's see what happens let's just check yes we are really joe her permissions on the database not so much all right but she has permission on the purchasing schema select and execute that's what we granted her via the role and what can she see she can see a few tables those tables who happen to be in the purchasing schema you can see it also on the left side here and can she select any other tables no definitely not right because we didn't uh grant that but you can select now from the purchasing tables so that all works what about this newly created procedure remember i granted the execute permission way up in the script and shakti just created the procedure let's see it works that's fancy all right so the procedure was created after the permission on the schema was assigned so this is how it works right he doesn't uh jio doesn't have the permission to execute this individual procedure and she has the permission on any procedure inside the purchasing schema so yes it applies to any future object as well now the second clue here is why can't you see the data even though she may not have select permission on the actual table right this is another thing that i will show in a later slide due to ownership chaining all right let's log out back who i am and so on okay this was a small example on sql permissions um i hope this explains a little bit how you can go about it yeah that that was actually really cool andreas uh we might have to uh add another uh url to our description to give people access to kind of play with play with this because i think it's a really interesting learning exercise to see how those different um permissions that you're giving change what people can access i really liked it so thanks for sharing it with us sure thing so one of the concepts i mentioned in the end of the uh of the demo was ownership chain this is something that people who are new to seekers sometimes get surprised by and is important and a very efficient method that sql has actually specific to sql azure sql or sql server even this is ownership chaining so in sql you can have a following scenario you may have a user or a role and a table or multiple tables inside a certain schema data in my case in the demo the schema was called purchasing this is the level that i'm talking about a schema called data they're tables inside maybe you don't want them to actually directly accessing those tables we have columns that you don't want them to ever see or maybe rows you basically want to ensure they can see the data but only after you have treated it with certain business logic via a view or procedure all right so let's say you don't grant select permission.schema obviously then they can't select from there now you do create views in a different schema let's call it access schema those views they can be accessed by the by the users if you grant the select permission on the views on the view schema and the views will access the tables just fine how would this be possible this works if both of those schemas are owned by the same person so the idea here is basically that as long as the object owners are the same between the views and the tables this is the chain same owner app schema owner if you then place the select permission on the higher level in this case the view or the whole schema containing the view you do not need to grant explicit select on the lower level permission objects like the tables here but they can use the views just fine and access the data obviously views can contain business logic like filters or even conversions of data columns calculations etc so this is a way to ensure they can't even see the raw data from the tables but they can see the data as presented by the viewers so this is thanks to ownership chaining a very interesting concept very specific special to sql server also cool that's very interesting i i never really thought about how you might go about doing this but it kind of makes sense and sql permissions are gonna give uh it's gonna cover a lot right like if you have access to databases or tables even procedures you showed us um now one question i have is have you ever seen scenarios where customers need to use tables in such a way that like certain people only need to see certain rows or certain columns um i guess what i'm asking is is there a way to do like vertical or horizontal splitting yes there is indeed uh anna thanks for asking so now you're getting really into the nitty-gritty details here right even level lead deeper than that um there is a technology that we have in azure sql is called role level security which basically uh gives you this ability to filter out rows based on the current user who is accessing the very same uh table versus another user who will see it in different roles all right so this is used in like multi-tenant scenarios where you have big tables shared by multiple let's say end customers you have a shared schema multiple end customers end users using the same schema database schema this is where this becomes it comes very handy it can also be used even for the need to know principle where you would basically again have a logic to say well you may have permission on that table you have to select ambition you need that but on top of that you need to have a valid need to know to actually see that certain role right maybe you can't see every patient or every product patent details etc based on your whole function in this company right so those are the high level examples where you would have this this very use case so i will not show a t-secret demo um i'm gonna spare you this for now for this for this video but just to give you an idea of what this works like uh so we have here an example of a hospital right nurses for example they are in a database schema they have a representation with a employee id in this case 100 986 and they use an application all right and there is a table inside my database called patients with data from all my patients all right so in this scenario we don't want really every nurse to be able to see every patient's data because there are many many patients the hospital is organized in different departments here for simplicity i just use a wing so we have a way to reorganize our our dude our staff by wing that they are assigned to so in this example we can see the 100 986 employee idea is assigned to wing a1 versus other employees uh being in other wings so what we want to accomplish here is to make sure yeah the nurse can look at every patient's data as long as it's in the same wing of the hospital that she is working in but you can't look at look across wings fine so and to accomplish that we behind the scenes uh can implement a security policy that's what actually is called as a t sql object assigned to the table and when now the application initiates the select from the table like so it will behind using the security policy filter out the rules that don't uh belong to the current user's employee id so there is a little bit of logic and you're very flexible in in terms of what exact logic you want to use um to make sure that you only return the data that in this scenario makes sense so this is one way of filtering yeah that's a cool example i i like that example because not only i mean i guess of course from a security perspective you're only showing uh the the data that you know a certain or should be able to see but also from a business logic perspective it's kind of making it easier because by by limiting this person's access they're only going to see what's what's actually relevant to their job so that's kind of helpful too exactly yeah point one so this is a great way to enhance the functionality of your application the security of your application using t-sql you don't have to bring this logic inside the application yeah cool all right so this kind of sums up our today's topic so remember we first talked about access control and mirik showed us some great examples of uh how you can harden basically the authentication process with mfa remember we really recommend you to use azure 80 authentication over sql authentication or bait it's simpler sometimes but simpler also makes it easier uh to circumvent all right so we simply have more security around the aad mechanisms so that's one take take key take a break here and by the way it also simply makes life way easier if you want to communicate with other azure services they don't speak uh tc creatively so hr ad is the authentication of choice there really the second part that we looked at is uh azure arabic so they are remember this is a a technology it wisely and your hierarchies assign holes in in your hierarchies of the azure of the azure um subscription and lastly the sql security permission system very big very huge lots of fine fine knobs that you can use to really limit your users access again making sure that your users can do the exactly what they need to do but not so much more to limit uh exposure in case and we always assume breach all right remember always account for that it could happen so this is one on number or number one rule to keep in mind cool awesome thanks so much andreas uh and merrick you know uh so now we've kind of talked about authentication and access management uh so does that mean that next we talk about data protection is that the next topic in our series i suppose so anna you will be your host again i hope all right so yeah so yeah we talked today about authentication it was the first bucket why as you can see we covered a lot there and the next bucket pillars are going to be equally uh informative i'm sure all right so lots of encryption things coming up in data protection and network security and then the eta governance so it's really a lot of uh functionality this these that we can provide you so you can ensure that your systems can be compliant uh according to your requirements awesome well thanks so much uh andreas i'm also going to bring back uh myrick up here i want to remind people that if you want more of this or you want to follow along with this series uh we are grouping all the episodes in this series in a playlist which you can see on the bottom there so we'd love if you would join us and stick around um i think the next one will be in june um andreas if you don't mind yeah so the next one will be in june we'll be talking about data protections you can see we have a series plan throughout the whole year uh to help you and me and everyone else watching uh learn more about azure sql security um oh we got some nice com comments uh kev ball says great info thanks andreas we've seen some other comments come out come through throughout thank you um so thanks all for tuning in uh this is a great episode uh any final closing thoughts andreas from eric well thanks for giving us this opportunity to be exposed in your famous channel here all right to talk about one of the most crucial things for i think all of our customers right data security protect your data that's your assets absolutely want to make sure of that so it's a huge huge field we have obviously many pillars that we covered and will cover so we are glad you took the time to talk with us and uh our viewers are taking time to look at these detail hope it was useful for everyone let us know if you have any feedback of any sort right we are always listening although we can't do everything at once but we want to help you as our customers but obviously and from my side thank you for thank you all for participating in this presentation as you know security is the key element in every development we may have the the best applications if they are not secure this is a big problem so i hope that we enjoy this particular presentation and those that will be coming i in the in the upcoming time that maria will announce thank you so much awesome thanks so much folks thanks to our viewers uh if you like this video go ahead give it a like uh comment and let us know what you thought and we hope to see you next week on data exposed [Music]

Channel: Azure SQL

Views: 582

Rating: 5 out of 5

Keywords: azure sql database, sql server, row level security, azure sql, azure active directory, azure RBAC

Id: _yjK65JmwJY

Length: 59min 30sec (3570 seconds)

Published: Wed Apr 21 2021