Oracle creates a user and grants the user permission to query the specified table or view

Log in to the database using the DNINMSV31 account to do the following:

1) Create User:

CREATE USER NORTHBOUND identified by NORTHBOUND
DEFAULT tablespace "Tbs_dninmsv31"
Temporary tablespace "TEMP2"
QUOTA UNLIMITED on "tbs_dninmsv31";

( Note: Quota is to limit the user's use of the table space, such as you limit the user Guotu in tablespace cyyd quota is 10m, when the user Guotu in Tablespace cyyd The amount of data reached 10m, No matter how much space you have in your tablespace cyyd, Guotu can no longer use tablespace cyyd. )

2) User Authorization

GRANT "CONNECT" to NORTHBOUND;
ALTER USER NORTHBOUND DEFAULT ROLE NONE;

GRANT CREATE SESSION to northbound;--login permission

GRANT SELECT on "DNINMSV31". " V_dndevice "to northbound;--View" DNINMSV31 "." V_dndevice "Permissions
GRANT SELECT on "DNINMSV31". " V_dnsubne "to northbound;--View" DNINMSV31 "." V_dnsubne "Permissions
GRANT SELECT on "DNINMSV31". " V_dnpackage "to northbound;--View" DNINMSV31 "." V_dnpackage "Permissions
GRANT SELECT on "DNINMSV31". " V_dnport "to northbound;--View" DNINMSV31 "." V_dnport "Permissions

Note: When querying with northbound log in, add DNINMSV31 to the view before the report or view does not exist.
For example:
SELECT * from DNINMSV31.     V_dndevice; --Can execute normally

SELECT * from DNINMSV31.   Tb_device where rownum<5; --The report or view does not exist when executing

3) Login User view

Use northbound users to create synonyms for the other company system to access:

CREATE synonym V_dndevice for DNINMSV31. V_dndevice;

CREATE synonym V_dnsubne for DNINMSV31. V_dnsubne;

CREATE synonym V_dnpackage for DNINMSV31. V_dnpackage;

CREATE synonym V_dnport for DNINMSV31. V_dnport;

Third-party systems can access the views in user DNINMSV31 directly from this synonym. This is also a protection of the system security measures. When a third-party system logs on, it can only see its synonyms, and none of the others will be accessible.

4) Limit the number of third-party user connections

Finally, the number of connections to the northbound user limit, so as to avoid the third party unrestricted connection to the database, causing the database session soared:

Conn/as SYSDBA

Alter system set Resource_limite=true scope=both sid= ' * ';

New profile with an initial limit of 1 for testing.

Create profile Third_user limit Sessions_per_user 1 failed_login_attempts unlimited;

Alter user NORTHBOUND profile Third_user;

Adjust the number of sessions to 30

Alter profile Third_user limit Sessions_per_user 30;

--------------------------------------------------------------------------------------------------

When you do an interface with a third party, you can only query certain view users by following the steps below:

We have a need to build two users in the database, user A is used to create some views, direct access to a table in one of its own databases, and access to tables in other databases through a database link, and another user B can access the views in this user A, and access only the views, with limited access to the number of connections.

This User B is used for access to other systems and therefore requires restrictions on permissions and resource usage.

This requirement is common in many industry applications. If this is an interview question, how do you answer it?

I use the following method to answer this question.

The first step is to create new users A and B.

The user names are ryd_interface_src and Ryd_interface, each of which grants very limited permissions to all two users.

Drop user RYD_INTERFACE_SRC cascade;

Create user ryd_interface_src identified by RYD_INTERFACE_SRC;

Grant Connect,create view to RYD_INTERFACE_SRC;

Drop user ryd_interface cascade;

Create user ryd_interface identified by Ryd_interface;

Grant connect,create synonym to Ryd_interface;

The second step is to log in to the database in a schema, authorizing user A to create a view

Conn Qlzqclient/qlzq+client8

Grant SELECT on Invest_clock to ryd_interface_src with GRANT option;

Grant SELECT on Invest_log to ryd_interface_src with GRANT option;

Here the authorization method adds a With GRANT option, please note.

Step three, log in to database User A, create a view

Conn RYD_INTERFACE_SRC/RYD_INTERFACE_SRC

Create or replace view run_views as

Select ID as doc_id, title,fbsj as Upload_date, "as Branch_code from [email protected]_qlzqweb

where ext1= ' 1 ' and sysdate > Start_time

and Sysdate < Solid_time

Union

Select A.doc_id,a.title,a.upload_date,a.branch_code from [email Protected]_qlzqweb a

Left JOIN [e-mail protected]_qlzqweb b on a.doc_id=b.doc_id

where

A.state =1 and A.is_delete =0

and a.upload_date> sysdate-90

and b.cat_id=4;

Create or replace view invest_clock_views as

SELECT * from Qlzqclient. Invest_clock;

Create or replace view invest_log_views as

SELECT * from Qlzqclient. Invest_log;

Step fourth, in database User A, grant the view query permission to User B

Because in step two, with GRANT option is added, the view query permission here can be granted successfully.

Grant SELECT on Invest_clock_views to Ryd_interface;

Grant SELECT on Invest_log_views to Ryd_interface;

Grant SELECT on Run_views to Ryd_interface;

Fifth, in database User B, check that the view can be queried, and then create synonyms.

Conn Ryd_interface/ryd_interface

