Parse Grant Connect, resource to user statement

A new user was created with the grant Connect,resource to user, but the view was set to fail with insufficient permissions to assign the user permission to create the view. Why does the user have no way to create the view directly in their own space, but also have to increase the view permission? ”

When you create a new user, you often use a grant connect,resource to user, so that you can log in to the database with this user, what is the actual function of this statement?

1, first, grant XXX to User;,grant is the role of authorization, where XXX can be a role roles, can also be permissions, such as Grant role to user, or grant insert on the table to user;.

Standard SQL statements given in MOS:

To create a role:

Create role <role name> [identified by <password>/using <package>/externally/globally];

To assign a role permission:

Grant <object/system privilege> to <role name>;

To reclaim permissions from a role:

Revoke <privilege> from <role name>;

To assign a role to another role or user:

Grant <role> to <username or role>;

2. Second, connect and resource are the built-in roles of the two systems, and the DBA is tied to the relationship.

Referring to some of the posts, permissions can be divided into two categories:

System permissions: The system specifies the user's permission to use the database. (System permissions are for users).

Entity permissions: A permission user's access to other users ' tables or views. (For a table or view).

Next look at the system permissions,

DBA: Has full privileges, is the highest system privilege, and only the DBA can create the database structure.

RESOURCE: A user with RESOURCE permission can only create entities and cannot create a database structure.

Connect: A user with connect permission can only log on to Oracle, not create an entity, and cannot create a database structure.

For normal users: Grant Connect, resource permissions.

For DBA administration users: Grant Connect,resource, dba authority.

And the system permissions can only be granted by the DBA User: sys, system (which can only be the first two users). An ordinary user can have the same user rights as the system through authorization.

However, the same permissions as the SYS user can never be reached, and the permissions of the system user may be recycled.

In addition, for the issue of cascading authorizations with the ADMIN option,

1) If you use with ADMIN option to grant system permissions to a user, then for all users who are granted the same permissions by this user, the user's

System permissions do not cascade the same permissions that are removed by these users.

2) There is no cascade of system permissions, that is, a grants B permission, b grants C permissions, if a reclaims the permissions of B, the permissions of C are not affected; System permissions can be recycled across users, i.e. a can

Directly retract the permissions of the C user.

3, prove why the role of resource and connect cannot create views.

Sql> Select Role, COUNT (*) from ROLE_SYS_PRIVS GROUP by role;

ROLE COUNT (*)

------------------------------ ----------

Exp_full_database 8

DBA 160

Scheduler_admin 6

RESOURCE 8

Imp_full_database 68

CONNECT 1

You can see the number of permissions that resource and connect have.

Here's a look at each of these permissions:

Sql> Select Grantee,privilege from Dba_sys_privs where grantee= ' RESOURCE ' order by privilege;

GRANTEE PRIVILEGE

------------------------------ ----------------------------------------
RESOURCE CREATE CLUSTER
RESOURCE CREATE Indextype
RESOURCE CREATE OPERATOR
RESOURCE CREATE PROCEDURE
RESOURCE CREATE SEQUENCE
RESOURCE CREATE TABLE
RESOURCE CREATE TRIGGER
RESOURCE CREATE TYPE
8 rows selected.

Sql> Select Grantee,privilege from Dba_sys_privs where grantee= ' CONNECT ' order by privilege;

GRANTEE PRIVILEGE
------------------------------ ----------------------------------------
CONNECT CREATE SESSION

The result is self-explanatory, the CREATE VIEW permission is not in both roles, so additional grant create view to user is required to enable this user to create the view. Practice is the best teacher!!

Parse Grant Connect, resource to user statement