--:sys
1.create tablespace adduser_own datafile '/opt/oracle/oradata/adduser01.dbf' size 10m;
2.create temporary tablespace adduser_own_tmp tempfile '/opt/oracle/oradata/adduser_tmp01.tmp' size 100m;
3.create user adduser_own identified by adduser_own default tablespace adduser_own temporary tablespace adduser_own_tmp;
4.create user adduser identified by adduser default tablespace admin temporary tablespace adduser_own_tmp;
5.grant obj_own_user to adduser_own;
grant obj_use_user to adduser;
6.alter user adduser_own quota unlimited on adduser_own;
alter user adduser quota unlimited on admin;
--:adduser_own
7.create role ADDUSER_OWN_OBJ_PRIVS;
8.
set pagesize 0
set heading off
set feedback off
spool adduser_own_obj_privs.sql
SELECT 'grant select,insert,update,delete on '||OBJECT_NAME||' TO ADDUSER_OWN_OBJ_PRIVS;' from user_objects where object_type in ('TABLE','VIEW');
SELECT 'grant select on '||OBJECT_NAME||' TO ADDUSER_OWN_OBJ_PRIVS;' from user_objects where object_type in ('SEQUENCE');
SELECT 'grant execute on '||OBJECT_NAME||' TO ADDUSER_OWN_OBJ_PRIVS;' from user_objects where object_type in ('PRODUCE','FUNCTION','PACKGE');
spool off
@adduser_own_obj_privs.sql;
spool adduser_own_obj_synonyms.sql
SELECT 'create or replace synonym '||OBJECT_NAME||' for adduser_own.'||OBJECT_NAME||';' from user_objects where object_type in ('TABLE','VIEW','SEQUENCE','PRODUCE','FUNCTION','PACKGE');
spool off
9.grant adduser_own_obj_privs to adduser;
--:adduser
10.@adduser_own_obj_synonyms.sql
Notes:
1.obj_own_user
create role obj_own_user; --create by sys for the object's owner; For: user_own
grant connect,resource,create role to role obj_own_user;
revoke unlimited tablespace from obj_own_user;
2.obj_use_user
create role role obj_use_user; --create by sys for the object's user; For:user
grant create sequence to OBJ_USE_USER;
grant create session to OBJ_USE_USER;
grant create synonym to OBJ_USE_USER;
grant create table to OBJ_USE_USER;
grant create view to OBJ_USE_USER;
3.The grammar of create synonym
CREATE [PUBLIC] SYNONYM synonym_name FOR [schema.] object[@db_link];
Oracle 无法通过同义词访问远端分区表的某个分区,访问本地没有问题;