1. The default user of the system
Java code
sys;//System administrator, has the highest authority
system;//Local administrator, second highest authority
scott;//For ordinary users, the password defaults to tiger and is not unlocked by default.
2. Login
Java code
sqlplus conn / as sysdba;//Log in to sys account
sqlplus sys as sysdba;//Same as above
sqlplus scott/tiger;//Log in to ordinary user scott
3. Manage users
Java code
create user zhangsan;//Under the administrator account, create user zhangsan
alert user scott identified by tiger;//Change password
4. Grant permissions
1. The default ordinary user Scott is not unlocked by default and cannot be used. The newly created user does not have any permissions and must be granted permissions.
Java code
/*Administrator authorization*/
grant create session to zhangsan;//Grant zhangsan user the permission to create session, that is, login permission
grant unlimited session to zhangsan;//Grant zhangsan user permission to use table space
grant create table to zhangsan;//Grant permission to create table
grant drop table to zhangsan;//Grant permission to delete the table
grant insert table to zhangsan;//Permission to insert table
grant update table to zhangsan;//Permission to modify the table
grant all to public;//This is more important, grant all permissions (all) to all users (public)
2. Oralce has strict permission management. Ordinary users cannot access each other by default and need to authorize each other.
Java code
/*oralce is strict about permission management, and ordinary users cannot access each other by default*/
grant select on tablename to zhangsan;//Grant user zhangsan permission to view the specified table
grant drop on tablename to zhangsan;//Grant permission to delete the table
grant insert on tablename to zhangsan;//Grant permission to insert
grant update on tablename to zhangsan;//Grant permission to modify the table
grant insert(id) on tablename to zhangsan;
grant update(id) on tablename to zhangsan;//Grant insert and modify permissions to specific fields of the specified table. Note that it can only be insert and update.
grant alert all table to zhangsan;//Grant zhangsan user the permission to alert any table
5. Revoking permissions
The basic syntax of Java code is the same as grant, and the keyword is revoke
6. View permissions
Java code
select * from user_sys_privs;//View all permissions of the current user
select * from user_tab_privs;//View the user's permissions on the table
7. Table of users who operate the table
Java code
/*You need to add the user name before the table name, as follows*/
select * from zhangsan.tablename
8. Permission transfer means that user A grants permission to B, and B can grant the operation permission to C. The command is as follows:
Java code
grant alert table on tablename to zhangsan with admin option; //Keyword with admin option
grant alert table on tablename to zhangsan with grant option;//The effect of keyword with grant option is similar to admin
9. Role A role is a collection of permissions, which can grant a role to a user.
Java code
create role myrole;//Create role
grant create session to myrole;//Grant the permission to create session to myrole
grant myrole to zhangsan;//Grant the role of myrole to zhangsan user
drop role myrole; delete role
/*But some permissions cannot be granted to roles, such as unlimited tablespace and any keyword*/
This article comes from the CSDN blog. Please indicate the source when reprinting: http://blog.csdn.net/JustForFly/archive/2009/10/16/4681707.aspx
Object permissions:
1. Object permission grant syntax:
GRANT object_privilege ON object_name TO username [ WITH GRANT OPTION ];
Note: After using the WITH GRANT OPTION statement, the user can grant the same permissions to other users, which is the same as the system permissions.
2. Object permission revocation syntax:
REVOKE object_privilege ON object_name FROM username;
Note: When the permissions of user A are deleted, the permissions granted to B by A will automatically disappear, which is the opposite of the system permissions.
3. View specific object permissions:
select * from dba_tab_privs where grantee= 'WANGXIAOQI' ;
select * fromTABLE_PRIVILEGESwhere GRANTEE='WANGXIAOQI';
Note 1: Both can view object permissions, but the display forms are different.
Note 2: The table name is TABLE_PRIV, but it is not just the table, but all object information, including function, procedure, package, etc.
4. There are relatively few types of object privileges, and they vary depending on the object type.
You can use all to replace all permission types of this type of object when assigning values, such as:
grant select , update , delete , insert on table_name to user_name;
grant all on table_name to user_name;
Note: For TABLE, all includes: ALTER, DELETE, INDEX, INSERT, SELECT, UPDATE, REFERENCES, ON COMMIT REFRESH, QUERY REWRITE, DEBUG, FLASHBACK
Role management:
1. Simplify the authorization operation through roles. Each role contains several system permissions. Roles include system predefined and customized roles.
select * from dba_roles; --Query all current ROLE lists, including custom ones
select * from dba_role_privs; --Query the ROLE permissions of a user
select * from ROLE_SYS_PRIVS; --Query the current user's ROLE and the system permissions it contains
2. System predefined roles:
CONNECT:
CREATE VIEW
CREATE TABLE
ALTER SESSION
CREATE CLUSTER
CREATE SESSION
CREATE SYNONYM
CREATE SEQUENCE
CREATE DATABASE LINK
RESOURCE:
CREATE TYPE
CREATE TABLE
CREATE CLUSTER
CREATE TRIGGER
CREATE OPERATOR
CREATE SEQUENCE
CREATEINDEXTYPE
CREATE PROCEDURE
In addition, it includes more important ROLEs such as: DBA, EXP_FULL_DATABASE, IMP_FULL_DATABASE, etc.
3. Customized roles:
Create ROLE:
CREATE ROLE role_name
[ NOT IDENTIFIED | IDENTIFIED BY password]
Note: IDENTIFIED indicates whether a password is required when modifying the ROLE <modification, excluding authorization and cancellation of permissions>
After creating the role, use grant and revoke to manually set the permissions corresponding to the role.
Then use grant and revoke to assign the role to the user
Note: role can be assigned to role
4. Enable and disable ROLE:
SET ROLE [role [identified by password] |,role [identified by password]...]
| ALL [EXCEPT role[,role]...]
| NONE ];
Note: ALL means enabling all roles of the user, NONE means disabling all roles.
example:
Disable all roles: setrolenone;
Enable all roles: setroleall; --role cannot have a password
Enable a role: setrole role_test identified by test; --if there is a password
Disable a role: setroleallexcept role_test;
Note: The setrole command is overwriting, that is, you cannot enable one first and then enable another. All must be started in one command;
5. Set roles when modifying users:
ALTER USER username
[default role [role_name[,role_name,...]]
| all [except role_name[,role_name,...]]
| none ];
This article comes from the CSDN blog. Please indicate the source when reprinting: http://blog.csdn.net/cosio/archive/2009/08/18/4459756.aspx