Shulou(Shulou.com)06/01 Report--

When it comes to users and permissions, it feels like there's nothing to share or communicate. In fact, it is not appropriate to describe it as if you are a customer to buy a house, you go to see a 200W house and a 1000W house, which is absolutely different, and the service level enjoys different treatment.

Users and permissions in the database are like money and houses. The higher your user level, the higher the corresponding permissions. Technology is nothing more than a humanization evolution from this world. If you pull more, then the most basic information about the user password in my database, the characteristics and operation permissions of the user using the specific user account teammate. A role is then a set of permissions that can be assigned to a user (giving you a key), assigning that particular set of permissions to the user.

What do you mean user?

It is divided into two categories: 1. Users created by the system itself when creating the library (system users) 2. Another type of users created by the system users is called ordinary users.

Format:

select username,account_status,default_tablespace,temprorary_tablespace from user_users;

Explanation:

Query the tablespaces to which all users and states belong in the system for the current year.

Format:

create user user_name identified by password default tablespace users;

Explanation:

Oracle must learn to use English to read, with English translation of the last command is, create user user_name confirm password default table space is users; in fact, a word is, no pressure (black is the keyword).

dba_user This table can also view detailed information, a library table, a user!

Users and Schema

A schema is a collection of objects owned by the user. Objects have tables, indexes, triggers, and so on. Each user will have its own schema information, except for new users whose collections are empty. Schema is empty, Schema must depend on user presence.

Format:

select sys_count('userenv','current_user')current_user,sys_countext('userevn','current_schema')current_schema from dual;

Explanation:

Through the query statement of this article, we find that we can see the collection after logging in with System. What is the meaning of this collection? That is to say, Schema_object name combination can restrict the unique objects in the database.

System users sys and system

System users sys and system are the two most commonly used user systems, so sys is the user with the highest privileges in the ORACLE database, and the role is sysdba (database administrator). System is second only to sys users, and the role is SYSOPER(database operator). For example, sys can create libraries, so system cannot be combined without this permission (default does not).

Format:

show parameter db_name;

Explanation:

The code show parameter displays information describing the db_name parameter.

Format:

alter user system account unlock;

Explanation:

Unlock users, such as scott user default is unable to log in status, after unlocking dba_users query status is OPEN open

Format:

alter user scott identified by password;

Explanation:

Change scott's password and log in.

Permission is like your level, like law is your highest level in the social system, Privilege object users, as long as it is a database operation object and executable multi-action will be limited. Then there are two kinds of permissions:1, system permissions 2, object permissions

System Authority:

System permissions are for database specific operations, such as creating data tables, and object permissions are not used to access and operate existing objects.

Format:

select distinct privilege from dba_sys_privs;

Explanation:

See what system permissions are (main components)

Assign permissions:

Format:

grant create session to tiger; --Grant permission to create session (connection)

grant create table to tiger; --Grant create table permission

alter user tiger quota 10M on users quota 2M on temp;--Because there is no permission to allocate how much table space users can use, use system to allocate space size

select * from dba_sys_privs where lower(grantee) = 'tiger';--Query the system permissions currently held by the tigeruser

revoke create table from tiger; --revoke create table from tiger

Object permissions:

Format:

select grantee,table_name,privilege from dba_tab_privs where lower(grantee)='tiger';

Explanation:

View object permissions that grantee has.

Format:

grant select on dual to tiger;--Assign system query dual to tiger

grant insert on table to tiger; --Grant permission to create table(specify table name)

grant update(table_name) to tiger; --grant updates only the name column in this table;

grant all on table to tiger;--Grant all object permissions

revoke all on table to tiger;--revoke all object permissions on tiger

Character:

Although we can use system and object permissions to control, we will find that the task volume can become very complicated and troublesome. In this context, Oralce proposed the concept of roles, which we should have introduced as a collection of system permissions or object permissions. Oracle allows you to create roles, and then assign role information to users, thus indirectly adding permission information to users. The important thing is that roles can be reused, which greatly reduces the amount of duplicate work and code to a certain extent.

Using inherited characteristics to derive complex roles from simple roles will undoubtedly improve the work and efficiency of authority allocation.

to create a character together.

Format:

create role role_employee;

Explanation:

create role Keyword for creating a role

Format:

grant all on table to role_employee;--Grant all permissions to role

grant role_employee to tiger;--Gives all the permissions of the role to the tiger user. Note that you must log in to the database again to take effect.

Inherited roles:

A character can inherit permission information from another character and reduce action commands of its own grant.

Format:

grant system to role_employee;

Explanation:

Although it doesn't feel meaningful to assign system permissions to role, some rigorous databases will attach great importance to user safety. Each role will have classification. When the code volume is more complex, the role's use will of course be very important.

Format:

alter user cat default role none;

Explanation:

Forbidden cat user default role All forbidden, query session_roles is empty

Format:

set role role_hr;

Explanation:

Start the role role_hr, if there are multiple roles can be separated by commas

To finally look back at the article, is the user role permissions three changes and interdependence, database security only depends on the user's control is not feasible, then a relatively secure logic of the database without user role permissions and other strategies must not work, clear thinking All problems will be solved!

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.