So, where we left off yesterday we had done a quick tutorial of SQL, today's primary goal is ER modeling, however I need to cover a little bit more of SQL because today's lab in the afternoon is again SQL, so there were a few more things in SQL, there is a lot in SQL, the current SQL standard back by around 1992, 1992 had already gone up to several thousand pages, 2000 pages and it has been increasing steadily ever since, they broke it into parts because it looked very embarrassing, so it goes into I do not know how many thousand pages today and there is a huge variety of features in the SQL language. Now not all databases support all features, so the parts that we have been covering are standard SQL features which everyone implements, now the set of things which everyone implements has been steadily increasing over the years. So there are many things which we are not going to cover because of limited time which database systems today do implement and chapter 5 has coverage of many of those things, but we would not have time to go into it in this workshop or even in the main workshop we will have a little bit of time we will skim the surface on chapter 5, just to give you an idea of what are the new features which SQL implementation support these days. But today we are going back to simpler features in particular we are going to look at the outer join operation, so what is a join expression in SQL, we already saw the natural join operation, the outer join operation seeks to match rows just like the join, but there is a difference from the normal join operation, what is the difference? The difference is that if one of the tables has a row which does not match any row in the other table in the normal join it is discarded, there are many situations where you do not want to do this, but let me give you an example, we saw yesterday a query to find out the number of instructors in each department, now supposing I want to get a list of departments with the count of instructors, we could write the query as we did yesterday using a sub query, supposing I want to get a list of departments with their associated faculty and if a department does not have any associated instructor I still want the department name to appear in there, so what would I use, I would use an outer join operation and we will see the syntax shortly, so an early version of SQL this was not supported, you could actually get the same effect using more complex SQL query, you could take a join and then in a separate query you can find tuples in the first relation which do not match anything in the other relation, so then what do you do with those tuples, you union them with the first set, so a join, so let us look at the syntax for outer join and then we will see what it exactly does, so the basic join expression in SQL in the from clause is a Cartesian product which of course is usually a bad idea and it requires you to add a join condition and the problem with this is that it is easy to make mistakes and forget the join but it is okay, people have got used to it, but if you want to write it from scratch you can write it differently, so let us take a slightly different example which is course and prerequisite, so supposing I want to show all courses along with their prerequisites, now not all courses have prerequisites, so I still want the course to appear with no prerequisite shown against it, so that it is clear to people that there is no prerequisite for this course, so here is a small relation course with only 3 roles and a prereq with 3 roles, you will notice that bio 301 has a prereq, CS 190 has a prereq but CS 315 does not have a prereq, on the other hand there is CS 347 here which is not in the course relation which actually violates the foreign key dependency, but just for the purpose of illustration let us pretend there is no foreign key dependency and this is the database that we have, so now let us look at how outer joints work, so this first query is course left outer joint 3 req, what is the left outer join mean, it means the relation on the left side in this case course, even if it does not, if a particular row in course does not match any row in 3 req, in the normal join it would be thrown away, in the left outer join the rows are preserved and they are retained, but of course you do not have a matching value for columns from the other relation, so what you do with those columns you put a null value, so in this case this is a natural left outer joint, so let us just see the schema in the previous slide, what is the common attribute here, course ID, so natural join equates course ID, note also that course ID is not null here, if you see the course ID from the left relation is 315, pre-req ID is null, there is no matching tuple, so if you actually looked up the course ID from the pre-req relation for this third tuple it would be also null because there is no tuple there, but because this is a natural join it takes a non-null value, the column appears only once, remember natural join column appears only once, so the non-null value is taken for if you split this course ID into course dot course ID and pre-req dot course ID, pre-req dot course ID would be null, course dot course ID would be not null, but since they are merged we keep the non-null value that should be straight forward, now there is a symmetric natural right auto join which preserves rows in the right hand input, in this case pre-req, so there is a row in pre-req which is CS 347, CS 101 which does not have any matching course, so you will observe that it appears here with null for all the fields from course, which are not there in pre-req, course ID is now not null because it is in pre-req and that non-null value is taken for course dot course ID it would be null, but it is merged into the non-null value and finally there is a full auto join which preserves rows in both relations, so the first two tuples as before are from the inner join, so not that the regular join is called the inner join to contrast it with outer join, so the first two rows here are common in all the relations they are the inner join and then the outer join adds extra rows, in this case all rows from both sides are preserved, so we get those two extractable, any question? So, the SQL syntax for this they could not quite put it in the wire class, in fact oracle tried to do this, oracle those of you who have used oracle may know about the syntax where you say plus equal to, it is actually a very clumsy notation and oracle eventually has given up on it and they have they still supported for backward compatibility but they discourage you from using it, so don't use it, this is the right syntax which is supported by all the major databases, so now the general syntax for outer join has to say which rows have to be preserved, so it turns out that in the standard SQL from clause you just give a list of relations, there is no ordering, what order you list I mean really does not matter except for the order in which attributes appear in the result, other than that it has no impact, for outer join the way you do the outer join, so supposing let me write it here supposing you did R and let me show the symbolic notation for outer join here, R left out sorry R left outer join S, left outer join P, so this is a natural left outer join in the relational algebra syntax, supposing this is your query, now in what order do we do the outer join, you could interpret this in two ways, one is like this and the other is, now if you did this with natural join it doesn't matter which one you use, whether you use this one or this one the result is exactly the same for natural join, it doesn't matter, however for outer join it does matter, if you did this you will get a one result, if you did this you would get another result, so what is the difference here supposing there is a T tuple which does not have any matching S tuple, but it does match an R tuple, in this case the T tuple could match the R tuple in get output, in this case what would happen if it does not match any S tuple it would be eliminated and it would not appear in the final result, because it's a left outer join not a right outer join, so these two are different and therefore the syntax of the query itself has to make it clear whether you're using this or this, S tuple actually has a default, if you say nothing it assumes that, but at the least you should be able to write it out in order, if you write this using the old oracle syntax, it's not clear which you mean, there's no way to clearly specify whether you meant this or that, so the syntax now explicitly asks you to list the join almost as if it were relation algebra in the from clause, this is where the natural join also came in, remember we wrote the natural join in the from clause with parenthesis, all the join expressions in SQL follow the same basic principle that you have to list it as an parenthesis expression, the parenthesis can be left out but it's implicit, you have to do that in the from clause and we will see examples, but the syntax is fairly general, it has two parts, the first part is a join type and the second part is a join condition and you can mix and match between these two, so what are the join types in a join which is a regular thing, there is the left outer join, right outer join, full outer join, the join condition you could have a natural join, what does that do? Matches all columns with the same name, but it has one extra effect which is it removes duplicate column, on predicate applies the condition and only allows through matching rows which satisfy the condition, using a 1 through a n list of attributes is exactly the same as natural join except it's only on the attribute which is specified, the other attributes are not equated, they stay as is, so that's the, these are the two types, the join type and the join condition, you can mix and match and get any expression you want, so here is an example, course inner join freereck on course dot course id equal to freereck dot course id, this is the standard inner join, you could have written it as from course comma freereck where course dot id equal to freereck dot course id, in contrast you write course left outer join freereck on course dot course id equal to freereck dot course id, now supposing you instead did the following, course inner join freereck and we need some on condition, say on true which means all rows match where course dot course id equal freereck dot course id, this is the normal SQL, this is what happens in effect because it takes a cross product logically, the optimizer will try to evaluate it in a more efficient way but this is normally what happens, now supposing instead of saying inner join I said left outer join, what will happen, so the first step is every course and freereck pair is generated, whether it matches or not and then the filter is applied but what happens, does this give you the left outer join, no why does it not give you the left outer join, you are applying the condition of course dot course id equal to freereck dot course id after the left outer join, in this case the left outer join did not really have an impact because anyway everything matches but if you had a condition here instead of on true, I put some other condition here, you would get the left outer join but then this condition may eliminate all those rows which had null on the right hand side, in this case there would not be any but if there were a condition here, if freereck dot course id is null this condition would eliminate all those rows, so the point is if you want a left outer join, the condition has to be part of the outer join specification, the on class, you cannot do it in the where class, that is the important point to note, is this clear, so whenever you specify left outer join, right outer join, full outer join, the condition should be specified right there not in the where class, do not put it back in the where class, so moving on, the right way to do it here is course left outer join freereck on that condition and then there are the variants which we discussed, we have the course natural right outer join, course natural left outer join and course right outer join freereck using course id, which in this case is the same as natural join because that is the only common attribute but in general it may not be the same, this should have been full outer join, it is not right outer join. So here is, here are a couple of questions, you do not have to actually answer it with a clicker but just for you to think about, are s left outer join and s right outer join the same, no of course not, very simple question, then the next one, which of the following give exactly the same, are natural join s, r join s using b, r join s on r dot b equal to s dot b, again a very simple question, exactly the same result meaning the same set of columns, remember natural join and join using both retain only one copy of a column, if the same column is there in both retains only one copy, whereas on retains both columns, whether they match or not both columns are retained. So it is not b and c, a and b, a and b are the same, c gives an extra column, which in the case of, in this case it is a regular inner join, so both will have exactly the same value but they will be that way, r dot, what is the common, r dot b and s dot b will both be there. In the case of left outer join, supposing we had the same thing, but c part is r left outer join s on r dot b equal to s dot b, then you may have rows where r dot b is not null, s dot b is null, you would get that. So that leaves us just a couple of small topics, which we will wrap up SQL with. The first is view definitions, you are all familiar with view definitions, we saw the width clause yesterday, views are more or less the same except that they are persistent, they are stored in the database. Once you declare a view, it is like a regular relation in the database, almost like a regular relation, why? Because its contents are not necessarily stored, it is just a definition which is stored. If you use it, the view can be computed on demand, in fact it need not be computed fully on demand, in fact that is not how it works. The way the database is handled views is they take the view definition and expand it into the query and we will see how that works in a moment. So let us take a view, create view faculty as select id name, department name from instructor, what is this doing? It is just removing the salve attribute. This is one example of the motivation for views which is to hide things from certain tables. It turns out that the SQL has a means of allowing people to see certain tables and not others. So this is called access control. Access control in SQL is at the level of an entire table or on certain columns of a table and it is very useful in certain ways. For example, if you build an application, you have different people managing the application, you can predict this only certain people have full right to it, other people have only read rights and so on. However, the SQL access control mechanism which was originally meant for end user who are using a database system, these days is not all that useful because on most databases the front end is a web or some other tool which does not actually take end user logins. It uses some internal logins. So the access control mechanism, SQL are not useful for web applications typically because web users do not correspond to database users. So there is a whole means in SQL which many of you might know for granting privileges and views can be part of that. So if you want to allow people access to certain things, you can create a view and grant them rights to view but not to the underlying relation. In this case, the view simply hid a certain column but you can have it differently. You can create a view which hides certain roles. So maybe you want to reveal the salary of certain people but not others. You can have a query which only outputs the salary of those people and then that can be exposed while the salary of others is hidden. So that view can be made available to a user but not the original relation. So views are still very useful for access control but not for web applications. Okay, now coming back, here is another example of a view. Oh sorry, before that, here is a use of a view, select name from faculty where department name is biology. So what happens here? Conceptually this faculty relation here is replaced by the view definition. And in fact it's very easy in SQL today. You can have a subquery in the frontcloth. So you can just replace faculty by that whole query and you're done. That's a valid SQL query and then that's what it's executed. There's other kinds of views which are also very useful which is to get aggregate, not the raw data. In fact this is very common where you do not want to expose raw data but you do want to expose aggregate to users. And so this is an example where you don't show the individual salaries of instructors but you are willing to show the total salary budget of a particular department. So select department name from salary from instructor group by department name. Now what happens to a department without instructor? Yeah, that department is missing from the instructor table, therefore it will not appear in this result. So supposing you did want it to appear, departments with zero instructors wanted to appear with zero budget. What would you do? We have had such department site. When a new department is formed, sometimes it's created with nobody in the department actually. There's somebody from some other department who is initially in charge of recruitment or whatever. So there can be periods when this happens. Yeah. So we can use left out. We saw another way yesterday which was to write a subquery in the select law. The other way is to use the left auto join. So how would you write this query? We want department left auto join instructor. So just for variety, let me write it using relational algebra. So department left auto join instructor. This is going to give me every department with null for all the instructor fields if there is no instructor. Now what do I want? I'm going to use the gamma or the calligraphy operator. And I want to group by department name. And what is the aggregate that I want here? Some of salary. I can write this in SQL also. So syntax should be what? Select department name comma some salve from department left auto join instructor. If you say natural left auto join instructor group by department name. Now what will happen? If a department had no instructor, the set would be empty. It would have only a single element, sorry not empty set. The set has a single element which is null. And that null is eliminated. You get an empty set. So what is the sum of an empty set? 0 null. Here is the lab assignment. Write this out today. We saw yesterday what to do. If we want to replace null by 0, we saw what to do yesterday. No, NVL is oracle. In standard SQL there is a way which is supported now by everybody. You can use coalesce. Coalesce something let us say some salary comma 0 would take some salary if it is non-null. If it is null, it goes to the next one which is 0. So that is what if you do not use NVL. I think oracle also supports coalesce. So try this out in the lab. So there are other examples of views, view expansion. I will just skip all those. I will just show this slide which points out that you can define one view using another and then use that to define a third view and you can have a cascade of views like this. So the view expansion basically replaces, it takes a given query. If there is any view in it, it replaces by its definition. That in turn may have another view in it. Again it replaces all those views by their definitions and keeps doing it until there are no more views left. So that is the basic algorithm. Find any view relation, replace it by the expression until no more view relations are present in e1. Now as long as the view definitions are not recursive, this will terminate. Now what do I mean by recursive? Supposing I define a view in terms of itself. Is this even a good idea? Why should we allow it? It sounds like a bad idea. This algorithm will never terminate. So should you even consider it? Should you just ban it and be happy? Does it make any sense to have recursive view? What? Let's decide this democratically. How many words for having recursive views? Nobody. One. How many words for totally banning recursive views? Why? Nobody is voting. Zero. Okay, one. Poor water turned out. Election cancelled. Okay. So of course this is not democratic. So there are in fact uses for recursive views which were not supported by the SQL standard earlier. So there were a whole bunch of researchers working on recursive query processing before it came into SQL. And in fact my own PhD was in this area, recursive query processing. How to efficiently evaluate queries where there are views which are defined recursively in terms of density. There is a section in the book which in Chapter 5 which we don't have time to get into. But it gives several motivating examples for recursive views. So one such example is if I have a chain of managers. Most organizations, there is an organization hierarchy. Each person has a manager. The person they report to. Eventually there is a boss, the CEO. And who does the CEO or the director report to? Sometimes there is a board or for private institutions there is an owner. The owner doesn't report to anybody. So you have a natural hierarchy. Similarly there are parts. So you may say that a car consists of these parts. An engine, a door and so on. Engine itself consists of these parts. Now one of those parts, engine itself consists of subparts. So you have a hierarchy. So hierarchy is one example where supposing I want to say find me the bill of materials for this part. What is the bill of materials? This is an individual part, indivisible part from which this thing is constructed. I have this information in terms of hierarchy. This part consists of these parts. So many numbers of these parts.  And then one engine has maybe so many subparts, cylinders, pistons, head assembly, whatever you name it. You can have all of this represented in a database. How do you represent it? You could, for example, represent it like this. Subpart, part, subpart, number. So this part, say car has, you can say car, wheel, four and so on. Car, engine one, car, sorry, engine, something maybe four and so forth. So I can represent it as a relation like this. Now supposing I want to say find me all the bottom level parts of car. How many are there? So there is a way to write this which SQL standard supports this and several SQL databases support it. Some of them have slightly varying syntaxes for this. Oracle has its own syntax. PostgreSQL has its own slightly varying syntax and so forth. But most database systems today support at least some form of recursion. And such queries can be written in SQL by defining a view which says, which is defined in terms of itself. So how would you define such a view? You can say that a view, for example, contains, this is just a base part. That is the individual one, which you can't break up even further. This is the view which I want to define. So how would I do this? There are a few more relations. I won't get into the exact syntax for this, but let me give you an intuition. Supposing I know that this, I know what are the base parts. So if I know that actually this number part is harder. So let me cut this out for the moment. A little trickier to define it. So let me skip that for the moment. So supposing I want to say that a part contains a base part. So if I have a subpart, I know that something is a subpart of another directly and that part is a base part. It does not have a further subpart. So I have a relation base part, which says these are the ones which do not have subparts. Or I can infer it by saying here is a part which is not in the subpart relation. So either way, I know that this does not have any further subpart. So this is the kind of thing that I want in this output. So I can say that it is contained in this if it is directly subpart. Otherwise I can have two levels. I can have essentially a join between subpart, part one and contain, this is again just intuition. This is not any particular syntax. So I have a structure like this, where I have a subpart of a particular part and its subpart part one. And I know that part one contains a base part. I have already inferred this somehow. Then what can I infer? From this I can infer, contains, part, base part. From this I can infer, contain, part. There is syntax in SQL to write such things. And then there is a means of evaluating it also, reasonably efficiently. So I will just stop here as far as recursive views are concerned. But you can go back and read it up from chapter five. So you can't obviously do it by view expansion. There is a different mechanism for handling this. And there is even a mechanism to handle the count of numbers and so on, which is a little trickier to define. So I won't get into that. Finally, to wrap up views, can you update a view? What does it mean to update a view? So the first thing which you could say is don't ever allow view update. Views are defined in terms of other relations. If you intend it to update something, go intend the underlying relation. Don't update the view. So that's a first cut solution. But then in practice, people found that there are many situations where somebody is given access to certain roles of a relation with some condition. But they should be allowed to update those roles. So then practically speaking people found that it is useful to allow people to update views under some limited conditions. So in general, so you cannot actually update a view. All you can do is take an update defined on the view and translate it into an update on the underlying relation, such that the view will reflect the same. So if I had a selection, I can only look at and modify faculty in computer science. That's an instructor relation, but I am allowed to view or update things in computer science. So I cannot directly go write an update on instructor. I can write update on the my view of instructor, which is only computer science. And that will be translated by the database into an update on the instructor relation. So it turns out you can't always do this. But there are some limited cases where you can in fact do this. And the slide over here which I won't get into detail talk about this. For example, if you have a join, it becomes very difficult in general. If I update the result of a join, how do I know what update should do to the underlying relation? It's not well defined. There are many ways of doing it. Which do you choose? The answer is don't allow it. But if it's a simple select query, I'm just seeing a subset of the rows, it's usually a lot easier. So those are the cases which database support. Initially the SQL standard didn't say much about it. Today the SQL standard does say that in certain simple cases you should allow updates through view. So I'm going to skip the details here. There are slides here on which show examples of when it can be done uniquely and when it cannot. So for example, where it cannot, I have a view history instructor, a select staff of instructor where department name equal to history. If we try to insert into this somebody in the biology relation, what will happen? It can never appear in the view result because this is a biology, not a history. So you cannot do this. You should not allow this update. So many SQL implementations will check even for a view like this. They will check if the condition is satisfied that department name equal to history. If it's satisfied they will allow an insert. If it is not satisfied they will reject it. It can be done for, it can be done only for the history department. That's the answer to that quiz question. So we are talking about the privileges, granting privileges to that particular individual users. So is there any possibilities to grant such kind of privileges based on the domains, based on the fields, for each and every user? By fields you mean the value of a field? A domain I'm talking about. We are talking about views and create view on the selected domains. So when we are using granting and all we are permitting insert, update such kind of privileges to each and every user. Is it possible to grant such kind of privileges along with that particular field, on particular domains? On particular means? On the type itself? For example register number like that. For example faculty ID like that. Sequence number. Yes. Most SQL implementations have privileges for every object which is in the database. Even for the privilege of creating a foreign key. The privilege to use a sequence number. What is the sequence number? So there are many applications which need a sequential count which ensures that no two tuples have the same value. And the sequence number is a facility provided by many databases which lets you say use this value and it automatically gets incremented when it's used. So when you insert a new tuple you can say that this column should take a value from this sequence number and each time you add a new tuple a new value is derived from that sequence number. So there are privileges on that. There are privileges on views. So I don't have time to get into the full set of privileges but the SQL privilege system is fairly expensive. It's very good except for the fact that it does not have a web application user. It only has a notion of poor database users. So it's useful in that context but not in other context. Sir, typically updation of a view would be in regard with addition of rows or columns. No, no, not that way. So I think you may send us to what I meant by update a view. By update of a view I meant insert a tuple into a view relation or delete a tuple or update a tuple. So I'm altering a view to change the view definition. So altering a view is effectively the same as dropping the view and then recreating it. You can treat it like that. So because there are no tuples stored with it normally. So you can just delete it and recreate it. Well, of course there are some issues. If somewhere other view uses this view you can't just drop it. So the alter view in fact can take care of. So you can have a alter view. I think the SQL standard does support it. Dropping it and creating it may affect other views. So ideally you should have an alter view which simply changes the view definition without affecting other views which depend on this. I think you should check this out. I have not actually tried this on Postgres but you can try it out. Difference between with class and view. Difference between the with class and view. So as I said yesterday the with class is like a temporary view defined in the context of that one query. It does not go into the database schema. A view is stored in the database schema like other relations. That's the main difference. Does a view take a memory byte separately for the view data? Memory part. Memory byte. Memory byte. Memory memory. Memory. Exactly. What is the data for this view stored? Is that the question? Is there any mapping from the original table or view is separately existed in a the default is that the view data does not exist at all. All that is stored in the database is the view definition. That means view is the mapping. That text is what is stored and when you use the view it is expanded inline. That is the default. However it's good to ask this question because many databases today support what is called a materialized view. Let me just write the term here. So what is a materialized view? It's a view for which the data has been computed and is stored. So if I use the view I'm not actually computing the data. It's already there. I'm just looking it up. Most databases today I think PostgreSQL doesn't yet support it. Maybe it will in future. But the commercial databases do support it. Oracle SQL server DB2 and so on. This is in contrast to what I just told you. I just told you that view definition is stored. No data is stored. Now I'm saying in a materialized view data is stored. So there are some issues here. Supposing I'm storing the data and now I update the underlying relation. So I have a view defined in terms. So let me give you an example of the most useful so look at the query at the bottom. View of department salary totals. Now supposing I have a lot of instructors which is unrealistic but let's say that instead of instructors I have a view like this which is total sales of each item in a big retail shop. We have a lot of big shops like BigBetire, Chroma, Ezone, whatever. We have a lot of these. Now they have a lot of sales. Each shop must be selling tens of thousands of items a day. If you aggregate across all the shops of a particular retailer they may have maybe millions of items sold each day. And then if you aggregate across a year you have hundreds of millions of items sold in a year. Now supposing I want to know across all these shop how many copies of a particular thing were sold. How many copies of the database system concept book did Flipkart sell? Now I can go through all the sales data and select out rows, add it up. It could take a lot of time if I run on 200 million records in there. But this is a kind of query which is very natural. In fact many sites will show you some information like if you go to Amazon it will say this book is ranked they don't tell you the actual raw number but they give a rank which is for which you have to anyway compute the raw number. So the idea is that you pre-compute these things. So this result you pre-compute and store. So now if I want to know the sales of a particular item I just look it up. You also build an index on this result. So I just look it up. So what they mean by build an index if you are not familiar I will come to it. But it lets you very quickly get to a particular row of that table and return that answer very quickly. So such views which do aggregates are very good candidates for materialization. So the issue is that if the moment one more item is sold the view is now out of date. Because one more copy of the book was sold the view recorded it as 100 copies now the 101st copy has been sold. What to do about it? There are many possible answers. For the sales do I really care about the up to date sales as of this minute? Probably not. If you tell me the sales up to last week was this much I am happy with that. If you tell me the sales up to last night was this much in others I may be happy with that. So what does this mean? I can re-compute the result once in a week or once in a day or whatever and store it. But there are some applications when I need up to date results as of now I want the results. In which case the moment I update an underlying relation I add one more sales item I need to update the count of total sales of that item. As part of the same transaction this update is done automatically that is done. How do you do that? In earlier days a standard we are doing that was by using trigger. You could define a trigger on sales every time a sales occurred it will find a corresponding row in the total and add it to the total. It turned out that this is actually a pain to keep track of do it properly. What if there was a sale done by mistake it was deleted so you have to undo that.  What if a sale was updated? Instead of 5 it was updated to 3. So for each such thing in search delete, update and so on you have to write this trigger code carefully to update the total sales. Expecting users to do this correctly is a pain. If they make a mistake they are giving wrong information to the user. So most databases found that initially started off by using triggers for keeping these aggregate results up to date but later they provided separate syntax which is actually much easier for the user. So you could simply say instead of create view you could say create materialized view and then the database automatically stores that result. There are other syntax which other database is used but with the equivalent result that the view is computed and stored and can be used. And you can even create an index on the view which lets you efficiently access tuples based on certain conditions. So you no longer need triggers for that for those databases. In fact it's a lot more efficient if it's done by the database. Triggers are slow. The database directly takes care of this much, much faster. The question is how many views can you create on a table. That depends on the implementation. There's no standard answer to it. So go look it up for whichever one you're using. But I think it's a fairly large, I don't think it's a very tight restriction. I've not heard of anybody creating so many views that they run into trouble but yes, there may be limits. So there's this area called online analytical processing which focuses on giving users very very fast access to aggregate views, aggregates in different ways. So the aggregate we saw was a salve, some of salve grouped by department. There may be other things that you want. I want the salve per year. What was the salve this year, what was the salve last year, the year before. I want to see how the salve trend is going. So it's a different group by year, whether a year in department or just year across the whole institute, across all department. So there are many combinations of group by which one might ask for. So an OLAP system is designed to very quickly give you answers to such aggregate queries. So OLAP, let me write it down here. Again, there is material in the book on OLAP. So you can go read it up. OLAP is short for online online analytical processing. So analytical is basically data analysis for analyzing data. And the core operation analysis is statistic. Some count, media and whatever, the usual aggregate. So it's focused on very quickly answering aggregate queries over very large amounts of data. Now how do they do this? How do they answer queries very fast, essentially by using materialize. That's the core underlying technology. So one way is to materialize every possible aggregate view on a particular set of on a given relation or a given set of relations. Every possible query that an analyst may ask, grouped by every possible combination, you pre-compute everything and materialize it. So this is what initially people did. But then they realized that this can become very, very large. You don't want to store so much data. So then they said well, can we materialize certain things and then compute others from it. For example, supposing I have computed group by department, year from this, supposing I want to get the same sum but group by department. What do I have to do? I have to take the salary for each year, if I have this group by result, I take this and add it up across all the year. And then I will get the total salary of the, well, this is not salary, salary is just a six number, let's say sales. Sales by something else, instead of department, item, year. So I have this, now I can, let me just change this. So I want the sales group by item, I can simply take the previous one and add it up. Now how many topics do I have to add up? Typically, you know, these shops have been around only for five years. Even the oldest companies have been around only for a few hundred years and of course they don't have such old data. So if you add up maybe 20, 30 years, I can get this. It is quite fast. So they reduce the amount of things which they pre-compute and store but they can still give you results very quickly. That's the idea of OLAC system. So that's a big business in itself. Many companies need this kind of analysis. Why do they need it? They need it for forecasting what items to talk, how much to keep in stock, what items to manufacture. This is a huge, huge commercial interest in doing this because it affects profits of companies severely. If you produce too many cars, this happened to Maruti. They've produced a lot of cars, I think Altos or something. They were not selling but their desire and swift were backlogged. There was not enough capacity. And it's hard to go build a new plant for it. If they could have predicted this, they could have ahead of time they could have switched factory from producing, let's say Altos to producing swift and desired. They didn't quite anticipate this. It's hard to predict in general. How do you know the future? Like they say it's very hard to predict the future. So mistakes will happen but the goal is to minimize the chances of making such mistakes by using analysis. If you can see the trend, you could have maybe seen a trend that over the year, consumer preferences are slowly shifting from smaller cars to larger cars. If they had anticipated this trend, they could have caught it. But of course sometimes trends are changed by what the company does. Maybe the company created the trend by creating a nice product but they anticipate it's such a roaring success. But analysis is very, very important for this. So there's a huge market for OLAP online analytical processing system. They're essentially databases with extra stuff in there for deciding what to pre-compute and some of that is stored in memory, some of it is stored on disk and so forth. Now last, a few topics on SQL. There's a notion of transactions. We'll come back to transactions a little bit later, very briefly in the implementation issue. But if you first we need to understand what is a transaction. You have all done transactions at a shop.  You give the shopkeeper money, the shopkeeper gives you the item. That's a transaction. Now there are typically at least two parts of a transaction. One is taking the goods, the other is paying the money. If you paid the money and didn't get the goods, you would be very unhappy. If you got the goods and didn't pay the money, you would be happy but the shopkeeper would be very unhappy. So both should happen or neither should happen. You can walk out of the shop without buying anything. Or you can pay the money and get it. Either of those is acceptable. But one happening, the other not is not acceptable. So in the context of a database, similar thing happens. If I want to transfer money from one account to another, I want to create a deposit cash. I am handing over cash. It should be recorded or the person should give it back. So there are transactions which involve the external world and the database. I go to an ATM machine, I withdraw cash. Now, either I get the cash and my account is debited or neither. If my account is debited but I don't get the cash, I will be unhappy and vice-versa. Now implementing transactions in general is a complex task. It is done routinely but it is non-trivial. So for ATM, what all can go wrong? The machine might jam. It has counted the bill. It's pushed it out. Now it's jammed. You're pulling at the nodes. It doesn't come out. It's rare but could happen conceivably. What then? Or the machine could go bonkers. Power goes off. Some glitch. It has counted the cash. It has not dispensed it. What happens then? Now maybe your account is debited still by mistake. So what is done? In the real world, handling making sure that transactions are atomic. Since both parts happen or neither happens. It's actually quite hard when you have the external world. It gets fairly complex. In ATM, it goes to the point where the ATMs have a little camera and if a customer says, look, I tried to withdraw cash I didn't actually receive the cash. He complains to the bank. You have debited my account. And the bank will look at that camera and say what actually happened. And if they see the cash coming out, they'll say you're lying. We'll report you to the police. Or if the cash didn't come out, they'll say, okay, we will credit it. So it's fairly complex. It involves recovery. You should have a record of what happened. That camera image, the video, is a record of what actually happened over there. And based on that, you either refund the amount from the debiting of the account or you say, no, it's okay. Now all of these have counterparts which are entirely in the database. So external actions are more complicated. That requires a case-by-case handling. But there is a direct counterpart inside the database where you want these two updates to happen in the database on neither hand. For this, there is a standardized way of handling it internally. So that is part of what it means to be an atomic transaction. There's also an issue of concurrency and so on which we'll come to later. But the focus here is atomic transaction. So in the SQL standard, it says that you can run a sequence of statements. But only when you say commit to work will all those updates be reflected in the database. Till then, you can always say abort or rollback work, which means everything that was done up to that point to rollback. So one of these two should happen. If neither happens, the database crashes for whatever reason or you go away and the session is terminated, then the default should be rollback. That's the standard. In real life, though, this was inconvenient and most implementations don't do it that way. What they do is, by default, as soon as you run an SQL command, it's done. So if I want to update two things, I'm transferring money from one account to another, I update this, I update that. I update this, if the crash happens, the other won't get updated. That's a problem. The database is inconsistent. And the way to do it correctly is to tell the database don't automatically commit. So by default, most databases automatically commit, as soon as you run an SQL statement, it's committed. Done. But you can tell the system, don't do that. How do you tell it? It depends. There's an SQL 99 standard, which says begin atomic and everything in between will either be completely committed or rollback. It can't be intermediate state. But many databases don't support this. There's another alternative. If you're using an API, JDBC and so on, there's a way to say turn off auto commit. So by default, most databases have auto commit turned on.  One of the two. So how is this implemented? We'll briefly look at it later. Here is, I just want you to be aware of this idea of what is this one. Now to wrap up SQL, we'll come back to integrity constraint. Somebody asked me about cascading integrity constraint. We already saw the not null primary key foreign key. There are other constraints like check predicate, which can be any predicate. I'm going to skip these because you've already seen most of these. Check is a simple thing. Just look at the bottom here. Check semester in, fall, winter, spring, it's one way of doing it. So this has a section with a semester. Now this was partly done to illustrate this feature. A better way to have done it might have been actually to have a semester master table and then the semester is a foreign key referencing that table. That will force it to be one of the values in that table. But then if I want to add a new semester, I can easily add it. Here, I can't add a semester without going and saying alter table, modify constraint, alter constraint. The syntax support is very non-standard. Foreign keys we already know but the point I wanted to talk about here is cascading action. So here is a variant of the earlier course declaration which says department name is there and it is a foreign key department name that references department on delete cascade, on update cascade. What does this cascade mean? If I delete a department, automatically all courses in the department should be deleted. If I update a department name, automatically the department name of all the referring courses should be updated. That's what this is. There are alternatives. I can say on delete set null, on delete set default. If I want to retain that information without losing it, I can still do that. For example, I have a certain transaction and I am recording which employee did it. I want to delete the employee totally. I might still keep the transaction in set null. It turns out, actually deleting data is a bad idea in many situations. I may want to keep historical data. So this should be used very rapidly. Cascade should be used rapidly. More often you want to keep the historical data. There are variants people work on. A department is closed. No more students come in the department. However, there are old students who are in the department. I don't want to change the department. I don't want to set the department to null. None of this makes sense. So then how is that handled? Usually by maybe creating a view called active department and there's a table called department which includes historical department and when I want to see what departments are there, currently I will see the view active department. How do you know which is active? I will set a flag with the department saying active is white or true. So then the view will say departments where active is true. So then I will see only active department. Otherwise I will see all the clutter of old departments which are closed. We have quite a few like that. There is a certain turn. More than department programs. So I have a degree in something or a dual degree in something that is closed but students did graduate with that. So that program should still be remembered and not deleted. But no new people should be added to that program. Okay. Now there are some details here about integrity constraint violation during a transaction. Now why would this happen? I think I am going to skip this for lack of time. I will urge you to go read these slides later on to see what is going on. Deferred checking and so forth. The sums up on other built in data types. So this is useful. The SQL standard has a lot of time related data types. So date is one. Time which is time of day is another. Time stamp which is date plus time is the third. And finally there is something called interval which is a gap of time. It is not like, you know, interval is not necessarily saying from May 6 to May 8. It is two days less than interval. These are all types which are valid in SQL. The support for these types varies. I think Oracle does not do date and time. It only has time or had. This keeps changing. But this is what the SQL standards specify. And there are many more variants which I will not get into. And the last topic section is index creation. I mentioned this briefly. What is an index? It is a secondary structure which is used to efficiently retrieve data. So here I have created a table student. I want to look up students based on their ID very efficiently. I do not usually look up on name. So if I have a lot of students, if I give an ID should be able to zoom in on that tuple very fast.  So how do I efficiently retrieve a particular tuple given an ID? So there are data structures for indices. Many of you probably know about it. If you are not familiar with it we will get back to it later. But the SQL database is typically have a statement like this. Create index can give it a name on relation with a set of attributes there. Indices are covered in great detail in chapter 11. So there are a few other things large objects, authorization, privileges which I am going to skip. Many questions now? So the question is are assertions supported in Postgres? The answer is the last I checked nobody supports assertions. At least nobody supports any useful form of assertions. So this is one of those things. Sometimes the standard bodies wait for demand for a feature to be big enough before they standardize it. And then what happens is meanwhile each database implements it in its own way. So this happened for a while then they said wait we do not want to do this let us anticipate what people might need and what databases may support and provide a standard for it ahead of time. Assertions were one such they anticipated a need for it but most databases never ended up supporting it. So it is there in the standard nobody supports it. Can you repeat the question? What will happen if we go for a materialized view that does not contain the prime attributes of the parent relations over which it is defined and we are going to insert some data into it. So materialized views often for example aggregate view do not have a primary key of the underlying failed relation. That is not an issue. What will happen if you go for inserting some data into that materialized view? So that will be reflected in the original relation. In what sense? If you will insert some data a tuple in the materialized view will it be reflected in the original relation? So supposing I have a materialized view and I insert into the materialized view no no. So typically updates on views first of all are restricted and the same rules will apply for updates on materialized views. So it is very restricted where you are allowed to do that. The main use for materialized views is where it is read only not with updates. Updates on views were more for views intended for security that was a motivation. Whereas materialized views the primary motivation is querying their aggregates. It cannot meaningfully translate an update on a materialized view to an update on the underlying relation. So most of the time it is not supported. You cannot update them. Typically materialized views will not be updateable. So there is a couple more topics on related to SQL indirectly. Which is how to access SQL from not from a PSQL, not from PGA admin and so on but from a programming language.


