ORACLE Role Authorization

Direct Examples:

1.CREATEuser username identified by password default tablespace table space name;


Grant Alter,delete,update,insert,select on table name to user name;

2. Directly with the above user login Plsql (for example, the user name is User1, password is User1, the authorized table is table1,table1 created with the system account)

Then we find that the authorized table name Table1 is not in the list, with select * from Table1 The table does not exist , because Table1 is created with the system account and is not part of the User1. We need to query data like this:

SELECT * from System. table1--Accessing table Data

Add a system to the front of the table each time. It is inconvenient, we can create a synonym directly.

create public synonym table_name for user.table_name; --Create synonyms, give User.table_name an individual named table_name

Synonyms are literally the meaning of aliases, and the function of a view is similar to a mapping relationship.

Here are the instructions:

First, create

sys;//system Administrator with the highest privileges

system;//Local Administrator, sub-high privilege

scott;//Normal user, password default is tiger, default unlocked

Second, landing

Sqlplus/as sysdba;//Login SYS account

Sqlplus Sys as sysdba;//ibid.

Sqlplus scott/tiger;//Landing Ordinary user Scott

Third, manage users

Create user zhangsan;//under the Administrator account, creating the users Zhangsan

Alert user Scott identified by tiger;//change password

Four, grant permissions

1, the default ordinary user Scott is not unlocked by default, cannot do that use, the new user does not have any permissions, must be granted permissions

　　

Grant Create session to zhangsan;//Grant Zhangsan user permission to create session, that is, login permission

Grant Unlimited tablespace to zhangsan;//grants Zhangsan users permission to use tablespaces

Grant CREATE table to zhangsan;//grants permissions for creating tables

Grante drop table to zhangsan;//grant permission to delete tables

Grant Insert table to zhangsan;//permissions for inserting tables

Grant Update table to zhangsan;//permissions to modify tables

Grant all to public;//this is more important, grant all permissions (all) for all users (public)

2, Oralce on the rights management more rigorous, ordinary users are also the default can not access each other, need to authorize each other

　　

Grant SELECT on TableName to zhangsan;//Grant Zhangsan user permission to view the specified table

Grant drop on TableName to zhangsan;//granting permission to delete table

Grant insert on TableName to zhangsan;//permission to be inserted

Grant update on TableName to zhangsan;//granting permission to modify tables

Grant Insert (ID) on tablename to Zhangsan;

Grant Update (ID) on TableName to zhangsan;//grants insert and Modify permissions to specific fields of the specified table, note that only the INSERT and update

Grant alert all table to zhangsan;//grants Zhangsan user alert permission to any table

V. Revocation of Rights

Basic syntax with GRANT, keyword revoke

Vi. Viewing permissions

SELECT * from user_sys_privs;//View all permissions for the current user

SELECT * from user_tab_privs;//View the user's permissions on the table

Vii. Table of users of the action table

　　

SELECT * FROM Zhangsan.tablename

Viii. Transfer of rights

That is, user a grants permission to B,b to grant the permission of the operation to C again, with the following command:

Grant alert table on TableName to Zhangsan with admin option;//keyword with admin option

Grant alert table on TableName to Zhangsan with Grant option;//keyword with GRANT option effect similar to admin

Nine, the role

A role is a collection of permissions that can grant a role to a user

Create role myrole;//creating roles

Grant create session to myrole;//grants permission to create session Myrole

Grant Myrole to zhangsan;//the role of Zhangsan user Myrole

Drop role myrole; remove roles

Transferred from: http://www.cnblogs.com/shlcn/archive/2011/07/21/2112879.html

Ten, password reset

Sql>alter user Scott identified by password; Password is the password you want to set, preferably starting with a letter

But if SYS and the system password are forgotten, they can be modified.

Here's how:

Open Run, enter

Sqlplus/nolog

Open Oracle's console and enter

Conn/as Sysdba; You are prompted to connect

And then change the password just like the normal user.

Sql>alter user system identified by password; Password is the password you want to set, preferably starting with a letter

Transferred from: http://www.cnblogs.com/zyw-205520/archive/2012/12/23/2829928.html

ORACLE Role Authorization