Computer Science 9616a, Set 1 1. Introduction to Database Security 2. DAC for Relations

1. Computer Science 9616a, Set 11. Introduction to Database Security2. DAC for Relations Set 1, Introduction and DAC for relations

2. 1. What is a Database? 1. Introduction to Database Security2. DAC for Relations Set 1, Introduction and DAC for relations

3. What is a Database? data model: way of declaring types and relating them to each other, stored in a schema languages: for creating, deleting and updating tuples/objects for querying -- usually now high-level, ad-hoc queries; can be interactive or embedded in programs persistence: the data exists after the program that created it finishes its execution sharing: many users and applications can access and share the persistent data recovery: data persists in spite of failures transactions: can be defined and run concurrently Set 1, Introduction and DAC for relations

4. What is a Database? cont’d arbitrary size: amount of data not limited by the computer's main memory or virtual memory integrity constraints: an be declared and the system will enforce them. Examples are uniqueness of keys, data types, referential integrity security: authorization controls can be declared and will be enforced by the system views: definition of virtual or derived data is provided for by the system versions: multiple versions of an evolving schema are allowed and the connections maintained by the system database administration tools: things like backup, bulk loading provided by the system distribution: maintaining multiple, related, replicated, persistent data sets and allowing for their querying Set 1, Introduction and DAC for relations

5. What is Database Security? Set 1, Introduction and DAC for relations

6. What is Database Security? • Protection from threats to the database Set 1, Introduction and DAC for relations

7. What are the threats? • Improper release of information • Improper modification of data • Denial of service Threats can be fraudulent or non-fraudulent Webster's definition of fraud: 1a: DECEIT, TRICKERY; specif: intentional perversion of truth in order to induce another to part with something of value or to surrender a legal right Set 1, Introduction and DAC for relations

8. Database Protection Requirements • Protection from improper access by unauthorized users • Protection from inference (usually statistical databases) • Integrity of the database (partly the job of atomic transactions, partly of the recovery mechanism of the database, and partly access control) • Operational integrity (mainly the job of concurrency control - two-phase locking) • Semantic integrity of data (mainly the job of the DBMS and integrity constraints) Set 1, Introduction and DAC for relations

9. Protection Requirements, cont’d • Accountability and auditing: to act as a deterrent, also for analysis of security failures • User authentication • Management and protection of sensitive data - for various reasons, some data should be kept secret from some or most users • Multilevel protection: enhancement of previous point, where data exists at many levels • Confinement: compartmentalizing of information to prevent transfer to other compartments • There are privacy requirements in government legislation which govern privacy issues. Set 1, Introduction and DAC for relations

10. User Types for DBMS Including Security Features • database administrator • application programmer • on-line query user • parametric user (uses canned applications) • security administrator • security auditor Set 1, Introduction and DAC for relations

11. Data Repositories for DBMS Including Security • database schema • actual data • performance data (indexes, histograms) • log for recovery purposes • user profiles/permissions • security rules or axioms • security log Set 1, Introduction and DAC for relations

12. Some Definitions (partly from the “orange book”) Subject: an entity using a system which wishes to gain access to data or system resources. • A subject can be a user, set of users, a process or a domain. • A domain is further defined as the context or protection environment in which a process operates (e.g. DBMS inside Unix) Set 1, Introduction and DAC for relations

13. Definitions, cont’d Object:an entity that must be protected. • Can be an operating system resource, a file, parts of a database, or subjects (like a process or a domain). • All objects are uniquely identified by a name. • In a database, an object is any granule the system can talk about: e.g. a relation, an index, a database, a record, an application, an attribute value, but nothing smaller than an attribute value. • If all users are represented by processes within a system, then subjects are regarded as objects to be protected, so that Subjects ⊂ Objects. • In general, it is probably true that Objects ∩ Subjects ≠∅, and the exact relationshipshould be specifically stated. Set 1, Introduction and DAC for relations

14. Definitions, cont’d Accessmode: some operation on the object,e.g. read, write, execute (a program or a method), use (if the object is memory or printer). SecurityPolicy: high level guidelines (off-line) defining the basic choices made by an organization about the control of security. Closed System:only explicitly authorized accessesallowed. Open System:accesses not explicitly forbidden are allowed. Mandatory Access Control (MAC): access of subjects to objects is governed by security labels on the subjects and objects. Usually centrally controlled by a security administrator. Discretionary Access Control (DAC): access of subjects to objects is at the discretion of the original owner of the data. Rights can be passed from one subject to another. Set 1, Introduction and DAC for relations

15. Access Matrix Model – basis for DAC A[si,oj] contains, in general, a list (set) of access modes allowed by subject si on object oj. Set 1, Introduction and DAC for relations

