Create a user in Oracle and Set permissions in oracle

Source: Internet
Author: User

Create a user in Oracle and Set permissions in oracle

(To: http://www.cnblogs.com/yangy608/archive/2011/08/22/2148893.html)

Create user TEST identified by "TEST" -- create a TEST user

Default tablespace USERS

Temporary tablespace TEMP

Profile DEFAULT;

Grant connect, create view, resource to TEST;

Grant unlimited tablespace to TEST;

 

 

-- Administrator authorization

Grant create session to TEST; -- grant the TEST user the permission to create a session, that is, the login permission.

Grant unlimited session to TEST; -- grant the TEST user the permission to use the tablespace.

Grant create table to TEST; -- grant the table creation permission

Grant drop table to TEST; -- grant the table deletion permission

Grant insert table to TEST; -- insert table permission

Grant update table to TEST; -- Modify table Permissions

Grant all to public; -- this is important. grant all permissions to all users (public)

-- Oralce has strict permission management, and common users cannot access each other by default.

Grant select on tablename to TEST; -- grant the TEST user the permission to view the specified table.

Grant drop on tablename to TEST; -- grant the table deletion permission

Grant insert on tablename to TEST; -- grant the insert permission

Grant update on tablename to TEST; -- grant the table modification permission.

Grant insert (id) on tablename to TEST;

Grant update (id) on tablename to TEST; -- grant the insert and modify permissions for specific fields in the specified table. Note that only insert and update are allowed.

 

-- Revoke permissions

The basic syntax is the same as grant. The keyword is revoke.

-- View Permissions

Select * from user_sys_privs; -- View All permissions of the current user

Select * from user_tab_privs; -- view the table permissions of the users used

-- Operate the table's user table

/* Add the user name before the table name, as shown below */

-- Transfer Permissions

That is, user A grants permissions to user B, and user B can grant operation permissions to user C. The command is as follows:

Grant alert table on tablename to TEST with admin option; -- Keyword with admin option

Grant alert table on tablename to TEST with grant option; -- the keyword with grant option has the same effect as admin.

-- Role

A role is a set of permissions. You can assign a role to a user.

Create role myrole; -- create a role

Grant create session to myrole; -- grant the permission to create a session to myrole.

Grant myrole to TEST; -- grant the role of myrole to the TEST user

Drop role myrole; delete a role

/* However, some permissions cannot be granted to the role, such as the unlimited tablespace and any keywords */

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: info-contact@alibabacloud.com 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.