Oracle permission settings

2. system permission management: 1. system permission classification: DBA: All privileges are granted, which is the highest system permission. only DBA can create a database structure. RESOURCE: owns a Resource.

2. system permission management: 1. system permission classification: DBA: All privileges are granted, which is the highest system permission. only DBA can create a database structure. RESOURCE: owns a Resource.

I. Permission classification:
System permission: The system specifies the user's permission to use the database. (System permissions are for users ).

Object permission: the access permission of a certain user to tables or views of other users. (For tables or views ).

Ii. system permission management:
1. system permission classification:
DBA: it has all the privileges and is the highest system permission. only DBA can create a database structure.

RESOURCE: users with Resource permissions can only create entities, but cannot create database structures.

CONNECT: users with the Connect permission can only log on to Oracle, create entities, and create database structures.

For normal users: grant connect and resource permissions.
For DBA management users: grant the connect, resource, and dba permissions.

2. 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/manager
SQL> 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.

3. pass system permissions:
If the with admin option is added, the obtained permissions can be passed.

SQL> grant connect, resorce to user50 with admin option; // You can pass the obtained permissions.

4. Revoke system permissions: only DBA users can revoke system permissions.
Command: SQL> Revoke connect, resource from user50;

Note:

1) if you use the with admin option to grant system permissions to a user, for all users granted the same permissions by this user, canceling the user's system permissions does not cascade the same permissions of these users.

2) The system permissions are continuously connected, that is, A grants B permissions, and B grants C permissions. If A revokes B permissions, C permissions are not affected. system permissions can be revoked across users ,, that is, A can directly revoke the permissions of user C.

Iii. Object permission management
1. Object permission classification: select, update, insert, alter, index, delete, all // all includes all Permissions
Execute // execute the Stored Procedure permission

User01:
SQL> grant select, update, insert on product to user02;
SQL> grant all on product to user02;

User02:
SQL> select * from user01.product;

// At this time, user02 queries user_tables, excluding the table user01.product. However, if all_tables is checked, it can be found because it can be accessed.

2. Grant table operation permissions to all users:
SQL> grant all on product to public; // public indicates all users. The all permission here does not include drop.

[Object permission data dictionary]:
SQL> select owner, table_name from all_tables; // tables that can be queried by the user
SQL> select table_name from user_tables; // table created by the user
SQL> select grantor, table_schema, table_name, privilege from all_tab_privs; // authorized table (authorized)
SQL> select grantee, owner, table_name, privilege from user_tab_privs; // The table that grants permissions (granted permissions)

3. DBA users can operate any base table of all users (no authorization required, including deletion ):
DBA User:
SQL> Create table stud02.product (
Id number (10 ),
Name varchar2 (20 ));
SQL> drop table stud02.emp;

SQL> create table stud02.employee
As
Select * from scott. emp;

4. Object permission transfer (with grant option ):
User01:

SQL> grant select, update on product to user02 with grant option; // get the permission and pass it.

5. Revoke object permissions:
User01:
SQL> Revoke select, update on product from user02; // all permissions passed will be lost.

Description

1) if you cancel the object permissions of a user, the same permissions of the users who use the with grant option to GRANT permissions are also revoked, that is to say, it is Cascade when authorization is canceled.

Oracle user management
1. Create a user's Profile file
SQL> create profile student limit // student is the resource file name
FAILED_LOGIN_ATTEMPTS 3 // specify the number of Logon failures for the user to be locked
PASSWORD_LOCK_TIME 5 // specify the number of days the user is locked
PASSWORD_LIFE_TIME 30 // specifies the password availability days

2. Create a user
SQL> Create User username
Identified by password
Default Tablespace tablespace
Temporary Tablespace tablespace
Profile profile
Quota integer/unlimited on tablespace;

Example:
SQL> Create user acc01
Identified by acc01 // if the password is a number, enclose it in double quotation marks.
Default tablespace account
Temporary tablespace temp
Profile default
Quota 50 m on account;
SQL> grant connect, resource to acc01;

[*] Query the user's default tablespace and temporary tablespace
SQL> select username, default_tablespace, temporary_tablespace from dba_users;

[*] Query system resource file name:
SQL> select * from dba_profiles;
Resource files are similar to tables, which are saved in the database once created.
SQL> select username, profile, default_tablespace, temporary_tablespace from dba_users;

SQL> create profile common limit
Failed_login_attempts 5
Idle_time 5;

SQL> Alter user acc01 profile common;