Hello, everyone. I hope you're all having a wonderful village. Welcome to my talk designing a C2 framework So my name is Daniel Duggan and Otherwise known as a raster mace I'm the director of zero point security. You may have seen a red team ops course. I Blog over raster mace.me as well as on offensive events I'm on Twitter get her discord slack all the things You want to get in touch with me after the talk by all means do so So what inspired this talk? Well It feels like to me prior to around 2018 The weren't really that many C2s or C2 frameworks available The main Commercial offering was probably Cobb Australian maybe a few others and And there weren't that many open source frameworks either we had PowerShell Empire for a long time We have posh T2 for a long time and then Covenant came along and some others came along and Suddenly we just had this huge boom in these C2 tools Coming out We've also had a lot more commercial ones as well. So it seems to be an area of interest for sure and It's not infrequent that I get approached and people are asking me If I've got any tips on how to build C2 and specifically in C sharp And so I thought that this kind of talk would be helpful for those who were looking to You know take on the process of trying to Put such a tool together and if you go over to the C2 matrix And it's a curated list of commercial and open source Frameworks it now lists over 70 which is pretty astounding really and More than 10 languages everything from Python Go rust C sharp Ruby even PowerShell There's no Shortage of variety and they all have different Capabilities so some By default will beacon over HTTP some will go over DNS some will go over completely custom channels and some might ride on legitimate services like You know the dropboxes office 365 different Google services and things like that If you're interested in learning more about some of the frameworks or some of the C2 tools already out there I hardly suggest you check out the matrix It has a really useful like Search tool I guess where you've you plug in your requirements and it will recommend and some tools for you Based on those requirements. So let's take a step back and talk about C2. What is C2? Well C2 is short for command and control You can imagine a scenario where you have us cowboy operators and we have a target and the operator will Deliver and some sort of implant or payload and sometimes also called a rat to that target and the operator needs to Maintain some control over that over that implant somehow The implant needs to talk to the operator the operator needs to be able to give it commands and the implant needs to give the results back to the operator and the the model that is Used most I guess is to have some sort of Intimidatory control server often called a team server. So the implant will communicate to the team server over Some sort of protocol again, that might be HTTP or DNS or some other legit service and The operator will have you know some sort of Admin interface to that control server So the implant will talk to the control server and it will kind of appear To the operator and the operator will be able to give it tasks The implant will grab those tasks from the server execute them and send send the results back It's worth noting that some of these servers have the Admin interface kind of built into them while others require the operators to have a Stand-alone client that will connect to that To that server for them So Conceptually not too complicated however, the point of this talk is about designing a C2 framework not just designing C2 and we need to Understand specifically for the purposes of this talk that C2 is not the same as a framework So if you go on to the matrix again, for example, you'll see a lot of C2 tools that don't Really readily provide that much flexibility to the operator You know, maybe a lot of those tools were designed in mind with I want to Demonstrate being able to use C2 over office 365 for example And that's pretty much all it's capable of and to me. That's not really a framework and This is all about frameworks So what does a framework provide? Well, they're quite clearly listed here So let's go through them. The first is inversion of control Now the overall flow of the program or the the programs that are Involved in this whole process and not strictly controlled by the user so the flow is that the implant is going to talk to a server and The operator is going to task that implant You know, you've got that back and forth. Now that is a flow that's not controlled by the user you also have on The server side and on the implant side a lot of internal flows So the implant will receive a job It'll process it in some way and then send results back that internal flow is not controlled by the operator and a lot of Flows internal to the team server and not necessarily going to be controlled by the user a Framework also provides default behaviors and But most importantly those are behaviors that can be Overridden by the operator So again, we're talking about the protocol that the implant is Talking over all the protocol that the team server is listening on a Framework may provide a default protocol for that such as HTTP but the operator should be allowed to Override that in some way Either change the behaviors within that protocol or add their own complete custom protocols a Framework also provides extensibility and That is to introduce new behaviors and capabilities that are not Currently within the tool set So if you think about an implant You might write your implant with a couple of commands But it needs to be able to Be customizable for the operator. They need to be able to add their own commands if they want their own capabilities their own post-exploitation capabilities and They also need to be able to do that on the server side as well so if you have Something like you want Reverse port forwarding on your implant You need to be able to introduce that capability to the implant the implant needs to send that data to the To somewhere probably the team server the team server needs to relay that traffic to wherever it needs to go And then it needs to send that traffic back. So Both sides of that process need to be Extensible by the operator to accommodate that a framework also provides reusable components which I think is self-explanatory, but they're They're components that the framework provides to make the operators life easier We'll see an example of that on the next slide So this is an example I've taken from the Metasploit framework Metasploit is a very mature product at this point and it's great to look at and If we're looking for inspiration on those frameworky things But if you're not familiar with Metasploit Anybody can write a module for it. Anybody can write the immersion module or an exploit module For that framework for other people to use and Being a framework it provides a lot of helpers for you to you know in writing those modules so this example is taken from the psexec module and The first thing you do in a module is to define some module information so this includes, you know a name and a description your an author or multiple authors references and so on and so forth and That information from the module is picked up by the rest of the framework So that as the operator whilst you're using the UI and you search for a module You can you know search it by name or whatever And then it comes up and you can see the name you can see its description and a bunch of other things You can also register options So this being a PSexec module the author here has said well you can define options for the service name Paths and a bunch of other things that are you know important to that module But more importantly There are some options that you don't have to explicitly define in the module So the framework knows that this is like a remote exploit module or whatever It knows that it needs the our host or you know your targets And the operator or the author of the module doesn't need to specifically put our host in as an option The framework already knows that that's required. So that takes you know that burden off the operator or the author And you also have includes and helpers which are down here So these includes are other met exploit modules that you can bring into your module and because this module is Using you know SMP exec in SMB. There's already a module for that So you don't have to actually implement an entire SMB library in your module or even the PS exec process in your module It's bringing in PowerShell and Xs because you know you need to execute something So as the module author you don't even have to worry about the payload that you're going to send the framework does it for you and you can see here that The service file name, so this is service file name here is an option If you haven't you know defined this option it takes the defaults And so these are the default behaviors. So if you haven't Provided a name it will pick it'll just make a random one for you and this round text alpha is Another module in the framework So you don't have to worry about okay. Well, I want a random string now have to write a function for that it's in the framework already and Those are the biggest strengths of frameworks is That as the module author they allow you just to focus on The task that you want and not worry about You know things that you don't want to worry about Okay, so where to start well This is like it seems pretty cliche, but the first thing you should really understand is what are your motivations? What does success look like and that kind of sounds like we're at some sort of management retreat but You kind of really need to think about what you're actually trying to achieve because You need to build it and you need to know what it's going to look like at the end So, you know, you might be doing this just for fun. You might be doing it just to teach yourself some stuff You might want it to teach other people You might be writing an internal tool if you're like a pen tester or a red teamer And you might even want to sell it. You might want to open source it And If I had to draw like some sort of parallel, I think about if you were gonna build a car It's very easy to say I'm gonna build a car But there are a lot of different types of car, right? if you Want something to take your Family to the beach. You probably don't want a McLaren P1 And likewise if you want to go round the Nürburgring pretty quick, you probably don't want I don't know Some sort of absurd people carrier So even though they're both cars They are quite different and they have different features to you know Make that goal a reality and there are all sorts of things that you You could think or that would be really cool to have in my framework. That would be you know, da-da-da-da-da but if it doesn't you know contribute towards What you're actually trying to achieve Then it's kind of pointless and if you Miss features that you need then you're not gonna achieve your goal And you're gonna end up with something that you didn't want so if you've never seen Moscow this is a pretty good way to try and narrow down what you think you want So this stands for must have could have sorry must have should have could have and won't have So your must haves are like mandatory things, right? These are things that your framework absolutely has to have to Performance function Should haves are important and they they add significant value But they are not strictly mandatory and To function could haves are nice to haves but not really important and Won't haves are least critical inappropriate or undesirable and Like the won't haves you can split into kind of two camps, I guess You can have won't haves like period and Won't haves this time and I'll sort of expand on this time In a minute So what you really want to do is you want to get your goal straight and think about all the things that you think you Might want in your tool You can also take inspiration from other frameworks as well and tooling that's perfectly okay. I don't think that's cheating Because you kind of want the best parts of everything and you know why Reinvent the wheel sometimes you can take good ideas from all sorts of places and that's perfectly fine in my book and Then what you want to do is narrow everything down to an attainable first release using that Moscow Method and by attainable. I mean attainable within your current skill set and your time budget and I think a mistake that a lot of people make is To look at things that have been out there for a long time to look at Empire You look at my sport framework and you look at Covenant posh C2 and all of those well established Projects and they think that's what I want to build. I'm going to build like my version of that framework But the thing is that those projects they didn't get there overnight They didn't just pop out of nowhere at the quality that they are now Some of them are months or years old So if you're trying to replicate that straight off the bat You know, you've probably got it easily six or 12 months worth of work and What you're going to do is you're going to work on it for a I don't know a couple of months or however long you can stand You're going to get fed up with it. You're going to get demotivated and then you're just going to put it to the side You'll come back to it maybe at some point and you'll look at it you'll look at it and you'll think well I've only done about 20% of what I hoped to achieve and You're probably not going to pick it up again and it's going to end up in the software graveyard so Small iterative releases are easily more achievable and far more likely to keep you motivated and You're also going to grow your project with your skillset. I mean you can you look at a lot of features in a lot of advanced frameworks now and you I mean, they're pretty they're pretty like advanced concepts So if you're if you're doing this to teach yourself You know coding It's probably not realistic to shoot for you know those kinds of You know features or whatever So having a small project that's a hundred percent complete is a lot better And it's a lot more satisfying than a large projects that that's you know, 10 or 20 percent complete So When I say attainable first release it's going to be like Probably the bare minimum of what will make a C2 Framework function no bells or whistles, but it's something to aim for right So you can set yourself a schedule So you're gonna say I'm gonna target my first release my initial release in One month or whatever you think is realistic. So you've got your Moscow You've got your must-haves and your should-haves and you're probably gonna prioritize those the most you could have You're probably not gonna really worry about too much Because you just want something that's gonna function that's gonna work and You need to be Cognizant of scope creep so it's really easy to just think I'll just add this I'll just add this People post things on like Twitter all the time or just Did you know you could do this or you know new techniques blah blah blah blah, and it's really easy just to try and add those in When really what you want to do is just stick to what you had planned for you know this release and If you see you know Cool stuff coming into the public domain. You can say well, okay Well, I'm not gonna do it now. Maybe I'll do it in the next release and For God's sake pace yourself So, you know two hours a day for a month is not the same as 12 hours on four Fridays in a month and Maybe this is just a personal thing for me but little and often is It's just a more enjoyable way to code than having to sit at your desk for however many hours because You feel like you need to get something out So let's talk about languages for for a sec There are you know a billion different languages out there and You're gonna have to decide on something for your server and your implant and Sort of depending on your model you might consider writing a client as well for the operators But the world I'm just gonna sort of focus on the server and the implant for the server side I Think you're much better off Building off a framework that already exists some sort of web Framework So you have Python ones like flasks and Django Lots of different ones in the C sharp you have blazer which provides you know like a nice web UI You can have something that's driven more by API eyes or RPC and you have lots of us like view react and angular and on the implant side you kind of have to Think about maybe Well, I mean this goes back to your goals really been what it what What platform you really want to target with your framework? I mean you can make your framework Implants agnostic a lot of a lot of them already do that like mystic You can write your own implants for a framework and that would be you know really great if your framework did that But you probably also want to include an implant Just to make it easier for people to pick up and use And so you have OS specific languages like C sharp target the dotnet framework and you have that Swift on Mac OS You have languages that cross compile like nim and go and rust and Then you have actual proper cross-platform languages like.net.net core and sort of Python but you certainly need to Sort of consider how these elements are going to talk to each other and Which language? facilitates that best so You know, I've already said that the control server should provide a means of Communicating with your implant over any protocol any means you want So you have to consider Is the framework that I'm choosing gonna be able to facilitate that can I do that in Python? Can I do that in you know, whatever and then you kind of consider well If my chosen language doesn't really do that do I need to consider a different language and If you don't really know that language is it worth learning or is it worth Sticking to what you know and just trying to make do the best you can I Think that's a decision only like you can make based on your goals and your priorities And if you're learning if you're doing this as a learning process, then maybe it's worth it maybe you specifically want to Use this project as an opportunity to learn You know C sharp, for example, I mean that's pretty pretty popular choice Then yeah by all means step out of your comfort zone and that kind of goes back to the attainable first Release thing is that if you're learning something new You've got a light starts more with it. So in terms of design patterns, there are two that I really Go for the first is this command design pattern and I think this is pretty good for picturing the flow of Stuff between the different components and by components I mean like separate components such as the server and the implant in the operator not really Internal to any one of those so we have the operator which is kind of like their clients and The operator wants to send a task To an implant so you're probably gonna send some information. Maybe it's over an API. You're gonna post some information I'll post this data to your team server, which is kind of like the director in This design pattern moment glacier So in this example, I'm simply I've got a simple task model that has a command and some arguments and I want to task an implant that we're going to identify by a GUID the server then takes that task and sends it to the implant So that's kind of the command and Then the implant which is the receiver is going to execute that task Now I've highlighted task GUID here Because it's in this model that the server Deals with but it's not in the model that the Operator sends So the server if I want my server to track like task progress with the implant It's gonna need something. So my server Excuse me in this example adds and a GUID to all of the tasks and Then when the implant can when the implant talks back to the server It can report it can use the same GUID so that the server can track commands with the implant and This kind of brings me on to the subject of contracts So you have contracts between the different elements Within your the overall solution. So you have contracts between the operator in the server The server and the implant and maybe even the server and any storage that you want So if you're storing data so you can Start and stop your team server without losing data And Then what you really need is different models For each of those contracts Don't try and use the same model Between every element because you're just going to get a little bit unstuck So here's a code example of a task request. So this is what an operator might post to the server This is a request. So it's got a command an array of arguments and an artifact. So If this was like an execute assembly command The artifact would be like the whole assembly. So you're gonna push like I don't know you're gonna push Rubius down to your implant and You're gonna tell it to execute with these arguments And The server will then add on that task GUID and give me back that GUID So as the operator I can then use that GUID to check the status of that task And when I'm asking for that, I don't necessarily I mean you might want to it's kind of up to you You might not want all of this original data back Like you don't you might not I don't need to see like the whole assembly coming back I don't need to have Rubius just going backwards and forwards on the wire between me and the server All I really want maybe is just the result and the status and You're gonna find that you know if you're using like Something like entity framework for storage if you're using C sharp You have to decorate your classes with all sorts of things. So you have to have like attributes on your properties that define like primary keys and stuff and That information just doesn't need to be coming back to the operator. It doesn't need to be going to the implant So at every point you have to Try sort of like translate each model to a different model and then pass it along the chain there's also this Template method pattern and this is pretty good for planning You know plan more carefully planning your code what's your actual code gonna look like and I'm sorry that all of my code examples are in C sharp Mainly because that's all I really know But also that's what people Kind of more interested in I think so on the left. I've got an abstract class. It's gonna act as like A Building block to build custom listeners on my server I'm gonna have a Protected field at the top which is an eye task manager and that is an interface that has these Methods on it so q task is The method that an operator would Use the task an agent so it would take in like a GUID and I've just put a byte array, but this would be the the implant task model and Then get tasks and receive output will be used on like the listener side So that when an implant is talking to the listener it can just use this task manager just to grab the tasks and Any tasks that are queued for it and also Give the server any output that the implant is sending and Then the listener it just has this in it or initialized method to bring in that task manager And then just to start on a stop So let's have a look at what that could look like in C sharp. So this is my abstract class You can see I've got the eye task manager here and This in it just brings in the task manager and just assigns it to that field and then it has two abstract methods start and stop and when somebody comes along and Implements their own custom listener. They inherit from this listener class And this is the entire Class here, it's obviously not very functional. It's just an example But you can see I've highlighted where it would use the task manager and as the author of The custom listener you don't have to worry about where the task manager is coming from or how it works because you've Implemented this abstract class the framework is taking care of that for you and all you have to do is Is just the task manager is there is a field for you to use as appropriate So to get tasks you just call get tasks and to send any output into the server To like process you just call and receive output So abstraction and Interfaces are you know, just they're just so useful And in terms of any implant that you're gonna write Base primitives are better in my view than I call it command proliferation and You know, you can see some CT tools just have like a bazillion you type like help whatever and just get a bazillion commands that you can execute and Like me I find it a bit overwhelming because I don't know. It's just a lot, but it's also not that Like flexible for the operator So Let's use mini cats as an example here. You could build in a command To a framework that will automatically push mini cats down to the implant Load it up in some way execute it and send the results back You could do that for seatbelt and for Rubius and it's very, you know, okay It's very nice just to have an automated way just to push all of these assemblies down But then as soon as the user will you know, the operator says well, I want to push down something custom You've not made it very easy to do that So the base primitives More about what allows these commands to actually happen so mini cats is tied to manual mapping in Like C sharp anyway, so instead of providing like a mini cats command you could just Provide some sort of means of just manual mapping in your implant and That will allow the user just to send down an arbitrary DLL or executable arbitrary commands Just map it into the implant execute it and send the results back You can also, you know expose reflective DLLs dot net reflection power shell Sockets, you know, whatever you want but the closer or The more easily you expose these base primitives to your operator the more easily They can write custom commands for your implant and in terms of commands This is something that I see quite often not even not just in like C2 tools But all sorts of tools that bring in some sort of user input This You know the command is a string and they're just well This is a switch statement, but it could easily be like If else if else if else if if the command equals this do this and It's just not good It's not very flexible. Obviously It's difficult to expand and maintain You know if you want to add another One in here, it's gonna be like massive and if you've got a more complicated complicated method Then these it's just Just don't It's difficult to handle exceptions you could wrap this whole thing in like a try catch In which case you're only catching like Maybe like generic exceptions or you could wrap each one of these in a try catch and that just makes it even bigger You can see we've got code duplication so We're requesting this like get current directory a whole bunch of times Which isn't really a problem for something so short, but if it's something more complicated Then it that just becomes untenable it's not particularly performant because Most of the time I think especially if it's an if else if else if it always starts from the top You go down and then the down We're also Forcing everything to be a string and it's just you know, it's just ugly. This is not a good way to Code something so what's a better? example, perhaps is again To implement those abstracts, so I've got an abstract class here, which is called implant command It has A string which is called command. I probably should have called it name or something, but this is like the name of the command and It's got an abstract method Called execute will bring in the task that's been sent down to it and It has another in it which brings in a class in this case in case it's called implant but implant has Several public methods you can see over on this right hand side here for Sending results back sending an error back and all sorts of things So the implant class again is not something the operator will Care about but you can expose public methods on it for them to do useful things So like the listener example, we want to create our own command That will inherit from implant command We give it a name in this case. It's just LS And I've also thrown like sharp split in here as an example as an example of like decoupling a Lot of like the back-end execution from the actual command and that means that as a user if I want to implement Another command that uses like charts like they can do that really easily You know again, we're just making it as flexible as we can and of course Abstracts you are forced to implement so you have to You know put some sort of implementation in when you override the this method and You can as the author you can just put whatever whatever you want in there And again, you don't have to worry about the task. It automatically is brought into the command for you You don't have to worry about where implant is coming from. It's automatically done for you You just call the methods that you want and Then you can this is within that implant and Class is that at some point we call load commands and we can use reflection to automatically instantiate every type of implant command and initialize them So they're ready to be used And then that handle task Method where in the previous example was that big old switch case This is what it would look like So all we need to do is find the class that has the name that we're looking for You know in the actual task that comes down If we didn't find it we send an error back saying that well the command isn't found otherwise we just Execute it I got another example here using attributes, but I'm actually running out of time So I have to skip to the summary Um So to start with absolutely know your goals Know what you're trying to build and why you're trying to build it Only by knowing your goals will you really understand what features you need to put into your framework? But I'd also encourage you to focus on framework features rather than c2 features So by c2 features, I don't know. Um, I guess I'm referring to command proliferation again But I would definitely focus especially in the early days of your of your framework is is focus on those framework elements You really want to provide the operators the means to customize and expand your framework the way they want to do it Prioritize those base primitives on your implant And provide an easy means for the operators to interact with them Um abstracts and interfaces are incredibly useful. I can't think Really of a a better way to provide that extensibility Um to the operators Plan small attainable releases. Don't try and do like a big big bang release for your, you know, your first one And if this is something that you want to maintain over the long term I would also say to limit each release to only one like big feature So if you look at usually if you look at release notes for for software Most of them the vast majority of the changes are bug fixes You'll then get other types of um, minor improvements And you'll probably only really see like one or maybe two like big new features Um, so that is a kind of software design life cycle thing that I would really encourage. Don't try and change too much in one go Um, yeah, I think that's pretty much it Um, I hope the talk was useful Um, if you've got any questions, please let me know. I'm going to try and be around for a q and a during the Um village if not Feel free to hit me up on any of those Um, socials I shared at the beginning I hope you enjoy the rest of the conference. Thanks very much


