Database Security

1. Database Security Antoine POTTIN

2. Database Security Management of users Introduction to Grant and Revoke commands Syntax and semantic of Grant and Revoke in : . MySQL . Postgres . Oracle . Microsoft SQL Example of Implementation (in Oracle)

3. Database Security Management of users Grant and Revoke The Grant and Revoke commands allow system system administrators to create users. Grant is implemented in MySQL Version 3.22.11 or later, for earlier versions, the Grant statment does nothing.

4. Database Security Management of users Grant and Revoke: Definition Grant: The GRANT statement is used to give permissions to a user or role. By using the GRANT statement, it is possible to assign permissions to both statements as well as objects. You can use the GRANT statement with the WITH GRANT OPTION clause to permit the user or role receiving the permission to further grant/revoke access to other accounts

5. Database Security Management of users Grant and Revoke: Definition Revoke: The REVOKE statement is used to remove a previously granted or denied permission from a user in the current database. You can use the REVOKE statement to remove both statements and objects permissions. You can specify the GRANT OPTION FOR clause with the REVOKE statement to remove the WITH GRANT OPTION permissions. Therefore, the user will have the objects permissions, but cannot grant the permissions to other users. Specify the CASCADE clause along with the WITH GRANT OPTION clause, if the permissions being revoked were originally granted using the WITH GRANT OPTION setting.

6. Database Security Management of users Rights Levels Grant and Revoke Users or Grant and Revoke rights are created at 4 levels Global level Database level Table level Column level

7. Database Security server Management of users Global level Grant and Revoke Rights Levels Global level Global privileges apply to all databases on a given server.

8. Database Security Management of users Database level Grant and Revoke Rights Levels Database level Database privileges apply to all tables in a given database. server database database

9. Database Security Management of users Table level Grant and Revoke Rights Levels Table level Table privileges apply to all columns in a given table. server table database table table database

10. Database Security Management of users Column level Grant and Revoke Rights Levels Column level Column privileges apply to single columns in a given table. server table database column column table column column table database

11. Database Security GRANT priv_type [(column_list)] [, priv_type [(column_list)] ...] ON {tbl_name | * | *.* | db_name.*} TO user_name [IDENTIFIED BY 'password'] [, user_name [IDENTIFIED BY 'password'] ...] [WITH GRANT OPTION] REVOKE priv_type [(column_list)] [, priv_type [(column_list)] ...] ON {tbl_name | * | *.* | db_name.*} FROM user_name [, user_name ...] Management of users Syntax & Sémantic Grant and Revoke MySQL

12. Database Security REVOKE { { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES | TRIGGER } [,...] | ALL [ PRIVILEGES ] } ON [ TABLE ] tablename [, ...] FROM { username | GROUP groupname | PUBLIC } [, ...] GRANT { { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES | TRIGGER } [,...] | ALL [ PRIVILEGES ] } ON [ TABLE ] tablename [, ...] TO { username | GROUP groupname | PUBLIC } [, ...] Management of users Syntax & Sémantic Grant and Revoke Postgres

13. Database Security Management of users Syntax & Sémantic Grant and Revoke Oracle GRANT SELECT, INSERT, UPDATE, DELETE ON DEPARTMENT TO username; REVOKE SELECT ON   employee       FROM   BOB;

14. Database Security Management of users Syntax & Sémantic Grant and Revoke Microsoft SQL GRANT SELECT, ... ON table TO username REVOKE SELECT, ... ON table TO username

15. Database Security Management of users Example of Implementation (Oracle) Grant and Revoke Implementation of Grant: For example, if you wanted to grant select, insert, update, and delete privileges on a table called suppliers to a user name smithj, you would execute the following statement: grant select, insert, update, delete on suppliers to smithj; You can also use the all keyword to indicate that you wish all permissions to be granted. For example: grant all on suppliers to smithj; If you wanted to grant select access on your table to all users, you could grant the privileges to the public keyword. For example: grant select on suppliers to public;

16. Database Security Management of users Example of Implementation (Oracle) Grant and Revoke Implementation of Revoke: Once having granted privileges, you may need to revoke some or all of these privileges.  To do this, you can execute a revoke command.  You can revoke any combination of select, insert, update, delete, references, alter, and index. For example, if you wanted to revoke delete privileges on a table called suppliers from a user named anderson, you would execute the following statement: revoke delete on suppliers from anderson; If you wanted to revoke all privileges on a table, you could use the all keyword. For example: revoke all on suppliers from anderson; If you had granted privileges to public (all users) and you wanted to revoke these privileges, you could execute the following statement: revoke all on suppliers from public;

