Row Level Security

Video in TIB AV-Portal: Row Level Security

Formal Metadata

Row Level Security
Title of Series
Number of Parts
CC Attribution - ShareAlike 3.0 Unported:
You are free to use, adapt and copy, distribute and transmit the work or content in adapted or unchanged form for any legal and non-commercial purpose as long as the work is attributed to the author in the manner specified by the author or licensor and the work or content is shared also in adapted form only under the conditions of this license.
Release Date
Production Place
Ottawa, Canada

Content Metadata

Subject Area
In this talk we'll review Row-Level Security (RLS), provide examples and use-cases, discuss the work which has been done on adding Row Level Security to PostgreSQL and the current state of that effort. PostgreSQL has long had a complex and interesting set of permissions available through the GRANT system. There is another system which exists in many other RDBMS's known as row-level security (RLS), where the rows returned is filtered based on a policy implemented on the table.
Table (information) Consistency Set (mathematics) Multiplication sign View (database) Relational database Set (mathematics) Limit (category theory) Perspective (visual) Mechanism design Bit rate Different (Kate Ryan album) Information security Physical system Key (cryptography) Consistency Internet service provider Set (mathematics) Fresnel integral Subject indexing Computer animation Personal digital assistant Normal (geometry) Right angle Physical system Online chat
Ocean current Digital filter Functional (mathematics) Vapor barrier Table (information) Sequel Multiplication sign View (database) Real number Execution unit Insertion loss Hyperbolic function Independence (probability theory) Revision control Mathematics Casting (performing arts) Bit rate Computer configuration Software testing Information security Mathematical optimization Area Standard deviation Bit Set (mathematics) System call Wave Computer configuration Computer animation Personal digital assistant Commodore VIC-20 Right angle Table (information) Resultant Row (database)
Point (geometry) Functional (mathematics) Game controller Table (information) Consistency Set (mathematics) 1 (number) Mass Mereology Event horizon Mathematics Information security Physical system Default (computer science) Arm Inheritance (object-oriented programming) Consistency Database transaction Bit Line (geometry) Density of states Computer animation Commitment scheme Personal digital assistant Query language Universe (mathematics) Right angle Object (grammar) Table (information) Routing Physical system
Mathematical singularity Set (mathematics) Insertion loss Mereology Bit rate Computer configuration Different (Kate Ryan album) Forest Core dump Error message Information security Physical system Covering space Namespace Electronic mailing list Bit Maxima and minima Message passing Order (biology) Right angle Metric system Resultant Row (database) Point (geometry) Asynchronous Transfer Mode Expression Backup Game controller Functional (mathematics) Regulärer Ausdruck <Textverarbeitung> Table (information) Set (mathematics) Student's t-test Latent heat Selectivity (electronic) Proxy server Default (computer science) Focus (optics) Inheritance (object-oriented programming) Forcing (mathematics) Expression Euler angles Computer animation Personal digital assistant Query language Factory (trading post) Video game Object (grammar) Table (information) Force
Aliasing Ocean current Point (geometry) Multiplication sign Equaliser (mathematics) View (database) Mathematical singularity Tape drive Number Goodness of fit Latent heat Bit rate Causality Different (Kate Ryan album) Error message Addition Default (computer science) Moment (mathematics) System call Type theory Computer animation Query language Personal digital assistant Right angle Quicksort Musical ensemble Table (information)
Context awareness Building Multiplication sign Mathematical singularity Insertion loss Latent heat Causality Different (Kate Ryan album) Operator (mathematics) Queue (abstract data type) Selectivity (electronic) Endliche Modelltheorie Condition number Physical system Graph (mathematics) Mathematical analysis Volume (thermodynamics) System call Computer animation Personal digital assistant Query language Right angle Table (information) Resultant Reverse engineering Row (database)
NP-hard Complex (psychology) Functional (mathematics) Table (information) Set (mathematics) Mathematical singularity Insertion loss Drop (liquid) Mathematics Bit rate Causality Different (Kate Ryan album) Computer configuration Integer Physical system Area Default (computer science) Key (cryptography) Quantum state Physical law Drop (liquid) Basis <Mathematik> Port scanner System call Mathematics Film editing Computer animation Personal digital assistant Internet service provider Right angle Object (grammar) Table (information) Resultant Row (database)
Table (information) Mathematical singularity Electronic mailing list Set (mathematics) Drop (liquid) Drop (liquid) Port scanner Rule of inference Product (business) Mathematics Computer animation Causality Right angle Drum memory Table (information) Row (database)
Point (geometry) Slide rule Group action Regulärer Ausdruck <Textverarbeitung> Table (information) Computer file View (database) Mathematical singularity Insertion loss Limit (category theory) Causality Different (Kate Ryan album) Error message Physical system Arm Bit Complete metric space Computer animation Personal digital assistant Query language Statement (computer science) Right angle Energy level Table (information) Physical system Row (database)
Regulärer Ausdruck <Textverarbeitung> Table (information) Code Set (mathematics) Mathematical singularity Virtual machine Urinary bladder Revision control Computer configuration Computer animation Personal digital assistant Order (biology) Right angle Error message Resultant
Ocean current Module (mathematics) Implementation Functional (mathematics) Regulärer Ausdruck <Textverarbeitung> Table (information) Set (mathematics) Mathematical singularity Insertion loss Primitive (album) Euler angles Port scanner Arithmetic mean Befehlsprozessor Computer animation Personal digital assistant Function (mathematics) System programming Right angle Table (information) Antiderivative Freezing Condition number Extension (kinesiology)
Table (information) Computer file System administrator Execution unit Password Set (mathematics) Number Insertion loss Logic gate Information security Physical system Metropolitan area network Real number Computer file System administrator Directory service Binary file Word Computer animation Password Normal (geometry) Right angle Gastropod shell Key (cryptography) Energy level Table (information)
Ocean current System administrator Mathematical singularity Password Set (mathematics) Limit (category theory) Rule of inference Theory Mathematics Different (Kate Ryan album) Gastropod shell Normal (geometry) Physical system Metropolitan area network Real number Computer file Graph (mathematics) System administrator Electronic mailing list Port scanner Binary file Process (computing) Computer animation Query language Personal digital assistant Statement (computer science) Data Encryption Standard Right angle Gastropod shell Row (database)
System administrator Password Regular graph Total S.A. Mathematics Data acquisition Finitary relation Normal (geometry) Error message Address space Physical system Metropolitan area network Real number Cellular automaton Computer file Binary file Port scanner Flow separation Mathematics Computer configuration Computer animation Software Password Order (biology) Right angle Gastropod shell Table (information) Reading (process) Row (database)
Metropolitan area network Digital filter Functional (mathematics) Vapor barrier Multiplication sign View (database) Bit Port scanner Sequence Proof theory Computer animation Right angle Error message Information security Mathematical optimization Condition number
Metropolitan area network Digital filter Functional (mathematics) Vapor barrier View (database) System administrator Weight Mathematical singularity Price index Port scanner Demoscene Subject indexing Computer animation Query language Right angle Gamma function Table (information) Information security Condition number Physical system
Expression Group action Regulärer Ausdruck <Textverarbeitung> Table (information) Computer file Set (mathematics) Multiplication sign Mathematical singularity Password Hand fan Urinary bladder Gamma function Drum memory Metropolitan area network Computer file Drop (liquid) Binary file Port scanner Euler angles Mathematics Computer animation Function (mathematics) Order (biology) System programming Gastropod shell Damping Solomon (pianist) Extension (kinesiology)
with 9 . 5 and things like integrate policy they deal with ROS is to you know have have a view that's different for different users rated views of the data that are different now you can think of this as a filtering mechanism that 1 approach but if you filter things and so in the right way you you can actually end up having 2 different sets of data rate that you know from the different users right you could have a set of data that says so and so a really nice guy you know this is a really bad guy and depending on who the person is a again the time they make it 1 of the many of other right there's all kinds of interesting use cases around that so the whole idea here is is that you your filtering and limiting but you're providing a different view right for each individual that is connected and where do all this while maintaining and the relational consistency and then all the normalization and everything you normally get you get you still get all of your primary keys used to get all your indexes your foreign key relationships all of that we still have while we have this oral capability the other really important thing from a security perspective was to make sure we always require policies to be applied this is this is you know it isn't it isn't right the way it works inside of those Fresnel differ that a little bit later but in the what is the full deny approach you wanna have a man and make sure the policies are actually getting applied because otherwise you know you run the risk of people being able to circumvent the system we want so
the 1st wave people attended before with security barrier views may at 1st but certainly a very common use cases a lot of people have already done this encampment it this way and when they do that instability able real view over whatever table is both had some problems in older versions prior to having a security barrier as an option of you because what could happen is that a given conditional could end up getting pushed down below the calls that were in the right so you know I could I could have my really really cheap functions that gets called and that happens to be called prior to any of the so called inside of the you get called and that's really bad because that little function might just spit it back out right is being passed that's happening 1st at the problem of security barrier views 6 right with security barrier views of what you can do is you can say OK these calls must be applied prior to any function being run where she change that a little bit on 9 5 I think it's really interesting but what we did was this work done by Rashid weighted was he was saying OK where changes so that functions can actually be pushed down if they don't receive any data from the view from the table that the reference right so this could be you know from the tables in the view or from the table that's in your last policy right that as of policy applied to because of the function is in data from the table then it's OK for it to be pushed down to the same level and be used in optimization so it becomes really really handy is when you're doing filtering and things like current time or date or any date rate of previously you couldn't put that down because it was a call that you know it it was a function that was not considerably peripheral talk about we prove a little more later but at the whole idea that security reviews were 1 way of getting a single functionality in are essentially built on top of security barriers to sort out a lot about the can select side of getting data out of this unit area view 1 of things we added 9 4 was this with
check option right so the with check option allows you to have user or automatically updateable but already has a user automatically obtainable with with jack up and says only allow a record to be added through this view either as an interrupted if the result of that role could be visible through that you 3 can understand that right this so this means you can insert data through the view that you don't get to see through the view that it's actually a sequel standard capability and we know that in the sequel standard when right and what we did was we actually combine this with and this was 1 of the other basis of for the implantation of our last because the the same thing right we need a way to say OK we have policies silly you cannot insert data into this table that you're not allowed to see races the exact same kind of capability what we did was built on top of that with oral on test local is essentially you can have sets of units there on top of each other right and cast with Czech cascaded basically says that you have to be able see that data through all of those used whereas with local you can say OK maybe this other view makes it so that you can't see it but that's OK as long as the topic of use that you can then review and let it go through again is also equal standard stuff it doesn't really apply on the RLS side of things because we're only dealing with 1 table ROS world and they were doing you know at a time anyway so it's not entirely applied by the way do you get this capability is just simply add the with check option onto your simple automatically updateable views lawyer they just letter by you know if you have any questions please I'm I'm more than happy to take questions or comments during the talk just in general so we show the other thing is that the next is
the multiscale approach a lot of people have used this approach and I'm sure a lot of people have hated it because it's ridiculous you have to have all of these independent objects in all of these different schemas and they all have the same makes it very difficult to maintain consistency across all of them you know updating a table definition is is just a nightmare and so there's a lot of problems with this and you also have but like massive user inheritance across all of the the could 1 we'll do reporting across here so I've seen a lot of cases where people were like well and have a table per customer writer a schema per customer right that the gets to be terrible so it might 2 sensors don't do that but now less provides a way of getting the same kind of functionality without having to go down that route right which is the the idea behind right right so the
next thing discusses notice talk about what we have in on both present our 9 5 for the 1st big thing and I wanna point itself further than everybody would jump right into creating policies and what not was really important realize yes yes to enable free turn on or less on upper tablebases right now right ways you can address and security-conscious system might be to have event-triggers which are now in 9 5 something unpleasant looking at require that every table have ROS enable 1 you know at the end the transaction or something along those lines that's the kind of thing event triggers and allow us to do that we have on so those those plus the parts which we might have to have or things that we would need to do but ballpoint years asked enable oralist for every table that you want to have a role filtering applied to and what that does for starters is is all access to that table must now go through a policy right so this is getting back to that default I Approach right if there are no policies on the table we create 1 right not without stored anywhere we created in line and during the actual query rewriting and we just say a false right essentially right so this is the automatic default deny policy 1 of thing things that's really key to understand you know is that table owners who were the ones who control policies being applied to table in PostgreSQL because Table 1 control everything with objects in the universe that we now want change that someday too but right now they control policies and therefore they are automatically excluded from roles security even when it's turned all right so if you alter table on and you're still in you're the owner and you pretty you August Europe right this also means that things like PG don't work right to really important that PG don't works but you know for regular users and as well as the super uses still think role security is not applied all of a super users by default right arm on the talk little bit about which you can make it happen but I just realized that decays so the other thing is that when you disable are left for the table the policies are no longer used but there's still in place right the policy still exist on the table so you can if you disable and re-enable are less given Table 1 everyone of course realize that if you disable policies indivisible or relies on the table anybody querying that table is there to be able to see something right so to just be aware of that that's you know released if you didn't commit rape and and other people will see that so it's not like a per-session thing instead we have is something
called the Rose security so what rose securities and you got the got added along with which allows you to control this behavior principally around the table orders and super users so will using several security to on is normal and we do the normal or list of that I've been talking to you considered too often says never ever apply oral less however what that means is that you're error back instead right if you don't have rights to bypass or a lesson that table you and I get an error thrown back at you because we don't want you to just be able to say on several security often then no policies applied northern work right so this is something also that PG dump dunce by default right digital-by-default sets it had to all and the reason for that is because when we are modifications that when using don't you want to be able to see all the data right you're doing a backup you shouldn't you probably won't be seal the data now that said there is an option to say PG dump you know several SecureID on instead so you can get a dump of just is visible to you in the system for the last option yes anyone conjectures often yes because if set off and you try to run a query where are was would have been applied and you are not you table or the superuser or you have a bypass got the part about then we throw an error you don't get any data and we use for an error if you ran PG done with this set will by default yet not yet if you said you weren't PG done by default where it with yeah exactly it will throw an error yes it if it comes across a table that you can read it in because of the policy yet it was wrong that I was why there is an option say go ahead and allowed to do that allow immediate so results this is principally because all felt that don't primary focus was doing backups and kind of uh I realize it yet it's certainly a huge use case for it I actually think it's more generalized than that but whatever and find the other options for for so what force does ROS is even if I'm the table on our or on the super user I can force low-level security to be enabled for me so I can see what happens right this is gonna debugging kind of tool rate of force oral less to be enabled when it would apply to me and I'll talk a little bit because different policies in the at 5 different people but but that a minute the whole point here is that I wanna be able to do kind of like debugging and what not so I considered the forest and policies get applied for me just as if I was in the table on or I was in the Super User in questions but rose security guard so it can actually talk
about creating policies OK so policy the table specific right they and the namespaces also per table right and the way it works is that on you know you create a policy and you have a whole bunch of different options for creating that policy OK that the different options that are available here are for we all list of commands here right so this says that for a particular kind of command because of often like selected entered you know we can apply the policies only in that case price we don't always know if you use all that old life for all cases again part of a more a white matter but to names says I want this policy be for this role right DeVault is public but you can set it to a specific individual student role names right and then we have using in with Czech expression so again the using expression is the expression that we used to filter rose right this is kind of your you're notion of what's visible OK to this particular it through this particular policy OK with Jack is just like it is the 1st security very reviews the idea of with objectives that this controls was allowed to go into the table right what is so have the pass the with check option to have this data be allowed to be added to the table right and that's true for either inserts or update the reason that we have both of these is that for some of these commands factory for most of them you only get 1 or the other right so select only use data using expression OK course if you use all that you can specify both is was it'll it'll order just do what's right however for insert only with Czech apply right only makes sense to have a with Czech policy on insert update however gets both right because update the using pieces what roses that update allowed operate on the existing table today and with check is what rows are allowed to go back and is the result of the optic right yes yes yes so this expression is a generalized expression Works basically the same way where cost us and and that under the covers what we actually does of doing it was shown in the where clause so you can put bread anything you can put nowhere cause there of course what that means you can put like aggregates in the use of query right and get the results of that and stick it in there but you cannot use an aggregate function directly inside of a policy yes so OK idea any columns metrics are used in Europe are expected the references to the table they all references to the did you say you know using I have I have some examples and about without but the the the short answer is that it gets hums along then is the minimum age record everything else and that the data so
here's an example policy right and what use the same policy moment in the same effective policy for a whole bunch of different ways of creating policy right so on a start very simple get more more complex so this is a very simple policy rate for creating a policy in P 1 on a table name might you know P 1 is saying a name that's inside of the tables actual name just name for policies on that table with it means that you can have the same name policy for a whole slew of tape right which makes things a little more convenient for scripting and what not right the next piece is this using calls right so using my user so my user in this case the column Additive writing that enters question about the earliest use reference the columns that you would otherwise maybe you might as an interesting question but if there is a risk that you can't do it because of his ability concerned as from a good idea for a while ago the guy curious I mean we can do it my user equal numbers of query here yet that works by the the issue tho is that if you want a reference this column inside the sub-query where you're doing a joint to some table where you look up in some other Taylor has the same column name it might end up being ambiguous and have a look at it and get it it does know local people spit that can use an error rate which is obviously not terribly helpful you might be a way of dealing with that but music thought probably and money's American getting 4 9 5 that make it so that we can have an alias to this like current . whatever but then of course look for you know if you chemical currents of the think about that that will we would want to break if you want actually use a typical current I should I might just work in that book yet it might not even elicit the answer table of yeah actually on my table I think that light of were already have a look at like your member of hands are it's a while there where I don't know you could do something like that yeah but that's a good point use similar know at the data probably wouldn't work at the look at the time the go back and what sort of interferon about the 2 great question no I this talk like 4 times the 1st person before some of other questions about the 2000 and is moving on the on this a simple 1 right so this
again is the same policy that we had before now I'm adding more not more things here so these 2 are equivalent right there exactly the same way as the by using if there is not already a year if you haven't by with checkpoints cause I will do is we take the using cause an applied for both sides right just Canada with check is done for the view by default right same kind of thing and so these 2 end up being exactly equivalent to moving on now I've added this for all here right so all is the the fall all types of commands you can change that to be selected so that they believe you what everyone to do right now and saying to public so to it you know again this is for specific roles by default it goes to the public you know because the public which means all roles 1 the big reasons we did we had the ability to specify roles because of performance consideration right you you may want to be able to have a bunch of different policies on the table there for specific roles and to have to do that with just a policy by itself would mean you get to do extra look up another work and so it seemed like a common enough OK someone add explicit syntactic capability for it so that's what it means so moving on
island into specific policies right of the command in particular the that this is they select policies and again this is a case where you can only say using you can't say with Jack but this means that it's only you apply for queries that are doing selects against the table right so essentially queries they're getting data out through select command from the table we have this policy applied so insert again only works with with Jack into the analysis valued specify a policy for insert based approach and then
this is 0 the 1 case where you can have an update policy right so this is kind of an interesting use case in and all other people we've been kind of mulling over this question about if it makes sense to have these kinds capabilities so this 1 allows you to give away roads right so you can only operate on your own rose right through the using cause but when it when you have with checked equals true that means that I can update that there have some other name inside of my user as the result of the up and so I can give my rose to someone else right I actually think that that is a pretty viable you pretty reasonable use case in a lot of scenario maybe you're in a hospital right and doctors are only allowed to see their own stop but they can give other people by other doctors in the united by some of the policies of the restriction to rounded beyond as the simple 1 but the idea stands right they can also be very interesting for queuing system right you're passing things between different cues you could do that with less than all on new annotation in the there are no I don't think so the the the using all I basically know with it but using is only going to be 4 rows exist in the table and with Jack is only in the 4 rows are being added to Uganda implicit we have that distinction but you cannot you cannot always check policy defined against what the old rows in the table workers addressed but only mean that we're using is for you filter those rows don't want them to be able to do it but the interesting thing here is that you can do this in reverse to right you could have using the true and then have some calls on with Jack of that is kind of a more interesting question is that useful I had have a hard time seeing use case for being allowed to update records which you don't have access to write these would be blind update but technically speaking we allow that through the grant system right to grant system using graph update without renting their however because conditionals no incentive aware cause if you're referencing any of the columns in the table if you have access to those columns you can use that conditional for the only updates you can do the grant system the day the volume of is update is the whole table right and this is how it works today has got an interesting twist if you will so still very much an open question about whether to use use case but for now it's there for a while it does however you have to consider that way they have that problem right so this is a it's really a question about whether flexibility is useful to have you know right now we have the flexibility you can put anything in these things what we could potentially have done instead will say here's a select policy you only to have 1 using policy for all operations on that table right but I actually kind of like the flexibility because I think that that would you know that we probably 1 of the the same thing on with Sakharov freedom is ending with checked and that would be weird right and so I actually feel like this is a very 3 simple he's you explain what because of ideas is perhaps a good enough reason is to keep the way it is now you absolutely I think that it's possible maybe euro in your queuing system you're allowed to acquire rose from the queue right and and then make them visible to yourself operate on right maybe that makes them invisible the other guy is going to do something like that rather than as more would uphold models of push model right so I could see I could see somebody building system like that are potentially using these are these interesting give abilities but we are so you
policy pretty standard stock and so actually the acts of only cut on 2 things right so that the delete policy here only as using cause because you only hear looking at rose exist in the table when not adding rows back right with Jack example modifying existing stuff really it's about what rose rather go back into the table that's what believes using what is also points out those that update takes using laws and that shows that allows what the visibility is for the rosy erupted right that's another of visibility for the rows that are in the table to the update command so some of you may realize that delete an update and insert all have returning right so if you did set up a system for whatever reason where you're allowed update rows that you're not allowed to see you right through the select policy you can still see them because you can returning clause that returns those rows back to you so again this might actually be a good thing may be a bad thing definitely if there is something important understand right these using policies are what is this command allowed to operate on and anything that that command is allowed to operate on is also something the document could potentially return back to the user right through returning so if you have a delete policy that said that they were allowed to delete rows that they can't see through the like policy they could use a returning calls and still get them back at I don't really see too much of an issue with this uncertainly certainly curious if anybody has any feeling about it 1 way or the other so there is something we can consider but that's a thing yes yes absolutely could use 1 of the yeah but they would actually be able to see how many rows got deleted right not the only 1 regret using a primary key than they would know based on where cause of that was there and I mean that's in there you can find all kinds of wacky ways to deal with that 2 from you have that kind of a problem but in a personal layer blind updates and blind elites are not things that you know I think if you create your policy that way you did it for a really good reason you understand results or you screwed up but I know that they can form or a likely was another common a want yeah but how about all all all how how I think you understood the common correctly what you're saying is that if you need to be able to do something like that you could go you the other approaches that focus on 5th all if you're allowed to a so we have column emissions that that before so you can just you can you can on a per-call basis deny access but that's completely independent will still work just fine and was completed binaural asked for the rate of ICT deformations C that T of which this all columns right so this is all of the state of the world so all of all of you or you are right if you take the policies of her heart by adding the biggest question there is I don't blind elites are the other thing that we should be trying to encourage people to to use in a social we try to put in protections to protect them from having that happen in our hand I really you know if you want you blindly from a lot of your policy that your your prerogative in my opinion but don't come crying when returning calls works the way it's supposed to be yes yeah yeah absolutely yeah yeah absolutely it's is not trivial to to make that happen in super simple case we used improvement then all again account all all all all all 9 6 2 100 million II at 100 per cent agreed we need funding policies and other things and all the other the other half of this is actually some from area of my talk which is on as the Linux support right if you go to Linux support you can have all very straightforward policies across all the tables and let as he Linux held a hard question of who's allowed actually get access to what's right away that's right make it that actually works already there and we haven't done but not 9 by but will be talking about that there anyway I will not move yet last thing for everyone but yeah this that's correct right here in the hall yeah yeah there was there was a lot of discussion about it I mean this is how we ended up the yet providers question about it and the knowledge that we have today I'm not sure when you go back to that discussion what I think I'd rather do is have something like default policies applied so they created automatically and it will let you create them in the problem with this tho is that you know different tables of different columns it it's for the trigger function when you actually going to find the trigger at upper tablebases yet and is the more you have to look at what yeah right the FIL but but you can use the same thing here you can have a function you know you have a function and you call a function from such policy definition so all your policy definitions are very simple like reading a trigger Aniela complexity in the function get idea the likes of works we have we something go inside of the policy that are going on
so all the policy relatively straightforward right so the older policy large integers a definition of all the big surprise and so here are the options for older policy pretty straightforward stuff you have the ability to rename them but you know obviously you always specify the table name because the object surreally policies or tablebases not that interesting on 1 of the things you can do is you can't change the command of a policy right now because of the whole update with check what's allowed we applied the what stuff right so you can't change that's why you don't see command in here as an option obviously you can simply drop the policy and created again if you really want that policy to be of the D P 1 balls for updates instead of inserts is that the drop in recreated it's it's really pretty for a a supposed yes setting in city a policy setting the using which our policies the true would effectively be the same as dropping it accepted Delaware actually dropping the gap here you can do that here's some
examples of alter policy and again pretty pretty straightforward and you can change the using clause with are cause were you know whatever I mean appropriate for for this this is for roles right change the a set of rules that are are notes on policy applies to new you have a list of roles to 1 . 4 of that later on when you create policies even list multiple roles for that policy to be applied to just like any other duty on both production all cannot
stand back at the right drop policy on ice remember that if you end up dropping all the policies of the table we will implement that people deny policy right so you dropping all policies of table that the way to get to see all that in all rows that they were a Boise all rows in the table was to turn our also the table like this is I got quality and you have the if exists support we don't really have a accretor replaced kind of thing but we do have doubts about exists on by a couple
slides here and I think we talked through a lot of different things here but as wondered describe a little bit about the group allograph system and policies some somewhat different right so is all about cable level actions and it's also all checks that happen at the beginning of a query right at the beginning of a query execution is where we figure out OK are you allowed to actually run this query at all right and answer is yes and no right you the you you can see everything you can run all query completion or you can see that was quite right so traditionally that's why we've gone to using views to limit data because you know that you can actually have conditional on it right so what policy gives users table level are below anniversary row-level capability right arm and it's simply the gives the conditional you Dad as I described before so the 1 case where we will ever with policies so when you do an update on insert basically when the with check cause fires right if the wind yeah cause fires and you try to do something that wasn't valid we will throw an error back and will throw an error racked Mary how many rows you're trying to insert at that time and you're entering 5 thousand Rosa values and 1 of them violates with check policy when a bowl whole thing out right a lot of different from how select work where you can only see certain road so this is something that to keep in mind of course you can look at what the policy is and use the conditional from the policy in your insert statement your make the absurd statement and inserts select that then has a conditional wanted to address that concern if you need to adjust the aware that we we whatever the whole thing back if I if the which are policy file fire and you were trying to do something you're not allowed to do through the policy so here's a
couple examples of what it looks like it when it comes out new of access to the tables so if there are any policies on it this is what the initial point right in here you can see I have a policy P 1 minus current user and then and in this table T 1 is what the current data in the table and then is a super using from have marked so you can tell that this is a case where we were doing you know I'm just showing off with India the now
actually several different role and see that the result of filter as I expect right so again if there's no which are called articulating specify go
back 1 I did that it there's no way to check here on this but this policy applies to all commands so we're taking this using clause and applied to both case so
here you can see if I try to insert data for a different user rather than my own get this error by this policy here this is actually slightly different now I did this against an older version opposed to that in order to make an updated is actually now says something about world policy right instead of saying with Jackson delivery clearer now thanks to the machines and work he did refactoring from the code allowed us to have a more descriptive error therefore oral so here we can talk
about data modification policies right so here ominous this policy my use co-occurrence current User again with check true right this is the case so so I go for I new blind inserts if you will so here you can see I insert data it does get in the table that I can't see that again just to be aware of can talk through other considerations around from doing much more here so this is made out of
but talking about about a lot of already in this but this idea that we have this check when and this could be my data might actually be like an old my label or we'll just label with that so everything as Linux away balls to actually you know if you train effort you can integrate this with the SEP T-SQL module which we've done and I hope to be able to publish some of that very soon to actually people at all or is it homework for you @ @ @ @ @ nothing up while Argentina publish we've actually because we've been able to get it to work so far all OK go ahead of the the prior condition was different what this implementation does essentially give you and all the the primitive functions that you need to be able to write policies they call up the functions that yeah yeah absolutely so and we've done that in like this or a new person that out soon it will make 9 5 obviously because of how far past the freeze but the hope anyway updated CPU SQL module 4 9 6 to include these primitives which will then allow you to create policies that can do that right so working looking forward to that mean of the ruining the
use of scanner final in in a big example of that word know rather than those little tiny contrived was a real and more real if you will so this is the idea that it's unit Harford file tool for most people are familiar with username password UID GATE bubble walk by and we have these 3 different roles in the system you the administrator whether you lot more stuff you know we have normal Bob and Alice stuff right so
you're 1st populate the tables right this is done as the administrator was kind of setting things up this is how we build the table we have this is the set of values right we have the username and then we have you know the actual name phone number of some directories and stuff right and then we have this enable rose security right through actually enabling the policies rose periodic policies of be applied
then we go through and create our policies so balls is that already create are this admin always says OK the admins allowed to do anything out this this could actually be simple reason using true rather than using an with Jack but in some cases maybe you want to both make it explicit right normal users can see all the rowte right and the other update their own record but they can only do certain updates with regard to the shell right this is pretty difficult writers commands out there CHS safe would allow users to change jobs for example this is the same kind of idea here so this is the using policy that says OK the current user queries can see there you know theory you name is there then they're like to see that on as the odd update that record right but when they updated they draw user has to stay the same they can't change that and the shell must be in this list OK what this allows the so to the administrator can set the shell to whatever they want but the user can always send it to this set not as this could be a select statement instead right you go out on the table and select what what shells people are allowed to use kind of the valley and the grand system so we set all the graphs so you know the ideas to do everything uses only to select axis on on what I'll call public column but they can't answer like taxes on profit column in particular for 1 you see that's not listed here right and then uses only allowed the update certain Colorado update the path the ruling home phone gel so when I said that they were
able to you know the have the result be different from those I think it's a good idea to have been the policy although it's not strictly required because another update that column anyway so they should actually be able to create a role they were that's not the case but for my 2 cents about skating policies on land that do anyway just make sure so anyway so there are allowed
update these column on traffic on and moving on hunger
than our several admin and show OK can see everything in here now and see
what Alaskan due from several Alice we do this table past the reading you have I'd right Alice doesn't have access to all the columns in the table where you get permission I allow us to specify what columns you want so here we are we working but by these columns and we can see all the date of roles got I wanted do an
update password achieved my user as I said before they're not all there were certain things than do right so 1 things that regular users in order to do is change this complicated Commissioner 9 but as the latter change a real name was used in that Alice so works just fine interesting thing no she tries to update where it's somebody else she gets an update 0 just an error because the visibility policy says it update is not allowed to see those rows so she simply doesn't know update want nothing happens that's the why this update only returns 1 to 2 lenses of no conditional here but this is updating all rows of Alice is allowed up to here is the update average address at the cell something different you get a violation of the policy that that now several policy I delete other delete so we get an error back from the grant system this is also from the grant system if you try to insert a new path to use a new row but Alice's allowed a change from past and networks as far as
I am unfortunately running out of time here is actually an hour-long talk with we only a 45 minutes here but do 1 I have mentioned again only prove function we talk about them a little bit before we prove function the function taking it pushes down through policy same they get pushed down through a security barrier view these are functions that pose grows we have decided that these functions can never ever returned data back to the user right and even an error condition so those are allowed to get pushed down that means on optimization 7 that couldn't otherwise great but we can certainly talk about some the things about it but a lot of you always hear users can make them we proof of course and they must actually be leakproof if you make a non we prove functions safely proven is not actually prove sorry don't do that on a
quick explain example sequence in sequence stand on that this is this is applied policy on
here you can see we are able use indexes and with policies which is great is
someone what's more complicated you can see here we 1st applied policy then we apply the filter that's
required and that's laughter hadn't 47 minutes so have talk further for a few minutes but I just want to know you have to get out and 1 only given the person's weight on dynamical you of the condition and a question that's right yes policies kept policies are kept even if you do yeah yes the answer is yes yes but that so there was some discussion about potentially allowing them to be applied to views and being applied to foreign tables and other things in the answer that right now now is something I definitely wanna look at it but currently you can't so it's an interesting idea but you but but the conditional inside the view of the wondering great another view over you what everyone of you there is a there's other ways of getting a little problem is friend would rush through that but curling up other questions in the back but but I show that the grant system is independent and gets applied so yeah you get you get you like select star and if you don't have access to a column not see it wouldn't do that if you select story the have right across all of them there's no way to do per column you can hide columns yes all the so if you pay if you use of function inside of your query at all yes you can have whatever whatever functions you wanted the ball of the same way as you know whatever whatever query whether the new ones that of a security barrier view same that so and we don't worry about putting that down because you defined right policy administrator defined it when used in a lot of the if it's the malicious user is allowed to change the function defined on the policy you're screwed yes go yet if it was worth funding actually something create policy supportive not
exist I could be wrong on our whole offhand where the
syntax for reality order
operate on think we have that I
don't show it here anyway I don't think we do today we we can add it yet yet another of you the that the desert and cantilever thing is and if you leave know look at which transmitted nicer anything like you should never edit this is we don't do that next week but I I have a very wrong idea updates the the digital time that the ad slot slot yet again summary file yeah yeah yeah all right OK did you don't has the ability to allow policies to be implemented while you're running yeah well what is the needle here all don't don't policies Solomon I'm doing that I have to go with that we have yeah I I don't think we did that we was in like probably a good idea to certainly would adding that again probably 9 6 material the action and the other questions we the middle by thank you