I have created a user "user1" who has dba role.i wanted to create a another user "user2" with same privileges which user1 has. so i granted dba role to user2 but i dont want user2 to have "drop" privilege on any objects except his own objects.
View 1 RepliesI have 2 users in my oracle DB. They both have very different privileges and they both have too many different privileges. Now I want to grant user 1 the same privilege that user2 has while user1 keeps his existing privilege. How can this be done without manually comparing their difference and manually grant user 1 each privilege that he doesn't have? Or second option, can I override user 1's privilege with user 2's privileges?
View 2 Replies View Relatedhow can i assign permissions to one user to add,delete,edit data and other user should be able to perform all functions or selected functions
View 12 Replies View RelatedHow can we find out what are the privileges the user is having on a object.
For example I want to find what are the privileges the "uesr1" is having on "stud_table".
> conn sys as sysdba;
enter password:TIGER
>create user ram identified by ram;
>grant create session to ram;
>conn scott/tiger
>create table ram.emp2
as select * from emp;
>ora-error:- 01520 NO PRIVILEGES ON TABLESPACE 'USERS'
this is the query and i want to create emp2 table for user ram while i am connected to scott,
ealier i was able to do this but now oracle shows error 01950,
then i connected to sys again and ran this command >alter user scott quota unlimited on scott;
but it also not worked
I am trying cleaner and simple way to spool out all privileges of a user "ABC" in a way that it generates grant statement for them.
View 6 Replies View RelatedAs how do I query, as when was the last time or rather list of privileges changes done for a db user and what was the changes made?
View 1 Replies View RelatedI am a java developer and would like to install data locally in my computer for testing purpose.
I login as sysdba in sqlplus, created a user.
In the installation documentation it said :
This user should have at least the following privileges.
CREATE TABLE, CREATE VIEW, CREATE PROCEDURE, CREATE PUBLIC SYNONYM, CREATE USER, CREATE ROLE, CREATE SEQUENCE, CREATE SESSION CREATE TRIGGER, AND CREATE TYPE.
If you choose, you can grant DBA role to this user.
Note: Coeus Schema owner should have CREATE USER and ALTER USER right explicitly granted to it, not through a role like DBA.
1)How to grant all this prvileges to that user ?
2)How this user will have CREATE USER and ALTER USER right explicitly granted to it, not through a role like DBA ?
i have created a new tablespace and assigned it to new user. What are the minimum no. of grants i should give, so that the user can made any kind of changes to all the objects within the tablespace and cant access other tablespace.
View 3 Replies View RelatedIs there any way of allowing the some menu items to access by the users, not the whole menu. The problem is that in the system where I am working is that the users are on application level not on database level. If the users are on db level then by using roles and grants one can set privileges for each db user. Is there any way to block user from accessing some part of menu or from the forms itself?
View 1 Replies View RelatedWHEN I CONNECT TO SCOTT OR ANY KIND OF USER (EXCLUDE SYS)I CONNECT EASILY BUT PROBLEM WITH SCOTT OR OTHER THEY CANNOT BE
SHUTDOWN THE DATABASE AT THAT TIME THE ERROR ARISES ARE
ORA-01031: insufficient privileges SO OUR DATABASE CANT BE SHUT IT DOWN.
i have to give 74 privileges from different users on different tables to one common user,how can i do this?
View 7 Replies View Relatedgrant insert,update,delete,select on staging_tb1 to public;
What is public here? i know something about public user like it is users exist in the database .
i want to give privilege of create trigger,procedures and functions privileges to a user "A"on the schema "B". how can i do it. i've already given select,insert,update,delete privilege to user "A"
how can user "A" create trigger(etc.) on tables of user "B".
i am trying to create materialized view but it give me insufficient privilege i give it all grant and i run it from sys it give the same.
View 3 Replies View RelatedWhich Privileges required to create a trigger like after logon on database. I have grant Create any trigger privilege to user but still not able to create and give me an error insufficient Privilege.
View 5 Replies View Relatedfrom sysdba i grant to my user hospital creat any synonym but give me this error why ?
SQL> conn sys as sysdba
Enter password:
Connected.
SQL> grant create any synonym to hospital
2 ;
Grant succeeded.
SQL> conn hospital
Enter password:
Connected.
SQL> create public synonym Sur
2 for surgeries;
create public synonym Sur
*
ERROR at line 1:
ORA-01031: insufficient privileges
CREATE MATERIALIZED VIEW Matview1
NOLOGGING
NOCACHE
NOPARALLEL
REFRESH COMPLETE ON DEMAND
START WITH sysdate
NEXT sysdate + 1
WITH ROWID
ENABLE QUERY REWRITE AS
select Query;
if i run select query it works fine .. also the user has create materialized view and query rewrite privs .. not sure why i am getting insufficient privileges error still ..
create view TodaySurgeries
as select s.surgery "Surgery", p.full_name"Patient Name",e.full_name"Doctor Name",
f.floor_id"FloorID",r.room_id"RoomID",
to_char(s.surgery_date,'dd-mm-yyyy hh24:mi:ss')"Surgery Date"
from floors f,rooms r,employees e ,patients p, surgeries s
where f.floor_id=r.floor_id
and p.patient_id= s.patient_id
and r.room_id= s.room_id
and s.doctor_id= e.employee_id
I had been granted to the user grant session and resource
but the error is
create view TodaySurgeries
*
ERROR at line 1:
ORA-01031: insufficient privileges
I Created One Trigger as Follows
CREATE OR REPLACE TRIGGER TRIGGER1
BEFORE INSERT
ON table1
FOR EACH ROW
[code]......
Here , I Want To Insert The Data From My User To Test User . In This Situation When I Execute The Above Trigger It Shows The Error PL/SQL: ORA-00942: table or view does not exist
I have an Index in User1 schema. can i grant access on this Index to User2. if so, what privs i can give..?
for proc's we will give "Grant Execute on proce1 to User2". Like this how on Indexes..
Consider following scenario
Schema Update Privilege on emp directly
User1 Yes
User2 No
I have created a package named pack in user1 schema which contains one procedure which updates table emp. ow i executed the following in schema user1
Grant execute on pack to user2;
I connected as user2 and executed
Create Synonym pack for user1.pack;
Note that user2 doesn't have update privileges on table emp
Tell me whether user2 can update emp through package pack
I grant sysdba privilege to user1. After that i connected with user1. But i could not shutdown the database.
View 3 Replies View RelatedUser1 is having 10000 tables in his schema...How can i grant "select" on a all tables of a user1 to another schema(user2) so that in future when user1 will create tables , the user2 will have "select" access on those tables automatically.
I dont want user2 to have "select any table" privillege.
User2 should not have "drop" privillege on his own tables.
I need to create PROCEDURE to create user in oracle
CREATE OR REPLACE PROCEDURE "CREATE_USER_ORACLE8"
(
USER_ID in VARCHAR2,
PASSWORD in VARCHAR2,
ROLES in VARCHAR2,
nReturnCode OUT NUMBER
)
BEGIN
[code].......
Compilation errors for PROCEDURE NOG.CREATE_USER_ORACLE8
Error: PLS-00103: Encountered the symbol "BEGIN" when expecting one of the following:
; is with authid deterministic parallel_enable as
Line: 9
Text: BEGIN
i want that the customer execute PROCEDURE (user_id,password,PROCEDURE )
Suppose two users (CONNECT and RESOURCE roles) A and B who work on the same project. How can B create synonyms of all tables of A without asking admin to do that? Of course, we assume that these users have the CREATE SYNONYM privilege. My problem is that B does not have access to user_tables of A, so he cannot obtain the list of A's tables to create synonyms.
View 3 Replies View RelatedI have encountered a problem with oracle db where by i'm unable to create a user.
oracle@kapstadt4:/export/home/oracle/scripts> ls -l
total 12
-rw-rw-rw- 1 oracle dba 1115 Sep 10 15:34 crdb.sql
-rwxrwxrwx 1 oracle dba 429 Sep 10 15:39 create_advantage_user.sql
-rw-rw-rw- 1 oracle dba 597 Sep 10 15:38 create_advantage_user_objects.sql
-rw-rw-rw- 1 oracle dba 408 Sep 10 15:37 create_advantage_user_roles.sql
-rw-rw-rw- 1 oracle dba 976 Sep 10 16:04 tbs.sql
oracle@kapstadt4:/export/home/oracle/scripts> cat create_advantage_user.sql
-- creates an advantage database user
-- the role 'advantage_admin_user' must be available!
define user_name = &1
rem define default_table_space = &2
create user &user_name
identified by &user_name#123
default tablespace IN_DEV
temporary tablespace TEMP;
grant connect to &user_name;
grant resource to &user_name;
grant advantage_admin_user to &user_name;
connect &user_name/&user_name#123
@create_advantage_user_objects.sql
[Code] ........
I am using Oracle Internet Directory to store user information and OID delegation administratibe services for user entry. An an admin I created a privileges group called admins. Users belonging to this group can create user, delete users, edit users, and edit groups. I created 'User A' and assigned him to the group. Now 'User A' creates 'User B' and assignes him to the group. However when 'User B' creates 'User C' he gets the error [LDAP: error code 50 - Insufficient Access Rights].
This happens even though 'User B' belongs to the admin group which has the privilege to create users.
To make users under one schema.
1) user should have access of all objects with limited privileges.
How can i make it under schema?
1. To create a read only user who can only query the views as well as db tables and views.
2. How to create such a read-only user. 3. I have taken a full expdp backup of the production and trying to import it on the pre-production.
I use impdp
system/password table_exists_action=replace full = y dumpfile=file_name.dmp remap_schema=SYSTEM:NEW_USER remap_tablespace=SYSTEM:NEW_USER ;
This can replace and import the tables but it can't replace and add the constraints , do I have to add some other parameter in the syntax if so what it should.