8/2 SQL 進階課程的建置檔

drop user Aha;
drop user Andy;
drop user Ann;
drop user Brian;
drop user Charles;
drop user Cloud;
drop user Clover;
drop user Danniel;
drop user Dennis;
drop user Dylan;
drop user Edgar;
drop user Edward;
drop user Eric;
drop user Frank;
drop user Howard;
drop user James;
drop user Jay;
drop user Jimmy;
drop user John;
drop user Joseph;
drop user Ken;
drop user kidd;
drop user Leon;
drop user Niles;
drop user Rainmo;
drop user Ray;
drop user Richie;
drop user Steven;
drop user Vincent;
drop user Walter;
drop user Xiandong;
drop user Yh;
drop user Yihhann;
drop user Apple;
drop user Banana;
drop user Cobby;
drop user Emily;
drop user Franky;
drop user Gimmy;
drop user Hocky;
drop user Ivy;
drop user Jacky;
drop user Kenny;
drop user Lemon;


create user Aha identified by Aha123 quota 100m on users;
create user Andy identified by Andy123 quota 100m on users;
create user Ann identified by Ann123 quota 100m on users;
create user Brian identified by Brian123 quota 100m on users;
create user Charles identified by Charles123 quota 100m on users;
create user Cloud identified by Cloud123 quota 100m on users;
create user Clover identified by Clover123 quota 100m on users;
create user Danniel identified by Danniel123 quota 100m on users;
create user Dennis identified by Dennis123 quota 100m on users;
create user Dylan identified by Dylan123 quota 100m on users;
create user Edgar identified by Edgar123 quota 100m on users;
create user Edward identified by Edward123 quota 100m on users;
create user Eric identified by Eric123 quota 100m on users;
create user Frank identified by Frank123 quota 100m on users;
create user Howard identified by Howard123 quota 100m on users;
create user James identified by James123 quota 100m on users;
create user Jay identified by Jay123 quota 100m on users;
create user Jimmy identified by Jimmy123 quota 100m on users;
create user John identified by John123 quota 100m on users;
create user Joseph identified by Joseph123 quota 100m on users;
create user Ken identified by Ken123 quota 100m on users;
create user kidd identified by kidd123 quota 100m on users;
create user Leon identified by Leon123 quota 100m on users;
create user Niles identified by Niles123 quota 100m on users;
create user Rainmo identified by Rainmo123 quota 100m on users;
create user Ray identified by Ray123 quota 100m on users;
create user Richie identified by Richie123 quota 100m on users;
create user Steven identified by Steven123 quota 100m on users;
create user Vincent identified by Vincent123 quota 100m on users;
create user Walter identified by Walter123 quota 100m on users;
create user Xiandong identified by Xiandong123 quota 100m on users;
create user Yh identified by Yh123 quota 100m on users;
create user Yihhann identified by Yihhann123 quota 100m on users;
create user Apple identified by Apple123 quota 100m on users;
create user Banana identified by Banana123 quota 100m on users;
create user Cobby identified by Cobby123 quota 100m on users;
create user Emily identified by Emily123 quota 100m on users;
create user Franky identified by Franky123 quota 100m on users;
create user Gimmy identified by Gimmy123 quota 100m on users;
create user Hocky identified by Hocky123 quota 100m on users;
create user Ivy identified by Ivy123 quota 100m on users;
create user Jacky identified by Jacky123 quota 100m on users;
create user Kenny identified by Kenny123 quota 100m on users;
create user Lemon identified by Lemon123 quota 100m on users;


create role rl_index;
grant connect,resource to rl_index;

create public synonym COUNTRIES for hr.COUNTRIES;
create public synonym  DEPARTMENTS for hr.DEPARTMENTS;
create public synonym EMPLOYEES for hr.EMPLOYEES;
create public synonym JOBS for hr.JOBS;
create public synonym  JOB_HISTORY for hr.JOB_HISTORY;
create public synonym LOCATIONS for hr.LOCATIONS;
create public synonym REGIONS for hr.REGIONS;


grant select on hr.COUNTRIES to rl_index;
grant select on hr.DEPARTMENTS to rl_index;
grant select on hr.EMPLOYEES to rl_index;
grant select on hr.JOBS to rl_index;
grant select on hr.JOB_HISTORY to rl_index;
grant select on hr.LOCATIONS to rl_index;
grant select on hr.REGIONS to rl_index;

grant rl_index to Aha ;
grant rl_index to Andy ;
grant rl_index to Ann ;
grant rl_index to Brian ;
grant rl_index to Charles ;
grant rl_index to Cloud ;
grant rl_index to Clover ;
grant rl_index to Danniel ;
grant rl_index to Dennis ;
grant rl_index to Dylan ;
grant rl_index to Edgar ;
grant rl_index to Edward ;
grant rl_index to Eric ;
grant rl_index to Frank ;
grant rl_index to Howard ;
grant rl_index to James ;
grant rl_index to Jay ;
grant rl_index to Jimmy ;
grant rl_index to John ;
grant rl_index to Joseph ;
grant rl_index to Ken ;
grant rl_index to kidd ;
grant rl_index to Leon ;
grant rl_index to Niles ;
grant rl_index to Rainmo ;
grant rl_index to Ray ;
grant rl_index to Richie ;
grant rl_index to Steven ;
grant rl_index to Vincent ;
grant rl_index to Walter ;
grant rl_index to Xiandong ;
grant rl_index to Yh ;
grant rl_index to Yihhann ;
grant rl_index to Apple ;
grant rl_index to Banana ;
grant rl_index to Cobby ;
grant rl_index to Emily ;
grant rl_index to Franky ;
grant rl_index to Gimmy ;
grant rl_index to Hocky ;
grant rl_index to Ivy ;
grant rl_index to Jacky ;
grant rl_index to Kenny ;
grant rl_index to Lemon ;

alter user scott identified by systex7720 account unlock;


create public synonym emp for scott.emp;
create public synonym dept for scott.dept;
create public synonym bonus for scott.dept;
create public synonym SALGRADE for scott.SALGRADE;

grant select on scott.emp to rl_index;
grant select on scott.dept to rl_index;
grant select on scott.dept to rl_index;
grant select on scott.SALGRADE to rl_index;