16. Access Matrix Model • all discretionary access control is ultimately represented by an access matrix. • since Objects ∩ Subjects ≠∅,the matrix is in general rectangular, not square. • A[si, oj] can also contain flags like r+ to indicate that read access is allowed (r) and can be passed on to othersubjects (+) • this model is used in operating systems as well as database systems. Set 1, Introduction and DAC for relations

17. Implementation • Access Matrices are sparse, or too large for main memory. • Thus, often stored by row, or by column. • by row: called a Capability List, because it lists, for a single subject all the accesses allowed. • by column: called an Access Control List, because it lists, for each object, all the (subject, access mode) pairs. Set 1, Introduction and DAC for relations

18. Summary of things to look for in a security model • definition of subjects • definition of objects • discussion of whether subjects ⊆ objects, or vice versa • definition of the access modes • administrative rights/procedures • additional predicates/constraints on access • additional predicates/constraints on administration of rights Set 1, Introduction and DAC for relations

19. Harrison-Ruzzo-Ullman1 access matrix model • represent the state as a triple Q = (S, O, A) where • S is the set of subjects, • O is the set of objects, • A is the access matrix. • enhanced by Denning (1982) to make each matrix entry a rule which specifies the authorization applies only if some condition is satisfied. • the conditions can be data dependent, time dependent, context dependent or history (of previous accesses) dependent. • access modes are usually: read, write, append, execute and own. • the “own’’ privilege means the subject is the owner of the object, and can administer authorizations on the object. • in some versions of the model, where processes can create subprocesses, there is a control access mode. If p1 creates p2 then p1 controls the grant and revoke of authorizations for p2. 1. Harrison, Michael A.; Ruzzo, Walter L.; Ullman, Jeffrey D. (August 1976). "Protection in Operating Systems". Communications of the ACM19 (8): 461–471. Set 1, Introduction and DAC for relations

20. Operations in HRU • Harrison et al. defined 6 primitive operations: • Enter access mode m into A[s,o] • Delete access mode m from A[s,o] • Create subject s • Destroy subject s • Create object o • Destroy object o • Each operation changes the state Q = (S, O, A). Set 1, Introduction and DAC for relations

21. Examples of commands for HRU • the following shows how a file creation command, and a command to grant read access on the file to another user, would be written. command Create(process, file) create object file enter o into A[process, file] end. command GrantRead(owner, friend, file) if o in A[owner, file] then enter r into A[friend, file] end. Set 1, Introduction and DAC for relations

22. Passing rights to others • Denning proposed some extensions to the model • m* in A[s,o] means that subject s can grant the privilege m on object o to other subjects. However in Denning's model, the privilege cannot be passed on by these other subjects. • in some relational database systems, the privilege can be granted by a GRANT...(WITH GRANT OPTION) statement, (passes on the ability to grant the privilege). • in Denning's extension, there is also a transfer privilege, indicated by m+, where if subject s passes the privilege m on o to someone else, then s loses the privilege. • transfer of privileges may also take place when a process, which has a privilege, creates a subprocess. Some privileges may be passed to the subprocess. • revocation of privileges may only be allowed by the owner of the object, or may be allowed by the subject that passed the privilege along. Set 1, Introduction and DAC for relations

23. Safety • In the original Harrison, Ruzzo and Ullman paper, there is a definition of safety of a protection mechanism. • Informally, a system is unsafe if some subject can get some right r on object o, (which presumably we did not want to happen). This is called a leak. • The formal definition says a command α leaks some generic right r from configuration Q = (S, O, A), if α when run on Q, can execute the primitive operation enter r into A[s,o] which did not previously contain r. • Theorem in HRU paper: If all commands contain only one primitive operation, the safety decision is NP-complete. • If commands are more general, the safety decision is undecidable (it is shown that the protection system with general commands can simulate a Turing machine). Proof is in the Harrison, Ruzzo, Ullman paper. a recent revisit to these matters has been carried out by Tripunitara and Li Set 1, Introduction and DAC for relations

24. Trojan Horses • All discretionary models are susceptible to Trojan Horseattacks • Suppose user U1 has read permission on file F1, and writepermission on file F2, anduser U2 has read permission on file F2, (U2 might be theowner of file F2 and have granted this write permission toU1). U2 is not supposed to know what is in F1. • ATrojan Horse is a program which pretends to do one thing butdoes something else as well. It could be run with thepermissions of user U1. It might be a corrupted systemprogram, or any program. In particular, U1 may not intend to leak any information fromfile F1 to user U2. A program containing hidden code,running with the permissions of U1, could however do justthat, by reading the information from F1 and writing it to F2. Set 1, Introduction and DAC for relations

