Create a user in Oracle and Set permissions in oracle

Create a user in Oracle and Set permissions in oracle

(To: http://www.cnblogs.com/yangy608/archive/2011/08/22/2148893.html)

Create user TEST identified by "TEST" -- create a TEST user

Default tablespace USERS

Temporary tablespace TEMP

Profile DEFAULT;

Grant connect, create view, resource to TEST;

Grant unlimited tablespace to TEST;

 

 

-- Administrator authorization

Grant create session to TEST; -- grant the TEST user the permission to create a session, that is, the login permission.

Grant unlimited session to TEST; -- grant the TEST user the permission to use the tablespace.

Grant create table to TEST; -- grant the table creation permission

Grant drop table to TEST; -- grant the table deletion permission

Grant insert table to TEST; -- insert table permission

Grant update table to TEST; -- Modify table Permissions

Grant all to public; -- this is important. grant all permissions to all users (public)

-- Oralce has strict permission management, and common users cannot access each other by default.

Grant select on tablename to TEST; -- grant the TEST user the permission to view the specified table.

Grant drop on tablename to TEST; -- grant the table deletion permission

Grant insert on tablename to TEST; -- grant the insert permission

Grant update on tablename to TEST; -- grant the table modification permission.

Grant insert (id) on tablename to TEST;

Grant update (id) on tablename to TEST; -- grant the insert and modify permissions for specific fields in the specified table. Note that only insert and update are allowed.

 

-- Revoke permissions

The basic syntax is the same as grant. The keyword is revoke.

-- View Permissions

Select * from user_sys_privs; -- View All permissions of the current user

Select * from user_tab_privs; -- view the table permissions of the users used

-- Operate the table's user table

/* Add the user name before the table name, as shown below */

-- Transfer Permissions

That is, user A grants permissions to user B, and user B can grant operation permissions to user C. The command is as follows:

Grant alert table on tablename to TEST with admin option; -- Keyword with admin option

Grant alert table on tablename to TEST with grant option; -- the keyword with grant option has the same effect as admin.

-- Role

A role is a set of permissions. You can assign a role to a user.

Create role myrole; -- create a role

Grant create session to myrole; -- grant the permission to create a session to myrole.

Grant myrole to TEST; -- grant the role of myrole to the TEST user

Drop role myrole; delete a role

/* However, some permissions cannot be granted to the role, such as the unlimited tablespace and any keywords */