17. Database Security Management of users Oracle Label Security (OLS) Available from the Oracle9i version Presentation of OLS in a project: sales force administration

18. Database Security Project Sales force administration Oracle OLS This demonstration is to prove the powerfull features of OLS. In the following diapos, I will illustrate how to implement the following business functional requirements for a new sales force administration application. Let’s assume that a growing company based in France ;) has decided to formalize the sales force along geographic boundaries: The sales force is responsible for managing customer contact in five french regions: northwest, northeast, southwest, southeast and Paris area.

19. Database Security Project Sales force administration Oracle OLS -A Regional Sales Director will manage each Region. -Each regional sales director reports to and is managed by the Executive sales director. -Each region will be divided into 2 districts, and each district will consist of a subset of french departments.

20. Database Security Project Sales force administration Oracle OLS So far, it looks like a standard implementation for a sales force. We know that database objects are needed to store informations about the region and districts that makes up the sales force. That is the next set of requirements that makes OLS an attractive option: -Each Regional Manager can view and maintain historical customer contact information only for those customers in the Region for which he/she is responsible. -Only the Executive Sales Director can view and maintain customer contact information history in all Regions.

21. Database Security Project Sales force administration Oracle OLS To demonstrate these requirements for the new sales administration system, it is necessary to: -create a new schema (SALESADM), a new role (SALESADM_ROLE), and several new users.like in Listing 1.1 -To built sample tables for Sales Regions, Sales Districts, Sales Zones (i.e. the geographical areas covered) and Customer Contact information. Like in Listing 1.2 -To creat a few views (see Listing 1.3) that will be used to gather data from the existing Sales History (SH) schema that is included as part of the standard Oracle example database to demonstrate how OLS-secured information can be used to control access to other, non-secured schemas as well. See Listing 1.3 -Finally, to load these sample tables with appropriate data to illustrate application of OLS features (seeListing 1.4).

22. Database Security Project A sample OLS implementation Oracle OLS Sales force administration Now that we have a realistic sample schema and sufficient data loaded to illustrate, let's turn our attention to applying OLS to these objects. OLS provides several packages that allow to create and maintain the necessary objects that enforce its security. Except where otherwise noted in the following examples,we will be running scripts from the OLS administrator login (LBACSYS)

23. Database Security Project Creating a new security policity Oracle OLS Sales force administration A sample OLS implementation The first step is to establish an OLS security policy. This policy will encompass all of the OLS settings and assignments that will enforce the security. Via the SA_SYSDBA.CREATE_POLICY function, I will create a new policy named SADM (Sales Administration), and I will specify the name of the column (SADM_LBL) that will be added to each table that will be needed to secure. For the sake of security, It will also be the security policy to hide the SADM_LBL from the prying eyes of developers or more advanced users who might be writing queries against database tables. See Listing 2.1 for the script used to create the security policy.

24. Database Security Project Creating Security Components: Levels, Compartments, and Groups Oracle OLS Sales force administration A sample OLS implementation • Now that is created the security policy, the next step is to create the necessary components for enforcement. • First, create a set of security levels that specify the sensitivity of the data being protected. OLS allows to specify: • Level Number. A numeric value used to uniquely identify each security level. It is a good idea to make the higher level numbers correspond to the increasing security required. • Short Name. Essentially an abbreviation for the level; it will be used when creating data and user labels, so it's a good idea to keep it short – one or two characters. • Long Name. A more detailed description of the security level.

25. Database Security Table 1. Security Levels Level ID Short Name Long Name 1000 UN Unsecured 3000 CW CompanyWide 5000 CC CompanyConfidential 7000 TS Trade Secret Project Creating Security Components: Levels, Compartments, and Groups Oracle OLS Sales force administration A sample OLS implementation Via the OLS package procedure SA_COMPONENTS.CREATE_LABEL, here are the security levels seted up for this policy: See Listing 2.2 for the script used to create the security levels.

26. Database Security Project Creating Security Components: Levels, Compartments, and Groups Oracle OLS Sales force administration A sample OLS implementation Next, to create a set of security compartments. Compartments are used to restrict the areas to which data is restricted. OLS allows to specify: -Compartment Number. A numeric value used to uniquely identify each security compartment. -Short Name. An abbreviation for the compartment that will be used when creating data and user labels, so it is a good idea to keep it short – one or two characters. Long Name. A more detailed description of the security compartment