25. Discretionary Access Controlfor Relational Databases 1. Introduction to Database Security2. DAC for Relations • all discretionary access control for relational databases is based on some original work for System R by Griffiths and Wade2. • basically look at the operations possible through SQL and the INSERT, UPDATE and DELETE statements in the language, and grant and revoke privileges which can be expressed by these statements. • basic concept is that the creator of the table owns it, and can give access to other users by name. • these rights can be given WITH GRANT OPTION or not. • when a right is revoked, if it had been passed on, there might be a cascading of the revocation. • Implies that the database system has to keep track of users as well as database granules • all commercial relational DBMS packages use something like this as the basis for their access control – the grant and revoke statements are part of the SQL standard. 2. P.G. Griffiths and B. Wade, “An Authorization Mechanism for a Relational Database,” ACM Trans. Database Systems, vol. 1, no. 3, pp. 242-255, 1976. Set 1, Introduction and DAC for relations

26. Basis for Griffiths and Wade Revocation • Alice owns table R • Alice grants select, insert on R to Bob with grant option • Bob grants insert on R to Carol • then Alice revokes the insert on R permission from Bob • should Carol still have it? • if Alice granted insert on R to Carol directly also, should Carol still have it after the revocation from Bob? • i.e. should we have cascading revoke? • one solution is to use timestamps • another solution is to reassign Alice as the grantor of the permission for Carol Set 1, Introduction and DAC for relations

27. Security in DB2 (from the manuals for version 9.5) There are the following kinds of authorities: • System Administration Authority (SYSADM) • Database Administration Authority (DBADM) • Security Administrator (SECADM) • System Control Authority (SYSCTRL) • System Maintenance Authority (SYSMAINT) • System monitor authority level (SYSMON) The following types of privileges are present: • Database Privileges • Schema Privileges • Table and View Privileges • Package Privileges • Index Privileges Set 1, Introduction and DAC for relations

28. from the DB2 manuals Set 1, Introduction and DAC for relations

29. Objects which can be Controlled include databases tables views indexes packages schemas aliases data types functions procedures triggers table spaces nodegroups buffer pools event monitors Set 1, Introduction and DAC for relations

30. System AdministrationAuthority Only SYSADM users can do: • migrate a database • change the database manager configuration file (includes specifying which groups have SYSCTRL or SYSMAINT authority) • Grant and revoke DBADM authority • Grant and revoke SECADM authority • As well, they can do whatever a SYSCTRL, SYSMAINT orDBADM can do. Set 1, Introduction and DAC for relations

31. System Control Authority Only users with SYSCTRL or higher authority can do: • Update a database, node or distributed connections services directory • Force users off the system • Create or drop a database • Drop, create or alter a table space • Restore to new database • As well, they can do whatever a SYSMAINT or SYSMON user can do. Set 1, Introduction and DAC for relations

32. System Maintenance Authority Only users with SYSMAINT authority or higher can: • Update database configuration files • Backup a database or table space • Restore to an existing database • Perform roll forward recovery • Start or stop a database instance • Restore a table space • Run trace • Take DB system monitor snapshots of a database manager instance or its databases. • Can do whatever SYSMON users can do Set 1, Introduction and DAC for relations

33. Security Administration authority(SECADM) • SECADM users can • create, alter and drop audit policies, security label components, security policies and trusted contexts • create and drop roles and security labels • grant and revoke roles, exemptions, security labels • execute the SQL statement TRANSFER OWNERSHIP on objects • it is only granted to users, not groups or roles • SECADM has no inherent privilege to access data stored in tables. Set 1, Introduction and DAC for relations

34. Database Administration Authority Only a user with DBADM authority or higher can: • Read log files • Create, activate and drop event monitors • Run the load utility Only a user with DBADM, SYSMAINT or higher authority can: • Query the state of a table space • Update log history files • Quiesce a table space • Reorganize a table • Collect catalog statistics using the RUNSTATS utility Set 1, Introduction and DAC for relations

35. Database Privileges • database privileges are actions on a database as a whole. • only users with SYSADM or DBADM can grant and revoke database privileges. • BINDADD, allows creation of new packages • CONNECT, allows a user to access the database • CREATETAB (create table) • the creator of a package automatically has CONTROL privilege on that package • the creator of a table automatically has CONTROL privilege on the table. • CREATE_NOT_ FENCED (has to do with running user-defined functions in a “not fenced” mode). Set 1, Introduction and DAC for relations

36. Database Privileges, cont’d • IMPLICIT_SCHEMA allows any user to create a schema implicitly by creating an object for a schema name whose name does not already exist. SYSIBM becomes the owner and PUBLIC is given the privilege to create objects in this schema. • When a database is created, the following privileges are automatically granted to public: • CREATETAB • BINDADD • CONNECT • IMPLICIT_SCHEMA • SELECT privilege on the system catalog views Set 1, Introduction and DAC for relations

