用户权限角色

Qualityisnotanact!It`sahabit.ObjectivesAftercompletingthislesson,youshouldbeabletodothefollowing:Createusers(创建用户)Createrolestoeasesetupandmaintenanceofthesecuritymodel(创建角色)UsetheGRANTandREVOKEstatementstograntandrevokeobjectprivileges(授予和回收权限)Qualityisnotanact!It`sahabit.ControllingUserAccess

(控制用户概述)DatabaseadministratorUsersUsernameandpasswordPrivilegesQualityisnotanact!It`sahabit.CreatingUsers(创建用户)TheDBAcreatesusersbyusingtheCREATEUSERstatement.CREATEUSERscottIDENTIFIEDBYtiger;Usercreated.CREATEUSERuser

IDENTIFIEDBYpassword;Qualityisnotanact!It`sahabit.UserSystemPrivileges

(用户的系统权限)Onceauseriscreated,theDBAcangrantspecificsystemprivilegestoauser.(DBA赋予用户系统权限)Anapplicationdeveloper,forexample,mayhavethefollowingsystemprivileges:(最常用的用户系统权限)CREATESESSIONCREATETABLECREATESEQUENCECREATEVIEWCREATEPROCEDUREGRANTprivilege[,privilege...] TOuser[,user|role,PUBLIC...];Qualityisnotanact!It`sahabit.GrantingSystemPrivileges

(赋权限)TheDBAcangrantauserspecificsystemprivileges.GRANTcreatesession,createtable,createsequence,createviewTOscott;Grantsucceeded.Qualityisnotanact!It`sahabit.WhatisaRole?

(角色的概念)AllocatingprivilegeswithoutaroleAllocatingprivilegeswitharolePrivilegesUsersManagerQualityisnotanact!It`sahabit.CreatingandGrantingPrivilegestoaRole

(创建角色，赋予权限)CREATEROLEmanager;Rolecreated.

GRANTcreatetable,createview TOmanager;Grantsucceeded.

GRANTmanagerTODEHAAN,KOCHHAR;Grantsucceeded.

Createarole(创建角色)Grantprivilegestoarole(赋予角色权限)Grantaroletousers(赋予用户角色)Qualityisnotanact!It`sahabit.Object

Privilege Table View Sequence ProcedureALTER

Ö ÖDELETE

Ö ÖEXECUTE

ÖINDEX

Ö INSERT

Ö ÖREFERENCES

Ö ÖSELECT

Ö Ö ÖUPDATE

Ö ÖObjectPrivileges(对象权限)Qualityisnotanact!It`sahabit.GrantingObjectPrivileges(例子)GrantqueryprivilegesontheEMPLOYEEStable.Grantprivilegestoupdatespecificcolumnstousersandroles.GRANTselectONemployeesTOsue,rich;Grantsucceeded.GRANTupdate(department_name,location_id)ONdepartmentsTOscott,manager;Grantsucceeded.Qualityisnotanact!It`sahabit.UsingtheWITH

GRANT

OPTIONandPUBLICKeywordsGiveauserauthoritytopassalongprivileges.(Withgrantoption可传递权限)AllowallusersonthesystemtoquerydatafromAlice’sDEPARTMENTStable.(Public是所有用户)GRANTselect,insertONdepartmentsTOscottWITHGRANTOPTION;Grantsucceeded.GRANTselectON alice.departmentsTO PUBLIC;Grantsucceeded.Qualityisnotanact!It`sahabit.HowtoRevokeObjectPrivileges

(回收权限)YouusetheREVOKEstatementtorevokeprivilegesgrantedtootherusers.PrivilegesgrantedtoothersthroughtheWITHGRANTOPTIONclausearealsorevoked.(Withgrantoption权限也同时回收)REVOKE{privilege[,privilege...]|ALL}ON objectFROM{user[,user...]|role|PUBLIC}[CASCADECONSTRAINTS];Qualityisnotanact!It`sahabit.RevokingObjectPrivileges

(回收权限例子)AsuserAlice,revoketheSELECTandINSERTprivilegesgiventouserScottontheDEPARTMENTStable.REVOKEselect,insertONdepartmentsFROMscott;Revokesucceeded.Qualityisnotanact!It`sahabit.Summary(小结)Statement ActionCREATEUSER Createsauser(usuallyperformedby aDBA)GRANT Givesotherusersprivilegesto accesstheyourobjectsCREATEROLE Createsacollectionofprivileges (usuallyperformedbyaDBA)ALTERUSER C