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 */