How 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".
How can i list all the object privileges from n user?
for example:
I granted to USER1:
GRANT ALL on my_table1 to USER1;
GRANT ALL on my_table2 to USER1;
GRANT ALL on my_tableN to USER1;
How can i list all privileges from this user over all tables?
I don't know if this is possible, but i wanna to list:
USER1 ----- UPDATE, INSERT, DELETE ON my_table1
USER1 ----- UPDATE, INSERT, DELETE ON my_table2
USER1 ----- UPDATE, INSERT, DELETE ON my_tableN
Is this possible?
Is there any data dictionary table to get the object grants for total objects in the database?
Using DBA_TAB_PRIVS, i get object grants for tables only. But i'm looking for remaining objects also. The list of remaining objects is below.
view, or materialized view
Sequence
Procedure, function, or package
User-defined type
Synonym for any of the preceding items
Directory, library, operator, or indextype
Java source, class, or resource
provide me the other data dictionary tables for querying.
I want grant object level privileges to some user so that he can view(select) any object that resides under another user.Not select any table option.
View 5 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 Related> 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 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 Replies View Relatedi 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 .
How to know what object/tab privileged a user have ? without connecting that user and using USER_TAB_PRIVS by connecting on that user I mean on DBA table .
View 0 Replies View RelatedWhenever I create a table using login A, the table gets created successfully. But when I query the dba_objects, the object owner is different, login B. What might be the reason behind this?
View 9 Replies View Relatedi have a thumb reader device and i want know how it works such as
A.How to read data from serial port or usb port
b.how to save data into a oracle table or in a text file
c.How to match finger prints
I need to transfer PL/SQL or NESTED table from LOCAL database to REMOTE.I need to be able to use that transfered table in SELECT statement.I tried:
--ON LOCAL
SELECT SYS_OP_GUID() FROM DUAL;
--966D2DFEAEEB80D6E0430A0166CB80D6
create or replace type varchar_number_oid OID '966D2DFEAEEB80D6E0430A0166CB80D6' as object (
v varchar2(10),
i number);
/
Type created
SELECT SYS_OP_GUID() FROM DUAL;
--966D2DFEAEFB80D6E0430A0166CB80D6
[code]....
I tried using PL/SQL types it worked, but then i have a problem that i can not use local collection in SQL.
I am having a OCCI problem, I try to retrieve a collection of user-define objects.
The major steps I am using are
1. define database object
CREATE OR REPLACE TYPE my_obj_t AS OBJECT
( id number, name varchar2(10) )
CREATE OR REPLACE
TYPE my_obj_tab_t as table of my_obj_t;
2. A stored procedure return a collection as out paramerter
my_stored_procedure(obj_list out my_obj_tab_t);
3. Using OTT to generate the *.cpp and header files.
4. Register out parameter like
vector<my_obj_t> vect;
stmt->registerOutParam(1, OCCIVECTOR,
sizeof (vect),
"MY_OBJ_TAB_T");
5. stmt->execute();
6. Try to get the results by
getVector(stmt, 1, vect);
I am getting compilation error:
Could not find a match for
oracle::occi::getVector(oracle::occi::Statement*,
int, std::vector<my_obj_t, std::allocator<my_obj_t>>).
Am I doing the right things ?What steps and function should I use ?
I have a two different Databases. I created a db link in DB 1 to connect to DB 2 and it is working fine when I select data from any table. but I have one table in the DB2 which has a column with user defined data type . so when I try to select this column from DB 2 by using the DB link it gives me this error :ORA-22804 remote operations not permitted on object tables or user-defined type columns.
View 1 Replies View RelatedWhile practicing with Triggers, the following error was encountered. An Object Type and an object Table are created.
create or replace
type typPerson is object
(id number,
firstname varchar2(30),
lastname varchar2(30)
[code].........
I executed the below insert statement, and I got the following error.
SQL> insert into person_obj_tab values (10,'Object1','From Trigger');
insert into person_obj_tab values (10,'Object1','From Trigger')
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 4153
Session ID: 136 Serial number: 305
[code]......
SQL> insert into person_obj_tab values (10,'Object','Original');
1 row created.Question:
1) Trigger of version 1 did not report any error during compilation, but during DML execution, hangs for sometime and gives the above error.
2) Whether direct assignment of Objects of greater size is possible inside triggers built on object Tables?
3) Suppose an object contain multiple attributes (say more than 20), then how to assign them inside a trigger?
I have a problem with executing oracleCommand.ExecuteReader() method. Whatever I try it always returns null and it won't create OracleData reader object. I'm using ODAC 1120320_x64 for .net 4.0 and timesten112241.win64. Don't sure what to do. Debugger is showing strange thing in OracleConnection object : ConnectionState = Closed, but output of ttStatus shows connection to TimesTen data store and ExecuteNonQuery() command works just fine with or without (in or out) parameters. But when I try to execute some query with multile output such as select *, I can't get any result.
I also have a strange problem with connection.Open() When I execute Open() i throws AccessViolationException that can be handled with [Handle ProcessCorruptedStateExceptions] attribute, but connection is established after that and my application works fine until I try to instance OracleDataReader object.
Here is the code:
OracleCommand select = null;
OracleDataReader reader = null;
select = new OracleCommand(selectStmt, connection);
select.CommandType = CommandType.Text;
try
{
reader = select.ExecuteReader(); // this line throws NullReferenceException
if (reader.HasRows)
{
[code]....
Just to mention, I tried it with different queries (pl/sql, plane sql, stored procedure) and all of them works fine in SQL Developer, but not in app.
Is there any query to find the dependent object details for any object. Like if mview is built on a table, then i should be able to find the table name with out checking code of the mview. similar way for view and functions or procedures etc...
View 5 Replies View RelatedI have an Type-object typeObj1 that consists another Type-object typeObj2. this def has another Type-object typeObj3. how to access variable declared inside typeObj3. I have syntax below for each Type.
CREATE OR REPLACE TYPE typeObj1
AS OBJECT
(
SYSTEM_IDENTIFER VARCHAR2(50),
PROCESS_TYPE VARCHAR2(50),
abc typeObj2
)
/
[Code]...
/I have tried to access the type-object in where clause in following way
FROM TABLE(CAST(I_typeObj1 AS typeObj1)) ITTPRC,
......
Where
.......
AND (ADDKEY.ADDTN_INFO_KEY_TYP_NM IN (SELECT ADDTN_INFO_KEY_TYP_NM FROM TABLE(ITTPRC.abc)))
AND (ADTINF.ADDTN_RQST_TYP_VAL_DT IN (SELECT ADDTN_RQST_VAL_DT FROM TABLE( ITTPRC.def)) OR ITTPRC.def IS NULL )
AND (ADTINF.ADDTN_RQST_TYP_VAL_NUM IN (SELECT ADDTN_RQST_VAL_NUM FROM TABLE( ITTPRC.def)) OR ITTPRC.def IS NULL )
AND (ADTINF.ADDTN_RQST_TYP_VALUE IN (SELECT ADDTN_RQST_VALUE FROM TABLE( ITTPRC.def)) OR ITTPRC.def IS NULL )
In this way i am able to access the variable inside typeObj3. But problem is i am getting error "ORA-01427 single-row subquery returns more than one row" when i pass more that one typeObj2.
I passed the values like this in proc execution.
T_T_A_I_V := typeObj3('asdasd',NULL,NULL),
typeObj3('String654',NULL,NULL),
typeObj3('abcdef',NULL,NULL));
T_T_A_I_I := typeObj2('CampusCode',T_T_A_I_V),
typeObj2('PlanNumber',T_T_A_I_V);
What i have done is removed typeObj3 from typeObj2, variables defined in typeObj3 are added in typeObj2 then i got ride of above error. is it correct
There is a requirement in my database that I want to restrict the user from directly running queries on database from third party tools such as pl/sql developer and toad.
There is a utility in SQL product_user_profile through which this can be done but it is only restricted if you run the query through sql plus. If I want to restrict and (give suppose select,insert) to a user for directly running queries through PL/SQL.
I created an externally authenticated user in database. And can login without password with below syntax.
SQL> connect / @TESTDB
Connected.
SQL> show user;
USER is "SCOTT"
This scott user has a proxy permission to another DBuser PROXY_USER. Previously I used to login using below syntax.
connect scott[proxy_user]/password_for_scott@TESTDB
So now, what syntax should be used for this "ExternallY Authenticated" user to login as a proxy user?
I would like to know if 'user creation definition' is exported in user mode export if export is done with DBA role..If it is Not, does it mean we always need to precreate the user before we import the dump created using 'user mode export'?
View 2 Replies View RelatedWe are trying to implement the following security to our database.
As of now, the access rights are same for all the windows users logging into the Oracle application with the same Oracle user Id.
But now, we want to improve our security by granting different levels of rights to the users based on their Network Id even though they use the same Oracle User Id to log into the application.
We are not looking for the users to be identified externally.
(CREATE USER "OPS$ORACLE-BASE.COMTIM_HALL" IDENTIFIED EXTERNALLY;
GRANT CONNECT TO "OPS$ORACLE-BASE.COMTIM_HALL";
)
- we have user id parameter.can we update the parameter(:parameter.p_userid) before firing "WHEN NEW FORM INSTANCE TRIGGER"(when new form instance trigger contain code for tree node), for login another canvas as per user rights?
- i have created a login form in one canvas.
- also tree node Hierarchy form created on another canvas. now we want to login through login screen, after login only those forms should show in tree Hierarchy which users have rights. for this purpose we want to pass the parameter of userid before connecting to tree node form through the following query
SELECT COUNT (*)
INTO v_count
FROM usersinfo
WHERE usersname = :USERBLOCK.usernames AND passwords = :USERBLOCK.passwords ;
[code]...
Every thing is working but when we click on button in login form at that time parameter.p_userid will return null. because above code is define on button click in login screen, i cannot understand where i need to define the above code .