Migration of User objects under Oracle, packages, tables, attempts, sequences

Problem: In the development of the system, sometimes we want to be able to limit the front page to the database access behavior, such as in the Oracle database, we use a user-created database table structure, package, view, sequence, so a user has access to these objects all permissions,

If the front-end page is another team (bteam) development, another team is not familiar with the table structure of the database, in order to prevent bteam wrong operation caused data confusion, we create a user for Bteam user b,bteam through User B to access a user under

Created object, because Bteam accesses the database through B users, at this point the database level can limit the access rights of B users, thus to some extent to prevent bteam errors in the database data confusion.

When developing a larger system, there may be many objects under a user, so if the object of a user is authorized in bulk to B users? The script is as follows

--authorizing and creating synonyms Declare       CURSORC_tabname is         SELECT object_name  fromuser_objectswhereObject_typeinch('TABLE'); V_tabname Dba_tables.table_name%TYPE; SqlstrVARCHAR2( $); CURSORC_package is          SELECT object_name  fromuser_objectswhereObject_typeinch(' Package'); R_package C_package%ROWTYPE; CURSORC_view_seq is        SELECT object_name  fromuser_objectswhereObject_typeinch('VIEW','SEQUENCE'); R_view_seq C_view_seq%ROWTYPE; CURSORC_synonym is        Select 'Create public synonym'||object_name||'For UserA.'||object_name||';'Syn_create,'drop public synonym'||object_nameSyn_drop fromuser_objectswhereobject_typeinch('TABLE','VIEW','PROCEDURE','SEQUENCE',' Package'); R_syn c_synonym%ROWTYPE; begin      OpenC_tabname; LoopFetchC_tabname intoV_tabname; Exit  whenC_tabname%NOTFOUND; SQLSTR:= 'Grant SELECT, UPDATE, insert on UserA.' ||V_tabname||                'to UserB;';        Dbms_output.put_line (SQLSTR); Executeimmediate sqlstr; EndLoop; CloseC_tabname;  forR_packageinchc_package LOOP sqlstr:= 'Grant execute on UserA.' ||R_package.object_name ||                'to UserB;';         Dbms_output.put_line (SQLSTR); Executeimmediate sqlstr; ENDLOOP; Dbms_output.put_line ('views and sequences');  forR_view_seqinchc_view_seq LOOP sqlstr:= 'Grant Select on UserA.' ||R_view_seq.object_name ||                'to UserB;';         Dbms_output.put_line (SQLSTR); Executeimmediate sqlstr; ENDLOOP; --Create synonymsDbms_output.put_line ('Create synonyms!');  forR_syninchc_synonym LOOPBEGINsqlstr:=R_syn.syn_drop;            Dbms_output.put_line (SQLSTR); EXECUTEIMMEDIATE sqlstr; SQLSTR:=r_syn.syn_create;            Dbms_output.put_line (SQLSTR); EXECUTEIMMEDIATE sqlstr; EXCEPTION whenOTHERS Then          NULL; END ; ENDLOOP; END;

Migration of User objects under Oracle, packages, tables, attempts, sequences