Grant, view, and manage user permissions in Oracle

Source: Internet
Author: User

In Oracle databases, user permissions are divided into two types (here we do not discuss dba or dbopr permissions, but only the permissions of common users ), system Privilege System permission and User Table Privilege User data Table permission.
  
1. First, create a user. The following commands can be used to create a user, provided that you must Log On As a dba (if you are not a DBA, do not check it ):
  
Create user DB_USER identified by DB_USER_PW 'create user DB_USER with the password DB_USER_PW
  
Grant create session to DB_USER permission to create a session for the user
  
Grant resource to DB_USER
  
2. after a user is created, Scheme of the user is automatically generated in the Oracle database system (it can be understood as all tables belonging to the user, view .... object set ).
  
This user can grant access permissions to these objects to other system users.
  
3. After the user logs on with sqlplus, the following command shows the user's permissions (this part is taken from the CNOUG website ):
  
This user's permission to read other user objects:
Select * from user_tab_privs;
System permissions of the User:
Select * from user_sys_privs;
  
4. System Privilege list
  
PRIVILEGE NAME PROPERTY
------------------------------------------------------------
-228 administer database trigger 0
-227 administer resource manager 1
-161 alter profile 0
-189 create any library 0
-201 create any operator 0
-224 create any outline 0-141 create any procedure 0
-120 create public database link 0
-90 create view 0
-50 delete any table 0
-220 dequeue any queue 1
-63 drop any cluster 0
-223 drop any context 0
-217 drop any dimension 0
-178 drop any directory 0
-73 drop any index 0
-208 drop any indextype 0
-191 drop any library 0
-203 drop any operator 0
-226 drop any outline 0
-209 extends any type 0-186 extends type 0
-139 force any transaction 0
-138 force transaction 0
-199 WRITEUP 0
-196 WRITEUP DBHIGH

......

......

-- Determine the role Permissions
Select * from role_tab_privs; the data dictionary view contains the object permissions granted to the role.
Select * from role_role_privs; contains the role assigned to another role
Select * from role_sys_privs; includes the system permissions granted to the role

-- Determine the permissions granted to the user account
Select * from DBA_tab_privs; grant the object permission of the user account directly.
Select * from DBA_role_privs; role granted to the user account
Select * from DBA_sys_privs; grant the system permission of the user account

View Current User Permissions:
SQL> select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION
CREATE TABLE
CREATE CLUSTER
CREATE SYNONYM
CREATE VIEW
CREATE SEQUENCE
CREATE PROCEDURE
CREATE TRIGGER

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.