How to assign permissions to oracle users

Source: Internet
Author: User

Here, we will introduce how to allocate permissions for oracle users and how to export data from the server using volume El, hoping to help you. Many people think it is not very difficult to allocate permissions for oracle users. However, you need to be careful and patient in assigning permissions for oracle users. This article will talk about how to assign to mid users.

Authorize all the tables under the stat user to the mid user, that is, the mid user can access the tables under the stat user; sqlplus STAT/STAT log on with this user

Select 'Grant select, insert, update, delete on Stat. '| table_name |' to mid; 'from user_tables;

Then select all the statements listed and enter the sysdba permission for the operation: sqlplus "/As sysdba"

Then, execute all the preceding statements and grant the Oracle user permissions;

Stored Procedures under the stat user access stored procedures under the mid User: access the sysdba permission for operations;

Grant execute any procedure to mid grant execute any procedure to stat

When the stored procedure of the stat user accesses the types temporary table of the MID user, the stat user is assigned to execute any type to stat.

The second method is to import the Oracle data on the server to the local machine. This method does not include the tablespace and temporary tablespace created on the server during export, and does not need to be created on the local machine, you only need to use the default tablespace to execute the following command in cmd mode: EXP username/password @ Server Database ID file = c: \ file name. dmp

For example:

Exp djyy/djyy @ zhwx file = c: \ djyy20090921.dmp exp wxzd/wxzd @ zhwx file = c: \ wxzd20090921.dmp

Import the data to the user on the local machine, create a user, log on with the new user name in plsqldeveloper, select imports tables under the Tools menu, and select the Oracle import label, in the import executable file, select the imp.exeexecutable file in the db_1 \ bindirectory under the Oracle Installation Directory (the general situation is that the imp.exe file can be used for manual modification. If this item does not exist, you can select the imp.exe file for import ). in the import file, select the exported DMP suffix file name, and then execute the Import Statement to import the created tablespace as follows:

Create tablespace zhwxsys datafile 'd: \ oracle \ product \ 10.2.0 \ oradata \ orcl \ zhwxsys. dbf' size 500 m uniform size 128 K;

The statement for creating a temporary tablespace is as follows:

Create temporary tablespace zhwxtmp tempfile 'd: \ oracle \ product \ 10.2.0 \ oradata \ orcl \ zhwxtmp. dbf' size 50 m


Grant permissions to users: grant connect, DBA, resource to user001

Revoke user permission: revoke resource from user001

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.