08.txt

Oracle database 10g基础教程（第二版） 源码

connect system/manager
create user oracle_admin
identified by oracle_admin
/
grant create session, dba
to oracle_admin;




create user emp_adult
identified by emp_adult
/
grant create session, resource
to emp_adult
/

 


connect emp_adult/emp_adult
alter user emp_adult
identified by dba_adult
/




connect scott/tiger
connect system/manager
alter user scott account lock;
connect scott/tiger



connect system/manager
alter user scott account unlock;
connect scott/tiger


select tablespace_name, contents
from dba_tablespaces;


alter user emp_adult
default tablespace users
temporary tablespace temp
/




connect system/manager
create user dropme
identified by dropme;
grant create session, create table to dropme;
connect dropme/dropme
create table employees_backup(
employee_id number,
employee_name varchar2(20),
email varchar2(128)
);


connect system/manager
alter user dropme
default tablespace sysaux
quota 10M on sysaux;

connect dropme/dropme
create table employees_backup(
employee_id number,
employee_name varchar2(20),
email varchar2(128)
);


connect system/manager
drop user dropme;

connect system/manager
drop user dropme cascade;


connect system/manager
desc dba_sys_privs
select *
from dba_sys_privs;










connect system/manager
create user athos
identified by domas;
connect athos/domas



connect system/manager
grant create session to athos;
connect athos/domas

create table mytable(
id number);

connect system/manager
grant create table to athos;





connect system/manager
alter user athos
quota 10M on sysaux;


connect athos/domas
create table mytable(
id number);

select *
from user_ts_quotas;



connect system/manager
revoke create table from athos;



connect athos/domas
grant select on mytable to scott;



connect scott/tiger
select count(*)
from athos.mytable;


connect scott/tiger
grant select on athos.mytable
to system;


connect athos/domas
grant select on mytable 
to scott
with grant option;



connect scott/tiger
grant select on athos.mytable
to system;



connect athos/domas
desc user_tab_privs


column grantee format a10
column owner format a10
column table_name format a10
column grantor format a10
column privilege format a10
column grantable format a10
column hierarchy format a10
select *
from user_tab_privs;


connect athos/domas
revoke select on mytable
from scott;



connect athos/domas
revoke select on mytable
from system;

connect system/manager
grant create role to athos;
connect athos/domas
create role assembly_line;

grant select, insert, update on mytable
to assembly_line
/




set linesize 200
column grantee format a20
column owner format a10
column table_name format a10
column grantor format a10
column privilege format a10
column grantable format a10
column hierarchy format a10
select *
from user_tab_privs;



connect athos/domas
grant assembly_line to scott;




connect scott/tiger
insert into athos.mytable values(1);
select * from athos.mytable;
delete from athos.mytable;


set linesize 100
set pagesize 20
column role format a20
column privilege format a20
column admin_option format a5
select *
from role_sys_privs;



connect system/manager
create role general_manager
identified by general_password;
grant general_manager to scott;

alter user scott
default role all except general_manager;


connect scott/tiger
set role general_manager 
identified by general_password;


connect scott/tiger
select *
from session_roles;

insert into athos.mytable
values(2);
rollback;



create or replace procedure add_mytable
as
begin
    insert into athos.mytable values(2);
end;
/

show errors



connect athos/domas
grant insert on mytable
to scott;

connect scott/tiger
create or replace procedure add_mytable
as
begin
    insert into athos.mytable values(2);
end;
/