Shulou(Shulou.com)05/31 Report--

This article mainly shows you how Oracle realizes user permission control. The content is simple and easy to understand. It is clearly organized. I hope it can help you solve your doubts. Let Xiaobian lead you to study and learn this article "How Oracle realizes user permission control".

1. User

Users in Oracle fall into two broad categories

1) When Oracle database server is created, users automatically created by the system are called system users, such as sys.

2) Users created by system users are called ordinary users, such as scott,hr, c##tiger,zhaojun,...

Log in with sys to query the name and status of existing users in the current Oracle database server.

username indicates login name

expired&locked indicates that the account is expired and locked

open indicates that the account is now available

sqlplus / as sysdba; col username for a30; col account_status for a30; set pagesize 100; select username,account_status from dba_users;

Query which users exist in Oracle

select * from all_users;

2. Create and delete ordinary users

You can create a new normal user in Oracle. The command to create a normal user is create user. At the same time as creating a normal user, a specific table space should be allocated to it, usually called users.

Log in with sys to query what storage space is available in Oracle. All ordinary users default to users storage space.

select * from v$tablespace;

Log in with sys, create ordinary user c##tiger, password abc, default to users storage space, that is, a DBF binary file on the corresponding hard disk

sqlplus / as sysdba; create user c##tiger identified by abc default tablespace users;

Log in with sys, allocate users space for c##tiger unlimited use, that is, DBF files in the database can be increased indefinitely, one DBF file is not enough, a second DBF file will be created

sqlplus / as sysdba; alter user c##tiger quota unlimited on users;

Log in with c##tiger, can you enter orcl database?

sqlplus c##tiger/abc

I can't get into the ORCL database.

Log in with sys and delete normal user c##tiger

sqlplus / as sysdba; drop user c##tiger cascade;

3. Understand the system users

sys is an important system user in Oracle, sys is the highest privileged user in Oracle, and its role is SYSDBA (System Administrator)

sqlplus / as sysdba

4. Authority

The ultimate effect of permissions is on the user. That is to say, all the user's operation objects and executable actions in the database are restricted.

There are two main types of permissions in Oracle:

1) System permissions

2) Object permissions

4.1, system permissions

Permits for specific operations in the database, such as enabling c##tiger to log in to the orcl database, creating tables in the orcl database

Log in with sys to obtain information related to system permissions, for example: select any table indicates select permissions for all tables

sqlplus / as sysdba; select distinct privilege from dba_sys_privs;

Log in with sys, assign the create session permission to the database for c##tiger, that is, allow the user to log in.

sqlplus / as sysdba; grant create session to c##tiger;

Log in with c##tiger, can you enter orcl database?

sqlplus c##tiger/abc

Access to the ORCL database

Log in with c##tiger and create a tiger table. Can you create it?

sqlplus c##tiger/abc create table tiger( name varchar2(20) );

c##tiger does not have permission to create tables at this time

Log in with sys, assign create table permission to c##tiger, that is, allow table creation

sqlplus / as sysdba; grant create table to c##tiger;

Log in with c##tiger and create a tiger table. Can you create it?

sqlplus c##tiger/abc create table tiger( name varchar2(20) );

You can create the c##tiger table

Log in with sys and query the system permissions owned by c##tiger

sqlplus / as sysdba;

select grantee,privilege from dba_sys_privs where lower(grantee) = 'c##tiger';

grant stands for a normal user name

privilege name

Log in with sys and revoke the create table permission of c##tiger

sqlplus / as sysdba; revoke create table from c##tiger;

4.2 object permissions

User's operation permissions on existing objects, including:

1) select can be used for tables, views and sequences

insert Insert a new record into a table or view

3) Update the data in the update table

4) delete Delete data in the table

5) execute function, execution of procedure

6) index Create an index for the table

7) references Create external references for tables

8) alter modifies attributes of a table or sequence

Log in with sys and query the object permissions owned by c##tiger

sqlplus / as sysdba; col grantee for a10; col table_name for a10; col privilege for a20; select grantee,table_name,privilege from dba_tab_privs where lower(grantee) = 'c##tiger';

Log in with sys and assign all permissions to the tiger table for c##tiger, i.e. add, delete, modify and query

sqlplus / as sysdba; grant all on c##tiger.tiger to c##tiger;

Note: c##tiger indicates the space name

tiger indicates the name of the table under this space

C##TIGER TIGER FLASHBACK

C##TIGER TIGER DEBUG

C##TIGER TIGER QUERY REWRITE

C##TIGER TIGER ON COMMIT REFRESH

C##TIGER TIGER REFERENCES

C##TIGER TIGER UPDATE

C##TIGER TIGER SELECT

C##TIGER TIGER INSERT

C##TIGER TIGER INDEX

C##TIGER TIGER DELETE

C##TIGER TIGER ALTER

Log in with c##tiger to add, delete and query the tiger table.

sqlplus c##tiger/abc; insert into tiger(name) values ('AA '); update tiger set name ='BB'; delete from tiger where rownum = 1; select * from tiger; above is "Oracle how to implement user permission control" All the contents of this article, thank you for reading! I believe that everyone has a certain understanding, hope to share the content to help everyone, if you still want to learn more knowledge, welcome to pay attention to the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.