For generations, women in Ukaiali, Peru have used products from the forest to benefit their families. Women also depend on the nearby forest rivers and lakes, but they use them in very different ways. As we see, men will go into the forest for hunting, for timber, and for certain non-timber forest products, honey recollection. So men are fishing predominantly, and women will go into the forest to gather non-timber forest products, predominantly seeds, bark, palm, mostly for their artisan products, but also for household needs. Rodriguez Ward and her research team from C4 are collecting data about forest use and livelihoods before and after the implementation of a Red Plus project, which is being led by Aidaer, a Peruvian non-profit, and the role of women is a key element. Researchers say the power to influence community decisions is a complex issue, but dynamics are changing. And women seem keen to learn about forest management. After Shepibo women protecting the forest is not only an environmental issue, it's part of their life.


So my name is Amit Basu. I am a professor and chairman of the Department of Information Technology and Operations Management at the Cox School of Business at SMU. So over the past few decades there's been a steady move towards increased digitization in most companies, key processes, and as a result the role of software and systems and networks is becoming ever increasing in organizations. In order to understand how to manage these companies and how to lead them forward it's vital for managers and budding executives to understand the impact of technology on their business. And we feel that that is a core element of our value proposition at the school. So we think that as we help our students to become more effective both users, consumers, and managers of these technologies we will place them, we will put them in a better situation to tap into and exploit the evolving work environment and the market. We also feel that and we are convinced that a very important effect of not only computerization of companies but also the whole evolution of various technologies such as the internet, social media, mobile computing, big data, the cloud computing, and AI is this vast explosion in the amount of data available. And as a result it creates huge opportunities for companies to become much more data driven and analytical in how they approach key decisions. And as a result we have taken a lot of steps to position our programs so that we will have, we will be producing students who think algorithmically, who think in terms of how to be competitively ahead of their peers and their competitors by being savvy users of information systems, data, and analytics.