数据库原理15.ppt

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 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 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 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… 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 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 Ownership Establishment Three points when creating a schema CREATE SCHEMA … AUTHORIZATION usr usr is the owner of the