37. Table and View Privileges CONTROL: a user with CONTROL privilege has all privileges on the table or view, and can drop the table or view, can execute the RUNSTATS utility, and grant or revoke privileges on the table. The creator of a view automatically has CONTROL privilege on the view if they have CONTROL privilege on all tables and views referenced in the view definition. ALTER privilege means the user can add columns to a table or change or add comments on a table and its columns. ALTER also means the user can create a primary key. A user with the ALTER and REFERENCES privileges can create or drop a foreign key. Set 1, Introduction and DAC for relations

38. Table and View Privileges, cont’d DELETE means the user can delete rows from a table or view. INDEX means the user can create an index on a table. The creator of an index automatically has CONTROL on the index. CONTROL allows the user to drop the index. INSERT allows insertion of tuples into a table or view. Also allows the user to run the IMPORT utility. REFERENCES allows the user to create and drop a foreign key. SELECT allows the user to retrieve rows from a table or view. Also allows the user to create a view on a table and to run the EXPORT utility. UPDATE allows the user to change an entry in a table or view. Set 1, Introduction and DAC for relations

39. Granting and Revoking Privileges There are 2 SQL statements: Grant/Revoke privilege on table to user Some Rules • users cannot grant privileges to themselves • granting of privileges can be to a list of authorized users by authorization ID, or to PUBLIC • to grant the CONTROL privilege, the user must have the SYSADM or DBADM authority • can either list the privileges granted, or say ALL (which does not include CONTROL) Set 1, Introduction and DAC for relations

40. Granting and Revoking Privileges, cont’d • to grant DBADM authority, the user must have SYSADM • WITH GRANT OPTION is now included in DB2 • REVOKE can only be issued by the SYSADM, DBADM or someone with CONTROL privileges on the object. • if a user received a privilege as an individual, and also via a GRANT to PUBLIC, and the PUBLIC one is revoked, the privilege remains (and if the individual one is revoked, the public one remains). • privileges can be granted and revoked only on existing database objects. • packages that are dependent on revoked privileges are marked unusable. They have to be rebound with the appropriate authority. • when you revoke the CONTROL privilege from a user, you do not revoke any other privileges the user has on that object. Set 1, Introduction and DAC for relations

41. Implicit Authorizations • When a user creates a table, the system issues an implicit GRANT statement giving that user the CONTROL privilege on the table. • The system also issues the GRANT statement to give the privileges to users with SYSADM and DBADM authority. • when the user creates a view, the implicit GRANT of CONTROL is issued only if the user has CONTROL on all the base tables used to make the view. The privileges granted on the view are the intersection of the privileges that the user has on the base tables. Set 1, Introduction and DAC for relations

42. Indirect Privileges Through Packages • a package consists of an application program with embedded (static or dynamic) SQL commands. • the user who BINDS the package must have all the privileges required to execute these SQL statements. • to execute the application defined by the package, where the package has only static SQL statements, a user only needs EXECUTE privilege on the package. • such a user may not have all the privileges required by the package, but can access the database this way through the package. • dynamic SQL is apparently created at run time and compiled at run time. A user executing a package with dynamic SQL statements must have the privileges for these dynamic SQL statements. Set 1, Introduction and DAC for relations

43. Storage of Security Information • All granted privileges are recorded in system tables. • by default these tables have SELECT granted to PUBLIC, but if the database must be kept secret, this can be revoked. • some of the tables are: SYSCAT.DBAUTH – database privileges SYSCAT.TABAUTH – table and view privileges SYSCAT.PLANAUTH – package privileges SYSCAT.INDEXAUTH – index privileges Set 1, Introduction and DAC for relations

44. To see Authorizations in DB2 Suppose Sylvia is the owner of an Employee relation and types: Grant insert on Employee to George Select * from Syscat.tabauth where tabname = ‘Employee’ Result is a table with columns: Grantor, grantee, granteetype, tabschema, tabname, controlauth, alterauth, deleteauth, indexauth, insertauth, selectauth, refauth, updateauth For the row with Sylvia as Grantee, controlauth is Y, all others are G (meaning Y with grant option). For George, all entries are N except for insertauth which is a Y i.e. it is a slightly reformatted access matrix Set 1, Introduction and DAC for relations

45. Notes/comments • I found a note somewhere that warns that if you grant a privilege to an ID which does not currently exist, but is created at a later time, then that ID will get the privilege when it is created • If Alice grants to Bob with grant option, and Bob grants to Carol, and then Alice revokes from Bob, • Bob loses the privilege even if it was granted also from someone else • Carol keeps the privilege • The details and the way the concepts interact are very complex. Set 1, Introduction and DAC for relations