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?
Oracle Database

Avatar of undefined
Last Comment
ajs_tech2516

8/22/2022 - Mon
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.
Peter Chan

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



slightwv (䄆 Netminder)

@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.
Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER
ajs_tech2516

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
slightwv (䄆 Netminder)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
ajs_tech2516

thank you so much
that answers my question!!