Link to home
Start Free TrialLog in
Avatar of ajs_tech2516
ajs_tech2516Flag for United States of America

asked on

grant privileges in oracle

Is it possible that if I grant a privilege to a schema in oracle database, it can cause to revoke some other privilege from that schema or other schema?
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Certain privileges can be granted with 'WITH ADMIN' option:
https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/GRANT.html#GUID-20B4E2C0-A7F8-4BC8-A5E8-BE61BDC41AC3
Specify WITH ADMIN OPTION to enable the grantee to:

    Grant the privilege or role to another user or role, unless the role is a GLOBAL role

    Revoke the privilege or role from another user or role

    Alter the privilege or role to change the authorization needed to access it

    Drop the privilege or role

    Grant the role to a program unit in the grantee's schema.

    Revoke the role from a program unit in the grantee's schema.



You specifically mention privilege so I'm not sure if you might also be asking about roles:  Grating DBA will definitely give them those permissions.
You should grant rights/permission to table, view, or user account. You can create also "roles" in Oracle.

https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_6012.htm



@Peter,
The question isn't about how to grant privileges not how to create a role as the link you provided describes.

Also, that is a 10g version of the documentation that is not even supported anymore.  If you are going to post random links, at least make sure it is for a supported version.
Avatar of ajs_tech2516

ASKER

thank you both
In my case,  the schemas were already created by DBA and some privileges and roles were granted by DBA.Now, I have to make it work for a front end application built by vendor. Vendor has advised to grant these privileges and roles.

ALTER ANY INDEX
CREATE ANY INDEX
CREATE ANY TRIGGER
CREATE ANY VIEW
DROP ANY INDEX
DROP ANY VIEW
SELECT ANY TABLE
CREATE TYPE
SELECT_CATALOG_ROLE
SELECT ON SYS.DBA_ROLES
ADMINISTER SATABASE TRIGGER
ALTER SESSION
ANALYZE ANY
CREATE DATABASE LINK
CREATE INDEXTYPE
CREATE LIBRARY
CREATE MATERIALIZED VIEW
CREATE OPERATOR
CREATE PUBLIC SYNONYM

EXECUTE ON SYS.DBMS_CRYPTO

CREATE SESSION
CREATE SEQUENCE
CREATE TABLE
CREATE TRIGGER
CREATE VIEW
CREATE PROCEDURE
CREATE SEQUENCE
UNLIMITED TABLESPACE

So, I want to make sure if I grant those, it would not mess up the database.

can you please guide?
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
thank you so much
that answers my question!!