Oracle Administrative Rights (iii) Administration of Oracle Object permissions

1, Grant object permissions

is the right to access other user scenario objects.

GRANT Object_priv|all [(columns)]

On object

to {user|role| public}

[with GRANT OPTION];

All: Permissions for all objects

Public: Grant to all users

With GRANT OPTION: Allows the user to authorize another user again.

--Grant object permissions for columns

11:24:05 sql> Grant Update (SAL) on Scott.emp to Tom;

Grant succeeded.

11:29:39 sql> Conn Tom/tom

Connected.

11:29:51 sql> Update scott.emp set comm=100 where empno=7788; --No permissions modified for this column

Update scott.emp set comm=100 where empno=7788

*

ERROR at line 1:

Ora-01031:insufficient Privileges

sql> Update scott.emp set sal=10000 where empno=7788;

1 row updated.

sql> rollback;

Rollback complete.

Sql> select Grantee,owner,table_name,column_name,privilege from User_col_privs;

GRANTEE OWNER TABLE_NAME COLUMN_NAME privilege

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

TOM SCOTT EMP SAL UPDATE

2, Display object permissions

1) Display object permissions

04:39:24 sql> Select grantor, owner | | TABLE_NAME Object,privilege

04:39:34 2 from Dba_tab_privs

04:39:41 3 where grantee= ' HR ';

Grantor OBJECT Privilege

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

SYS sys. Dbms_stats EXECUTE

Scott Scott. DEPT UPDATE

Scott Scott. DEPT SELECT

Scott Scott. DEPT DELETE

2 Display column Permissions

04:42:15 sql> Col owner for A10

04:42:58 sql> Col table_column for A15

04:43:08 sql> Col Privileg for A10

04:43:14 sql> Select owner, table_name| | column_name Table_column, privilege from Dba_col_privs

04:44:00 2 where grantee= ' HR ';

OWNER table_column Privilege

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

SCOTT EMP. SAL UPDATE

3 Display the column permissions granted by the user

04:47:57 sql> L

1 Select grantee,privilege,table_name| | '. ' | | column_name

2 Tab_column

3* from User_col_privs_made;

4 Display the column permissions that the user has

Select Privilege,table_name| | '. ' | | column_name Tab_column,

04:49:38 2 Grantor

04:49:43 3 from ALL_COL_PRIVS_RECD

04:49:53 4 where grantee= ' HR ';

No rows selected

5 Display the object permissions granted by the user

04:42:47 sql> Col table_name for a10for A10

04:51:19 sql> Select grantee, Privilege, table_name

04:51:34 2 from User_tab_privs_made;

GRANTEE Privilege table_name

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

HR DELETE DEPT

HR SELECT DEPT

HR UPDATE DEPT

OE SELECT EMP

6 Display the object permissions that the user has

04:52:45 sql> Select Privilege,table_name,grantor

04:52:58 2 from ALL_TAB_PRIVS_RECD

04:53:10 3 where grantee= ' HR ';

Privilege table_name GRANTOR

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

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/