All Questions

Tagged with
Filter by
Sorted by
Tagged with
0
votes
0answers
45 views

Executing 'GRANT SELECT' in a trigger possible?

Trying to grant select privilege on table USER1.AAA to USER2 when table USER1.AAA is created, using system trigger. The trigger below seems to be processed when table AAA is created but it receives ...
0
votes
1answer
39 views

Can a role be granted roles+privileges in oracle all at the same time?

I know that you can grant a role: Other roles System privileges Object privileges But, can you grant to the same role all at once? I mean, for example you grant a role: 2 roles 1 system ...
1
vote
2answers
39 views

Does an Oracle user have all privileges in its schema?

I'm learning Oracle and did some testing. I used the sys user and created a table t in the scott schema and I found t's owner is scott, and scott has full access to t. I want to confirm, does scott ...
1
vote
1answer
316 views

Grant all the privileges to a user over all other user's objects in Oracle

I would like to know how to grant all the privileges to a user over all other user's objects in oracle. Greetings and many thanks
2
votes
2answers
111 views

How can to make a new user see table in existing schema [closed]

I'm new into Oracle so this question might be dumb. I created new user and now I would like him to see table (or even better all tables by one command) in already existing schema (hr schema in this ...
1
vote
1answer
812 views

Error granting SELECT on view that uses ALL_OBJECTS

I'm in the middle of testing Oracle 12c for our application. I have something similar to this view in a particular schema, which we'll call MY_OWNER_SCHEMA for this question: CREATE OR REPLACE VIEW ...
0
votes
2answers
199 views

read/write/execute to pl/sql grants [closed]

For a current task and future references, I am wondering how pl/sql grants (select, insert, update, delete, references, alter, index, all) are categorized from a read/write/execute or read/read-write/...
0
votes
1answer
523 views

How to give read only access to a user to view the tables of different owner in oracle

