Oracle permission settings

Oracle's user security management mainly has permissions and Roles

When a user is created, the user does not have any permissions and cannot perform any operations. To perform a specific database operation, you must grant the system permission to the database.

If you want to access objects in other schemes, you must grant them the object permissions. To simplify permission management, you can use roles.

Permission:

This is the right to execute specific commands and access objects, including system and object permissions.

System Permissions

Is to execute specific types of SQL commands, mainly for users.Category

All DBA permissions. You can create a database structure. Resource: only entities can be created. database structure cannot be created. Connect: Only logon is allowed. entity and database structure cannot be created.

For normal users: grant connect and resource permissions.

For DBA management users: grant connect, resource, and dba Permissions

System permission authorization command:

[System permissions can only be granted by DBA users: sys and system (only these two users are allowed at the beginning)]

Authorization command: SQL> grant connect, resource, dba to username 1 [, username 2]...;

[Normal users can have the same user permissions as system through authorization, but they can never have the same permissions as sys users. system users can also be revoked.]

Example:

SQL> connect system/managerSQL> Create user user50 identified by user50;SQL> grant connect,resource to user50; 

Query the permissions of a user:

SQL> select * from dba_role_privs;SQL> select * from dba_sys_privs;SQL> select * from role_sys_privs;

Delete A user: SQL> drop user Username cascade; // Add cascade to delete all the users and their created items.

Note: The with admin option can be included when the system permission is granted. In this way, the authorized user or role can grant the system permission to other users.

Or role.

For example

// Create a user

sql>create user kenidentified by m123;sql>create user tomidentified by m123

Authorize user ken

sql>grant create session,create table to ken with admin option;sql>grant create view to ken;

Authorize user tom

We canPassKen authorizes tomBecause with admin option is added. Of course.Authorize tom through DBAHere, we use the ken to authorize tom.

SQL> grant createsession, create table to tom with admin option; SQL> grantcreate view to tom; -- this is not acceptable because the Ken does not have the permission to be granted.

Revoke system Permissions

Generally, dba revokes system permissions. If other users revoke system permissions, the user must have the corresponding system permissions and grant system permissions to the user.

With admin option ). Revoke system permissions using revoke.

After the system permissions are revoked, the user cannot perform the corresponding operations. However, note that the system permissions are not cascaded.

Run the following statement:

sql>revoke create session from ken;

Note: After the DBA executes this statement and revokes the logon permission of the Ken, the Ken cannot log on again. The owner tom with the permission granted by the Ken can still be normal.

Login.

Object permission:

A permission allows you to access tables or views of other users. (For tables or views ).

Users can directly access the objects of their own solutions. However, if you want to access an object in another scheme, you must have the object permission. For example, the Smith user wants to access scott. emp.

Table (scott: Scheme, emp: Table) must have the object permission on the scott. emp table.

Commonly used

Add, delete, modify, query, modify (data modification and table structure modification), index, execute (execute), all, etc.

Grant create session to zhangsan; // grant the zhangsan user the permission to create a session, that is, grant the unlimited session to zhangsan; // grant the zhangsan user the permission to create table to zhangsan using the tablespace; // grant the table creation permission grante drop table to zhangsan; // grant the table deletion permission grant insert table to zhangsan; // grant the table insertion permission grant update table to zhangsan; // grant all to public permission to modify the table; // This is important. grant all permissions to all users (public)

Of course, with authorization and permission revocation are also included.

Summary:

At the beginning, I started to operate on oracle without looking at any language. I feel like I have no way to start. Is it hard to learn. However, after checking the basic oracle operations

Different commands. It is also interesting to have some more command authorization. We did not write this in SQL. Because SQL has a client to do this for us.

Work. So let's write more frequently-used commands. It is useful to get familiar with the environment at the beginning.