Oracle user creation and permissions settings

Source: Internet
Author: User
Tags dba sqlplus

Permissions:

Create session

CREATE table

Unlimited tablespace

Connect

Resource

Dba

Cases:

#sqlplus/nolog

Sql> Conn/as sysdba;

Sql>create user username identified by password

Sql> Grant DBA to username;

Sql> Conn Username/password

Sql> select * from User_sys_privs;


We will start with the creation of Oracle User Rights table, then explain the general actions such as landing, so that you have a deep understanding of the Oracle User Rights table.

First, create

Sys System Administrator with the highest privileges

System Local administrator, sub-high privilege

Scott Normal user, password default to Tiger, default unlocked


Second, landing

Sqlplus/as Sysdba; Login SYS Account

Sqlplus Sys as SYSDBA; Ditto

Sqlplus Scott/tiger; Login to General user Scott


Third, manage users

Create user Zhangsan; Under the Administrator account, create a user Zhangsan

Alert user Scott identified by Tiger; Change Password


Iv. granting of rights

1, the default ordinary user Scott is not unlocked by default, cannot do that use, the new user does not have any permissions, must be granted permissions

Grant create session to Zhangsan; Grant Zhangsan user permission to create session, that is, login permission

Grant unlimited tablespace to Zhangsan; Granting Zhangsan users permission to use tablespaces

Grant CREATE table to Zhangsan; Granting permissions to create tables

Grante drop table to Zhangsan; Granting permission to delete a table

Grant Insert table to Zhangsan; Permissions to insert Tables

Grant Update table to Zhangsan; Permissions to modify tables

Grant all to public; This is more important, grant all permissions (all) to all users (public)


2, Oralce on the rights management more rigorous, ordinary users are also the default can not access each other, need to authorize each other

Grant SELECT on TableName to Zhangsan; Grant Zhangsan users permission to view the specified table

Grant drop on TableName to Zhangsan; Granting permission to delete a table

Grant insert on TableName to Zhangsan; Grant the Insert permission

Grant update on TableName to Zhangsan; Granting permissions to modify tables

Grant Insert (ID) on tablename to Zhangsan;

Grant Update (ID) on tablename to Zhangsan; Grant insert and Modify permissions on specific fields of the specified table, note that only insert and update

Grant alert all table to Zhangsan; Grant Zhangsan user alert permission to any table


V. Revocation of Rights

Basic syntax with GRANT, keyword revoke


Vi. Viewing permissions

SELECT * from User_sys_privs; View all permissions for the current user

SELECT * from User_tab_privs; To view the permissions used by the user on the table


Vii. Table of users of the action table

SELECT * FROM Zhangsan.tablename


Viii. Transfer of rights

That is, user a grants permission to B,b to grant the permission of the operation to C again, with the following command:

Grant alert table on TableName to zhangsan with admin option; Keyword with admin option

Grant alert table on TableName to Zhangsan with GRANT option; Keyword with GRANT option effect similar to admin


Nine, the role

A role is a collection of permissions that can grant a role to a user

Create role Myrole; Create a role

Grant create session to Myrole; Grant Myrole permission to create session

Grant Myrole to Zhangsan; Roles granted to Zhangsan user Myrole

Drop role Myrole; Remove a role


This article is from a "a little" blog, make sure to keep this source http://pengai.blog.51cto.com/6326789/1976367

Oracle user creation and permissions settings

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: 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.