Welcome to the Azure Security Podcast, where we discuss topics relating to security, privacy, reliability, and compliance on the Microsoft Cloud Platform. Hey everybody, welcome to Episode 64. This week's recording is a special Outer Band episode, where I get to talk to one of my colleagues, Andreas Walter, about SQL Server permissions. So it's only me, so Andreas and I can sort of geek out, and there's also no news.
There will be plenty of news in our next episode, though, thanks to Microsoft Ignite. So Andreas, welcome to the podcast. Would you like to take a moment and introduce yourself to our listeners? Yeah. So my background is actually quite practical. I have been working in the industry in SQL Server for about 20 years, before I joined Microsoft, and I have been dealing with all kinds of scenarios, including security, of course, and now my security PM and the team are mainly of the permission system.
SQL Server is kind of interesting, right? It's a relatively complex product, and it has a relatively complex permission model. And I'm going to be honest with you, and I want to see if you agree with this or not, you know? And that is, you know, I've worked with a lot of customers over the years on various SQL Server related projects, both SQL Server and Azure SQL Database.
I don't think I've ever come across a single customer who has really taken what I would say full advantage of the richness of the permission model that is built into the product. Is that a fair comment, or am I like just way off base or been speaking to the wrong customers? I think as a general observation, it's probably not incorrect, but I have met a couple of customers, and I have worked with a couple of customers in projects to actually make use of it.
But that has to end to do with my speciality, so I was never the average consultant for customers, so they used to call me when they needed specific security compliance, and that's where I have. But this is really the exception. Yeah, so I agree on the general statement. However, there are a few customers who actually go down to the very last and squeeze out what you can out of the permission system. So let's start at the very, very beginning. So we're talking authorization here.
We're not talking about authentication. We're not talking about audit or cryptographic controls. We're talking purely about authorization. So a user access is SQL Server. There's a login and there's a user. Actually, can you explain the difference there between a login and a user? Sure, so if you'd like to be precise whenever I communicate with someone, I very carefully choose my words. So login is reserved for accessing the server. That's the first point of contact to the server.
And then the principal, the user who is behind, okay, so that was incorrect. When the account that is behind that login, let's say in AD or AAD, Windows AD, and Windows Active Directory or Azure Active Directory, account has passed this first level of the server login. He then accesses a database and the access of a specific database is then a user account. The user account is backed or matched with a login on the server level usually.
And Azure SQL Database, you can do it differently, but in general SQL Server or managed instance, it's common to have the login at the server and then you access a specific database, a user database mostly, with the user account. So when you said user database there, is it therefore possible that one login could lead to more than one user account? Is that the case?
Or must it always, like if you've got a login of say ABC, will the user account for database A be ABC and the user account for database B also be ABC? Or can they be different? Yeah, so you can switch the names if you really want to make your life hard. You can call the login ABC and in the database you call this DEF, but that's certainly not a good practice. So normally you would keep the account names or the username equal to the login name. And yes, each database is independent.
So if you have 10 databases, you have maximum up to 10 independent user accounts, one in each database that matches with that login. But you don't have to, right? You can also say, well, he can only, like the actual account behind the login can only access five of those 10 databases, but then you only create five user accounts, one in each of those five user databases where you want them to access. I just want to just point, I didn't know that.
So just because you have a login doesn't mean you have access to a database. Exactly. So to be specific in SQL Server, let's exclude Azure SQL Database for now. It has slightly different behavior. But in SQL Server managed instance, in the moment that you have a login, you can access the server and you will be in the context of the system database master usually, unless you change the default database. Yeah, there's always an exception, but you can access the server, the system, right?
The system database is tempdb and master. You can always be in the context of them. You can't do anything in them. You don't have permissions yet, but you can exist. You can have a running session on the server. However, if you want to now access a user database, AdventureWorks, whatever, Wide World and Portals, you actually do need a user account in those databases. So this is the special case. There first comes the connection to the server and the login.
And now depending on which database you have a user account, you can connect to those. Actually, it's interesting you just say user database there. And I'm very happy that you said, want to be really precise with your words. Because I know that a lot of people actually confuse database and server. But I think that the database is like a SQL server. But it's not. You've got a server that can have multiple user databases.
You're essentially connecting to or being authenticated to the server and then to one or more databases, user databases, to use your parlance. OK. Maybe this confusion somehow comes. Some people who are used to work with Oracle, yeah, the concept is different. So that's maybe where the confusion is. Like you have a database that equals the instance. And SQL server, you have an instance, a server. And on the server, you have multiple databases that you can connect to. Right.
So a database server called ABC could have Contoso database and AdventureWorks database. OK. So I'm into a database now. Let's just go with AdventureWorks, as everyone knows it. I didn't realize that AdventureWorks LT, which is a lightweight version of AdventureWorks. My guess is it doesn't have as many columns and tables and columns. I don't know. But anyway. So OK. So now I'm into AdventureWorks. And I have a user account that lets me do stuff in AdventureWorks.
So how do I start locking down what people can do inside of that database? Thanks for putting it that way, because I can use it as a correction. You say start locking down. The actual correct way would be start granting the right permissions or start opening. Because by default, you start with no permissions. Just because you have a user account in a database doesn't mean anything. You can connect out of the database. So having user accounts automatically gives it a connect permission.
So far, so good. Now you're connected. You can run some queries on system level, like, oh, what's my current databases name? But that's about it. You can't even find out which user tables exist. And it's not a specific permission. It's a separate database. Until you get now specific permissions. So that would be the correct way. Instead of opening it out and granting something like, hey, you have all permissions.
Or you have a role which has all permissions, you should start with, well, what do I really need this person to have? And then pick either the right role that already exists. Or you create a new role where you add the permissions user account to have. Would it be fair to say that most customers will just stick with default roles that are built into SQL Server rather than starting creating their own custom roles? Yeah, I don't know the actual percentage. But there is a fair chance.
So let's say the database reader role, and DB reader, and DB writer role, they are certainly widely used in almost any environment. However, there are many customers who start using their own specific roles. The executor would be one that's very commonly created. You're allowed to execute all procedures in the database. But then customers also start creating custom roles who can only access specific schemas or have specific permissions to create objects in certain schemas and so on.
So now it really depends on how advanced the database model is, how much security inside the database is necessary. But yeah, as a start, those built-in roles are very commonly used. So I want to just hold that thought just for a minute about database reader and so on, and just talk about schemas for a minute. So I found out not too long ago, I'm sad to say, like about nine months ago, that I thought schema was like a table definition file or an XML schema or a JSON schema.
In the case of SQL databases, that's not true. A schema is something completely different. Yeah, that's unfortunately a double use of the same word. It's not incorrect to say, hey, my database has a certain schema. And then you understand by the way I'm formulating it, that it means, hey, we have certain tables laid out in this database. That's a schema of a database. You can say that. It's not incorrect.
However, in SQLSore, we also have an object code schema, a specific object type code schema. And the plural is schemas. So in that moment, you understand, hey, there's multiple schemas. So these are the objects versus, hey, my table has a certain schema could lead you the wrong path. So either a table is part of a certain schema or is under a certain schema. Then it's clear it's in the object hierarchy. Schema comes after database and before the table.
It's like you can think of a container, logical container, namespace for tables, which has a security border, versus the schema the way that the table is designed. So the word is not incorrect. It's database, database developer terminology. But in SQLSore, we have to be a bit more careful how you phrase it. So you might have a schema, I guess, that could be, say, HR. Let's just say, simple example, say, HR. Underneath that, you could have multiple databases that support HR.
Is that a fair analogy? Yeah, so let's just briefly talk about the schema. So in SQLSore, again, it's different to Oracle. So really just reset your brains here and start a new. A database can contain many tables. And all of these tables need to be part of a certain schema. So schema is sort of a logical container for objects. So if you look at the naming style in SQLSore, the three-part naming style would be database, schema, and table. The two-part naming style is schema and table.
Once inside the database, you would work with the two-part naming style. So the schema is what comes before the table. And you cannot omit that. You need that. So you need to decide when you develop databases which part, which schema a table is part of. Now, yes, we do have a default schema. If you don't decide, it will still lend in the schema and the default schema is DBO. However, you can create your own custom schema, like in Adventureworks. They did that in this example database.
They have various schemas like HR. Person as in is another one, sales, and so on. So in the Adventureworks database, to be honest, is not the best use case for schema. It's purely done for logical separation of objects. There's no further effect on the actual security. So that's not the exactly best way to do it. But the general idea of a schema is to separate out objects inside the database in different security areas.
Where you can then have an easier life granting permissions on one schema versus the other schema. That's the idea behind the schemas. So it is an isolation boundary on which you can put, essentially, access policies. OK. So let's go back to the database reader and database writer. So if I create a login, Mary, and I have a user account, Mary, by default, Mary has, let's just say Mary has, I've just created a brand new user account. Nothing funky whatsoever.
So just using whatever tools I would normally use like TSQL or SSMS, SQL Server Management Studio, whatever. Does that account have any roles associated with it by default or not? Not really, with the exception of the system role public. It's like an active directory. Everyone is member of the everyone group. You can't avoid that. And SQL Server, that group is called, or that role, is called public. And the public role does have a view permissions, like, for example, to view system objects.
But that's it. And you can't avoid being a member of public. You view the system objects, but not the contents of the objects. It depends. There are some very generic ones. For example, you can easily detect the name of your current database and such. But other than that, you can't see which user objects exist. And of course, not the contents of them either. But you can just see that there is an object that contains data, but you can't actually necessarily see the content.
Yeah. I mean, you can, for example, write it clearly. Hey, which other user accounts exist? And it will not give you all user accounts. It will only give you return you the system user accounts or the system roles. So it's not like it's returning an error. You can't see anything. But it will limit you, in that case, to the built-in functionalities of SQL Solace. So your queries don't fail, but it will be filtered. Actually, that's an interesting thought, actually.
You said filtered and not failed. That's really interesting. And I want to come back to that a little bit later on, because I want to ask you a question about columns. But we'll come on to that a little bit later on. OK, so I've granted Mary database reader. So that now gives her read access to, let's just keep it nice and simple, table objects in the database. If she has database X, was it executor or something, database execute, then they have access to store procedures.
But if she has just database reader, she can't call store procedures. Yes, that's correct. But you can also, let's be a little bit more precise, the data reader also allows access to views, basically anything that can be accessed with the select statement could even be table-valued functions. OK, and of course, the store procedure is not a, you know, you don't just. Store procedures would not be possible. Yeah, OK. So let's say Mary now has database reader. So that gives an only database reader.
So it gives her read access to all tables and all views. Like she can invoke a view and view something through the view. So now we're down to individual tables. Is it possible to, so if she has database reader, she has access to all tables, what would you do at this point? Would you say, let's say there's 10 tables, let's just keep it simple, and let's say that Mary only really has access to nine tables. Would you, can you deny Mary, a database reader on one table?
Yeah, so first of all, yeah, in this case, you could do, she would be a member of dvDataReader. And if it's only one exception out of 10, or let's say out of 100 to make it even more extreme, you could revert to the deny. Probably simple in that case, right? Just deny this one table on top of being granted everything on reading on all other tables. So in that case, you have two statements, right?
Make Mary a member of dvReader and deny access, deny read, deny select on that table, that specific table. So that's one way. In this case, it's effective. However, normally I wouldn't recommend relying on denies. Normally it would be more clean to work with just specific grants and not rely on a deny. Now, in this case, a bit more extreme, if you have 100 versus one table, deny is probably quite simple.
But let's say the other way, if you have like 100 tables and 50 of them, she should have access to, and the other 50 is not. You see, I'm getting to, you don't want to have 50 denies, right? It's just not manageable. So the clean way is to create a schema for the first 50 tables and another schema for the other 50 tables. Now, of course, it is very simplified. There will be other users in the database. So hopefully that same access restriction applies to everyone.
Probably has a reason why it's 50 tables versus the other 50 tables. Maybe in the one schema, we have data that concerns financial transactions, et cetera, versus the other ones, the other schema containing general product information, not so sensitive. So you could expect that the same logic having access to this bulk of tables versus the other bulk of tables applies hopefully to almost everyone in that database. And then you would basically have one schema for, what did I say, financials.
Financials.tableName would be the outcome, and the other schema would be products.tableName, product.tableName, and so on. And then you would only have one grant per schema. You can say, Mary, grant, select on schema product. And you don't have to say anything about the non-granted tables, because she doesn't have access by default to the financials tables. So you only have one statement for grant access to schema product. So that's the clean way.
But of course, you need to do that from beginning on, from implementation, from architecture, from designing your database on to create a proper schema. So if you're designing a database, you should already understand the different type of use cases and user access paths that the end product will support. Because then, if you want to support that very easily, then you really need to create the schemas from beginning in the proper way.
So that means if I've got a schema of products and another schema of financials, and I'm granting Mary, Reed on financials, or whatever, whichever one, that means she's not got database, Reed, all right? Exactly. So now you don't need this role at all, because that role would open the whole database, all tables, and no matter which schema. It's a database level select versus we want a schema level select permission. Right. OK, that's all falling, all signs have fallen into place now.
OK, next one. I think you know where I'm going to go next. OK, so let's say Mary has access to these 50 tables you mentioned, and let's say she doesn't have access to one column in one table. Let's say social security number, just to pick on something pathological. How would you do that? How would you design that? And what would the outcome sort of look like for Mary? Yeah, so if I'm smart from the very beginning, I would actually not ever grant access to tables.
I would create a specific schema that contains views that select the data from those tables. And in those views, you can basically do whatever you want. You can filter out columns. You can leave away, leave out columns that you don't want the users to access, et cetera. So I call this an access schema. You have a schema which only contains views, and the views do the logic in terms of which columns are actually necessary to be seen.
Like ID columns, for example, are not usually necessary for users, depends on the application, of course. And in this case, maybe the social security number, you don't ever want to disclose it for specific rows, maybe. So that would be one way. You create specific schemas, specific views, and a specific schema. You can create multiple views pointing to the same object in a different schema. So maybe you have a schema general users.
There you have a view that omits this column versus another schema, let's say, elevated users, which has another view, but which basically just select all of these columns from that table. So that would be one way to do that. An alternative to views, by the way, is using stored procedures. And stored procedures, you can do even much more than just leaving out columns. You can put much more logic into these procedures as a much more advanced way.
So this would be the way to implement this in terms of by using a smart database design. Now, the other way is, if you can't split it in any way, or maybe it's already done, you basically need to block access to this column. So let's say you can't do anything in both her not having access to the table. She has access to the table. So you can now either put a deny on the column level. Now, the nine column level is very tricky to manage, to be honest.
So I would in general never recommend working with column level permissions, because if you want to basically at the end have an oversight, who has access to which data, it's very hard to figure out if you have lots of these specific selects, denies, and column level. It just makes it very hard to have an overview of the actual effective permissions for users. But it is an option. If you have the exception, if it's very documented, you can do that. The other option is you use encryption.
So yeah, you can allow access to the column. It's just there. But the column content is encrypted properly. The social security number should be encrypted. I'm not talking about masking, really encryption. And then you're safe as well. That's actually really cool, actually. So I want to make sure I understand something here. So if I had a table, let's call it x, and it has three columns, a, b, and c. I'm not saying this is good practice. I'm just curious.
So the table x has three columns, a, b, c. And Mary has basically read access to a and b. If I denied her access to column c, would she get an error? Or she just wouldn't even see that c is there? So in the case of deny on a column level, you actually do get an error. OK, let's be precise. Depends on the query. If Mary writes a query, select a and b from a table, whatever.
Well, she's not even mentioning column c. So she will just see the data from column 1 and b. But either she says select a, b, and c, or she says select star from table, she would get all of the columns. And in that case, she gets an error because there is a deny on one column. She doesn't see anything. The error was out and doesn't omit columns. It just errs the whole statement. Yeah, that's why I asked the question earlier about filtering.
You're going to filter the results, or you're going to get an error. So OK, that's interesting. Again, I suppose it does depend on the query. If you just select a, b, then you're not going to see column c at all. You basically filter yourself already. That's not good in way by the system to say, hey, there is a deny. We just return your still as I said, but omit columns. No, this does not exist in c. We always get the same columns that you would expect or error message.
Also, you need to think about application. What if we actually would do something like that, that sometimes it returns three, and sometimes four columns? How is your application handling that? So this concept does not exist yet today. So that's kind of a database schema table column. And I can obviously filter columns by using views. So I could actually grant access to a view, but deny access to the underlying tables. And can I grant the view? Can I give the view access to the tables?
Yeah, so let's not deny the table. Let's just imagine we have two schemas. We have a schema containing all the tables, and we have a schema containing all the views. The one that I bought in five minutes ago.
So in that case, so let's say in this view schema, you have the view containing only column A and B, A and B, from the table in the table schema, which actually has columns A, B, and C. So the view has selecting select A and B from table x, y, z, omitting column C. So in this case, you simply grant the select on the schema that contains all these views, and then you're done.
So if Mary now queries, tries to query the tables in the table schema, she will get denied, because I didn't give her any grant on that table schema. There's no reason she could access it. However, if she says select from views schema dot the name of the view, then she will get the data. And this is a specialty of SQLSaw.
We have a concept so-called ownership chaining, as long as the objects in this query, let's say that the view and one table behind it, if they have the same owner, it is sufficient to have the permission granted on the outermost object, in this case, the view is the outermost object. And you will not be checked on the table. She doesn't have access to the table itself, but she has access to the view.
And if the view has the same owner as the table, she can access the data off the table via the view, but not the table directly. Even if you were to put a deny on the table, it would actually be ignored, in this case, as long as the owners are identical. It's actually really skipping the permission checking on the table. This is actually pretty cool. You do the same with store procedures, right? Same as store procedures, yes.
And so one thing I've sort of learned from this, actually, there's two things I've learned from this so far, but one of the big ones is basically just steer away from denies and just use schemas to segregate your information and then use things like views and store procedures or whatever to actually access that data. Where you're not giving directly access, you're not giving access to the underlying objects directly to your users.
Is that a relatively common model if you've got relatively complex systems? So this is the ideal model. And I have seen it, and I know many customers do that, but there's probably the same amount of customers who don't do it. So it's a matter of how experienced your folks are with these concepts, how advanced your whole database system is if you even need such complex solutions, but it is really ideal model. Because in the end, it's the simpler model.
It's simpler to come up with the proper permissions and to actually grant them in such a model versus a flat model where you have all the tables mixed with views and procedures in maybe the worst case, the DBO schema, or just one or two schemas that don't align at all with security. Because in that case, you have to, there's two options. You either have a lot of complex individual permissions picking a table here, picking out columns there, picking a view procedure there and denying it there.
Lots of chaotic permissions, the nice mixed with grants on different levels versus the other one. Sorry. And if you don't do that, most people just give up and say, OK, I just use a general grant on the whole table, on the whole database. And yeah, let's just fill it out in the application maybe. So either it's very complex or it's not very secure. That's the end story. If you don't align your database design with security. Yeah. It's what you should say filtering out in the client.
I mean, at the end of the day, the client now has all the data anyway. So you're sort of defeating the purpose against a, at least a relatively, well, somewhat skilled attacker. Yeah. You're ignoring one potential security forcing mechanism. And SQL Server is really strong, really good in that. If you don't have a grant on an object, you just don't get the data. You can't circumvent that. So we've talked about views, draw procedures, database schemas, tables, and so on. So what about row level?
How can I restrict access to specific rows based on some information? Yeah. So for row level, we have a feature that's actually called row level security. That is a very nifty way to create a filter query, the filter clause that behind the scenes filters out the rows that the user account is not supposed to see. So you can think of it of a function that is behind the scenes joint with the table that the user is trying to query.
And based on some attributes of the user account, you will see either or other rows of the table. So the attribute or the condition is usually solved with another helping table where you have maybe IDs that match to the account is locked in. So for example, you could have Michael taking care of specific customer accounts or the customer accounts have a customer ID. Michael has a accountant ID or user ID. And that matches to these bunch of customers.
And then in the filter, you would be filtered out to see only customers whose ID matches in the matching table to the Michael's user ID in such as an error. So that's a way you can do it behind the scenes. It has been done before with the means of views. You can also write your own views and do the same thing. But views are not as safe as the whole security solution that we have.
I like to think of row level security as it doesn't matter how you get to the data that often sometimes seen it referred to as a predicate or a function or whatever is going to get called. So whether you come in through Excel, let's say you've got a weakness in your permission model somewhere else and someone manages to connect to the system using Excel. That row level security predicate is still going to get called regardless.
Whereas if you're through a view, you've got to come through the view to restrict the data. And if you can't guarantee that, then that's where the row level security stuff comes into it. I actually had a health care customer a couple of years ago. It was really interesting when we're designing their system. And there's crypto here and crypto there. But there's also a lot of permissions that came into play.
And one of them was the system had to have a very, very high level of security because let's just say medical practitioners are not going to say what they are because they'll absolutely give it away. But medical practitioners had access to absolutely everybody's health care data going back. Let's pick a number, 20 years, probably more than that. Let's just say 20 years.
And one of the reasons why the security bar was so high is because any practitioner had access to absolutely anything going back 20 years. So the decision was made not because we wanted to reduce the security at all. We wanted to reduce the risk. And we put a row level security predicates in place that basically said that by default, without some kind of management override, a practitioner would only have access to 12 months of data. That's it.
And that actually really made the risk folks quite happy because it meant that in the case of someone getting in and deciding to print everyone's data out, they only had a year's worth of data. Now, don't get me wrong. A year's worth of data of medical data still sucks. But it sucks less than 20 years of medical data, right? And it was interesting seeing the security guys not so much pushback on the business, but pushback on the business. The pushback wasn't, no, we're not doing this.
We're not going to allow this. The pushback was basically, would you be OK if you could reduce the amount of data that you were coughing up? And then the business was like, yeah, by default, yeah. And so that made the security guys really happy. But the only way we actually did that was through using row level security predicates. Yeah, so I've actually written a couple of these things. They're actually relatively straightforward.
They look a lot like writing kind of like a trigger, that kind of idea, right? Yeah, like a select trigger sort of logic that runs when you're selecting it. OK, so I think we've covered off basically a lot of the core stuff. Is there any other sort of topic that you think people don't know much about or should know about, or any of your sort of pet features?
So yeah, let's say what I just mentioned in the end, this ownership chaining, that is a very crucial concept in Secrecy that can make your life much easier if you implement your database design by making use of that. So that's an important thing to look up. And I will try to come up with a good link here. And no, it's not super well documented. I haven't my plate to do that.
And you should probably be aware that we have been doing a lot of work in terms of more granular permissions in Secrecy over lately, splitting up permissions for objects into more granular permissions, or this trying to help customers to come up with just the right permissions without exposing unnecessary objects or commands, basically following the principle of least privilege.
So this is something I will put a link out here, which new permissions we have come up with, and a bunch of new server roles, not database roles, but on the server level, roles that should help to by default or by built-in roles to support customer to easier or just pick the right role, without having to think too much about the permission system and which permissions to pick. So those links I will definitely put in. So on the chaining stuff, I just want to make sure I get this right.
So this is where, in practical terms, where I might not have access to a resource, like a table. But if I call something or call something like a view, that view has access, even though I don't have access. Is that it? Yeah, you need to have access to the view. So somebody will grant you a select on the view. And this somebody also needs to understand now who the view is, the owner of the view, and who's the owner of the table. And the owner is something that's in the metadata.
It's not like a logical thing. It's actually written down. So if, in the moment, you create a table on a view, the ownership is fixed, it's either you who creates that, or you explicitly pick an account who this belongs to. So let's say you're Fred, and you made sure that you create a table on the Fred ownership, and you create a view on a Fred ownership, then the owners are equal.
That means if you now grant select to Michael on your Fred's view, Michael can access the table's data via the view. It doesn't need any additional permission. However, if Michael tries to access the table itself, it's owned by Fred as well, but Fred didn't grant access to the table, he will be denied. As long as soon as the table belongs to Ted, not Fred anymore, that concept will break.
Then you can query your view as much as you like, we just get an error message, because Ted did not grant you permission. Fred granted you permission on the view, but Fred is not Ted. So that's why ownership change really requires to be the same owner in place. OK, that's really interesting, I think. Yeah, that's something that I'm definitely going to have to read up on.
Yeah, because that sounds super powerful, but I think also at the same time, if you don't know how it works, you could either A, not use it properly, and B, things might not work the way you expect them to work. So yeah, I need to learn a little bit more about that. Any other pet projects? Do you want to just explain briefly some of these more granular permissions you're talking about? Yeah, so let's talk about that.
So one of the highlights, or one of the most elegant concepts of SQL Soap permission system is it's very hierarchical. You have a database, you have a schema, you have a table, and we have lots of permissions that behave hierarchically. So you can have an auto on a database, auto database permission. There's auto database permission, since it's on the highest one. It's excluded to the server for now. Grants you access to anything below.
So if you have auto database, you also have auto schema, auto table, you can do anything. That's because of this hierarchical concept that it inherits down to whichever permission fits. And auto is very powerful, which is why you get all these auto permissions on the other levels. If you have select on the database, you also get select on the schemas and tables, obviously. So this is very powerful on one hand.
On the other hand, it somehow makes it difficult to come up with the principle of these privilege approach. Because in the principle of these privilege, you want to grant specific permissions, but not other ones. You don't like to have automatically granted other permissions or other options, other commands beside the one that you actually need. So this is the area where we're trying to improve. So I'm going away from this hierarchy model, because this is one of the downside.
It's very nice, very elegant. But it doesn't help so much the principle of these privilege. This approach, so we are coming all new permissions that you will see from now on, basically, you can expect to be flat on a flat hierarchy directly under control. Control is always the highest permission on the server, on the database. So new permissions will be directly under control, not under auto and then schema and so on.
They will be directly under control, which means you can pick them individually without granting other permissions at the same time. So this is the general strategy from now on. And that's going to simplify things, right? It's going to simplify things in terms of permissions, just picking the right ones. I have to admit, it will not make it much more nice if you want to plot permissions on a diagram, because the diagram will not be a fancy multi-level tree diagram anymore.
As it would look today, it would be just a flat list. You've also got permissions at the control plane, right? We've mainly been talking about data plane stuff. What about control plane? Right. So this is a big, totally different area. So let's just say, what are we talking about here? So what we've just talked about was data plane in terms of what's inside the database inside a server in terms of Azure concepts.
So in Azure, we have this differentiation between the control plane and the data plane, the data plane being anything below a database normally. Now a few edge cases. And the control plane is basically what you can see in the Azure portal. If you open the Azure portal, go to Azure SQL Database Managed Instance. You see certain things like creating replicas, creating backups. But you don't see things like create database user or grant access to table, because those are data plane permissions.
These are the ones that we just talked about. For the control plane activities, like creating a new server or creating a new database in Azure SQL Database, you need a different permission system. You need to use a different permission system, which is the Azure IAM arm bank model today. And we are also working with Purview to bring an alternative to grant access to data plane permissions via Purview across multiple servers.
Because this control plane permission system really works on the whole hierarchy of your Azure subscription. So you can grant a permission, for example, to create many servers in your whole subscription. But you can't create a permission to access tables in all of these servers, because that is data plane.
And in Purview, we are closing this gap to have the ability to use the control plane view, this overview over the whole subscription, your enterprise objects, and then to grant actually data plane permissions for all of these servers in your subscription or resource group at once. Very cool. Very cool. Anything else you want to cover, or do you want to start just wrapping this thing up? I think if I would be a listener, I think I would be. I feel this was pretty deep.
If you're interested in more, we'd happy to have another one. But I think as far as it goes to data plane permissions, we have gone pretty deep. That just allowed the audience to follow that and walk through that with the links that I will provide. I learned a great deal. I mean, one thing that I definitely, definitely took away from this, one thing that's really important is the value of schemers.
I always knew that they're important, but I never really realized how you can model things around schemers. And I think from a security standpoint, access control from a standpoint, on the data plane, I think it's critically important. So I'm really glad that you sort of stress that in this talk. All right, so one thing we always ask all our guests is if they had just one little thought to leave our listeners with, what would it be?
All right, so that goes back the way that I had explained creating the right schemas for your tables. The essence of that is that I would urge everyone working with database to make sure that the designers of your database, the engineers, do take into account security from the very start that people who design databases need to understand the access patterns in terms of which application, which group of users will access which objects in terms of general terms.
There will always be some exceptions, but there are always typical patterns. And those patterns, they should be part of the architecture of the database, maybe even split databases. So for example, don't get me wrong here, Michael. You came pretty late in that project with these patients. But normally, I would expect a database where you want to limit what was it? Hascair providers to access only one year of data to actually not even have all these years in one database.
So I would maybe have a database per year, and then it would be so simple to just say, well, you only have access to this database. You wouldn't need to revert to a low-level security even to filter out the other databases. So if you think about security from the very start, you create the right schemas, put your objects in these right schemas, and create the right accessing objects, like the views or procedures, then you will have an easy life coming up as the right permissions data on.
Now, thanks for that. I really appreciate it. Yeah, and the split database idea, it's an even better idea because you've got absolutely physical separation of the data as well, which is awesome. So hey, Andreas, thank you so much for joining us this week. Again, I realized this was a one-off special episode, just to focus on one very specific topic. I really appreciate you taking the time to come on the podcast. And to all our listeners out there, thank you so much for listening.
And if you have any other scenarios you'd like to sort of pose to Andreas or any of our other products, especially the database products, because now that I'm in that group, I can get appropriate folks to talk about the area of expertise. I see Andreas touched on Perview. So perhaps we talk about Perview in depth as well. So again, thank you so much for joining us this week. Take care, and we'll see you next time. Thanks for listening to the Azure Security Podcast.
You can find show notes and other resources at our website, azsecuritypodcast.net. If you have any questions, please find us on Twitter at Azure Setpod. Background music is from ccmixter.com and licensed under the Creative Commons license.