Hot answers tagged privileges
6
List all users who have been assigned a particular role
-- Change 'DBA' to the required role
select * from dba_role_privs where granted_role = 'DBA'
List all roles given to a user
-- Change 'PHIL@ to the required user
select * from dba_role_privs where grantee = 'PHIL';
List all privileges given to a user
select
lpad(' ', 2*level) || granted_role ...
6
You don't need to grant SYSDBA privileges, and shouldn't unless really necessary. You should follow the principle of least privilege. From Oracle's security guidelines:
Do not provide database users or roles more privileges than are
necessary. (If possible, grant privileges to roles, not users.) In
other words, the principle of least privilege is ...
5
While there is no privilege you can revoke, and I'd wonder why you can't just create another user, the answer to your question is....
Yes
If you have the Enterprise Edition of Oracle you can use the Virtual Private Database feature to prevent tables from being selected. Here is an overview from the Oracle Database Security Guide 11g Release 2:
Oracle ...
5
What I think is that the database you are trying to dump contains procedures/methods that were defined by a user while logged in as root@'foobar'.
Now the solution is that you have to replace the definer's for that procedures/methods
then you can generate the dump without the error.
you can do this like ..
UPDATE `mysql`.`proc` p SET definer = ...
5
Check for "CONNECT" rights
SELECT SUSER_NAME(grantee_principal_id), *
FROM sys.server_permissions WHERE type = 'COSQ'
GO
USE MYDB
GO
SELECT USER_NAME(grantee_principal_id), *
FROM sys.database_permissions WHERE type = 'CO'
To fix as needed
USE master
GO
GRANT CONNECT SQL TO myLogin
GO
USE MYDB
GO
GRANT CONNECT TO MyUser
GO
5
postgres=> \l
Liste der Datenbanken
Name | Eigentümer | Kodierung | Sortierfolge | Zeichentyp | Zugriffsprivilegien
----------------+------------+-----------+--------------+------------+-----------------------
postgres | postgres | UTF8 | de_AT.utf8 | de_AT.utf8 |
template0 | ...
5
You need to grant the REFERENCES privilege on the reference table to user2.
(See GRANT, Table Privileges section. Note that this cannot be granted to a role, must be granted to the user directly.)
Here's a demo:
SQL> create user user1 identified by user1;
User created.
SQL> grant create session, create table, unlimited tablespace to user1;
Grant ...
4
I just realized -- so long as you don't mind locking out the user while you log in --
back up the mysql.user table (well, the user's hashed password, at the very least)
set their password to something you know : UPDATE mysql.user SET password=PASSWORD('new password') WHERE user='username' AND host='hostname';
log in as them
set their password back to what ...
4
Because of deferred segment creation.
In Oracle 11.2, when you create a table with no data, you no longer allocate any space in the tablespace. Oracle doesn't actually create the segment until you try to insert data into the table. This is a difference from earlier versions in which the segment was created when the table was created rather than when data ...
4
This error says that the user doesn't have quota on tablespace SYSTEM which is set as the default persistent tablespace. You can assign a user the quota like this:
sql> alter user scott quota 50m on system;
Here, 50m means that the user quota on the SYSTEM tablespace is 50 mebibytes. You can also set the quota to unlimited.
However it is a bad ...
3
The comment by gbn brought me to the solution: No idea how it came, but root@localhost was lacking some privileges. So first obtain them all via
UPDATE mysql.user SET XXX_priv = 'Y' WHERE user = 'root' AND host = 'localhost'
There are quite a few columns, so using something like
SELECT GROUP_CONCAT(column_name) FROM information_schema.columns
WHERE ...
3
You don't need to do this through the pg_hba.conf.
Simply revoke the connect privilege on the database from that user.
However by default public is granted the connect privilege. So you need to first revoke that:
revoke connect on db1 from public;
grant connect on db1 to dbuser;
You need to run the revoke ... from public statement for all databases to ...
3
USAGE means that user doesn't have any privileges.
You have to use 'DROP USER'.
drop user cptnotsoawesome@localhost;
You can't actually revoke USAGE, without dropping the user.USAGE is a global level privilege.
have a look at This Link.
3
There is no need to grant the CONNECT role. In 10.2, Oracle finally reduced the set of privileges assigned to that role to just CREATE SESSION but in previous versions, that role has many more privileges than the name would imply.
It would be more secure to create one users-- one that owns the tables, procedures, etc. but that does not have CREATE SESSION ...
3
perhaps you mean listing users and their privileges for a database - I can't quite tell from the question:
postgres=> \du
List of roles
Role name | Attributes | Member of
-----------------+--------------+------------------------------------------------
dba | Create role | ...
3
It is possible to emulate a user as of MySQL 5.5.7, with the introduction of Proxy Users. I had never done this before, so I tried it out using the test authentication plugin, as it seems proxy users only works with authentication plugins enabled. Here are the steps I took.
First steps as root:
mysql> INSTALL PLUGIN test_plugin_server SONAME ...
3
Sequences are separate objects with separate privileges.
Granting permission on a table does not automatically extend
permissions to any sequences used by the table, including sequences
tied to SERIAL columns. Permissions on sequences must be set
separately.
Indexes belong to the table. To DROP or CREATE an index you must be the owner of the ...
3
The table_privileges view is only preserved for compatibility with ancient versions of Oracle.
You should instead be using the user_tab_privs view, all_tab_privs view, or dba_tab_privs view (documentation links for each when you click).
3
The best thing you can do to understand this behaviour better is to ask the software vendor. If you don't want to grant those permissions, lodging a bug report might be appropriate.
I'm going to hazard a guess that EMS SQL Manager is trying to be too clever, and check whether you have Alter_routine_priv or Create_routine_priv so that it can return a warning ...
3
You want to make your functions SECURITY DEFINER and have them owned by a user that does have the requisite rights.
Be very careful when coding SECURITY DEFINER functions. Don't make them owned by a superuser and read the manual carefully. Create a role that has only the rights required and no more; give it ownership of the SECURITY DEFINER functions. Where ...
3
Unfortunately, administration of Agent jobs can currently only be done by members of the sysadmin role. The relevant documentation is found on MSDN. You'll note that the three Agent roles (User, Reader, and Operator) have different levels, but ultimately a user without sysadmin can only edit/manage jobs that user owns and not jobs created by other users.
3
The documentation of GRANT says the following:
CREATE
For databases, allows new schemas to be created within the database.
For schemas, allows new objects to be created within the schema. To rename an
existing object, you must own the object and have this privilege for the containing schema.
So let's simply try this. I created a user for this ...
2
It seems that using SHOW GRANTS did work in this case:
SHOW GRANTS FOR CURRENT_USER;
Although the manual states that you still need to have access to the mysql database:
SHOW GRANTS requires the SELECT privilege for the mysql database.
And accessing mysql.user directly actually didn’t work, probably because it does also contain further sensitive ...
2
Justin Cave is correct.+1 If you would like to learn more about roles you can get a good overview from the Concepts Guide. The Security Guide has more in depth information including limitations such as roles not being enabled in Definer rights methods.
2
ALL_TAB_PRIVS is a superset of ALL_TAB_PRIVS_RECD. You probably want ALL_TAB_PRIVS as it includes tables that the current user owns.
2
Under the hood, when you see a user with USAGE only, that the user is written in the mysql.user table with all global privileges turned off.
You originally stated the the user had this:
grant usage on statistics.* to cptnotsoawesome@localhost identified by 'password';
You should see a row in mysql.user with the MD5 password and all globals privs set to ...
2
The SYSMAN account is for adminstering Enterprise Manager, rather then the database; see the predefined user accounts. I would shy away from modifying anything about any of those accounts, even granting an additional role, unless specifically told to by Oracle.
The first message you got says that import isn't supported when logged in with the SYSDBA role. ...
2
To grant privilege to create a view:
GRANT CREATE VIEW TO STD01;
To grant the DML privileges:
GRANT SELECT,UPDATE,INSERT,DELETE ON STD00.CUSTOMER TO STD01;
But that's not all of the object privileges. If you did:
GRANT ALL ON STD00.CUSTOMER TO STD01;
you would also give other privileges such as ALTER, INDEX, FLASHBACK, etc.
2
The documentation is misleading. I read it exactly the same way you do, which isn't what the utility does.
Adding --flush-privileges causes mysqldump to include the following in the backup file, after dumping the mysql schema...
--
-- Flush Grant Tables
--
/*! FLUSH PRIVILEGES */;
...which of course causes the server where the dump is being restored to ...
Only top voted, non community-wiki answers of a minimum length are eligible
