实验二：用户权限和基本SQL语言.doc

实验二：用户权限和基本SQL语言 实验目的： 熟悉用户管理、权限管理等数据库安全管理机制； 熟悉用户外部表、普通表、视图创建方式，基本SQL语句如简单SELECT查询和统计功能的实用。 实验内容 创建faculty, phd, postgraduate, undergraduate四个用户，并相应的创建四个角色facultyRole, phdRole, postgraduateRole, undergraduateRole，并给各用户赋予相应的角色和权限，使得各数据库用户至少具有连接数据库、创建表、创建目录等权限。 创建用户： -- Create the user create user FACULTY identified externally default tablespace USERS temporary tablespace TEMP profile DEFAULT; -- Grant/Revoke role privileges grant facultyrole to FACULTY with admin option; -- Grant/Revoke system privileges grant alter any cluster to FACULTY with admin option; grant alter any table to FACULTY with admin option; grant alter database to FACULTY with admin option; -- Create the user create user PHD identified externally default tablespace USERS temporary tablespace TEMP profile DEFAULT; -- Grant/Revoke role privileges grant phdrole to PHD with admin option; -- Grant/Revoke system privileges grant alter any cluster to PHD; grant alter any table to PHD with admin option; grant alter database to PHD with admin option; -- Create the user create user POSTGRADUATE identified externally default tablespace USERS temporary tablespace TEMP profile DEFAULT; -- Grant/Revoke role privileges grant postgraduaterole to POSTGRADUATE with admin option; -- Grant/Revoke system privileges grant alter any table to POSTGRADUATE with admin option; grant alter database to POSTGRADUATE with admin option; -- Create the user create user UNDERGRADUATE identified externally default tablespace USERS temporary tablespace TEMP profile DEFAULT; -- Grant/Revoke role privileges grant undergraduaterole to UNDERGRADUATE with admin option; -- Grant/Revoke system privileges grant alter any cluster to UNDERGRADUATE with admin option; grant alter any table to UNDERGRADUATE with admin option; grant alter database to UNDERGRADUATE with admin option; 创建角色： -- Create the role create role FACULTYROLE identified externally; -- Grant/Revoke system privileges grant alter an