User rights management of Oracle

User rights management of Oracle

1, Oracle user management

Oracle user refers to the user who can add, delete, modify, and query the database. It mainly includes: Super administrator, such as SYS; general administrator, such as SYSTEM; general user, such as SCOTT; big data user. The operations that can be performed on users are as follows:

1. Create user

The syntax for creating users is as follows:

create user username identified by password;

Give an example:

14:36:09 SQL> show user;
14:36:21 SQL> create user wanggx identified by wanggx;

User created.

2. View user information

Switch to sys user and execute the following command:

select username from dba_users;

3. Change user

Change the syntax of the user as follows:

alter user username identified by password;

Give an example:

14:36:44 SQL> alter user wanggx identified by 123456;

User altered.

4. Delete user

If the user owns the object, it cannot be deleted directly. You can use the keyword cascade to delete all objects of a user, and then delete the user. The syntax for deleting users is as follows:

drop user user name [cascade];

Give an example:

SQL> create user zhang identified by 123;
User created.

SQL> grant create session,create table to zhang;
Grant succeeded.

SQL> connect zhang/123;

14:44:25 SQL> create table t1(id int);
Table created.

14:44:38 SQL> connect sys/123456 as sysdba;

14:44:56 SQL> drop user zhang;
drop user zhang
ERROR at line 1:
ORA-01922: CASCADE must be specified to drop 'ZHANG'

14:45:02 SQL> drop user zhang cascade;
User dropped.

2, Oracle user rights management

Permissions are divided into two types: system permissions and object permissions.
System permission: the system specifies the permission for users to use the database, such as creating tables, creating indexes, connecting instances, etc.
Object permissions: allows users to manipulate the permissions of some specific objects, such as reading views, updating certain columns, executing stored procedures, etc.

1. System authority management

You can use three predefined roles to manage system permissions:
DBA: with all permissions, it is the highest permission of the system. Only DBA can create database structure.
Resource: users with resource permission can only create entities, not database structures.
Connect: users with connect permission can only log in to Oracle, not create entities or database structures.
For ordinary users: Grant connect and resource permission.
For DBA management users: Grant connect, resource, dba permission.

(1) Common system permissions

CREATE SESSION: connect to database
CREATE TABLE: create a table in user mode
CREATE ANY TABLE: create a table in any schema
DROP ANY TABLE: drop a table in any schema
CREATE PROCEDURE: create a stored procedure
EXECUTE ANY PROCEDURE: a stored procedure that executes any mode
DROP USER: delete user

(2) Grant system permission command

Command format:

grant permission to user name 1 [, user name 2]... [with admin option];

(1) System permissions can only be granted by DBA users;
(2) The with admin option parameter enables the authorized user to grant his own permission to other users.

Give an example:

14:59:16 SQL> create user admin identified by admin;
User created.

15:00:17 SQL> grant dba,resource,connect to admin with admin option;
Grant succeeded.

(3) Query the permissions of the current user

select * from session_privs;

(4) Withdraw system permission:

revoke permission from username 1 [, username 2];

(1) If WITH ADMIN OPTION is used to grant system permission to a user, then for all users granted the same permission by this user, canceling the system permission of this user does not cascade to cancel the same permission of these users.
(2) The system permission is continuously linked, that is, A grants B permission and B grants C permission. If A withdraws B permission, C permission will not be affected; system permission can be recycled across users, that is, A can directly withdraw C user's permission.

Give an example:

revoke dba,connect,resource from admin;   --Take back admin All rights of the user
Revoke succeeded.

15:26:53 SQL> connect sys/123456 as sysdba;

15:27:46 SQL> grant create session,create table,create any table,drop any table to admin with admin option;
Grant succeeded.

15:29:09 SQL> connect admin/admin

15:29:17 SQL> select * from session_privs;


3, Oracle object rights management

1. Object permission classification

(1) All: all permissions
(2) select, update, insert, delete: add, delete, modify and query permission of data
(3) alter: permission to modify objects
(4) Execute: execute stored procedure permission

2. Object permission management

(1) Grant object permission


Grant permission object to user name 1 [, user name 2]... [with grant option];

Note: if you use WITH GRANT OPTION to grant object permissions to A user, for all users granted the same permissions by the user, canceling the system permissions of the user will cancel the same permissions of these users in A cascade. That is: A grants B permission, B grants C permission, if A withdraws B permission, C permission is also withdrawn.

You can give object permissions to all users using the following command:

grant all on product to public;

Give an example:

16:18:57 SQL> connect scott/tiger;

16:19:08 SQL> grant select,delete,update,insert on emp to wanggx with grant option;
Grant succeeded.

16:19:50 SQL> connect wanggx/123456;

16:20:38 SQL> select * from scott.emp where deptno=10;  --To access emp Table, user name must be added

---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7782 CLARK      MANAGER	      7839 1981-06-09 00:00:00	     2450		     10
      7839 x_KING     PRESIDENT 	   1981-11-17 00:00:00	     5000		     10
      7934 MILLER     CLERK	      7782 1982-01-23 00:00:00	     1300		     10

16:23:10 SQL> select table_name from user_tables where table_name='EMP';
no rows selected

16:23:41 SQL> select table_name from all_tables where table_name='EMP';


Note: at this time, log in as a wanggx user, and you can operate on the EMP table of Scott user. But at this time, you can view the EMP table through all tables instead of scott.emp.

58 original articles published, 48 praised, 10000 visitors+
Private letter follow

Tags: SQL Oracle Database Session

Posted on Fri, 07 Feb 2020 03:43:32 -0500 by nishanthc12