Oracle Database's user-related access control practices

The following articles mainly describe the actual operations of the Oracle database on user-related access control. The following articles describe the second volume of introduction to Oracle 9i SQL student guide, after writing, you can find that you have provided a comprehensive and relevant introduction. But release it.

1. control user access

It mainly includes the following aspects:

Control database Access

Control access to specific objects in the database

Grant certain permissions to access Oracle Data Dictionary

Create synonym for the database object

Database security can be defined as system security and data security. The former includes access and use of system-level databases, such as username and password, disk space allocated to users, and system operations that users can perform. The latter includes security control over database object access and operations.

1) system Permissions

Mainly include: create user, drop user, drop any table, backup any table, select any table, CREATE ANY TABLE

2) create users

Create user user identified by password;

3) grant permissions

GRANT privilege [, privilege...] TO user [, user | role, PUBLIC...];

For application developers, at least the following permissions are required:

Create session, create table, create sequence, create view, CREATE PROCEDURE

4) change the user Password

Alter user user identified by nowpassword;

5) object permissions

GRANT object_priv | ALL [(columns)] ON object TO {user | role | PUBLIC} [with grant option];

ALL indicates ALL object permissions.

2. role

1) role is a set of related permissions, which can be granted together with users to simplify authorization and revoke permissions. One user can be granted multiple roles, and one role can also be granted to multiple users.

Create role role;

Then, you can use the grant statement to authorize role. PUBLIC: grant the object permission to all users.

2) The operations granted to role are the same as those granted to the user mentioned above.

3. View of authorization information

4. Revoking object permissions

REVOKE {privilege [, privilege...] | ALL} ON object FROM {user [, user...] | role | PUBLIC} [cascade constraints];

The cascade constraints clause revokes all the permissions for the integrity CONSTRAINTS created using the REFERENCES method. After revoke, the corresponding permissions of all users authorized by with grant option are also revoked.

5. database link allows local users to access remote databases)

Database link is a pointer that defines how an Oracle database server interacts with another database server. The link pointer is actually an entity in a data dictionary table. To access the link, you must connect to the local database that contains the data dictionary entity.

The biggest advantage of database link is that it allows users to access the objects of other users in the remote database.

Create an instance:

Create public database link hq.acme.com USING 'sales ';

The USING clause specifies the service name of the remote database. After the database link is created, you can initiate the following access:

Select from fred.emp@HO.ACME.COM;