Tag Info

Hot answers tagged

12

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 ...


7

Perhaps you are looking for SYSTEM_PRIVILEGE_MAP?


7

From the CREATE ROLE documentation: Note that roles are defined at the database cluster level, and so are valid in all databases in the cluster. Since pg_dump dumps a single database, you can't extract roles with that utility. The pg_dumpall --roles-only command you proposed will do the work - however you may need to filter its output so that only ...


7

Yes, a role is a collection of system and/or object privileges. It simplifies privilege management by allowing you to manage bundles of privileges.


7

GRANTing ALL permissions for public to the database is mostly redundant (as public has connect, temporary by default, so you'd only be adding CREATE which you probably don't want to do). You probably expected a GRANT ALL on the database to result in a recursive GRANT ALL to contained schemas and tables. GRANT is not recursive, so this doesn't happen; a GRANT ...


7

Granting permissions on the schema (e.g. dbo) will cascade to all the objects in that schema. For individual exceptions you can just list those explicitly: GRANT SELECT ON SCHEMA::dbo TO [role]; GO GRANT INSERT, UPDATE --, DELETE ON dbo.table_they_can_write_to TO [role]; DENY SELECT ON dbo.table_they_cannot_read TO [role];


6

Apples and Oranges. Roles are roles and schemas are schemas. The fact that there is a role called db_accessadmin and also a schema called db_accessadmin does not mean a role is a schema nor that a schema is a role. Roles are security membership containers, a principal is member of a role. Schemas contain database schema bound objects and are owned by a ...


6

At this point, there's no right to grant, it's hardcoded to superuser. That's been discussed on the mailing list lately, and may change in 9.5 if someone finds the time to work on it. As a workaround, you can create a SECURITY DEFINER function that is owned by the superuser, and runs the query you want. This will allow non-superusers to see the contents of ...


6

My goal is to execute a command that requires the sysadmin role (DBCC TRACEON(1224)) You are punching a hole in your security by allowing an unprivileged user run as sysadmin role. If you are trying to set 1224 traceflag, which disables lock escalation based on the number of locks, you can do it on table level using ALTER TABLE e.g. Below enables lock ...


5

You want to manage your users, roles and user to role mappings in AD? I think Enterprise User Security is what you want, see http://docs.oracle.com/cd/E11882_01/network.112/e10744/concepts.htm#autoId10 Basically you'll need Oracle Enterprise Edition, and, after you've configured your database to use your directory (see ...


5

Try using the DDL trigger FOR DROP_TABLE: CREATE TRIGGER NO_DROP_TABLE ON DATABASE FOR DROP_TABLE AS PRINT 'Dropping tables are not allowed' ROLLBACK


5

If the same user owns both table and stored procedure (usually dbo), then permissions are not checked. This is known as "ownership chaining". So you could have a DENY on the table and it won't be checked. db_datareader makes no difference: it isn't checked. If the owners are different (and note that it's the owner of the schema that matters) then rights ...


5

It can be done but it's generally considered fairly dangerous. At a very basic level you set the trustworthy flag on the database and then when use you execute as on the sp it can take advantage of it's server level principals security access. Because of how dangerous this is I don't want to go into any detail here. However I've blogged about it here ...


4

Never a simple answer... For a direct DELETE, a user in both roles won't be able to DELETEDENY always has precedence when permissions are checked For indirect via a stored procedure, the permissions may not be checked if both table and proc have the same owner. So both GRANT and DENY will be ignored. This is called "ownership chaining" Personally, I don't ...


4

While you may have a lot of users, it would be unusual for them to require their own views. The views should be in one schema (possibly the one owning the tables) and the users should query them by either prefixing the schemaname (eg vwowner.view) or using the ALTER SESSION SET_CURRENT_SCHEMA=vwowner Roles are transient. You can do a SET ROLE NONE to ...


4

It looks like you need something like this: GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE contacts TO "user"; By the way, I hope "user" is not an actual user name you chose. Not only does it fail to convey much about the semantics of the role, but you will need to quote it everywhere, which can be something of a bother and lead to confusion when there is ...


4

The simple explanation is that the software was not designed to allow that. As Gary pointed out, since roles were designed to be able to be turned on and off the view could be valid for some sessions while it isn’t for others. However, the system could be designed to allow roles to work. What we need is a persistent role or a device similar to a role ...


4

It is not a system level role, it is assigned in each database. Assign the login "public" and "db_backupoperator" roles in each database it needs to backup. http://www.sqlbackuprestore.com/backupandrestorerights.htm


3

I just wrote about this in my answer to Granting rights on postgresql database to another user on ServerFault. Basically, the best solution when you have a single user and you want to grant other users the same rights is to turn that user into a group, create a new user with the same name as the original one that's a member of the group, and grant that ...


3

From the manual: http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_8004.htm#i2065510 The owner of the schema containing the view must have the privileges necessary to either select, insert, update, or delete rows from all the tables or views on which the view is based. The owner must be granted these privileges directly, rather ...


3

Use a database role. Database roles are database specific (obviously) so you can't create a role that grant's permissions to multiple databases at once. However within the database you create a role either through the GUI or using the command CREATE ROLE <rolename>. Once it is created you can then grant the role permissions just like you would a ...


3

If you need to prevent to drop the table by some user, try this: DENY DELETE ON OBJECT::dbo.table_to_deny TO restricted_user; http://msdn.microsoft.com/en-us/library/ms173724.aspx


3

to do it correctly is not very simple, although once setup, your done. Data users or application roles default have database scoped permissions. Database users are linked to Logins and therefore can have server wide permissions from their Login. Application roles aren't linked and can only get server wide permission by using a form of impersonation. Any ...


3

Database roles are security principals that are wholly contained within their respective database and are not shared or visible to other databases. So any roles and users that are in database X have no knowledge of database Y. To accomplish your goal, you'll need to recreate the role in database Y and add all the appropriate users to this database and ...


3

If you work with Windows Authentication, you can add your domain users to a domain group and add this group as a login to SQL Server. Then give that login the desired permissions on the relevant databases.


3

The roles db_owner and sysadmin are special cases and can't be denied anything. My guess is that db_ddladmin, db_datareader, db_datawriter and execute access will give you everything you want instead of using db_owner.


3

Superuser rights aren't in any config file, they're part of the pg_catalog.pg_authid database table, which is shared between all databases in a PostgreSQL install. You need to stop the PostgreSQL server, then restart it in single user mode, where it's always running as superuser. There, you can ALTER USER myuser SUPERUSER to grant superuser rights. Exit ...


3

This sounds like the usual problem with stored procedures: when they are compiled by developer A in his schema, the compiler only considers the privileges DIRECTLY granted to A - as if the roles were all stripped off before compiling. This is often addressed by writing your procedure as AUTHID CURRENT_USER, but if the procedure calls another procedure ...


2

The privilege to create tables is granted to new roles automatically. You need to REVOKE the role's CREATE privilege on the schema (not the database): REVOKE CREATE ON SCHEMA myschema FROM manager; Per documentation on GRANT: CREATE For databases, allows new schemas to be created within the database. For schemas, allows new objects to be ...


2

According to Microsoft (and you'd hope they'd know), as long as you've granted permissions to the SP then the underlying permissions will be sorted via permission chaining - http://msdn.microsoft.com/en-us/library/bb669058.aspx Should mean that while your users can read from any table in the DB, they'd only be able to write to them via stored procs they'd ...



Only top voted, non community-wiki answers of a minimum length are eligible