create user test identified by testdefault tablespace userstemporary tablespace tempquota 3M on users;grant connect,resource to test with admin option;conn test/testshow user;alter user test identified by test1;//修改用户口令grant create any view to scott;revoke create any view from scott;grant select,update on dept to public;//授予实体权限revoke update on dept from public; //回收实体权限create role app_user identified by hello; //创建角色grant create view to app_user;  //授予创建视图权限grant app_user to test; //角色授予用户，这些用户都具有这个角色的权限create user WangMing identified by WangMingdefault tablespace userwangtemporary tablespace tempwangquota 3M on userwang;create user LiYong identified by LiYongdefault tablespace userlitemporary tablespace templiquota 3M on userli; 

CREATE USER WangMing IDENTIFIED BY WangMingDEFAULT TABLESPACE usersTEMPORARY TABLESPACE tempQUOTA 3M ON users;CREATE USER LIYONG IDENTIFIED BY LIYONGDEFAULT TABLESPACE usersTEMPORARY TABLESPACE tempQUOTA 3M ON users;GRANT CONNECT,RESOURCE to WangMing WITH ADMIN OPTION;GRANT CONNECT,RESOURCE to LIYONG WITH ADMIN OPTION;CREATE TABLE Mydept(   DNO VARCHAR2(10),   DNAME VARCHAR2(10),   MANAGER VARCHAR2(10),   LOC VARCHAR2(10),   PHONE VARCHAR(10),   CONSTRAINT PK_Mydept PRIMARY KEY (DNO));   CREATE TABLE Myemp(    ENO VARCHAR2(10),   ENAME VARCHAR2(10),   AGE NUMBER(10),   JOB VARCHAR2(10),   SAL NUMBER(10),   DNO VARCHAR2(10),   CONSTRAINT PK_Myemp PRIMARY KEY (ENO),   CONSTRAINT FK_Myemp_To_Mydept FOREIGN KEY (DNO) REFERENCES Mydept (DNO));INSERT INTO Mydept VALUES(‘1‘,‘SALES‘,‘ZHAOSI‘,‘WuHan‘,‘12345678‘);INSERT INTO Myemp VALUES(‘2‘,‘LIYONG‘,26,‘SALESMAN‘,3400,‘1‘);  INSERT INTO Myemp VALUES(‘4‘,‘SCOTT‘,24,‘SALESMAN‘,3400,‘1‘);INSERT INTO Myemp VALUES(‘1‘,‘WANGMING‘,24,‘SALESMAN‘,3400,‘1‘);    INSERT INTO scott.Mydept VALUES(‘2‘,‘OFFICE‘,‘ZHANGHENG‘,‘BeiJing‘,‘33334567‘);GRANT select(MAX(SAL)) ON Myemp TO ZHANGXIN;

CREATE TABLE Employee(   ENO VARCHAR2(10),   ENAME VARCHAR2(10),   BIRTH VARCHAR(15),   JOB VARCHAR2(10),   SAL VARCHAR2(10),   DNO VARCHAR2(10),   STATE VARCHAR2(10),   PRIMARY KEY(ENO),   FOREIGN KEY(DNO) REFERENCE TO Department(DNO));CREATE TABLE Department(   DNO VARCHAR2(10),   DNAME VARCHAR2(10),   MAGNO VARCHAAR2(10),   LOC VARCHAR2(10),   PHONE VARCHAR2(12),   PRIMARY KEY(DNO));CREATE TABLE Teacher(   Eno NUMERIC(4),   Sal NUMERIC(7，2),   PJob char(10),   CONSTRAINT PK_Teacher PRIMARY KEY(Eno) VALIDATE);CREATE OR REPLACE TRIGGER Insert_Or_Update_SalBEFORE INSERT OR UPDATE ON TeacherFOR EACH ROWWHEN((NEW.PJob=‘教授‘) AND (new.Sal<4000))BEGIN   select 4000 into:new.Sal from dual;END;/INSERT INTO Teacher VALUES(‘1‘,‘3400‘,‘教授‘);INSERT INTO Teacher VALUES(‘2‘,‘4500‘,‘教授‘);INSERT INTO Teacher VALUES(‘3‘,‘4500‘,‘教授‘);CREATE TABLE Sal_log(   Eno NUMERIC(4),   Sal NUMERIC(7,2),   Username char(80),   DDate TIMESTAMP,   CONSTRAINT PK_Sal_log PRIMARY KEY(Eno));CREATE OR REPLACE TRIGGER Insert_SalAFTER INSERT ON TeacherFOR EACH ROWBEGIN  INSERT INTO Sal_log VALUES(:new.Eno,:new.Sal,user,sysdate);END;/CREATE TABLE Department(   No NUMBER(12),   Name CHAR(40),   PRIMARY KEY(No));CREATE TABLE Students(   No NUMBER(12),   Name CHAR(8),   Sex INTEGER DEFAULT 0,   Birthday DATE,   Class CHAR(40),   DeptNo NUMBER(12),   PRIMARY KEY(No),   FOREIGN KEY(DeptNo) REFERENCES Department(No));CREATE TABLE Course(   No NUMBER(12),   Name CHAR(8),   Credit FLOAT,   PRIMARY KEY(No));CREATE TABLE SC(   CNo NUMBER(12),   SNo NUMBER(12),   Grade FLOAT,   PRIMARY KEY(CNo,SNo),   FOREIGN KEY(CNo) REFERENCES Course(No),   FOREIGN KEY(SNo) REFERENCES Students(No));CREATE TABLE SC_U(   CNo NUMBER(12),   SNo NUMBER(12),   Oldgrade FLOAT,   Newgrade FLOAT);CREATE OR REPLACE TRIGGER SC_TAFTER UPDATE OF Grade ON SCFOR EACH ROWWHEN (NEW.Grade>=1.1*OLD.Grade)BEGIN  INSERT INTO SC_U(SNo,CNo,OldGrade,NewGrade)        VALUES(:OLD.SNo, :OLD.CNo, :OLD.Grade, :NEW.Grade);END;/CREATE TABLE StudentInsertLog(   InsertDate DATE,   InsertNumber NUMBER(12),   Operator CHAR(20));CREATE OR REPLACE TRIGGER Student_CountAFTER INSERT ON StudentsDECLARE    ICount NUMBER(12);   temp NUMBER(12);BEGIN   SELECT COUNT(*) INTO temp FROM StudentInsertLog;   SELECT COUNT(*) INTO ICount FROM Students;   IF(temp<>0) THEN    SELECT InsertNumber INTO temp FROM StudentInsertLog      WHERE InsertDate = (SELECT MAX(InsertDate) FROM　StudentInsertLog);END IF;INSERT INTO StudentInsertLog (InsertDate, InsertNumber, Operator)   VALUES(SYSDATE, ICount-temp,user);END;/