I have 2 users are present in oracle ex User1 and User2, Under user1 some tables are present ex t1,t2,t3 etc.. . what are the minimum permissions to view the tables of user1 by using user2 ( Just want ...
3
votes
1answer
59 views

Querying an Oracle view don't return records using some clients but it does using others

I was given this view that when I query it using OracleSQLDeveloper it returns nothing, but if I use Toad it does return records! I cannot inspect the definition of the view, and no errors are ...
2
votes
0answers
2k views

ORA-01031: insufficient privileges solution oracle

Create or replace procedure myprocedure is stmt varchar2(1000); stmt2 varchar2(1000); begin stmt := 'create global temporary table temp(id number(10))'; execute immediate stmt; ...
0
votes
1answer
216 views

Oracle SQL: How to give user the permissions to SELECT from a table?

I'm not very familiar with Oracle SQL at all, but using our admin account I ran the following query on our Oracle database server: GRANT SELECT ON Table1 TO OtherUser. I then committed this change (...
1
vote
1answer
1k views

DBMS_CRYPTO Privilege issue

I am trying to grant DBMS_CRYPTO privilege to one of the user in DB and referred all the forums and Oracle help page to do that. I have followed below steps to grant privilege.. SQL> grant execute ...
2
votes
1answer
82 views

Generating SQL with SQL [closed]

I'm trying to generate SQL statements using SQL to create SQL code that will be highlighted and run by an admin to process the statement. My issue is with the code I have created. It's not efficient ...
0
votes
3answers
5k views

ORA-01031: insufficient privileges ALTER PLUGGABLE DATABASE to OPEN

I'm currently working with Oracle database version 12c. I have created a common admin user - "C##ADMIN" with DBA Role. But when trying to execute the following script: ALTER PLUGGABLE DATABASE ...
0
votes
1answer
499 views

Oracle dbms_redefinition fails with ora-01749, why?

I ran this as a part of redefining a table DECLARE l_num_ers PLS_INTEGER; BEGIN DBMS_REDEFINITION.copy_table_dependents( uname => 'myuser', orig_table => 'mytable'...
2
votes
1answer
97 views

How to audit table permissions as a non-DBA

I'm not a DBA, but I do administer a schema called SCHEMA_1. I'm in charge of assigning privileges to the tables that I create in my schema. Since there aren't any sensitive or permission-unique ...
0
votes
1answer
584 views

Is `GRANT CREATE SESSION to “MYROLE”` working in Oracle 12 database?

In a Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production I'm trying to assign the CREATE SESSION privilege to a custom role MYROLE and then assign MYROLE role to MYUSER user. ...
0
votes
3answers
3k views

DBA privileges information

My colleague having DBA ROLE has created a user for me with the following role. CONNECT,DBA,RESOURCE from his own user. Then I login and check the detail by using the below query. select * from ...
1
vote
1answer
53 views

How to confirm that object owners can grant and revoke privileges on objects he owns?

I'm trying to bring my systems in compliance with a certain set of standards. One of them in confirming that the Oracle default behavior of allowing users to grant and revoke privileges to objects ...
1
vote
1answer
4k views

oracle 11g Grant on dbms_lock not persisting [closed]

I am using oewizard from swingbench to create an order-entry like schema on oracle 11g. When I run the wizard I get the following errors: ORA-01031: Cannot grant execute on dbms_lock to soe: ...
-1
votes
1answer
793 views

Does 'grant create procedure' will also allow to do delete it?

Does grant create procedure will also allow to do delete/alter or do I need to run individual Grants for Delete Procedure and Alter procedure?
0
votes
2answers
4k views

Oracle Schema Privileges Other Users

I have a user with only CONNECT privileges and I'd like to know if RESOURCE would be enough to query objects in other schemas and create objects in my own schema based on that. i.e.: create a view in ...
1
vote
1answer
533 views

oracle schema grants and tablespaces

I have a schema with its tablespace where are application's data are located. Now I need a separate schema which will have an access only to select the data. I wish to create a set of views for that ...
10
votes
3answers
279 views

Database staging environment for non-IT personnel

I'm in the process of proposing a database staging environment to my IT department. The idea is that a non-IT person like me (public works data analyst) would have a place to test solutions, and then ...
2
votes
1answer
2k views

Oracle: Not able to create tablespace from stored procedure

I am trying to create tablespace from stored procedure. create or replace procedure testprc is query_string VARCHAR2(4000); tbs_name VARCHAR2(20):='test77'; begin dbms_output.put_line('test..'); ...
3
votes
1answer
5k views

Problem granting user privileges via roles in Oracle 12c

I'm starting to learn Oracle 12c so please excuse my bad terminology. I wrote this basic script to set up a user and privileges: CREATE USER wertyq_db_usr IDENTIFIED BY justatest ACCOUNT UNLOCK ...
2
votes
1answer
3k views

“Insufficient privileges” while shutting down as sysoper

I can't shutdown the database with a common user that is granted sysoper. There is clearly a discrepancy between this and PDBs that I am unaware of (and that the documentation also doesn't clarify), ...
-2
votes
1answer
2k views

Procedure “undeclared” despite execute being granted [closed]

I have created two identical procedures in two schemas. I have then granted execute on each procedure to a third schema. I call each procedure using the qualified object name. When calling the ...
0
votes
1answer
68 views

Privileges to select on a view - Issue

I have two schemas: A and B. A has one table with the name 'TEST'. B has created a view 'BASE' which does select on table 'TEST'. Now I create another user C to which I grant, select on the view '...
0
votes
1answer
2k views

Insufficient Privileges on selecting Views

I am using Oracle 12. Given 2 schemas, SCHEMA_01 and SCHEMA_09. I have a View_01 owns by SCHEMA_01 but it is made up of SCHEMA_01 and SCHEMA_09 tables. Then I have a User_02 that could select both ...
0
votes
1answer
9k views

How to grant execution rights on DBMS packages to a PDB user?

I'd like to grant execution right on dbms_lock package to a local PDB user. I have created new PDB with a local administrative user. I'm assuming I can use that local user for all management tasks in ...
0
votes
1answer
1k views

Oracle - User to not see data from other users

I've done some research about it but I found some answers that are not clearly saying if it is possible or not. Right now, I have a user admin. Everyone in my team is using it to create other users. ...
-1
votes
2answers
4k views

How to find out to which roles have grants on certain tables?

I have 2 tables and I want to know what object (table) grants are assigned to a role. Is there a way to find it out?
0
votes
1answer
10k views

Select catalog role - Oracle

Does select_catalog_role if granted to a schema provide access only to dba tables and v$ views only and not to other schemas created for any application? The reason i'm asking this is I need to grant ...
0
votes
3answers
28k views

Grant permissions to a user to grant select to specific tables in several schemas in Oracle

I administer a small datawarehouse in Oracle. My user dwhmanager was given the SELECT ANY TABLE privilege: GRANT SELECT ANY TABLE TO dwhmanager WITH ADMIN OPTION; The idea was to have the ability to ...
1
vote
0answers
570 views

Why can not user set current schema to SYS?

See the example bellow. User is created with dba (but not sysdba) privs. He's allows to access v$session (which is public synonym to view in schema SYS, having different name). When executing alter ...
1
vote
1answer
724 views

Why doesn't Oracle show my privileges?

I have a user that is able to drop, alter, create tables and indexes. But when I list the privileges for my Oracle user it does not show any CREATE TABLE or other such privileges. How am I able to do ...
2
votes
1answer
8k views

PL/SQL Can't grant privileges while logged as system

I can't grant the execute privilege on DBMS_LOB (I need it to operate on files), even when I'm logged as system. What can I do? Granting DBA ROLE doesn't help at all. Error starting at line 7 in ...
2
votes
1answer
2k views

permissions for yet another “ORA-06512: table or view does not exist” scenario

See the code below. I'm running the code with user x in schema y. The exact same code works if I run it by user x in schema x, but not with user x in schema y. I get the error ORA-06512: table or ...
1
vote
1answer
80 views

special setup to create views in Oracle in default schema

When creating views in Oracle, do you need special setup for a given user if the view and table in which the view is reading from are in two different schemas? The user creating the view has dba ...
2
votes
1answer
24k views

Grant select on all synonyms of schema to another schema in Oracle

I need to write a query for granting the select privilege to all synonyms and tables of one schema to another schema in Oracle. Example: Admin schema has all the tables. I want the User schema to ...
0
votes
1answer
52 views

Changing own user permissions with Oracle SQL not being the DBA

I have been making changes to a couple of schemas of different environments (they belong to the same application). Sometimes I'll want to copy data from one environment to the other, and sometimes I'...
0
votes
1answer
693 views

Cannot fix ERROR:ORA-01031: insufficient privileges

I have tried almost every solution on the internet however I still cannot fix this problem. I cannot login as sysdba every time I try SQL>connect /as sysdba I get the error ERROR:ORA-01031: ...
4
votes
4answers
69k views

grant all privileges of all tables in database to a user

I use oracle 12c. I try to grant all privileges of all tables to a user. And in future I don't need to add any privileges to that user. I need to do this. I don't want to grant system tables to that ...
1
vote
2answers
3k views

Permissions needed for dbms_metadata.get_ddl in a function

Oracle Enterprise 11.2.0.3 I'm receiving: ORA-31603: object "string of type "string" not found in schema "string" when executing a function that calls dbms_metadata.get_ddl. I know that ...
4
votes
1answer
16k views

How do I set permissions for Package Body

Here's my situation (slightly envaguened to protect the innocent): I have a package on a Test system and a package on Prod system that need to have identical permissions to access exiting packages and ...
0
votes
1answer
291 views

Oracle 11g XE - Using a stored procedure to grant privileges to a table

I need to write a stored procedure that will grant certain privileges over a table to a user. However it appears that GRANT cannot be used within a Stored Procedure. My Curent code is as follows: ...
1
vote
0answers
95 views

Allow user select from table in another user's schema that has policies set

I have an oracle10g database with 2 users - user A and user B. I want to select data from a table in user A's schema from user B. I have granted user B select privileges on the table in question but I'...
0
votes
1answer
899 views

Missing view privileges in USER_TAB_PRIVS

I can't figure out how to view the grant privileges I've given users on views -- FIRSTUSER@SQL> create view remotetabletest 2 as select * from A_TABLE where PKEY = '123456' 3 / FIRSTUSER@...
1
vote
1answer
4k views

Grant execute on specific type in Oracle

I have created a package and defined a type for collections as follows: create or replace package <schema>.<package> as ------------------------------------- type <type> is ...