27. Database Security Table 2. Security Compartments Compartment ID Short Name Long Name 100 AC Accounting 200 SA Sales Administration 300 HR Human Resources 400 OP Operations 500 OE Order Entry Project Creating Security Components: Levels, Compartments, and Groups Oracle OLS Sales force administration A sample OLS implementation Here are the security compartments seted up for this policy using the OLS package procedure SA_COMPONENTS.CREATE_COMPARTMENT: See Listing 2.3 for the script used to create the security compartments

28. Database Security Project Creating Security Components: Levels, Compartments, and Groups Oracle OLS Sales force administration A sample OLS implementation • Finally, create a set of security groups. Groups are used to limit data access to the owners of the data; they can also store hierarchical relationships. OLS allows to specify: • Group Number. A numeric value used to uniquely identify each security group. It’s helpful to create group numbers that represent their hierarchical relationships (see below). • Short Name. An abbreviation for the group that will be used when creating data and user labels. Again, best to keep this short as possible. • Long Name. A more detailed description of the security group. • Parent. Identifies which one group is the parent of the current group entry; used in building a hierarchical relationship.

29. Database Security Table 3. Security Groups Group ID Short Name Long Name Parent 0 T Top of Sales Force Hierarchy (none) 10 NE Northeastern Sales Region T 20 SE Southeastern Sales Region T 30 CN Central Sales Region T 40 SW Southwestern Sales Region T 50 NW Northwestern Sales Region T Project Creating Security Components: Levels, Compartments, and Groups Oracle OLS Sales force administration A sample OLS implementation Via the OLS package procedure SA_COMPONENTS.CREATE_GROUP, set up the following security groups for this policy: See Listing 2.4 for the script used to create the security groups.

30. Database Security Project Creating Policy Labels Oracle OLS Sales force administration A sample OLS implementation Now that we have all the security policy's components in place, ready to build the actual labels that will be used to enforce the policy. Recall that these need to be applied to both users and to the data to be protected. OLS allows to specify: -Label ID. A numeric value used to uniquely identify each policy label. Oracle recommends that it is best to use the Label ID value to arrange the labels into common-sense groupings, since the Label ID is used extensively during retrieval of and decision making about secured data. -Label Tag. The tag represents the intersection of security level, security compartment, and security groupings, and takes the format of level:[compartments]:[groups].

31. Database Security Table 4. Policy Labels Label ID Label Tag 10000 UN 10100 UN:AC 10200 UN:SA 10300 UN:HR 10400 UN:OP 10500 UN:OE 30000 CW 30100 CW:SA:T 30110 CW:SA:NE 30120 CW:SA:SE 30130 CW:SA:CN 30140 CW:SA:SW 30150 CW:SA:NW 50000 CC 70000 TS Project Creating Policy Labels Oracle OLS Sales force administration A sample OLS implementation We set up the following policy labels for this policy using the OLS package procedure SA_LABEL_ADMIN.CREATE_LABEL. Note the labels in the 30100-30199 range; they will be used extensively in my next steps for applying security to the sales force administration application's users tables: See Listing 2.5 for the script used to create the security groups.

32. Database Security Project Applying Policy Labels to Users Oracle OLS Sales force administration A sample OLS implementation Once policy labels have been established, it's time to apply them to the users whose data access must be restricted. Previously is created six users: SLSMGR (for use by the Executive Sales Director) and RGNMGR1 through RGNMGR5 (for use by the five regional sales directors). We have applied the appropriate SADM policy labels to these users via the OLS package procedure SA_USER_ADMIN.SET_USER_LABELS. See Listing 2.7 for the script used to apply the policy to database object tables.

33. Database Security Project Authorizing Schema Owner Rights Oracle OLS Sales force administration A sample OLS implementation Just before we start labeling data in the tables for which the policy has been approved, it’s made sure that the owner of those tables – SALESADM – has the appropriate permission to maintain security policies for the data within its schema. It’s done this via the OLS package procedure SA_USER_ADMIN.SET_USER_PRIVS. See Listing 2.8 for the script used to authorize the schema owner to maintain this information.

34. Database Security Project Applying Security Labeling to Specific Rows Oracle OLS Sales force administration A sample OLS implementation Now is ready to apply row-level security to individual rows in the tables that is identified to OLS for such control. Start at the highest level in the sales force hierarchy by securing specific rows in the SALES_REGION table based on the regions represented by each row. Note that is used the CHAR_TO_LABEL function to translate the text-based label into its corresponding label identifier. See Listing 2.9 for the script used to update selected tables with the appropriate security policy labels.