Politechnika Śląska Gliwice 29.03.2004
Wydział AEiI
LABORATORIUM BAZ DANYCH
BD3 - DDL
Sekcja II
Dutkowski Dominik
Kocur Łukasz
Bienia Tomasz
Biały KrzysztofZadanie 2.
create table zesp1 (nazwazesp char (15), nrz smallint not null, nrpkz smallint, primary key (nrz));
TABLE CREATED
create unique index nr_z on zesp1 (nrz);
INDEX CREATED WITH 0 ROWS
create table prac1 (nrp smallint not null, kobieta char(1),data_ur timestamp not null,nazwisko char (15) not null, nrz smallint, primary key (nrp));
TABLE CREATED
create unique index nr_p on prac1 (nrp);
INDEX CREATED WITH 0 ROWS
create index nrz on prac1 (nrz);
INDEX CREATED WITH 0 ROWS
Zadanie 3
insert into zesp1 select * from zespol;
6 ROWS INSERTED
insert into prac1 select * from pracowni;
36 ROWS INSERTED
Zadanie 4.
alter table zesp1 foreign key (nrpkz) references prac1 on delete restrict;
FOREIGN KEY ADDED
alter table prac1 foreign key (nrz) references zesp1 on delete set null;
FOREIGN KEY ADDED
Zadanie 5.
select * from prac1;
select *from zesp1;
delete from prac1 where nrp=10;
Error: Cannot delete row until all the dependent rows are deleted
delete from zesp1 where nrz=5;
1 ROW DELETED
Zadanie6.
grant connect to admin identified by admin;
CONNECT AUTHORITY GRANTED
disconnect 1;
CURSOR 1 DISCONNECTED
connect baza3 admin/admin;
CURSOR 1 CONNECTED TO baza3
select * from sysadm.zesp1;
Error: Security violation attempting to access ZESP1
disconnect 1;
CURSOR 1 DISCONNECTED
connect baza3;
CURSOR 1 CONNECTED TO baza3
grant resource to admin;
RESOURCE AUTHORITY GRANTED
grant dba to admin;
DBA AUTHORITY GRANTED
disconnect 1;
CURSOR 1 DISCONNECTED
connect baza3 admin/admin;
select * from sysadm.zesp1;
5 ROWS SELECTED
select * from sysadm.sysuserauth;
4 ROWS SELECTED
grant connect to prac identified by prac;
Error: No grant authority
CONNECT AUTHORITY GRANTED
grant connect to stud identified by stud;
CONNECT AUTHORITY GRANTED
grant select on sysadm.zesp1 to prac;
PRIVILEGE(S) GRANTED ON TABLE OR VIEW
create synonym zesp1 for sysadm.zesp1;
SYNONYM CREATED
connect baza3 prac/prac;
CURSOR 1 CONNECTED TO baza3
select * from sysadm.zesp1;
5 ROWS SELECTED
insert into sysadm.zesp1 values ('mysz',7,13);
Error: Security violation attempting to access ZESP1
create table tem1 (nazwatemat char(30), data_odb timestamp, nrt integer not null, nrpkt smallint not null);
Error: No resource authority
TABLE CREATED
grant resource to prac;
RESOURCE AUTHORITY GRANTED
insert into prac.tem1 values ('mysz',0,1,4);
Error: Security violation attempting to access TEM1
grant insert on prac.prac1 to stud;
Error: Security violation attempting to access PRAC1
grant insert on prac.tem1 to stud;
PRIVILEGE(S) GRANTED ON TABLE OR VIEW
grant insert on sysadm.prac1 to stud;
PRIVILEGE(S) GRANTED ON TABLE OR VIEW
grant select on zesp1 to public;
PRIVILEGE(S) GRANTED ON TABLE OR VIEW
insert into prac.tem1 values ('mysz',0,1,4);
1 ROW INSERTED
select * from sysadm.zesp1;
5 ROWS SELECTED
revoke resource from prac;
Error: No revoke authority
grant connect to stud identified by student;
Error: No grant authority
revoke resource from prac;
RESOURCE AUTHORITY REVOKED
grant connect to stud identified by student;
CONNECT AUTHORITY GRANTED
revoke connect from stud;
Error: Privileges exist for user STUD
revoke connect from prac;
Error: Table(s) exist for specified user PRAC
drop table prac.tem1;
TABLE DROPPED
4