Oracle – the difference between the ADMIN option and the GRANT option in Oracle

oracle

I am studying SQL syntax for Oracle and have come across both an ADMIN option and a GRANT option for giving users the ability to grant privileges. For example, if I want to give the SELECT privilege to user SCOTT on HR's EMPLOYEES table and give the ability to grant that access to someone else, I might do

GRANT SELECT ON HR.EMPLOYEES TO SCOTT WITH ADMIN OPTION

GRANT SELECT ON HR.EMPLOYEES TO SCOTT WITH GRANT OPTION.

It seems either is supposed to work. Does anybody know what the difference is?

Best Answer

Read This

Both the “with grant” and “with admin” options serve to relinquish central security control, but they are for different types of privileges.

With Grant option:

Only for object privileges, not system privileges.
Only the person who granted the privilege can revoke the privilege.
Revoked privileges can "cascade", allowing the first grantor to revoke many
subsequent grants.

With Admin option:

Only for system privileges, not object privileges.

Related Solutions

Sql – the difference between Views and Materialized Views in Oracle

Materialized views are disk based and are updated periodically based upon the query definition.

Views are virtual only and run the query definition each time they are accessed.

Sql – How to limit the number of rows returned by an Oracle query after ordering

You can use a subquery for this like

select *
from  
( select * 
  from emp 
  order by sal desc ) 
where ROWNUM <= 5;

Have also a look at the topic On ROWNUM and limiting results at Oracle/AskTom for more information.

Update: To limit the result with both lower and upper bounds things get a bit more bloated with

select * from 
( select a.*, ROWNUM rnum from 
  ( <your_query_goes_here, with order by> ) a 
  where ROWNUM <= :MAX_ROW_TO_FETCH )
where rnum  >= :MIN_ROW_TO_FETCH;

(Copied from specified AskTom-article)

Update 2: Starting with Oracle 12c (12.1) there is a syntax available to limit rows or start at offsets.

SELECT * 
FROM   sometable
ORDER BY name
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

See this answer for more examples. Thanks to Krumia for the hint.

Best Answer

Related Solutions

Sql – the difference between Views and Materialized Views in Oracle

Sql – How to limit the number of rows returned by an Oracle query after ordering

Related Topic