Oracle permission management

1. Default users of the system
Java code
Sys; // System Administrator with the highest Permissions
System; // local administrator with the next high permission
Scott; // common user. The default password is tiger and is not unlocked by default.

2. Login
Java code
Sqlplus conn/as sysdba; // log on to the sys account
Sqlplus sys as sysdba; // same as above
Sqlplus scott/tiger; // log on to normal user scott

3. Manage Users
Java code
Create user zhangsan; // under the Administrator account, create the user zhangsan
Alert user scott identified by tiger; // modify the password

4. Grant Permissions
1. The default normal user scott is not unlocked by default and cannot be used. The new user does not have any permissions and must be granted permissions.
Java code
/* Administrator authorization */
Grant create session to zhangsan; // grant the zhangsan user the permission to create a session, that is, the login permission.
Grant unlimited session to zhangsan; // grant the zhangsan user the permission to use the tablespace.
Grant create table to zhangsan; // grant the table creation permission
Grant drop table to zhangsan; // grant the table deletion permission.
Grant insert table to zhangsan; // insert table permission
Grant update table to zhangsan; // Modify table Permissions
Grant all to public; // This is important. grant all permissions to all users (public)

2. oralce is more rigorous in permission management. Common users cannot access each other by default and must be authorized to each other.
Java code
/* Oralce strictly manages permissions, and common users cannot access each other by default */
Grant select on tablename to zhangsan; // grant the zhangsan user the permission to view the specified table.
Grant drop on tablename to zhangsan; // grant the table deletion permission.
Grant insert on tablename to zhangsan; // grant the insert permission
Grant update on tablename to zhangsan; // grant the table modification permission.
Grant insert (id) on tablename to zhangsan;
Grant update (id) on tablename to zhangsan; // grant the insert and modify permissions for specific fields in the specified table. Note that only insert and update are allowed.
Grant alert all table to zhangsan; // grant permissions to any table of the zhangsan user alert

5. revoke permissions
Java code
The basic syntax is the same as grant. The keyword is revoke.

6. View Permissions
Java code
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

VII. User table of the operation table
Java code
/* Add the user name before the table name, as shown below */
Select * from zhangsan. tablename

8. Permission Transfer
That is, user A grants permissions to user B, and user B can grant operation permissions to user C. The command is as follows:
Java code
Grant alert table on tablename to zhangsan with admin option; // keyword with admin option
Grant alert table on tablename to zhangsan with grant option; // The keyword with grant option is similar to admin.

9. Roles
A role is a set of permissions. You can assign a role to a user.
Java code
Create role myrole; // create a role
Grant create session to myrole; // grant the create session permission to myrole
Grant myrole to zhangsan; // grant the role of myrole to THE zhangsan user
Drop role myrole; delete a role
/* However, some permissions cannot be granted to the role, such as the unlimited tablespace and any keywords */

This article from the CSDN blog, reprinted please indicate the source: aspx "> http://blog.csdn.net/JustForFly/archive/2009/10/16/4681707.aspx

Object permission:

1. syntax for granting object permissions:
GRANT object_privilege ON object_name TO username [with grant option];
Note: after using the with grant option statement, you can GRANT the same permissions to other users, which is the same as the system permissions.

2. Object permission revocation Syntax:
REVOKE object_privilege ON object_name FROM username;
Note: When user A's permissions are deleted, the permissions granted to user B through user A will automatically disappear, which is opposite to system permissions.

3. View specific object permissions:
Select * from dba_tab_privs where grantee = WANGXIAOQI;
Select * fromTABLE_PRIVILEGESwhere GRANTEE = WANGXIAOQI;
Note 1: both of them can view object permissions, but the presentation form is different.
Note 2: The table name is TABLE_PRIV, but not only table, but all object information, including function, procedure, and package.

4. The object privilege type is relatively small and varies according to the object type.

When assigning values, you can use all to replace all permission types of objects of this type, for example:
Grant select, update, delete, insert on table_name to user_name;

Grant all on table_name to user_name;

Note: For tables, all includes: ALTER, DELETE, INDEX, INSERT, SELECT, UPDATE, REFERENCES, on commit refresh, query rewrite, DEBUG, FlashBACK

 

Role management:

1. Simplify authorization operations using role. Each role contains several system permissions. Role includes system predefine and custom.
Select * from dba_roles; -- Query all current ROLE lists, including custom
Select * from dba_role_privs; -- query the ROLE permission of a user
Select * from ROLE_SYS_PRIVS; -- query the current user's ROLE and its system Permissions

2. System predefined roles:
CONNECT:
CREATE VIEW
CREATE TABLE
ALTER SESSION
CREATE CLUSTER
CREATE SESSION
CREATE SYNONYM
CREATE SEQUENCE
CREATE DATABASE LINK
RESOURCE:
CREATE TYPE
CREATE TABLE
CREATE CLUSTER
CREATE TRIGGER
CREATE OperaTOR
CREATE SEQUENCE
CREATE INDEXTYPE
CREATE PROCEDURE
It also includes important ROLE such as DBA, EXP_FULL_DATABASE, and IMP_FULL_DATABASE.

3. Custom roles:

Create a ROLE:
Create role role_name
[Not identified | identified by passWord]
Note: IDENTIFIED indicates whether a password is required when the ROLE is modified <modification, excluding authorization and permission cancellation>

After creating a role, use grant and revoke to manually set the permissions of the role.
Then grant the role to the user using grant and revoke.
Note: You can assign a role to a role.

4. Enable and disable ROLE:
Set role [role [identified by password] |, role [identified by password]...]
| ALL [role t role [, role]...]
| NONE];
Note: "ALL" indicates to enable ALL roles of the modified user, and "NONE" indicates to disable ALL roles.

Example:
Disable all roles: setrolenone;
Enable all roles: setroleall; -- the role cannot have a password.
Enable a role: setrole role_test identifiedby test; -- with a password
Disable a role: setroleallexcept role_test;
Note: The setrole command is overwrite, that is, one cannot be started first, and the other can be enabled again. All commands must be started;

5. Set roles when modifying users:
Alter user username
[Default role [role_name [, role_name ,.