Oracle (Control user rights)

Permissions allow users to access objects or execute programs that belong to other users.

ORACLE System provides permissions: Object-level, system -level

To view the data dictionary for permissions:

Dictionary name meaning

Role_sys_privs System privileges granted to roles

Role_tab_privs Table privileges granted to roles

User_role_privs Roles accessible by the user

User_tab_privs_made Object privileges Granted on the user ' s objects

USER_TAB_PRIVS_RECD Object privileges granted to the user

User_col_privs_made Object privileges Granted on the columns of the user ' s objects

USER_COL_PRIVS_RECD Object privileges granted to the user on specific columns

1. System permissions (System permissions are for users):

the DBA has the highest system privileges:

1, you can create a user

syntax:create user username identified by password;

For example:create user briup identified by BRIUP;

When the user is created successfully, the user has no permissions or even log on to the database.

2. granting permissions:

The basic permissions a user should have include:

CREATE SESSION

CREATE TABLE

CREATE SEQUENCE

CREATE VIEW

CREATE PROCEDURE

If there are multiple users, they all have the same permissions (Create session,create table,create sequence), assigning permissions is too cumbersome to assign to each user, so Oracle presents the concept of a role, You can assign permissions to a role, and then assign a role to the user.

For example, when we were doing the operation, we wrote:

Grant Resource,connect to Briup;

at this point resource,connect is the role.

Query resource,connect What permissions are available:

Select Privilege,role

From Role_sys_privs

where role = ' CONNECT ' or role = ' RESOURCE ';

Grammar:

Grant XXXX to user_name;

For example:

Grant CREATE view to BRIUP;

3. Reclaim Permissions

syntax:revoke xxx from user_name;

For example:

Revoke CREATE view from BRIUP;

4. Change Password:

syntax:alter user XXX identified by XXXX;

For example:

Alert user briup identified by BRIUP;

5. Delete the User:

Syntax:drop user username [cascade];

Note:cascade: When the user has a table, you must use the cascade Cascade Delete.

For example: drop user test cascade;

2. Object Permissions ( for objects, like table objects, etc. ):

Object permissions:Select, UPDATE, INSERT, ALTER, INDEX, delete, all//all include all permissions

object that the owner has all the permissions.

1. give the user permission to manipulate objects:

GRANT Object_priv [(columns)]

On object

to {user|role| public}

[with GRANT OPTION]; allow users assigned to permissions to continue assigning permissions to other users

For example:

Grant SELECT on S_emp to jd1613;

gives jd1613 users the right to query on the s_emp table.

Grant Update (ID) on s_emp to jd1613;

gives jd1613 permission to update the ID column on the s_emp table .

2. Reclaim permissions: Same as System permissions.

syntax:revoke XXX on obj from user;

Note: Permissions granted with grant option are also recycled.

For example:

Revoke SELECT, update on s_emp from jd1613;

3. Create synonyms: equivalent to alias an object

syntax:create[public] synonym sy_name for obje_name;

Note: only DBAs have the right to create synonyms for public

For example:

Create synonym emp for s_emp;

4. Delete synonyms:

syntax : drop synonym syn_name;

For example:

drop synonym emp;

5. Export the database

Exp,imp does not belong to the sqlplus command, so it is not executed at the sqlplus terminal.

system Terminal:exp userid=briup/briup full=y file=briup.dmp

Import:imp userid=briup/briup full=y file=briup.dmp;

Oracle (Control user rights)