1 / 40

Security and User Authorization in SQL

Security and User Authorization in SQL. Security. Two aspects: Users only see the data they’re supposed to; Guard against malicious users. How SQL control it? Authorization ID Privileges. Authorization ID. An element of SQL environment

halla-gordon
Download Presentation

Security and User Authorization in SQL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Security and User Authorization in SQL

  2. Security • Two aspects: • Users only see the data they’re supposed to; • Guard against malicious users. • How SQL control it? • Authorization ID • Privileges Lu Chaojun, SJTU

  3. Authorization ID • An element of SQL environment • A user or a group of users who may be granted some particular privileges on objects • User ID: personal security account on behalf of individuals, applications, system services • Not defined in SQL standard regarding its creation • Role: a defined set of privileges • CREATE ROLE • Granted to users or other roles • PUBLIC: a special built-in authorization ID Lu Chaojun, SJTU

  4. Authorization in A Session • A session provides the authorization ID a context to execute SQL statements during the connection • A session is associated with a user ID or a role name. • On session initialization, session uid is determined by: • Explicit CONNECT TO … USER usr; • Implementation-defined manner Lu Chaojun, SJTU

  5. Authorization in A Session(cont.) • In a session, embedded SQL, client module and SQL-invoked routines may specify authorization ID, so the current auth. ID is changing. • SESSION_USER: SQL session user ID • CURRENT_USER: the current user ID • SET SESSION AUTHORIZATION… • CURRENT_ROLE: the current rolename • SET ROLE… Lu Chaojun, SJTU

  6. Privileges • Privileges are associated with authorization ID • 9 types: SELECT, INSERT, DELETE, UPDATE: applied to a relation (base table or view) • SELECT, INSERT, UPDATE may be associated with a list of attributes. REFERENCES: the right to refer to relations in IC • May have attached list of attributes USAGE: the right to use some kinds of DB elements in ones’s own column definition TRIGGER: the right to define triggers on a relation EXECUTE: the right to execute PSM proc/func UNDER: the right to create subtypes of a UDT Lu Chaojun, SJTU

  7. Lu Chaojun, SJTU

  8. Obtaining Privileges • Owner vs. granted user • SQL elements (e.g. schemas, modules) have an owner. • Owner has all privileges and may GRANT them to others Lu Chaojun, SJTU

  9. Ownership Establishment • Three points • when creating a schema • CREATE SCHEMA … AUTHORIZATION usr • usr is the owner of the schema; • If in a module def., then owner is module owner; • Otherwise, session uid. • when creating a module • MODULE modname … AUTHORIZATION usr • usr is used as the current auth_id for the execution. • If no auth_id is defined, use session uid. • when initiating a session: explicit or implicit • CONNECT TO svr AS conn AUTHORIZATION usr Lu Chaojun, SJTU

  10. Privilege-Checking • Each schema, module, and session has an associated authorization ID. • Let agent A operates on a DB element: A’s privileges derive from the current auth. ID that is either • auth. ID of the module that A is executing, if there is one; or • session auth. ID if not. • We may execute the SQL operation only if the current auth. ID possesses all the privileges on the DB elements. Lu Chaojun, SJTU

  11. Principle 1 • Privileges are always available if the data is owned by U and U is the current authorization ID. • Module owner is U; or when module has no owner, • Session owner is U. D-owner CAI M/S-owner U = U U Module/Session data Lu Chaojun, SJTU

  12. Principle 2 • Privileges are available if the current auth. ID U has been granted those privileges by the owner of the data, or if the privileges have been granted to PUBLIC. D-owner CAI M/S-owner O U U GRANT Module/Session data Lu Chaojun, SJTU

  13. Principle 3 • Executing a module owned by the owner of the data, or by someone who has been granted privileges on the data, makes the needed privileges available. One needs the EXECUTE privilege on the module itself. D-owner CAI M-owner S-owner O V O/U V GRANT session module data EXECUTE Lu Chaojun, SJTU

  14. Principle 4 • Executing a publicly available module during a session whose auth. ID is that of a user with the needed privileges. D-owner CAI M-owner S-owner O O/U O/U GRANT session module data Lu Chaojun, SJTU

  15. Granting Privileges • Syntax GRANT privileges ON DB-element TO users [WITH GRANT OPTION] • The granter must possess the privileges granted or more general privileges (with the grant option) • privileges: SELECT, INSERT, DELETE, ... INSERT(A), UPDATE(A), … ALL PRIVILEGES • DB-element: usu. a relation. • Other DB-element: e.g. ASSERTION myAssertion, TYPE myType, etc. Lu Chaojun, SJTU

  16. WITH GRANT OPTION • Users having been granted WITH GRANT OPTION may grant equal or lesser privileges to other users. with grant option User A User B User C Lu Chaojun, SJTU

  17. Grant Diagram • To keep track of both privileges and their origins • Node: user/privilege • * = WITH GRANT OPTION • ** = derived from ownership • Arc: grants. If U1/Q grants P to U2, then U1 Q ** U2 P * Q is P or more general than P Lu Chaojun, SJTU

  18. Revoking Privileges • Syntax REVOKE privileges ON DB-element FROM users [CASCADE | RESTRICT] • CASCADE: also revoke any privileges that were granted only because of the revoked privileges. • Any node that is not accessible from some ownership node is also deleted. • RESTRICT: Revoke statement cannot be executed if it would result in the cascading revoking of any other privilege. Lu Chaojun, SJTU

  19. Revoking GRANT OPTION • Syntax REVOKE GRANT OPTION FOR privilege ON relation FROM users [CASCADE | RESTRICT] • Only revoke the grant option, not the privilege itself. Lu Chaojun, SJTU

  20. Roles • Syntax CREATE ROLE rolename [ WITH ADMIN { CURRENT_USER | CURRENT_ROLE}] DROP ROLE rolename Lu Chaojun, SJTU

  21. Recursion in SQL

  22. Problem • Why do we need recursion? Parent(person, parent) • Direct Ancestor(person,ancestor) • Indirect: transitive closure • It saves space if we only store Parent and compute Ancestor when we need it. Lu Chaojun, SJTU

  23. Computation • Find “parent of parent” first,third(R(first,second)(Parent) R(second,third)(Parents)) • Find “parent of parent of parent” • Join three copies of Parent • Find i th grandparent by (i-1) joins: Ri • Find all ancestors up to i th : ik=1 Rk • How to do infinite union? • Limit i not known Lu Chaojun, SJTU

  24. Recursive Rules in Datalog • Dependency If P( )  ...Q( ) ... , we say IDB P depends on Q • Dependency graph Nodes: IDB predicates Arc: PQ if P depends on Q • Recursive iff cycles • An IDB predicate appears in both the head and the body of rules Lu Chaojun, SJTU

  25. Example • Example: define IDB Ancestors by ancestor(x,y)  parent(x,y) ancestor(x,y)  parent(x,z) AND ancestor(z,y) Lu Chaojun, SJTU

  26. Evaluation of Recursive Rules • The following works when there is no negation: • Start by assuming all IDB relations are empty. • Repeatedly evaluate the rules using the EDB and the previous IDB, to get a new IDB. • End when no change to IDB. Lu Chaojun, SJTU

  27. Evaluation Algorithm Start: IDB = 0 Apply rules to IDB, EDB no yes Change to IDB? done Lu Chaojun, SJTU

  28. Example Sib(x,y)  Par(x,p) AND Par(y,p) AND x <> y Cousin(x,y)  Sib(x,y) Cousin(x,y)  Par(x,xp) AND Par(y,yp) AND Cousin(xp,yp) Let EDB Par( ) = Sib Cousin a  b c bc,ef  d e f bc,ef de,df Lu Chaojun, SJTU

  29. Recursion in SQL • Since SQL:1999 • WITH statement: • Define and use temporary relations, recursive or not. • Syntax WITH R AS definition_of_R query involving R • R is only available within WITH statement • We may define multiple temporary relations in one WITH statement. Lu Chaojun, SJTU

  30. Defining Recursive Relations WITH [RECURSIVE] R1 AS query1, ...... [RECURSIVE] Rn AS queryn query involving R1,...,Rn and other relations • R1,...,Rn may be recursive or mutually recursive Lu Chaojun, SJTU

  31. Meaning 1. Compute R1,...,Rn 2. Evaluate query involving R1,...,Rn and other relations 3. Destroy R1,...,Rn Lu Chaojun, SJTU

  32. Example WITH RECURSIVE Ancestor(x,y) AS (SELECT person AS x, parent AS y FROM Parent) UNION (SELECT a.x, p.parent AS y FROM Ancestor a, Parent p WHERE a.y = p.person) SELECT y FROM Ancestor WHERE x = ‘James Bond’; Lu Chaojun, SJTU

  33. Example WITH Sib(x,y) AS SELECT p1.child, p2.child FROM Par p1, Par p2 WHERE p1.parent = p2.parent AND p1.child <> p2.child, RECURSIVE Cousin(x,y) AS Sib UNION (SELECT p1.child, p2.child FROM Par p1, Par p2, Cousin WHERE p1.parent = Cousin.x AND p2.parent = Cousin.y) SELECT y FROM Cousin WHERE x = ‘Sally’; Lu Chaojun, SJTU

  34. Legal SQL Recursion • It is possible to define SQL recursions that do not have a meaning. • The SQL standard restricts recursion so there is a meaning. • Restrictions • Linear recursion P(x)  …P(x)… • Only one subgoal is mutually recursive with head. • Monotonicity Lu Chaojun, SJTU

  35. Monotonicity • If relation P is a function of relation Q (and perhaps other things), we say P is monotone in Q if adding tuples to Q cannot cause any tuple of P to be deleted. • Examples: P = Q ∪ R P = σa =10 (Q ) • To be a legal SQL recursion, the definition of a recursive relation R may only involve the use of a mutually recursive relation S (S can be R itself) if that use is monotone in S. Lu Chaojun, SJTU

  36. Example: Meaningless Recursion • EDB: R(x) = {(1)}. • IDB: P(x)  R(x) AND NOT P(x). • Is (1) in P(x)? • If so, the recursive rule says it is not. • If not, the recursive rule says it is. • IDB: P(x)  R(x) AND NOT Q(x) Q(x)  R(x) AND NOT P(x) • Is P(0) true? • Two solutions: P={(0)} and Q={} P={} and Q={(0)} Lu Chaojun, SJTU

  37. Example • For the Sib/Cousin example, there are three nodes: Sib, Cousin and SQ (the second term of the union in the rule for Cousin). • No nonmonotonicity, hence legal. Cousin Sib SQ Lu Chaojun, SJTU

  38. A Nonmonotonic Example • Change the UNION to EXCEPT in the rule for Cousin. RECURSIVE Cousin(x,y) AS Sib EXCEPT (SELECT p1.child, p2.child FROM Par p1, Par p2, Cousin WHERE p1.parent = Cousin.x AND p2.parent = Cousin.y) • Now, adding to the result of the subquery can delete Cousin facts; i.e. Cousin is nonmonotone in SQ. Lu Chaojun, SJTU

  39. Another Source • Aggregation can also leads to nonmonotonicity. • Example SELECT AVG(grade) FROM SC WHERE sno = ‘S1’; • Adding to SC a tuple that gives a new course of S1 will usually change the result, i.e. the old tuple is lost. Lu Chaojun, SJTU

  40. Nonmonotonicity Example WITH RECURSIVE P(x) AS (SELECT * FROM R) UNION (SELECT * FROM Q), RECURSIVE Q(x) AS SELECT SUM(x) FROM P SELECT * FROM P; Lu Chaojun, SJTU

More Related