Oracle Users and Permissions

In Oracle, there is typically only one database on a server. In a database, different user projects are accessed by different users, each user has a database object that they create, and when a user wants to access a database object under another user, they must be granted certain permissions, so the user and permissions are important in Oracle. Here is a summary of some of my knowledge about users and permissions, and share with you.

Before connecting to a database, you must ensure that the Oracle instance's service and listener are turned on. In Oracle, there are two database administrator users (SYS and system) and an ordinary user (Scott) by default. SYS user has all the functions of database management, the system user can not be used for database backup and recovery, the Scott table is a user for program testing.

First, the user

1. Create a user

Format: Create user username identified by password;

Create User  by 123456;

After the user is created, the database is not connected, and permissions to manipulate database objects are created, and appropriate authorization is required.

Grant  to Test1; -- allow users to connect to the database Grant  to Test1; -- allows users to create related database objects, such as tables, sequences, and so on. 

With the above authorization, you can connect to the database and create objects such as tables.

2. Common system permissions

System permissions	Allow user's actions
Create session	Connecting to a database
Create sequence	Create a sequence
Create synonym	Create synonyms
CREATE table	Create a table in the user schema
Create any table	Create a table in any frame
drop table	To delete a table from the user frame
Drop any table	Delete a table in any frame
CREATE PROCEDURE	Create a stored procedure
Execute any procedure	Execute a stored procedure in any frame
Create user	Create user
Drop user	Delete User
CREATE view	Create a View

3. Database Roles

　　(1) Definition: A collection of several system permissions.

(2) Common characters

1>connect role: Used primarily in temporary tables, users with the Connect role can establish connection sessions with the server (session, client-to-server connections, called sessions).

Grant  to Test1;

　　　　2>resource Role: Resource provides users with additional permissions to create their tables, sequences, procedures, triggers, indexes, and so on. However, you cannot create a view.

Grant  to Test1;

　　3>DBA role: Has all system permissions and the user system has a DBA role.

Grant  to Test1;

　(3) One user's authorization to other users

When a user authorizes the use of with admin option, the user can continue to say that the permission is assigned to another user. And when the user's permissions are retracted, the user's assigned system permissions are not affected.

Grant Connect,resource,dropuserto withoption;

4. Revoke Permissions

Revoke  from Test2;

5. Modify User Password

-- Modify User Password Alter User  by 111111 ; -- or visual change user password conn test2/123456  password; -- password must be placed on the next line

6. Query users

Select *  from dba_users; Select *  from User_role_privs; SELECT *  from User_sys_privs

7. Delete a user

Drop user test2;

Note: When deleting a user, if the user schema contains objects (such as tables, etc.), then a cascade delete is required when deleting the user, that is, drop user test2 cascade;

Summary: 1. Under normal circumstances, if a normal user is only doing test users, do not need to build a table, etc., only assign connect permissions.

2. Assign Connect and resource permissions if the user needs to create a table.

3. When a user needs to grant the system permission to another user, the other user must assign the user a privilege limit with admin option

Second, object permissions

1. Definition: Object permissions allow users to perform certain database object operations, such as executing DML statements on a table.

Object permissions	Allow user's actions
Select	Inquire
Update	Update
Add	Increase
Delete	Delete
Execute	Executing stored procedures and functions

2. Allocation and recovery of object permissions

　　The syntax and allocation of the system permission type is not used when the authorized user is allowed to grant permissions to other users using the WITH GRANT option, rather than with the admin option. And when the user's permissions are retracted, the permission that it previously granted to other users is also revoked.

Oracle Users and Permissions