SQL Advanced Section one (controlling user rights)

Create user

Sql> Create user username  2  identified by password;

Give user permissions

Sql> GRANT Create table,create view,create sequence, create session--permissions  2 to  username;

After the user has the CREATE TABLE permission, you also need to give the user table space to create the table

ALTER user username QUOTA space size on what table

Sql> ALTER USER Wang QUOTA unlimited   2 on  users;

Ps:unlimited Unlimited, you can also specify how many K.

Role: When the role is not used, 3 people need 3 permissions, you must give each permission to the user, and use the role, you can assign these three permissions to the role, directly to the role of the user
Create a role


CREATE role roles;

sql> Create role Wang1;

Assigning permissions to Roles

Sql> GRANT Create table,create session, create VIEW  2 to  wang1;

Assigning roles to users
GRANT role to user;

DBAs can create users and modify passwords
User can change password using ALTER USER statement

ALTER user username  identified by new password;

Alter user wangidentfitied by Wang

Object permissions
Different objects have different object permissions
The owner of the object has all permissions
The owner of the object can assign permissions outside the

The DBA owns the Scott object, so the DBA can assign the permissions of the Scott object to the other

Grantobject_priv [(columns)] Permission Onobject     Object to{user|role| Public} user/role/all Users

--with GRANT OPTION Users also have the right to assign permissions, simply said, I share to you, I allow you can also share to others


Assign the query permission for Scott's Employees table to the user Wang

Sql> Grant Select   2 on  scott.employees  3 to  Wang;

Revoke object permissions
Using the REVOKE statement to reclaim permissions
The permissions assigned by using the WITH GRANT OPTION clause are also retracted

REVOKE {privilege [, privilege...]| All}on  objectfrom   {user[, user...]| role| Public}[cascade CONSTRAINTS];

SQL Example

sql> Revoke Select   2 on  scott.employees  3 from  Wang;

SQL Advanced Section one (controlling user rights)