Select COUNT (*) from ryd_interface_src.run_views;

Select COUNT (*) from RYD_INTERFACE_SRC. Invest_clock_views;

Select COUNT (*) from RYD_INTERFACE_SRC. Invest_log_views;

Create synonym run_views for ryd_interface_src.run_views;

Create synonym Invest_clock_views for ryd_interface_src. Invest_clock_views;

Create synonym Invest_log_views for ryd_interface_src. Invest_log_views;

Third-party systems can access the view in user a directly through this synonym. This is also a protection of the system security measures. When a third-party system logs on, it can only see its synonyms, and none of the others will be accessible.

Sixth, restricting the use of resources

Because User B is used for third-party systems, we have no control over the quality of third-party applications, and in order to prevent the database's overall service from exploding as the number of database sessions spikes when the application has an abnormally connected connection, our database for that user

Number of words to make a limit.

The Sessions_per_user function of profile is used here to limit the number of individual sessions.

Conn/as SYSDBA

Alter system set Resource_limite=true scope=both sid= ' * ';

New profile with an initial limit of 1 for testing.

Create profile Third_user limit Sessions_per_user 1 failed_login_attempts unlimited;

Alter user Ryd_interface profile Third_user;

Adjust the number of sessions to 30

Alter profile Third_user limit Sessions_per_user 30;

By doing this, we can achieve this requirement.

--------------------------------------------------------------------------------------------------

GRANT Debug any procedure, debug connect session to HNCKB;
Grant SELECT on Sys.dba_pending_transactions to HNCKB;
Grant CREATE view,create job,create synonym to hnckb;
Grant Connect,resource to hnckb;
1. Standard roles
CONNECT
RESOURCE

2. System permissions
CREATE VIEW
CREATE DATABASE LINK
CREATE JOB
CREATE synonym
UNLIMITED tablespace
Change quotas
Command: ALTER USER name QUOTA 0 on table space name
ALTER USER name QUOTA (value) k| m| UNLIMITED on table space name;
How to use:
A. Control user Data growth
B, when the user has a certain amount of data, and the administrator does not want him to add new data.
C, when the user quota is set to zero, the user cannot create new data, but the original data can still be accessed.

3. Object permissions
Dba_pending_transactions (SELECT)-XA transaction support

Grant SELECT on Sys.dba_pending_transactions to user;
4. Proposed Open
Debug CONNECT session-Debugging stored Procedures
For example:
GRANT Debug any procedure, debug connect session to HR;

Oracle creates table spaces, creates users, grants, access to authorized objects, and view permissions
1. Create a temporary table space
The Oracle temp table space is used primarily for querying and storing some buffer data. The primary reason for the temporary tablespace consumption is that the intermediate junction of the query needs to be

To sort the results.
The primary role of temporal tablespace:
index Create or rebuild
Order by or GROUP by
Distinct operation
Union or intersect or minus
Sort-merge joins

CREATE temporary tablespace "Test_temp"
Tempfile ' D:\ORACLE\PRODUCT\10.2.0\ORADATA\GIS\TEST_DATA.ora '
SIZE 20M
Autoextend on
NEXT 32M MAXSIZE 2048M
EXTENT MANAGEMENT LOCAL;

2. Create a user table space
CREATE tablespace "Test_data"
LOGGING
DataFile ' D:\ORACLE\PRODUCT\10.2.0\ORADATA\GIS\TEST_DATA.ora '
SIZE 20M
Autoextend on
NEXT 32M MAXSIZE 2048M
EXTENT MANAGEMENT LOCAL;

3. Create a user and set its tablespace
Username:usertest
Password:userpwd

CREATE USER Usertest identified by userpwd
DEFAULT tablespace Test_data
Temporary tablespace test_temp;

4. Authorization to the user
GRANT
Create SESSION, create any TABLE, create any VIEW, create any INDEX, create any PROCEDURE,
Alter any TABLE, alter any PROCEDURE,
Drop any TABLE, drop no VIEW, drop any INDEX, drop any PROCEDURE,
SELECT any table, INSERT any table, UPDATE all table, DELETE any table
to username;

First authorize users to testuser two basic role permissions

Connect Role: – is the typical right to grant the end user the most basic
Create session– session

Resource role: – is granted to developers
Create cluster– Build Cluster
Create procedure– Setup process
Create sequence– Build Sequence
Create table– Build Table
Create trigger– CREATE Trigger
Create type– Build Type
Create operator– created by operator
Create indextype– Creating an index type
Create table– creating a table

The SQL statement that authorizes the role to the user:
GRANT role to username;

5. View User Permissions

View All Users
SELECT * from Dba_users;
SELECT * from All_users;
SELECT * from User_users;

View User system permissions
SELECT * from Dba_sys_privs;
SELECT * from User_sys_privs;

View user object or role permissions
SELECT * from Dba_tab_privs;
SELECT * from All_tab_privs;
SELECT * from User_tab_privs;

View all roles
SELECT * from Dba_roles;

To view the roles owned by a user or role
SELECT * from Dba_role_privs;
SELECT * from User_role_privs;

Encountered no privileges on tablespace ' tablespace '
Alter user UserQuota 10m[unlimited] on tablespace;

Original: http://blog.sina.com.cn/s/blog_4f925fc30102dtdb.html

Oracle creates a user and grants the user permission to query the specified table or view